Chuck Bai wrote:
> I have the following function:
>=20
> CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT=20
> o_user refcursor, OUT o_name refcursor)
> RETURNS record AS
> $BODY$
> BEGIN
> tcount :=3D tcount + 1;
> OPEN o_user FOR SELECT * FROM user_table;
> OPEN o_name FOR SELECT * FROM name_table;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>=20
> Question 1: The function is not working with Npgsql .NET data provider.=
=20
> It did not return a valid .NET DataSet. But the INOUT parameter
tcount=20
> works fine. How could I test the above function with SQL in pgAdmin
III?=
=20
> 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=3D> BEGIN;
BEGIN
test=3D> SELECT * FROM test_refcursor(42);
tcount | o_user | o_name=20=20=20=20=20=20=20
--------+--------------------+--------------------
43 | <unnamed ****tal 3> | <unnamed ****tal 4>
(1 row)
test=3D> FETCH ALL FROM "<unnamed ****tal 3>";
.....
.....
(n rows)
test=3D> FETCH ALL FROM "<unnamed ****tal 4>";
.....
.....
(m row)
test=3D> COMMIT;
COMMIT
So it looks to me like your funktion works well.
> Question 2: pgAdmin III automatically added "RETURNS record" in the=20
> above function when RETURNS clause is not specified initially. Why is=20
> that? Is this the problem since it returns only single data table
with=20
> 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 ****tal 1> <unnamed ****tal 2>
>=20
>=20
> Question 3: I want to return a single DataSet with each OUT RefCursor=20
> map to a DataTable within the DataSet, plus extra OUT parameters for=20
> 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
--=20
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
