How to get data from a PostgreSQL Procedure refcursor parameter using Npgsql

Question

We are migrating from on-prem SQL Server to cloud-hosted PostgreSQL. Our databases are being translated automatically and this has resulted in a lot of procedures where the data is being returned as refcursor parameters rather than an ordinary recordset e.g.

CREATE OR REPLACE PROCEDURE myschema_dbo.usp_getcustomers(
    IN par_includeinactive numeric,
    INOUT p_refcur refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    OPEN p_refcur FOR
    SELECT customer.name AS customername, customer.isactive, customer.emailaddress
        FROM myschema_dbo.customer
        WHERE customer.isactive = 1 OR par_IncludeInactive = 0
END;
$BODY$;

The calling applications are predominantly C# and we are attempting to call these procedures using the Npgsql data provider:

using (var dataSource = NpgsqlDataSource.Create(this.connectionString))
{
    using (var command = dataSource.CreateCommand("myschema_dbo.usp_getcustomers"))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new NpgsqlParameter()
        {
            ParameterName = "par_includeinactive",
            NpgsqlDbType = NpgsqlDbType.Numeric,
            Direction = ParameterDirection.Input,
            Value = 1
        });
        command.Parameters.Add(new NpgsqlParameter()
        {
            ParameterName = "p_refcur",
            NpgsqlDbType = NpgsqlDbType.Refcursor,
            Direction = ParameterDirection.InputOutput,
            Value = "p_refcur"
        });
        command.ExecuteNonQuery();

        command.CommandText = "fetch all in \"p_refcur\"";
        command.CommandType = CommandType.Text;
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            // Process the record
        }
    }
}

This results in a Npgsql.PostgresException with error message 34000: cursor "p_refcur" does not exist. Because of the automated translation process and volume of procedures across our databases, modifying the stored procedures would be a very expensive exercise that we would prefer to avoid.

I have tried fetching the ref cursor parameter using its value, or passing a different value in. I have also searched through stack overflow but the only similar questions refer to functions where a ref cursor is returned rather than used as a parameter for a procedure.

Answer 1

I'd highly recommend modifying those stored procedures to return a table directly rather than a refcursor - the automatic translation from SQL Server stored procedures doesn't seem to be doing a great job there.

For example, your function above could be simplified to the following:

CREATE FUNCTION usp_getcustomers(include_active bool) RETURNS customer
AS $$ 
    SELECT customer.name AS customername, customer.isactive, customer.emailaddress
    FROM myschema_dbo.customer
    WHERE customer.isactive = 1 OR NOT include_active
$$$
LANGUAGE SQL;

This is more efficient since you get back the results in one roundtrip; if your function returns a refcursor, you then need to execute an additional roundtrip to get the results from that refcursor; it's also needlessly complicated and not natural to PostgreSQL.

Note also that for such simple functions you can simply use SQL rather than pl/pgsql. In addition, PostgreSQL has a 1st-class boolean data type which you should use e.g. for include_active rather than numeric.

Answer 2

Jonathan,

I believe your problem is due to not using a transaction. It's needed as this is a two step operation returning the portal and reading from it.

Another thing I noticed is you should enclose your reader in using() clause. Otherwise it can cause issues with the transaction when it ends (it needs to execute rollback/commit) if your reader didn't read all rows.

So just change your code to below (I've removed usage of NpgsqlDataSource as I've never used it before):

using (var conn = new NpgsqlConnection(this.connectionString))
{
    conn.Open();

    using (var trans = conn.BeginTransaction())
    {
        using (var command = new NpgsqlCommand("myschema_dbo.usp_getcustomers", conn))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new NpgsqlParameter()
            {
                ParameterName = "par_includeinactive",
                NpgsqlDbType = NpgsqlDbType.Numeric,
                Direction = ParameterDirection.Input,
                Value = 1
            });
            command.Parameters.Add(new NpgsqlParameter()
            {
                ParameterName = "p_refcur",
                NpgsqlDbType = NpgsqlDbType.Refcursor,
                Direction = ParameterDirection.InputOutput,
                Value = "p_refcur"
            });
            command.ExecuteNonQuery();

            command.CommandText = "fetch all in \"p_refcur\"";
            command.CommandType = CommandType.Text;
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    // Process the record
                }
            }
        }
    }
}

