Re: Passing refcursors between pl/pgsql functions

On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner <reuven(at)lerner(dot)co(dot)il> wrote:
>
>
> Hi, Merlin.  You wrote:
>
>> In other words, something like this:
>>
>> create or replace function test() returns setof foo as
>> $$
>> declare
>>   r refcursor;
>>   f foo;
>>   i int;
>> begin
>>   open r for select * from foo;
>>
>>   for i in 1..10
>>   loop
>>     fetch 1 from r into f;
>>     exit when not found;
>>     return next f;
>>   end loop;
>> end;
>> $$ language plpgsql;
>>
>> Having defined refcursor separately from the place it is being used
>> really had no bearing on the peculiarities of the 'fetch' statement.
>
> This isn't quite what I was looking for; perhaps I didn't make myself clear.
>
> I want to invoke one function, and get an open refcursor returned.  That
> much, I know how to do.
>
> I then want to be able to call a second function, repeatedly, which will
> essentially perform a "fetch 20" from that open refcursor.  The second
> function should have an input of a refcursor (already open), and should
> return a set of rows from the table on which it was opened.
>
> This isn't the way that I would want to do things, but my client's
> application structure seems to require it, at least for now.  So, is there a
> way to do this?

yes: if you review the example above, the key snippet is:
for i in 1..10
loop
fetch 1 from r into f;
exit when not found;
return next f;
end loop;
Which would make the body of your consumer function. I understand
that you need to do it in separate functions -- that part is easy and
covered via the documentation on cursors. The problem is you can't
direct the ouput of 'fetch n' into the return of a function or some
other variable, except in the special case of 'fetch 1' where we can
use a record variable. So we have to simulate 'fetch 10/20 etc' with
a loop. You can split the function above into two separate functions
and you should have what you want.

A hypothetical improvement to postgresql that would make life
easier/faster would be to allow fetch to be used in a CTE:

with rows as (fetch 20 from r) ...

So you could point it at 'return next', record array, temp table, etc.

merlin