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
>
