Re: plpgsql question - Mailing list pgsql-general

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