Re: Cursor Example Needed

On 10/28/2013 03:49 PM, Perry Smith wrote:
>
> On Oct 28, 2013, at 5:21 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> On 10/28/2013 02:27 PM, Perry Smith wrote:
>>>
>>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>
>>>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote:
>>>>> When I execute the SELECT statement directly I get:
>>>>>
>>>>> psql:table.sql:28: out of memory for query result
>>>>
>>>> psql will do this automatically if you tell it to:
>>>> http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html
>>>>
>>>> cursors can work with your code, but they need participation from the
>>>> client side.  basically you stage the cursor then repeatedly FETCH
>>>> until done.
>>>
>>> Ok.  I'll try that.  I'm still curious how to do it using cursors if anyone wants
>>> to provide a sample.
>>
>> http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html
>
> Yea, I looked at those.  Part of my original email got lost.  Here is from the original
> email:

Well the point of the example at the above link is that you do not have
to do it in a function:)

>
>> CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$
>>
>> DECLARE
>>   xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack =
'7100-01-06';
>>
>> BEGIN
>>   FOR my_rec IN xyz LOOP
>>     -- xyxyxy what to do where? xyxyxy  These things don't work:
>>     COPY ( my_rec ) TO stdout;
>>     SELECT * FROM my_rec;
>>     etc.
>>   END LOOP;
>>
>>   RETURN 5;
>> END
>> $$ LANGUAGE plpgsql;
>>
>> SELECT * FROM blah();
>
> Perhaps my question is what do I do with my_rec once I have it?  I want
> output the same as if I did the top SELECT statement directly.

The docs do a good job of illustrating:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html

>


--
Adrian Klaver
adrian.klaver@gmail.com