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:
- 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. - 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;
- 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.) - 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) - 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?