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(dot)klaver(at)gmail(dot)com> wrote:
>
>> On 10/28/2013 02:27 PM, Perry Smith wrote:
>>>
>>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>>
>>>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan(at)gmail(dot)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(dot)klaver(at)gmail(dot)com