Re: How return a refcusor using functions????

frank@chagford.com (Frank Millman) writes:
> create or replace function v_ArMaster() returns refcursor as '
>   declare
>     curs1 refcursor;
>   begin
>     open curs1 for select AccNo, Name, Contact, Phone from ArMaster
> order by AccNo;
>     return curs1;
>   end;'
> language 'plpgsql';

> begin;
> select v_ArMaster();
> fetch all from curs1;
> end;

> ... fails with the following error -

> NOTICE: PerformPortalFetch: portal "curs1" not found.

The actual name of the cursor is not "curs1" in this case, but some
internally assigned name.  You would have to pay attention to the string
returned by v_ArMaster() to know what to FETCH from.

I believe you can work around this by assigning a value to the refcursor
before you OPEN:

    declare curs1 refcursor := ''curs1'';
    begin;
    open curs1 for ...

This forces "curs1" to be the internal cursor name --- meaning you get an
error if that name is already in use for a cursor.  You pays your money
and takes your choice whether you'd rather have an autogenerated unique
name or a predictable name.

There is an example discussing this at the bottom of the plpgsql manual
section that covers cursors, but the implications are perhaps not very
obvious.

            regards, tom lane