Re: How to pass array of values to a pgplsql function

Erik Jones wrote:
> 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.
>
Whoa, replied to this out of the General mailing list before I saw the
other answers on the SQL list... Sorry guys

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)