Re: How to create a function with multiple RefCursor OUT parameters
От | Chuck Bai |
---|---|
Тема | Re: How to create a function with multiple RefCursor OUT parameters |
Дата | |
Msg-id | 482CF7B3.9050700@gmail.com обсуждение исходный текст |
Ответ на | Re: How to create a function with multiple RefCursor OUT parameters ("Albe Laurenz *EXTERN*" <laurenz.albe@wien.gv.at>) |
Ответы |
Re: How to create a function with multiple RefCursor OUT parameters
("Albe Laurenz" <laurenz.albe@wien.gv.at>)
|
Список | pgsql-general |
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. 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 | <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 > >
В списке pgsql-general по дате отправления:
Следующее
От: Klint GoreДата:
Сообщение: Re: triggers: dynamic references to fields in NEW and OLD?