How to do WHERE x IN (val1, val2,...) in plpgsql

Question

I would like to update a selection of rows in a table; this works...

UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5);

But how can I do the same in a plpgsql function? The following gives a syntax error...

CREATE OR REPLACE FUNCTION foo(intarray int[])
RETURNS VOID AS 
$BODY$

BEGIN
  UPDATE t1 SET col1 = 'newvalue'
  WHERE col0 in intarray;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

The error...

ERROR:  syntax error at or near "intarray"
LINE 7:       WHERE col0 in intarray;

Edit:

If we replace in intarray; with in (intarray); the function is recorded but when running SELECT * FROM foo(ARRAY[1,3,5]) the error becomes...

ERROR:  operator does not exist: integer = integer[]
LINE 2:       WHERE col0 in (intarray)

intarray is the variable name of the array passed in the function. — Dave, Mar 22 '14 at 14:17

score 5 · Answer 1 · answered Mar 23 '14 at 11:18

IN (...) expects a literal list of values.

If you write x IN (some_array) then PostgreSQL expects x to also be an array, since you're testing to see whether the array x appears in the one-element list of arrays (some_array).

If you instead want to see if the value x appears as an element of the array some_array you must write:

x = ANY (some_array)

(Note that you can use ANY with pretty much any operator, not just =, and that there's an ALL as well as an ANY.)

score 5 · Accepted Answer · edited Apr 13 '17 at 12:42

@a_horse already pointed out: to use x = ANY (array_value) instead of x IN (set of values). Read the manual about ANY.

With long arrays (100+ elements, it depends), it is faster to unnest() and join. And it may be more convenient to use a VARIADIC function:

CREATE OR REPLACE FUNCTION foo(VARIADIC intarray int[])
  RETURNS void AS 
$func$
BEGIN
  UPDATE t1
  SET    col1 = 'newvalue'
  FROM   unnest (intarray) a(elem)
  WHERE  t1.col0 = a.elem;
END
$func$ LANGUAGE plpgsql;

Then you can call the function as:

SELECT foo(1,2,3);

But you can still hand in a whole array, too:

SELECT foo(VARIADIC intarray := '{1,2,3}');

Details in the manual here and here.

How to do WHERE x IN (val1, val2,...) in plpgsql

2 Answers2

Linked