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@myemma.com>
software development
emma(r)