Options and best practices for Multiple Result Sets in Postgres11

Question

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<int>("username"));
        ...
    }
    reader.NextResult();
    while (reader.Read())
    {
        result2.Add(reader.Get<string>("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:

1. 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.
2. 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;

3. 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.)
4. 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)
5. Combine the data from each result set in a join.
   Gives the benefit of precompilation and 1 round trip, but it just feels gross when I consume the data in code. In my case, I want to get user data and a list of roles for the user. I suppose I could get data like
   userA columns..., role1 columns
   userA columns..., role2 columns

Which of these (or some other?) is the recommended way to get multiple result sets? i.e., is there a way to return 2 table types in a function or procedure?

Answer 1

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.