Skip site navigation (1) Skip section navigation (2)

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

From: David Schmitt <david(at)dasz(dot)at>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5549: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...'
Date: 2010-07-12 08:10:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
On 7/9/2010 4:53 PM, Tom Lane wrote:
> "David Schmitt"<david(at)dasz(dot)at>  writes:
>> This is not implemented:
>>      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

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


-- OG              Tel: +43 (0)664 2602670     Web:
Klosterneuburg                                         UID: ATU64260999

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

In response to

pgsql-bugs by date

Next:From: jose soaresDate: 2010-07-12 10:23:09
Subject: BUG #5554: PostgreSQL 8.4.0 doesn't update my table
Previous:From: SergeyDate: 2010-07-12 07:47:07
Subject: BUG #5553: Repeating the keyword DECLARE in the CREATE FUNCTION statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group