BUG #5549: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...'
The following bug has been logged online:
Bug reference: 5549
Logged by: David Schmitt
Email address: david@dasz.at
PostgreSQL version: 8 and 9
Operating system: n/a
Description: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE
'...'
Details:
This is not implemented:
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION foo(tbl text) RETURNS void AS
$BODY$
DECLARE
working_cursor NO SCROLL CURSOR FOR EXECUTE $$SELECT something $$ || tbl
|| $$ ... $$;
BEGIN
FOR rec IN working_cursor LOOP
-- ...
END LOOP;
END$BODY$
LANGUAGE 'plpgsql' VOLATILE;
---------------------------------------------------------------------
The obvious workaround is hand-coding the LOOP with OPEN ... FOR EXECUTE and
manual FETCHing, but it would be great to have this missing piece supported
in syntax and avoid duplicating the error-prone hand-coding.
Thanks for your time and work!
"David Schmitt" <david@dasz.at> writes:
This is not implemented:
DECLARE
working_cursor NO SCROLL CURSOR FOR EXECUTE $$SELECT something $$ || tbl
|| $$ ... $$;
What's wrong with OPEN FOR EXECUTE?
The proposed addition seems a bit weird anyway since it presumes
nontrivial calculation to be done during variable initialization.
regards, tom lane
On 7/9/2010 4:53 PM, Tom Lane wrote:
"David Schmitt"<david@dasz.at> writes:
This is not implemented:
DECLARE
working_cursor NO SCROLL CURSOR FOR EXECUTE $$SELECT something $$ || tbl
|| $$ ... $$;What's wrong with OPEN FOR EXECUTE?
The proposed addition seems a bit weird anyway since it presumes
nontrivial calculation to be done during variable initialization.
I've investigated further and found that the correct formulation is:
FOR rec IN EXECUTE $$SELECT something $$ || tbl || $$ ... $$ LOOP
END LOOP
If I understand the documentation correctly this does exactly what I
need: open and close a cursor automatically with the dynamic statement
and loop over its result set. Actually, this is even more consise and to
the point that what I had in mind first.
This syntax is documented in the lower half of "Looping Through Query
Results"[1]http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING. It is not mentioned in "Looping Through a Cursor's
Result"[2]http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999 where I would have (naively) expected it.
My confusion seems to arise from the fact that FOR loops are described
first without mentioning cursors at all and then a "different" FOR is
introduced specifically for use with cursors.
Thanks for your time and work, David Schmitt
[2]: http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999
--
dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at
Klosterneuburg UID: ATU64260999
FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg