Re: Passing refcursors between pl/pgsql functions

On Wed, Oct 13, 2010 at 5:35 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>
>> What I would like is something like the following, assuming it's possible:
>>
>> CREATE OR REPLACE FUNCTION fetch_from_refcursor(ref refcursor) RETURNS
>> SETOF test_table AS $$
>> BEGIN
>>  RETURN FETCH 1 FROM ref; -- Does not work, but can it?
>> END $$ language plpgsql;
>>
>> Is it possible to do such a thing?  I have a feeling that it isn't, but
>> I'd love to be proven wrong.
>
> Hello, there isn't any available statement for transformation from
> cursor to table. You have to iterate over FETCH statement and to use a
> RETURN NEXT statement.

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.

merlin