On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner <reuven@lerner.co.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