Variable assignment from dynamic SQL in PL/PgSQL
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!
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