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
