multiple statements in a single db.Query()

[gabor]

this is one of those "works in lib/pq does not work in pgx, what should i do" posts.

with lib/pq this works:

db, err := sql.Open("postgres", "...")
rows, err := db.Query("SELECT 10; SELECT 20")
// and then with some `rows.Next` and `rows.NextResultSet` i am able to retrieve both `10` and `20`

this does not work with pgx, it says cannot insert multiple commands into a prepared statement

if i switch the DefaultQueryExecMode to QueryExecModeSimpleProtocol, then the query does not fail, but the results only contain 10, and not 20.

(NOTE: this was just an example. a more typical use-case our users do is for example SET TIMEZONE TO 'UTC'; select ...)

is there a way to make this work somehow?

(unfortunately i need the "standard" go sql.Rows structure at the end)

[gabor]

i played around with this a bit and i guess the "correct" solution would be:

• 1. split the SELECT 10; SELECT 20 into statements
• 2. send all the statements using pgx.Batch

with that said, is there a helper function maybe in pgx that can help me with splitting the string into sql statements?

[gabor]

spent some more time here, i found the recommendation at #1512 to use pgconn.Exec ... i'll look more into this approach too.

[jackc]

As you figured out pgx does not support rows.NextResultSet. If you don't know how many queries your SQL text has or where the queries are split then the recommendation in #1512 is probably the best.

If you really wanted to split the queries and use a pgx.Batch then you probably could get some inspiration from some internal code from one of my other projects: https://github.com/jackc/tern/tree/master/migrate/internal/sqlsplit.