Postgres Stored Procedure from C#

Question

I have a Postgres database with a stored procedure that returns JSON documents, based on the article here: http://www.sqlines.com/postgresql/npgsql_cs_result_sets

The procedure is represented like this:

-- Procedure that returns a single result set (cursor)
CREATE OR REPLACE FUNCTION get_data_test() RETURNS refcursor AS $$
DECLARE
ref refcursor;              -- Declare a cursor variable
BEGIN
    OPEN ref FOR            -- Open a cursor
        SELECT row_to_json(r) AS data
        FROM 
        (
            SELECT *
            FROM data AS d
        ) r;
    RETURN ref;             -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

I am then running the following code from a .net console app:

// Making connection with Npgsql provider
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
    conn.Open();

    var trans = conn.BeginTransaction();
    var cmd = new NpgsqlCommand("get_data_test", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Transaction = trans;

    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
            Trace.WriteLine(reader[0]);
    }
}

The output is a single instance wit the name of the refcursor, rather than the actual data: < unnamed portal 1 >

If I run the select query directly in the command text, the result set is returned as expected. I've also tried explicitly calling the proc via text using "SELECT get_data_test()" but this also fails with just the cursor name.

I don't believe I am missing a step and this refcursor should be returned unbundled. What am I doing wrong?

Possible duplicate of how can I get cursor data with calling stored procedure in npgsql — JGH
– JGH, Commented Apr 20, 2018 at 15:35

Tristan Rhodes · Accepted Answer · 2018-04-20 15:36:45Z

2

As it happens, the tutorial was wrong:

Nice GitHub bug report: https://github.com/npgsql/npgsql/issues/1777

Correct answer:

-- Procedure that returns a single result set (cursor)
CREATE OR REPLACE FUNCTION get_data_test() RETURNS TABLE (data JSON) AS $$
BEGIN
    RETURN query
        SELECT row_to_json(r) AS data
        FROM 
        (
            SELECT *
            FROM data AS d
        ) r;
END;
$$ LANGUAGE plpgsql;

answered Apr 20, 2018 at 15:36

Tristan Rhodes

3936 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Stored Procedure from C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related