CODEWITHSUNDEEP
arrayspostgresqlstored-proceduresparameter-passingplpgsql
Nitseg
Nitseg

Reputation: 1277

Stored procedure syntax with IN condition

(1)

=>CREATE TABLE T1(id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE

(2)

=>INSERT INTO T1
(name) VALUES
('Robert'),
('Simone');
INSERT 0 2

(3)

SELECT * FROM T1;
 id |  name  
----+--------
  1 | Robert
  2 | Simone
(2 rows)

(4)

CREATE OR REPLACE FUNCTION test_me(id_list BIGINT[]) 
RETURNS BOOLEAN AS
$$
BEGIN
  PERFORM * FROM T1 WHERE id IN ($1);
  IF FOUND THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$
  LANGUAGE 'plpgsql';
CREATE FUNCTION

My problem is when calling the procedure. I'm not able to find an example on the net showing how to pass a list of values of type BIGINT (or integer, whatsoever).

I tried what follows without success (syntax errors):

First syntax:

eway=> SELECT * FROM test_me('{1,2}'::BIGINT[]);
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

Second syntax:

eway=> SELECT * FROM test_me('{1,2}');
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

Third syntax:

eway=> SELECT * FROM test_me(ARRAY [1,2]);
ERROR:  operator does not exist: bigint = bigint[]
LINE 1: SELECT * FROM T1 WHERE id IN ($1)
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT * FROM T1 WHERE id IN ($1)
CONTEXT:  PL/pgSQL function test_me(bigint[]) line 3 at PERFORM

Any clues about a working syntax?

It's like the parser was trying to translate a BIGINT to BIGINT[] in the PEFORM REQUEST but it doesn't make any sense to me...

Upvotes: 3

Views: 2303

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

All your syntax variants to pass an array are correct.

The problem is with the expression inside the function. You can test with the ANY construct like @Mureinik provided or a number of other syntax variants. In any case run the test with an EXISTS expression:

CREATE OR REPLACE FUNCTION test_me(id_list bigint[]) 
  RETURNS bool AS
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM t1 WHERE id = ANY ($1)) THEN
      RETURN true;
   ELSE
      RETURN false;
   END IF;
END
$func$ LANGUAGE plpgsql STABLE;

Notes

Simple variant

While you are returning a boolean value, it can be even simpler. It's probably just for the demo, but as a proof of concept:

CREATE OR REPLACE FUNCTION test_me(id_list bigint[]) 
  RETURNS bool AS
$func$
SELECT EXISTS (SELECT 1 FROM t1 WHERE id = ANY ($1))
$func$ LANGUAGE sql STABLE;

Same result.

Upvotes: 4

Mureinik
Mureinik

Reputation: 311228

The easiest way to check if an item is in an array is with = ANY:

CREATE OR REPLACE FUNCTION test_me(id_list BIGINT[]) 
RETURNS BOOLEAN AS
$$
BEGIN
  PERFORM * FROM T1 WHERE id = ANY ($1);
  IF FOUND THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$
LANGUAGE 'plpgsql';

Upvotes: 2

Related Questions