Add support for handling multiple result sets in a single npgsql command

[baronfel]

It's possible in psql to execute multiple commands in a single transaction, returning distinct result sets:

SELECT u.username, ... FROM user u WHERE u.user_id = $1;
SELECT r.rolename, ... FROM role r WHERE r.user_id = $1;

It would be lovely to be able to execute these kinds of queries in Npgsql.FSharp and retain the ability to use the analyzer, etc to maintain compatibility.

Logically, the data reader returned from such queries has embedded 'stop' points between the result sets of such queries, so it's possible to detect and use different mappers for each result set (here's a simple logical example for two queries in the result set):

let reader = <do queries>
let result1 = ResizeArray<_>()
while (reader.Read()) do
	result1.Add(read rowReader1)
reader.NextResult()
let result2 = ResizeArray<_>()
while (reader.Read()) do
	result2.Add(read rowReader2)

return (Seq.toList result1, Seq.toList result2)

Is such a thing possible and/or easy in the current design?

[Zaid-Ajaj]

Hi @baronfel, I somehow missed this issue 😓 sorry for the late reply! This is an interesting problem and the challenge really lies in making an API that can be analyzed and then actually analyzing each result. Initial thoughts are

let (users, roles) = 
  connectionString
  |> Sql.connect
  |> Sql.query "<query with multiple result sets>"
  |> Sql.executeReaderTwo { 
       firstResultSet = fun reader -> User.create(...)
       secondResultSet = fun reader -> Role.create(...)
  }

Having a Sql.executeReaderTwo and Sql.executeReaderThree would solve the most common scenarios right?

What do you think?

[aaronmu]

ADO.NET 6 will introduce a batching API.

• New System.Data.Common batching API dotnet/runtime#28633
• Implement new ADO.NET batching API npgsql/npgsql#2317