FR: Support batching different queries together

[jgiles]

What do you want to change?

Context

https://github.com/sqlc-dev/sqlc/issues/1275 was resolved by adding :batchexec, :batchone, and :batchmany commands generating code for submitting multiple instances of the same query in a batch. https://github.com/sqlc-dev/sqlc/issues/1275#issuecomment-2368589913 on that feature requests batching for different types of queries. This FR is for addressing that request.

Related FR: https://github.com/sqlc-dev/sqlc/issues/3752 requests "a query annotation to enable batching across multiple tables". This FR would address that usecase, though not through the addition of a new query annotation.

Proposal

• Generate a QueryBatch type which supports queueing and typesafe processing of any eligible query on the current generated Queries object.
  • "Eligible" here likely excludes :batchexec, :batchone, and :batchmany, :copyfrom, and possibly others that do not work in the context of an existing batch.
• For each supported method X on Queries, we create a corresponding method on QueryBatch which returns an XBatchResult object for retrieving batch results in a typesafe manner.
• A QueryBatch is submitted to get a QueryBatchResults type, which the XBatchResult objects can call to get and return typesafe results.
• Internally we will need to ensure the correct ordered processing of the batch results coming back from the database.

The goal is to support convenient batch execution of any already-defined queries, rather than requiring a separate query with a custom annotation and limiting usage to a single query type in a batch.

I can draft a PR proposing an implementation. In doing this, we should be careful not to conflict with the existing batch command support.

While a similar construct could be created in other languages and databases, I am interested in adding this for Go / pgx.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[jgiles]

Looking a bit more closely, I may model the results-handling more closely with the way pgx.Batch does under the hood.