Stored procedure syntax with IN condition

Question

(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...

score 4 · Accepted Answer · edited May 23 '17 at 12:02

4

All your syntax variants to pass an array are correct.

Pass array literal to PostgreSQL function

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

EXISTS is shortest and most efficient:
- PL/pgSQL checking if a row exists - SELECT INTO boolean
The ANY construct applied to arrays is only efficient with small arrays. For longer arrays, other syntax variants are faster. Like:
```
IF EXISTS (SELECT 1 FROM unnest($1) id JOIN t1 USING (id)) THEN ...
```
- How to do WHERE x IN (val1, val2,…) in plpgsql
Don't quote the language name, it's an identifier, not a string: LANGUAGE plpgsql

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.

edited May 23 '17 at 12:02

Community

1
1

answered May 20 '15 at 14:43

Erwin Brandstetter

605,456
145
1,078
1,228

Even though your solution is very good I'm wondering if I couldn't pass a list of scalar expressions to the stored procedure and hence use them directly with the "IN" option. Here (http://www.postgresql.org/docs/9.1/static/extend-type-system.html#AEN49791) they seem to say that a scalar is somehow converted to a kind of array "For each scalar type, a corresponding array type is automatically created". So I guess there is a way to pass from scalars to ARRAY and vice versa. – Nitseg May 21 '15 at 07:58
Or even pass directly a scalar list to the procedure somehow. – Nitseg May 21 '15 at 08:28
@Nitseg: You can use a `VARIADIC` parameter (as the last one) to pass an array as dynamic list of scalar values. That's sometimes handy for the call, but it's still a plain array inside the function: http://stackoverflow.com/questions/19202832/pass-multiple-values-in-single-parameter/19204560#19204560. – Erwin Brandstetter May 21 '15 at 11:40
@Nitseg: You can also pass any number of scalar parameters and use them in an `IN` expression by listing them separately, but that's very unwieldy (or impossible) for a dynamic number of elements. Only an option if you know the max. number of elements at the time you write the function. Also, [`IN (row of values)`](http://www.postgresql.org/docs/current/interactive/functions-comparisons.html#AEN19674) performs equally bad for long lists and is tricky with null handling, so there is really no good reason to force it. – Erwin Brandstetter May 21 '15 at 11:43
OK, thanks a lot for answering my subsidiary questions. Scalars are to be used with static number of expressions, that makes sense. – Nitseg May 21 '15 at 12:11

score 2 · Answer 2 · answered May 20 '15 at 14:15

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';

Stored procedure syntax with IN condition

2 Answers2

Notes

Simple variant