Re: Passing refcursors between pl/pgsql functions

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