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:
- 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 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?