Kind people,

I've figured out something that many others probably have, but I
thought I'd put it out there anyhow.

As PL/PgSQL doesn't allow assignment of singleton SELECTs and dynamic
queries to variables, as mentioned in the docs.

Here's a working hack that gets around this.

DECLARE foo_rec RECORD;
BEGIN
    FOR foo_rec IN EXECUTE -- put together a singleton sql function here.
    LOOP
    END LOOP;
    -- do stuff with foo_rec.bar, foo_rec.baz, &c.
END;

Should this hack turn into a TODO item?  If so, what problems should
get addressed?  Features added?  Is PL/PgSQL in such shape as it needs
a big overhaul?

Anyway, thanks for taking the time to read this.

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Tue, 21 Sep 2004 20:28:05 -0700
David Fetter <david@fetter.org>
Variable assignment from dynamic SQL in PL/PgSQL
On Tue, 2004-09-21 at 23:28, David Fetter wrote:
> Kind people,
> 
> I've figured out something that many others probably have, but I
> thought I'd put it out there anyhow.
> 
> As PL/PgSQL doesn't allow assignment of singleton SELECTs and dynamic
> queries to variables, as mentioned in the docs.
> 
> Here's a working hack that gets around this.
> 
> DECLARE foo_rec RECORD;
> BEGIN
>     FOR foo_rec IN EXECUTE -- put together a singleton sql function here.
>     LOOP
>     END LOOP;
>     -- do stuff with foo_rec.bar, foo_rec.baz, &c.
> END;
> 
> Should this hack turn into a TODO item?  If so, what problems should
> get addressed?  Features added?  Is PL/PgSQL in such shape as it needs
> a big overhaul?
> 

I believe this to be addressed already in 8.0, though I haven't had time
to confirm this... maybe you want to try it out on the beta release.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


22 Sep 2004 13:19:09 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: Variable assignment from dynamic SQL in PL/PgSQL