Re: How return a refcusor using functions???? - Mailing list pgsql-general

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