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

On Sun, May 11, 2008 at 2:43 PM, Chuck Bai <cbai22@gmail.com> 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 <unnamed portal 1> <unnamed portal 2>

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