'How to get RETURNING ID

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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source