Postgresql 11 Create or Replace Procedure GetMultipleResultSets(INOUT ref1 refcursor, INOUT ref2 refcursor); Automatic cursor deferencing using Npgsql

Question

Requirement: How to get back the multiple refcursor data from a postgresql 11 procedure (not a function) without using the fetch statement using npgsql 4.0 from ado.net.

Here is the sample which i have tried:

Postgresql Procedure:

CREATE OR REPLACE PROCEDURE public.GetMultipleResultSets(
    INOUT ref1 refcursor,
    INOUT ref2 refcursor)
LANGUAGE 'plpgsql'

AS $BODY$
begin
    open ref1 for
    select * from public."tblTestTable1";

    open ref2 for
    select * from public."tblTestTable2";   
end;
$BODY$;

C# Code using Npgsql 4.0:

    public DataSet ReturnAsDataSet(string procedureName)
    {
        this.dataSet = new DataSet();   

        OpenConnection();
        NpgsqlTransaction objTransaction = this.Connection.BeginTransaction();

        NpgsqlDataAdapter adapter = new NpgsqlDataAdapter();
        NpgsqlCommand command = this.Connection.CreateCommand();

        try
        {
            NpgsqlParameter refCursorParam1 = new NpgsqlParameter("@ref1", NpgsqlTypes.NpgsqlDbType.Refcursor);
            refCursorParam1.Direction = ParameterDirection.InputOutput;
            refCursorParam1.Value = "ref1";
            command.Parameters.Add(refCursorParam1);  

            refCursorParam2 = new NpgsqlParameter("@ref2", NpgsqlTypes.NpgsqlDbType.Refcursor);
            refCursorParam2.Direction = ParameterDirection.InputOutput;
            refCursorParam2.Value = "ref2";
            command.Parameters.Add(refCursorParam2);

            command.CommandText = "call " + procedureName + "(@ref1, @ref2)";               
            command.Transaction = objTransaction;

            adapter.SelectCommand = command;
            adapter.Fill(dataSet);          

            objTransaction.Commit();                

        }

        catch (NpgsqlException ex)
        {       
            if (objTransaction != null)                
                objTransaction.Rollback();                

            throw new Exception(ex.Message);
        }
        finally
        {
            CloseConnection();
            command.Dispose();
            objTransaction.Dispose();
        }

        return this.dataSet;
    }

This code will return a table having the "ref1", "ref2" as the columns and "ref1" and "ref2" as the values inside it as follows:

enter image description here

But I need the actual result sets returned from the procedure. How can I achieve it without manually fetching those refcursor data. I mean without using "fetch all ref" statement how can we retrieve the data by executing either ExecuteReader() or adapter.Fill() methods as above. Is there any automatic cursor dereferencing available in npgsql?

Please provide the answer if anyone knows. Thanks for your help in advance.

Answer 1

This is currently not done for you by Npgsql, this issue tracks it. You can see this long discussions on the pros and cons of this. At the moment you'll have to call FETCH on the cursors yourself.