• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3144
  • Last Modified:

Returning Result set as refcursor in postgre sql procedure

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
seshu123
Asked:
seshu123
  • 5
  • 3
1 Solution
 
earth man2Commented:
why not post your code fragment ?
0
 
seshu123Author Commented:
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
 
earth man2Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
seshu123Author Commented:
I think this is same as like my code only.
0
 
earth man2Commented:
you are using $1 and $2 for arguments and = true.
0
 
seshu123Author Commented:
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
 
earth man2Commented:
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
 
earth man2Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now