how can I get multi result set with postgresql function

Question

I want to return multiple result set with function. But reader.NextResult() does not work. Query result as follows

"<unnamed portal 1>"
"<unnamed portal 2>"

Function (stored procedure), PostgreSQL

CREATE OR REPLACE FUNCTION public.sp_get_multiviewlist
( )
RETURNS SETOF refcursor
DECLARE
    ref1 refcursor;
    ref2 refcursor;
BEGIN
OPEN ref1 FOR
SELECT * FROM public."Customer";
RETURN NEXT ref1;

OPEN ref2 FOR 
SELECT * FROM public."Order";
RETURN NEXT ref2;

END;

$$ LANGUAGE plpgsql;

Other databases are working properly. How can I do it ?

npgsql : ver 3.2.5

Answer 1

Using setof refcursor is a way you can emulate multiple result sets in PG. But it's not actually the same thing as multiple result sets.

Npgsql used to automatically fetch the contents of cursors returned from stored proc's, but that't not alway expected either, so it was removed. Read the whole (long) discussion at this github issue.

Anyway, to get the data you must execute FETCH ALL FROM "<unnamed portal 1>" - where the unnamed portal 1 must be the string returned from the stored proc. The cursors will only stay alive long enough if you execute the stored procedure inside a transaction.

You can look at how Npgsql used to do this here, or follow the general approach below:

using(var trans = db.BeginTransaction())
using(var cmd = db.CreateCommand()) {           
    cmd.CommandText = "sp_get_multiviewlist";
    cmd.CommandType = CommandType.StoredProcedure;  

    string cursor1Name,cursor2Name;
    using (var reader = cmd.ExecuteReader())
    {
        reader.Read();
        cursor1Name = reader.GetString(0);
        reader.Read();
        cursor2Name = reader.GetString(0);
    }


    using(var resultSet1 = db.CreateCommand())
    {
        resultSet1.CommandText = $@"FETCH ALL FROM ""{cursor1Name}""";
        using (var reader = resultSet1.ExecuteReader())
        {
            while (reader.Read())
            {
                // Do something with the customer row
            }
        }
    }

    using (var resultSet2 = db.CreateCommand())
    {
        resultSet2.CommandText = $@"FETCH ALL FROM ""{cursor2Name}""";
        using (var reader = resultSet2.ExecuteReader())
        {
            while (reader.Read()) {
                // Do something with the order row
            }
        }
    }
}