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

Please don't top post!

Chuck Bai 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.
>>
>> 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.
>
> 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 "<unnamed portal #>" kind of
> thing from client. I tested the function using Npgsql connector and it
> did not work. I got only thing like "43 | <unnamed portal 3> | <unnamed
> portal 4>" returned as a single row to my .NET client. Any other clients
> can use the function? Please advise.

Easy as pie.

If you have trouble with unnamed cursors, name them:

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

Yours,
Laurenz Albe