Re: BUG #5549: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...'

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]. It is not mentioned in "Looping Through a Cursor's
Result"[2] 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



[1]http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

[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

        FB-Nr.: FN 309285 g          FB-Gericht: LG Korneuburg