Re: Array Parameters in EXECUTE

From: "Shakil Shaikh" <sshaikh(at)hotmail(dot)com>
To: "Sim Zacks" <sim(at)compulab(dot)co(dot)il>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Array Parameters in EXECUTE
Date: 2009-06-11 10:07:59
Message-ID: BAY117-DS8F526168D5307C78E4796AC420@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2009-06-11 10:24:41 Re: Different handling of PL/pgSQL for-loop variables in 8.3.7 vs. 8.2.7 ???
Previous Message Alban Hertroys 2009-06-11 10:06:43 Re: When to use cascading deletes?