Re: Cursor Example Needed

On 10/28/2013 04:36 PM, Perry Smith wrote:
>
> On Oct 28, 2013, at 6:13 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>
>> On 10/28/2013 3:58 PM, Adrian Klaver wrote:
>>> The docs do a good job of illustrating:
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html
>>
>> thats for cursors created within a plpgsql function.
>>
>> I think what the OP wants is a top level cursor, which is a different thing...
>>
>> see
>> http://www.postgresql.org/docs/current/static/sql-declare.html
>> http://www.postgresql.org/docs/current/static/sql-fetch.html
>> http://www.postgresql.org/docs/current/static/sql-close.html
>>
>> the fetch page shows an example of the complete usage in the context of a database transaction.
>
> Thank you to Merlin. I now understand better where my confusion was.
>
> John:
>
> Those examples are great except there is no way that I know of to loop on
> the "top level" as you call it. I'm trying to do something that I can give to
> psql which will loop through the entire set that is produced.

The FETCH example shows you how. You do not have FOR but you do have
FORWARD and if you DECLARE SCROLL, BACKWARD. If you need to do actions
on each row as it is fetched then you will probably need to do it in a
function. Your original post though was concerned with dealing with an
out of memory error caused by returning to large a result set at one
time and that can be handled in psql as illustrated.

>
> I came across the FOR-IN loop but that needs a function. But as Merlin
> points out, the function is on the server side and I need the loop on the
> client side.
>
> Perhaps the
>
> \set FETCH_COUNT 500000
>
> solution that Merln point out originally is the only choice.
>
> I feel like I've learned a lot even though it might not have been what
> I was originally trying to learn :-)
>
> Thank you again,
> Perry
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com