Re: plpgsql question - Mailing list pgsql-general

From Matthew Peter
Subject Re: plpgsql question
Date
Msg-id 20060107053729.93541.qmail@web35214.mail.mud.yahoo.com
Whole thread Raw
In response to Re: plpgsql question  (Michael Fuhr <mike@fuhr.org>)
List pgsql-general


Michael Fuhr <mike@fuhr.org> wrote:
On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:
> Michael Fuhr wrote:
> > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:
> > > Is it possible to skip the loop and just return all records in a
> > > single query and shove all those rows into a table variable?
> >
> > Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
> > generally from within a loop. Why do you want to avoid that?
>
> I was thinking it would be more efficient to pull all the records in
> one call rather than 50 calls. For all I know it probably executes 50
> calls in the internals when translating the IN (IDs).

I wouldn't worry about that unless you can demonstrate that it's
causing a performance problem. Even then you're stuck because
that's how set-returning functions work.

> > * You could use an IF statement to execute the query you need.
>
> That's what I was trying to do, but I'm not sure i was doing it in
> the right context, since it was IN the query, not testing after it.
> Figured I'd ask the list if I was trying something impossible or if
> I was close to help get me on track.

The IF statement needs to be part of the PL/pgSQL logic, not part
of the query string. However, you might be able to use CASE or
COALESCE in the query, as in

WHERE my_tbl_id = $1
AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END

or

WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)

or

WHERE my_tbl_id = $1 AND COALESCE($2, username) = username

With predicates such as these you wouldn't need to use EXECUTE and< br>you could write the query only once.

--
Michael Fuhr



I'll try that out tomorrow. Thanks Micheal


Yahoo! DSL Something to write home about. Just $16.99/mo. or less

pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: Strange behavior
Next
From: "Qingqing Zhou"
Date:
Subject: Re: contrib library in windows