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

I have looked into materials in www.npgsql.org, 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 $$     DECLARE       ref refcursor;     BEGIN       OPEN ref FOR SELECT city, state FROM cities;       RETURN ref;                                      END;     $$ LANGUAGE plpgsql; 

Code, C#

using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString)) {     conn.Open();     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);              dr.Close();         }         tran.Commit();     } } 

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

(added) 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 ?

(reference) http://www.sqlines.com/postgresql/npgsql_cs_result_sets

Add Comment
3 Answer(s)

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.

Add Comment

With Shay’s help, I figured out how we can fetch cursor in v3.x after removal of “dereferenced” feature. I think there are not much of good example on this, I hope this might help people save time to search example.

You can do this in npgsql ver3.x

1. CommandType.StoredProcedure (cursor name not defined)

conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction();  NpgsqlCommand command = new NpgsqlCommand("show_cities", conn); command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery();  command.CommandText = "fetch all in \"<unnamed portal 1>\""; command.CommandType = CommandType.Text;  NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) {     // do what you want with data, convert this to json or...     Console.WriteLine(dr[0]); } dr.Close();  tran.Commit(); conn.Close(); 

2. CommandType.StoredProcedure (cursor name defined)

conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction();  NpgsqlCommand command = new NpgsqlCommand("select show_cities(@ref)", conn); command.CommandType = CommandType.Text; NpgsqlParameter p = new NpgsqlParameter(); p.ParameterName = "@ref"; p.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor; p.Direction = ParameterDirection.InputOutput; p.Value = "ref"; command.Parameters.Add(p); command.ExecuteNonQuery();  command.CommandText = "fetch all in \"ref\""; command.CommandType = CommandType.Text;  NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) {     // do what you want with data, convert this to json or...     Console.WriteLine(dr[0]); } dr.Close();  tran.Commit(); conn.Close(); 

3. CommandType.Text (cursor name defined)

conn.Open(); NpgsqlTransaction tran = conn.BeginTransaction();  NpgsqlCommand command = new NpgsqlCommand("select show_cities(@ref)", conn); command.CommandType = CommandType.Text; NpgsqlParameter p = new NpgsqlParameter(); p.ParameterName = "@ref"; p.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor; p.Direction = ParameterDirection.InputOutput; p.Value = "ref"; command.Parameters.Add(p); command.ExecuteNonQuery();  command.CommandText = "fetch all in \"ref\""; command.CommandType = CommandType.Text;  NpgsqlDataReader dr = command.ExecuteReader(); while (dr.Read()) {     // do what you want with data, convert this to json or...     Console.WriteLine(dr[0]); } dr.Close();  tran.Commit(); conn.Close(); 

(reference for v2.x) http://www.sqlines.com/postgresql/npgsql_cs_result_sets

This is example for easy understanding, so if you want your function to return single result set, then instead of returning cursor, please consider it to return table as @Shay suggested or create a view not a function as @CeOnSql suggested.

Thanks !

Add Comment

I have following working code with Npgsql 2.2.7 version:

 private static DataSet ExecuteFunction(string functionName)     {         DataSet ds = new DataSet();          var conn = new NpgsqlConnection("replace with connection string");         conn.Open();         var tran = conn.BeginTransaction();         var cmd = new NpgsqlCommand(functionName, conn);         cmd.CommandType = CommandType.StoredProcedure;          NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);         da.Fill(ds);          //foreach (DataRow r in ds.Tables[0].Rows)         //{         //    Console.WriteLine("{0}", r[0]);         //}          tran.Commit();         conn.Close();         return ds;     } 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.