Curtis Scheer wrote:
Does anyone have any examples of how I would make a stored procedure in
plpgsql that would allow for passing a list or arrays of values to be used
in an sql IN clause? Like so: select * from table where field1 in (values).
Is this possible?
Well, a good thing to note here is that there is a very distinct
semantic difference between an array in postgres and what IN clauses
take as input: and array
is a data type whereas IN clauses take a parenthesized list of comma
separated values. So, if you pass an array into a function wherein
you then need
to use those values in an IN clause, you can build yourself an string of
the values in the array, comma separated of course.
e.g.
CREATE OR REPLACE FUNCTION somefunc(ids int[]) RETURNS boolean AS $sf$
DECLARE
in_values varchar;
good int;
BEGIN
FOR i IN array_upper(ids, 1) LOOP
in_values := in_values || ids[i] || ',';
END LOOP;
in_values := substring(in_values FROM 1 FOR
character_length(in_values) - 1); -- this will chop off the last comma
EXECUTE 'SELECT 1 FROM blah WHERE blah.id IN (' || in_values ||');'
INTO good;
IF(good = 1) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$sf$ LANGUAGE plpgsql;
Or, it may be easier given whatever your situation to simply use the
array as the argument to a row-wise AND or SOME expression.
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
