Re: Cursor Example Needed

On Mon, Oct 28, 2013 at 5:49 PM, Perry Smith <pedzsan@gmail.com> 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:
>
>> 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.

pl/pgsql is a language for creating functions.   From within a
function, you can interact with the database directly or with the
calling query through returning data.  You can't really send data to
the client side (unless you count 'COPY' or RAISE NOTICE').  So
although pl/pgsql FETCH has similar mechanics to SQL fetch, they are
very different in that pl/pgsql FETCH is generally directed at
variables.

pl/pgsql FETCH is somewhat baroque; typically it's cleaner and easier
to just iterate with a vanilla FOR-IN loop.  The only reasons why I
use cursors inside functions any more that I can think of off the top
of my head are when you need fancy scrolling (say to cycle through a
result set multiple times) or to pass data around between different
functions (but that is mostly displaced by temp tables for large sets
and or record arrays for very small ones)

merlin