I wish to get two (small) consequence units and benefit from precompilation and 1 spherical journey to the database. SQL Server permits a saved process to comprise a number of choose statements.

SELECT u.username, ... FROM Person u WHERE u.userId = p_userID;
SELECT r.roleName, ... FROM Position r WHERE r.userId = p_userID;

When retrieving information from every consequence set, the shopper does one thing like:

utilizing (var reader = cmd.ExecuteReader())
{
    whereas (reader.Learn())
    {
        result1.Add(reader.Get<int>("username"));
        ...
    }
    reader.NextResult();
    whereas (reader.Learn())
    {
        result2.Add(reader.Get<string>("roleName"));
        ...
    }
}

What is the really helpful approach to do that in PostgreSQL 11? (I understand many variations of this query have already been requested and answered concerning a number of consequence units, however I would really like a concise checklist of choices and a really helpful greatest follow if it exists.) Listed below are the choices I’ve discovered thus far:

1. Use a Perform that RETURNS SETOF refcursor.
   This prolonged debate is complicated and I am undecided what the ultimate standing is on the difficulty. Requires that the perform name be wrapped in a transaction(?) after which execute a Fetch on every. (one other hyperlink). I am hoping there’s a extra easy approach to do that that does not require a transaction.
2. Do not use a perform or process, simply use inline sql and batch statements.
   (This works with my shopper code, however then I do not get the benefit of precompilation. We will not have a number of statements in a single PreparedStatement):

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

3. Do not use cursors, create 2 capabilities, every returning a Desk kind that defines the construction of the consequence.
   (Requires a visit to the database for every perform name.)
4. Use the brand new StoreProcedure in PostgreSQL 11 that have been going to assist a number of consequence units.
   (Appears like this characteristic did not make it into model 11)
5. Mix the information from every consequence set in a be part of.
   Offers the advantage of precompilation and 1 spherical journey, nevertheless it simply feels gross once I eat the information in code. In my case, I wish to get person information and a listing of roles for the person. I suppose I might get information like
   userA columns…, role1 columns
   userA columns…, role2 columns

Which of those (or another?) is the really helpful approach to get a number of consequence units? i.e., is there a approach to return 2 desk varieties in a perform or process?