I have looked into materials in www.npgsql.org, but couldn't find how to solve my problem...
"Los Angeles", "CA"
"San Diego", "CA"
-- Procedure that returns a single result set (cursor)
CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
OPEN ref FOR SELECT city, state FROM cities;
$$ LANGUAGE plpgsql;
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();
str += dr.GetValue(0);
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
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.