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

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 | <unnamed portal 3> | <unnamed portal 4>
(1 row)

test=> FETCH ALL FROM "<unnamed portal 3>";
....
....
(n rows)

test=> FETCH ALL FROM "<unnamed portal 4>";
....
....
(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     <unnamed portal 1>     <unnamed portal 2>
>
>
> 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