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

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)

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

StackExchange Database Administrators Q#61520, answer score: 6