Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Add your error message? –  Mihai Mar 22 at 13:01
    
error message added in question; thanks –  Dave Mar 22 at 13:18
    
Shoudnt that be in_array instead of intarray? –  Mihai Mar 22 at 14:02
    
intarray is the variable name of the array passed in the function. –  Dave Mar 22 at 14:17
3  
WHERE col0 = ANY (intarray) –  a_horse_with_no_name Mar 22 at 14:46

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.