[GENERAL] Function to return both integer and SETOF refcursor

[Chuck Bai]

I got the following error when try to create a function to return an 
integer and SETOF refcursor. I want to get refcursors back along with an 
Out parameter in one function. There seems to be a conflict on return 
type. How do I fix it? Thanks.


ERROR:  function result type must be integer because of OUT parameters

CREATE OR REPLACE FUNCTION testrefcursor(IN uid integer, OUT tcount integer)
 RETURNS SETOF  refcursor AS
$BODY$
DECLARE
 o_user refcursor;
 o_name refcursor;
BEGIN
   tcount := 100; -- add some logic to calculate tcount
   OPEN o_user FOR SELECT * FROM usr_table where usr_id = uid;
   RETURN NEXT o_user;
   OPEN o_name FOR SELECT * FROM temp_table;
   RETURN NEXT o_name;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters

[Chuck Bai]

Thank you Albe. I test your script using psql and it works as you found 
out. If the function is correct. Now the problem is how to use the 
function from client side. It could not use "" kind of 
thing from client. I tested the function using Npgsql connector and it 
did not work. I got only thing like "43 |  | portal 4>" returned as a single row to my .NET client. Any other clients 
can use the function? Please advise.


Albe Laurenz *EXTERN* wrote:

Chuck Bai wrote:
  

I have the following function:

CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT 
o_user refcursor, OUT o_name refcursor)

  RETURNS record AS
$BODY$
BEGIN
tcount := tcount + 1;
OPEN o_user FOR SELECT * FROM user_table;
OPEN o_name FOR SELECT * FROM name_table;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE

Question 1: The function is not working with Npgsql .NET data provider. 
It did not return a valid .NET DataSet. But the INOUT parameter tcount 
works fine. How could I test the above function with SQL in pgAdmin III? 
I want to find out if problem is in the function or in the Npgsql.



psql is easier, but you can also issue SQL commands with pgAdmin III:

test=> BEGIN;
BEGIN
test=> SELECT * FROM test_refcursor(42);
 tcount |   o_user   |   o_name   
++

 43 |  | 
(1 row)

test=> FETCH ALL FROM "";


(n rows)

test=> FETCH ALL FROM "";


(m row)

test=> COMMIT;
COMMIT

So it looks to me like your funktion works well.

  
Question 2: pgAdmin III automatically added "RETURNS record" in the 
above function when RETURNS clause is not specified initially. Why is 
that? Is this the problem since it returns only single data table with 
the following value? How to fix it?



It is unnecessary to have "RETURNS record", but it is not a problem.
It just means: "returns something".

You do not need to fix it.

  

tcount  o_user  o_name
23  


Question 3: I want to return a single DataSet with each OUT RefCursor 
map to a DataTable within the DataSet,  plus extra OUT parameters for 
individual OUT values. How could I create such a function?



DataSet and DataTable ate .NET things, so you'd better ask on the Npgsql
forum.
What keeps you from adding extra OUT parameters?

Yours,
Laurenz Albe

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters

[Chuck Bai]

The following is a function from PosgreSQL documentation to return 
multiple cursors from a single function:


CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
   OPEN $1 FOR SELECT * FROM table_1;
   RETURN NEXT $1;
   OPEN $2 FOR SELECT * FROM table_2;
   RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;


What I want to achieve is to modify the function to take an INOUT 
parameter. For example:
myfunc(INOUT tcount integer, refcursor, refcursor). I want to add logic 
to my INOUT parameter inside the function and return it back to client, 
as well as returning the two refcursor results. How to modify this 
function and how to test it in SQL to achieve my goal?


Merlin Moncure wrote:

On Sun, May 11, 2008 at 2:43 PM, Chuck Bai <[EMAIL PROTECTED]> wrote:
  

 CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_user
refcursor, OUT o_name refcursor)
  RETURNS record AS
 $BODY$
 BEGIN
   tcount := tcount + 1;
   OPEN o_user FOR SELECT * FROM user_table;
   OPEN o_name FOR SELECT * FROM name_table;
 END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE

 Question 1: The function is not working with Npgsql .NET data provider. It
did not return a valid .NET DataSet. But the INOUT parameter tcount works
fine. How could I test the above function with SQL in pgAdmin III? I want to
find out if problem is in the function or in the Npgsql.



You can test from pgAdmin by simply running queries in the query
window.  This sort of thing however might be a better fit for psql
(pasting your queries in the query window).  You need to use
transactions since refcursors only only good inside a transaction.

  

 Question 2: pgAdmin III automatically added "RETURNS record" in the above
function when RETURNS clause is not specified initially. Why is that? Is
this the problem since it returns only single data table with the following
value? How to fix it?



For a function with >1 out parameters, the output type is a record.
Your function returns (int, refcursor, refcursor) as defined.  SELECT
* FROM test_refcursor(7); would returns a row with three variables ( a
record).

  

 tcount  o_user  o_name
 23



You probably want to name your refcursors.  The way to do this is
simply o_user := 'something'; inside your pl/pgsql function.

-- inside pl/pgsql_function
refcur_variable := 'mycursor'

-- outside function, but in same transaction
FETCH ALL FROM mycursor -- or, "mycursor"

So, it would at least take a few 'queries' from the perppective of the
client to do what you are attempting.  However, all the data is 'set
up' for return to the client by the server in the main function.  The
server will hang on to it as long as the current transaction is valid
and then release it.

  

 Question 3: I want to return a single DataSet with each OUT RefCursor map
to a DataTable within the DataSet,  plus extra OUT parameters for individual
OUT values. How could I create such a function?



Your question is a little opaque to me.  A refcursor is in PostgreSQL
terms a 'hande' to a set, not a DataTable the way you are
thinking...it's really a fancy string.  so, (INOUT int, OUT refcursor,
OUT refcursor) returns takes an 'int' in and returns an int and two
refcursors (strings), with extra work to return this to the client, at
least in terms of SQL statements.

I haven't used .net for a while but IIRC it's probably not possible to
'fill' multiple data tables in a single query without at least some
manual work.  Some of the npgsql experts might have some suggestions
however.  It really depends on how the code operates inside the npgsql
library.

merlin

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to create a function with multiple RefCursor OUT parameters

[Chuck Bai]

I have the following function:

CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT 
o_user refcursor, OUT o_name refcursor)

 RETURNS record AS
$BODY$
BEGIN
   tcount := tcount + 1;
   OPEN o_user FOR SELECT * FROM user_table;
   OPEN o_name FOR SELECT * FROM name_table;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE

Question 1: The function is not working with Npgsql .NET data provider. 
It did not return a valid .NET DataSet. But the INOUT parameter tcount 
works fine. How could I test the above function with SQL in pgAdmin III? 
I want to find out if problem is in the function or in the Npgsql.


Question 2: pgAdmin III automatically added "RETURNS record" in the 
above function when RETURNS clause is not specified initially. Why is 
that? Is this the problem since it returns only single data table with 
the following value? How to fix it?


tcount  o_user  o_name
23  


Question 3: I want to return a single DataSet with each OUT RefCursor 
map to a DataTable within the DataSet,  plus extra OUT parameters for 
individual OUT values. How could I create such a function?


Any help is appreciated.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general