rubberduck rubberduck - 1 year ago 205
C# Question

how can I get cursor data with calling stored procedure in npgsql

I have looked into materials in, but couldn't find how to solve my problem...

Table, PostgreSQL

[City], [State]
"Austin", "TX"
"Houston", "TX"
"Los Angeles", "CA"
"San Diego", "CA"
"San Fransisco";"CA"
"St.Louis", "MO"

Function (stored procedure), PostgreSQL

-- Procedure that returns a single result set (cursor)
CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
ref refcursor;
OPEN ref FOR SELECT city, state FROM cities;
$$ LANGUAGE plpgsql;

Code, C#

using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
using (NpgsqlTransaction tran = conn.BeginTransaction())
using (var command = new NpgsqlCommand("show_cities", conn))
command.Transaction = tran;
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();

while (dr.Read())
str += dr.GetValue(0);


This returns "unnamed portal 1" and it's a cursor to be fetched not data,
Is there any way to convert this to data like Austin, Houston, Los Angeles... ?

There are some posts over internet about this, but I'm not sure what I'm doing wrong.

  • npgsql : ver3.0.3

  • c# : vs2012

I have found this is happening at npgsql ver3.x, while in ver2.x it is working fine with my code. Is there any change in usage for fetching cursor ?


Answer Source

Npgsql 2.x had a feature whereby it automatically "dereferenced" cursors returned from functions. This feature was dropped from Npgsql 3.0; this is mentioned in our migration nodes for 3.0, and the discussion is in this issue. Since the cursor is simply returned and isn't dereferenced, Npgsql returns the cursor name itself (unnamed portal 1); you can now fetch results from this query by sending FETCH etc.

However, as was mentioned, wrapping a single SELECT in a function doesn't make much sense. If you do need to write a function that returns a single resultset, make it return a SETOF or a TABLE instead of a cursor: CREATE FUNCTION ... RETURNS TABLE (column_name column_type [, ...]). Apart from being simpler and cleaner, this is also more efficient, as the query results are returned directly (dereferencing the cursor involves another database roundtrip).

See the PostgreSQL docs for more info on how to define a function returning a table.