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

spraveen2001(at)yahoo(dot)com (Praveen) wrote in message news:<98d8ec76(dot)0305020423(dot)951c12f(at)posting(dot)google(dot)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