FAQ
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.

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?

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?

Any help is appreciated.

Search Discussions

  • Merlin Moncure at May 12, 2008 at 3:09 pm

    On Sun, May 11, 2008 at 2:43 PM, 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.
    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
  • Chuck Bai at May 13, 2008 at 2:29 am
    The following is a function from PosgreSQL documentation to return
    multiple cursors from a single function:

    CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
    BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
    END;
    $$ LANGUAGE plpgsql;

    -- need to be in a transaction to use cursors.
    BEGIN;

    SELECT * FROM myfunc('a', 'b');

    FETCH ALL FROM a;
    FETCH ALL FROM b;
    COMMIT;


    What I want to achieve is to modify the function to take an INOUT
    parameter. For example:
    myfunc(INOUT tcount integer, refcursor, refcursor). I want to add logic
    to my INOUT parameter inside the function and return it back to client,
    as well as returning the two refcursor results. How to modify this
    function and how to test it in SQL to achieve my goal?

    Merlin Moncure wrote:
    On Sun, May 11, 2008 at 2:43 PM, 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.
    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
  • Albe Laurenz *EXTERN* at May 13, 2008 at 6:20 am

    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
  • Chuck Bai at May 16, 2008 at 2:55 am
    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
  • Albe Laurenz at May 16, 2008 at 7:36 am
    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
  • Merlin Moncure at May 16, 2008 at 1:06 pm

    On Fri, May 16, 2008 at 2:17 AM, Albe Laurenz wrote:
    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
    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
    also don't forget, the refcursors are only valid for the duration of
    the transaction.

    merlin
  • Francisco Figueiredo Jr. at May 17, 2008 at 6:14 am

    On Fri, May 16, 2008 at 10:06 AM, Merlin Moncure wrote:
    On Fri, May 16, 2008 at 2:17 AM, Albe Laurenz wrote:
    Chuck Bai wrote:

    Hi, Chuck!

    What's the Npgsql code you are using to call this function?

    Thanks in advance.


    --
    Regards,

    Francisco Figueiredo Jr.
    http://fxjr.blogspot.com
    http://www.npgsql.org

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 11, '08 at 6:43p
activeMay 17, '08 at 6:14a
posts8
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase