?
Solved

Returning Result set as refcursor in postgre sql procedure

Posted on 2007-07-31
10
Medium Priority
?
2,729 Views
Last Modified: 2009-01-20
Hi experts,
    I am working on VS2005 and Back end is PostgreSql8.2.  Here i am using PostgreSQLDirect .NET Data Provider as the connectivity between VS and postgre.  I am not able to get the result set from the postgre sql stored procedure.  How can i get the result set from the procedure using ref cursor as out parameter.

Pls. Assist me on this issue and it is very urgent.


Thanks in advance.
Seshu Kumar
0
Comment
Question by:seshu123
  • 5
  • 3
8 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 19607362
why not post your code fragment ?
0
 
LVL 4

Author Comment

by:seshu123
ID: 19607969
hai,
   This is the procedure that i wrote but when i executed through the VS i am getting <Unnamed Portal 1> only i am not getting the result set.  Is there any other way to get the Result set as out parameters like in oracle.

CREATE OR REPLACE FUNCTION public.usp_validateuser
(
iusername text,
ipassword text
)
RETURNS refcursor AS
$$
DECLARE
retval refcursor;
begin
open retval for
select user_code, user_name, user_password,
user_fullname, user_address,user_postalcode,
user_phone_number,user_mobile_number, user_emailid,
user_comments,user_is_active,user_type_code,user_type
from vwuserlist where user_name = $1 and user_password = $2
and user_is_active = true;
return retval;
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19612389
try

CREATE OR REPLACE FUNCTION public.usp_validateuser( iusername text,  ipassword text ) RETURNS refcursor AS $$
DECLARE
retval refcursor;
begin
  open retval for select user_code, user_name, user_password, user_fullname, user_address,user_postalcode,
    user_phone_number,user_mobile_number, user_emailid, user_comments,user_is_active, user_type_code,user_type
    from vwuserlist where user_name = iusername and user_password = ipassword and user_is_active;
  return retval;
end;  $$ LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Author Comment

by:seshu123
ID: 19613063
I think this is same as like my code only.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19620505
you are using $1 and $2 for arguments and = true.
0
 
LVL 4

Author Comment

by:seshu123
ID: 19622799
Hey It is working fine with me but i am not able to return the refcursor.  Is there any way to retrun a refcursor or multiple rows from the procedure to the calling application.  I told u that i am using Visual Studio 2005 and the language is C#.net

Seshu Kumar
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19623045
If the plpgsql is working then the problem is with the csharp code.

Maybe using a set returning function instead is a better fit with how C# interface works.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 1000 total points
ID: 19647567
As I remember it refcursor is returned as a string ie the name of a refcursor in your
environment.

http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS

"The portal name used for a cursor can be specified by the programmer or automatically generated. To specify a portal name, simply assign a string to the refcursor variable before opening it. The string value of the refcursor variable will be used by OPEN as the name of the underlying portal. However, if the refcursor variable is null, OPEN automatically generates a name that does not conflict with any existing portal, and assigns it to the refcursor variable. "



CREATE FUNCTION reffunc2() RETURNS refcursor AS $$
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT reffunc2();

      reffunc2      
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question