Multiple Result Sets from a Stored Procedure with PostgreSQL and C#

Multiple Result Sets from a Stored Procedure with PostgreSQL and C#.

PostgreSQL has long supported returning multiple result sets from a stored procedure, but using it is rather tricky. Rather than returning the actual result set, you have to instead return a set of refcursor objects. For example,

CREATE FUNCTION Sales.CustomerWithOrdersByState(param_state CHAR(2)) RETURNS SETOF refcursor AS $$
    DECLARE
      ref1 refcursor;           -- Declare cursor variables
      ref2 refcursor;                             
    BEGIN
      OPEN ref1 FOR  SELECT  c.CustomerKey ,
            c.FullName ,
            c.State ,
            c.CreatedByKey ,
            c.UpdatedByKey ,
            c.CreatedDate ,
            c.UpdatedDate ,
            c.DeletedFlag ,
            c.DeletedDate ,
            c.DeletedByKey
    FROM    Sales.Customer c
    WHERE   c.State = param_state;
    RETURN NEXT ref1;                                                                              
 
    OPEN ref2 FOR   SELECT  o.OrderKey ,
            o.CustomerKey ,
            o.OrderDate
    FROM    Sales.Order o
            INNER JOIN Sales.Customer c ON o.CustomerKey = c.CustomerKey
    WHERE   c.State = param_state;
    RETURN NEXT ref2; 

    END;
    $$ LANGUAGE plpgsql;

Multiple result sets with C# and ADO.NET

In older versions of npgsql, .NET’s PostgreSQL driver, you could execute this function as if it were a stored procedure. It would then automatically dereference the cursors, doing the right thing.

There was a trick however. Cursors are automatically closed at the end of the transaction, which by default happens after each statement. To avoid this problem, you can to explicitly wrap every stored procedure call in a transaction so that it has time to fetch the result sets.

This was confusing, so starting with npgsql 3.0, you have to dereference the cursors manually. Here is the pattern:

//assume connection and transaction are already open and assigned to cmd

var sql = new StringBuilder();
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    while (reader.Read())
        sql.AppendLine($"FETCH ALL IN \"{ reader.GetString(0) }\";");

using (var cmd2 = new NpgsqlCommand())
{
    cmd2.Connection = cmd.Connection;
    cmd2.Transaction = cmd.Transaction;
    cmd2.CommandTimeout = cmd.CommandTimeout;
    cmd2.CommandText = sql.ToString();
    cmd2.CommandType = CommandType.Text;
                    
    //Execute cmd2 and process the results as normal
}

Multiple result sets with C# and Chain

A major design goal of Chain is to make using the capabilities of your database easy. So we brought back automatic cursor dereferencing.

var result = dataSource.Procedure("Sales.CustomerWithOrdersByState", new { @param_state = "CA" }).ToTableSet("cust", "order").Execute();

Note that we’re not explicitly dealing with transactions here. Chain will automatically detect if a refcursor is being returned and automatically create a transaction if one doesn’t already exist.