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
