0

golang-1.18.1, postgresql-12

...

var fk = []int{11, 22}
var img = []string{"a", "b"}
var prim = []string{"a1", "b1"}

err = conn.QueryRow(context.Background(),
"INSERT INTO tb (fk, img, prim) VALUES($1,$2,$3),($4,$5,$6)", fk[0],img[0],prim[0],fk[1],img[1],prim[1]).Scan(&fk[0],&img[0],&prim[0])

...

so it works, insert two records.
How to get returning id
1. if inserting one record
2. or inserting multiple

...

? something like:

var idx []int
err = conn.QueryRow(context.Background(),
"WITH ins AS (INSERT INTO tb (fk, img, prim) VALUES($1,$2,$3),($4,$5,$6) RETURNING id) SELECT array_agg(id) INTO idx FROM ins", fk[0],img[0],prim[0],fk[1],img[1],prim[1]).Scan(&fk[0],&img[0],&prim[0])

tb has fields: id, fk, img, prim insert into tb (fk, img, prim) values(...) returning id into idx does not work in my pgx _"github.com/jackc/pgx/v4"

UPD1

**conn.Query**
var fk = []int{11, 22}
var img = []string{"a1", "b1"}
var prim = []string{"a2", "b2"}

rows, err := conn.Query(context.Background(),"INSERT INTO tb (fk,img,prim) VALUES($1,$2,$3),($4,$5,$6) RETURNING id", fk[0],img[0],prim[0],fk[1],img[1],prim[1])

var idx []int
for rows.Next() {
    var id int
    rows.Scan(&id)
    idx = append(idx, id)
}
fmt.Println("idx: ", idx)


**conn.QueryRow**
// works for inserting a single record, but only without a prepared state
var id int
err = conn.QueryRow(context.Background(), "INSERT INTO tb (fk,img,prim) VALUES (11,'aa','bb') RETURNING id").Scan(&id)
fmt.Println("idx: ", id)

// don`t work RETURNING with prepared state, but inserting:
err = conn.QueryRow(context.Background(), "INSERT INTO tb (fk,img,prim) VALUES($1,$2,$3) RETURNING id", fk[0],img[0],prim[0]).Scan(&id,&fk[0],&img[0],&prim[0])

conn.Query works for inserts SINGLE and MULTY records with prepared state and RETURNING.

conn.QueryRow works for inserting a single record, but only without a prepared state.

Alex
  • 150
  • 1
  • 10
  • 1
    In the SQL you can drop the `WITH` and the `SELECT array_agg...`. Use just plain `INSERT ... RETURNING`. And in Go use `conn.Query` to get the `*sql.Rows` (instead of `*sql.Row` [singular]). And then scan each id individually in a `for rows.Next() { ...` loop and append them to your `idx` slice. And you're done. – mkopriva May 01 '22 at 20:43
  • 1
    ... and when you are inserting only a single record and you want to use `RETURNING` then you can use `db.QueryRow` (instead of `db.Query`) and just scan that single `*sql.Row`. – mkopriva May 01 '22 at 20:46
  • @mkopriva, Thanks, your advice works. conn.Query works for inserts SINGLE and MULTY records with prepared state and RETURNING. conn.QueryRow works for inserting a single record, but only without a prepared state. Please, correct my code to mark your answer. – Alex May 02 '22 at 09:54
  • 1
    Make sure to close the `rows` returned from `conn.Query`, if you don't you will leak connections. You can utilize `defer` so that you don't have to close it manually on every `return`. Also `rows.Scan(&id)` returns an error value that you should never ignore, always check it against `nil` and if it's not `nil` then you should handle it properly. The `RETURNING` clause in your last `QueryRow` example has only the `id` column listed, if you want to return other columns then you have to list them too, e.g. `RETURNING id,fk,img,prim`... – mkopriva May 02 '22 at 10:03
  • 1
    ... basically you ALWAYS have to pass the *same* number of arguments to `Scan` as the number of columns returned by the query. – mkopriva May 02 '22 at 10:04

0 Answers0