Re: plpgsql question

On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:
> Michael Fuhr <mike@fuhr.org> 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
you could write the query only once.

--
Michael Fuhr