Re: plpgsql question

From: Matthew Peter <survivedsushi(at)yahoo(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: plpgsql question
Date: 2006-01-06 09:14:38
Message-ID: 20060106091438.69917.qmail@web35201.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Michael Fuhr <mike(at)fuhr(dot)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).
* 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.
* You could put the queries in separate functions.
The query is so similiar (occasionally match on extra WHERE arg) it would be nice just to use a conditional to match if that extra argument is given as not null...rather than maintain two simliar functions if possible, while keeping it planned after the first run.

Does using an IF predicate in the WHERE in the SQL call require EXECUTE since (I guess) I'm making the SQL statement somewhat dynamic? All I've been able to find is IF handling after the query, not in it.

Thanks again

Matt


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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mordicus 2006-01-06 09:25:53 Problems building pg 8.1.1
Previous Message Bjørn T Johansen 2006-01-06 09:09:05 Re: Hardware recommendation for PostgreSQL on Windows?