Another option is to hide this mess and wrap it in a NpgsqlRefcursorCommand class. It will take care of the transaction and adding the refcursor. Then you can just use it as below:

using (var conn = new NpgsqlConnection(this.connectionString))
{
    conn.Open();

    using (var command = new NpgsqlRefcursorCommand("myschema_dbo.usp_getcustomers", conn))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new NpgsqlParameter()
        {
            ParameterName = "par_includeinactive",
            NpgsqlDbType = NpgsqlDbType.Numeric,
            Direction = ParameterDirection.Input,
            Value = 1
        });

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Process the record
            }
        }
    }
}

The NpgsqlRefcursorCommand would look like:

public class NpgsqlRefcursorCommand : NpgsqlCommand
{
    NpgsqlTransaction? mInternalTrans = null;

    public NpgsqlRefcursorCommand() 
        : base(null, null, null) { }

    public NpgsqlRefcursorCommand(string? cmdText) 
        : base(cmdText, null, null) { }

    public NpgsqlRefcursorCommand(string? cmdText, NpgsqlConnection? connection) 
        : base(cmdText, connection) { }

    public NpgsqlRefcursorCommand(string? cmdText, NpgsqlConnection? connection, NpgsqlTransaction? transaction)
        : base(cmdText, connection, transaction) { }

    public new NpgsqlDataReader ExecuteReader(CommandBehavior behavior = CommandBehavior.Default)
        => ExecuteReaderAsync(behavior, CancellationToken.None).GetAwaiter().GetResult();

    public new async Task<NpgsqlDataReader> ExecuteReaderAsync(CommandBehavior behavior = CommandBehavior.Default, CancellationToken cancellationToken = default)
    {
        if (Connection == null || Connection.State != ConnectionState.Open)
            throw new ArgumentException("Connection is not open!");

        if (Transaction == null)
        {
            using (NpgsqlCommand checkTransCmd = new NpgsqlCommand("select txid_current_if_assigned()", Connection))
            {
                object? oTransId = await checkTransCmd.ExecuteScalarAsync(cancellationToken);
                if (oTransId == DBNull.Value)
                {
                    mInternalTrans = await Connection.BeginTransactionAsync();
                }
            }
        }

        if (!Parameters.Any(x => x.NpgsqlDbType == NpgsqlDbType.Refcursor))
        {
            Parameters.Add(new NpgsqlParameter()
            {
                //ParameterName is not needed if procedure only has one ref cursor
                NpgsqlDbType = NpgsqlDbType.Refcursor,
                Direction = ParameterDirection.Input,
                Value = DBNull.Value
            });
        }

        var refCursorName = await ExecuteScalarAsync();

        if (refCursorName == null)
            throw new ArgumentException("Expected procedure to return cursor!");

        CommandText = $"fetch all in \"{refCursorName}\"";
        CommandType = CommandType.Text;

        return await base.ExecuteReaderAsync(behavior, cancellationToken);
    }

    protected override void Dispose(bool disposing)
    {
        if (mInternalTrans != null)
        {
            mInternalTrans.Rollback();
            mInternalTrans.Dispose();
        }
        base.Dispose(disposing);
    }

    public override ValueTask DisposeAsync()
    {
        if (mInternalTrans != null)
        {
            mInternalTrans.RollbackAsync();
            mInternalTrans.DisposeAsync();
        }
        return base.DisposeAsync();
    }
}

As a note. I believe the recommended way to return data is to use "stored function" and return data as either RETURNS TABLE(specify the columns) or as RETURNS SETOF your_table_name.

-- john