Thread: Array Parameters in EXECUTE

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

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
>

Re added list!

--------------------------------------------------
Sent: Thursday, June 11, 2009 11:04 AM
To: "Shakil Shaikh" <>
Subject: Re: [GENERAL] Array Parameters in EXECUTE

>> 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
>
>
> If I understand what you are trying to do, if your passed in parameter
> is null then select * and if there is something there then check it.
> I would put the statement in an if:
>
> if v_ids is null then
> RETURN QUERY select * from a;
> else
> return query SELECT * FROM a WHERE a.id = ANY(v_ids)
> end if;
>

This works fine for the trivial case given but not for when there are
multiple optional array parameters (I know, I know...). I will probably use
branching for one or two parameters, but for three or four it might get a
bit cumbersome so I was wondering if there was a more general solution.

Shak

> 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;
>


If I understand what you are trying to do, if your passed in parameter
is null then select * and if there is something there then check it.
I would put the statement in an if:


if v_ids is null then
    RETURN QUERY select * from a;
else
    return query SELECT * FROM a WHERE a.id = ANY(v_ids)
end if;


It's definitely better then the loop.


Sim

You get what you ask for :-)
You can use the function array-to-string with a comma as the separator.
The array should look like '{val1,val2,val3}' in text format.

Sim

Shakil Shaikh wrote:
> Re added list!
>
> --------------------------------------------------
> Sent: Thursday, June 11, 2009 11:04 AM
> To: "Shakil Shaikh" <>
> Subject: Re: [GENERAL] Array Parameters in EXECUTE
>
>>> 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
>>
>>
>> If I understand what you are trying to do, if your passed in parameter
>> is null then select * and if there is something there then check it.
>> I would put the statement in an if:
>>
>> if v_ids is null then
>> RETURN QUERY select * from a;
>> else
>> return query SELECT * FROM a WHERE a.id = ANY(v_ids)
>> end if;
>>
>
> This works fine for the trivial case given but not for when there are
> multiple optional array parameters (I know, I know...). I will probably
> use branching for one or two parameters, but for three or four it might
> get a bit cumbersome so I was wondering if there was a more general
> solution.
>
> Shak
>