On Wed, Oct 13, 2010 at 5:35 AM, Pavel Stehule <pavel.stehule@gmail.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