Re: Array Parameters in EXECUTE

Hello

8.4 suport USING clause - so there is possible use array variable directly.

regards
Pavel Stehule

2009/6/11 Shakil Shaikh <sshaikh@hotmail.com>:
> Hi,
>
> Here's my general situation: I have a function which takes in an optional
> ARRAY of Ids as so:
>
> RETURN QUERY SELECT * FROM a WHERE a.id = ANY(v_ids) or v_ids is null;
>
> However it seems that the ...or v_ids is null... bit forces a sequential
> scan on a. Reading this list, it seems the best way to get over this is to
> dynamically prepare a statement, perhaps something like this:
>
> DECLARE
> v_base text;
> v_where text := '';
> v_rec record;
> BEGIN
> v_base := 'SELECT * FROM a';
>
> IF (v_ids IS NOT NULL) then
>  v_where := ' WHERE a.id = ANY(v_ids)';
> END IF;
>
> FOR v_rec IN EXECUTE v_base || v_where LOOP
>  RETURN NEXT v_rec;
> END LOOP;
>
> I picked up the looping returning trick next elsewhere in this list, and
> presume that's the only way to handle dynamically returning a SET OF.
> However the problem I'm having is with substituting in the ARRAY parameter
> in to the WHERE string. Obviously the above doesn't quite work since the
> named parameter v_ids isn't valid in the statement. I probably need some
> kind of array_tostring function to write out the array explicitly, but I was
> wondering if there was any other way to do this since the excessive
> parameter processing could potentially defeat the purpose of using arrays in
> the first place!
>
> Thanks
>
> Shak
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>