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

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
>