Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements.

SELECT u.username, ... FROM User u WHERE u.userId = p_userID;
SELECT r.roleName, ... FROM Role r WHERE r.userId = p_userID;


When retrieving data from each result set, the client does something like:

using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        result1.Add(reader.Get("username"));
        ...
    }
    reader.NextResult();
    while (reader.Read())
    {
        result2.Add(reader.Get("roleName"));
        ...
    }
}

What's the recommended way to do this in PostgreSQL 11? (I realize many variations of this question have already been asked and answered regarding multiple result sets, but I would like a concise list of options and a recommended best practice if it exists.) Here are the options I've found so far:

• Use a Function that RETURNS SETOF refcursor.

This lengthy debate is confusing and I'm not sure what the final status is on the issue. Requires that the function call be wrapped in a transaction(?) and then execute a Fetch on each. (another link). I'm hoping there is a more straightforward way to do this that doesn't require a transaction.

• Don't use a function or procedure, just use inline sql and batch statements.

(This works with my client code, but then I don't get the advantage of precompilation. We can't have multiple statements in a single PreparedStatement):

SELECT ... FROM TableA a; SELECT ... FROM TableB b;


• Don't use cursors, create 2 functions, each returning a Table type that defines the structure of the result.

(Requires a trip to the database for each function call.)

• Use the new StoreProcedure in PostgreSQL 11 that were going to support multiple result sets.

(Sounds like this feature didn't make it into version 11)

• Combine the data from each result set in a join.

Gives the benefit o

The third solution is the best one if you want to save round trips and want to use prepared statements.

• If you use cursors, you'll get a round trip per fetch.

• When sending multiple statements in one call, you cannot have prepared statements.

• You cannot use a stored procedure.

• Putting two result sets into one with a join: that way lies madness.

Is is good to avoid excessive client-server round trips, but trying too hard to optimize can result in the opposite.

StackExchange Database Administrators Q#243245, answer score: 2