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

spraveen2001@yahoo.com (Praveen) wrote in message news:<98d8ec76.0305020423.951c12f@posting.google.com>...
> Hi All,
>
> How return a ref cursor using function in postgress db. Please can anyone
> send me sample function? It is very very urgent.
>
> Praveen

Hi Praveen

I know part of the answer, but I was about to post my own question in
this regard, so I hope someone else reads this and can answer my
question as well.

Here are two alternative methods of defining a function to return a
refcursor -

1)
create or replace function v_ArMaster() returns refcursor as '
  declare
    curs1 cursor for select AccNo, Name, Contact, Phone from ArMaster
order by AccNo;
  begin
    open curs1;
    return curs1;
  end;'
language 'plpgsql';

2)
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';

According to my reading of the documentation, they should be
equivalent. I tested them as follows -

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

Version 1 works correctly. Version 2 fails with the following error -

NOTICE: PerformPortalFetch: portal "curs1" not found.

Please could someone advise on the correct syntax for version 2. I
need this because I want to add some if...then...else statements to
vary the building of the cursor, and you cannot do this inside the
"declare" section of the function.

Thanks in advance

Frank Millman

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

Hi fellows,

 I faced the same problem before... It's hard to come up with this
solution, took me a while to read the documentation and browsing
google... But I managed...

 Try this:

 create or replace function v_ArMaster(refcursor) returns refcursor as '
   declare
    p_cursor AS ALIAS FOR $1;
   begin
     open curs1 for select AccNo, Name, Contact, Phone from ArMaster
 order by AccNo;
     return p_cursor;
   end;'
 language 'plpgsql';

 begin;
  select v_ArMaster('cursor1');
  fetch all from cursor1;
 end;


On 9 May 2003 03:24:13 -0700
frank@chagford.com (Frank Millman) wrote:

> spraveen2001@yahoo.com (Praveen) wrote in message
> news:<98d8ec76.0305020423.951c12f@posting.google.com>...
> > Hi All,
> >
> > How return a ref cursor using function in postgress db. Please can
> > anyone send me sample function? It is very very urgent.
> >
> > Praveen
>
> Hi Praveen
>
> I know part of the answer, but I was about to post my own question in
> this regard, so I hope someone else reads this and can answer my
> question as well.
>
> Here are two alternative methods of defining a function to return a
> refcursor -
>
> 1)
> create or replace function v_ArMaster() returns refcursor as '
>   declare
>     curs1 cursor for select AccNo, Name, Contact, Phone from ArMaster
> order by AccNo;
>   begin
>     open curs1;
>     return curs1;
>   end;'
> language 'plpgsql';
>
> 2)
> 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';
>
> According to my reading of the documentation, they should be
> equivalent. I tested them as follows -
>
> begin;
> select v_ArMaster();
> fetch all from curs1;
> end;
>
> Version 1 works correctly. Version 2 fails with the following error -
>
> NOTICE: PerformPortalFetch: portal "curs1" not found.
>
> Please could someone advise on the correct syntax for version 2. I
> need this because I want to add some if...then...else statements to
> vary the building of the cursor, and you cannot do this inside the
> "declare" section of the function.
>
> Thanks in advance
>
> Frank Millman
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> commands go to majordomo@postgresql.org
>