How to get the "RETURNING" data in "INSERT"？

[danclive]

How to get the "RETURNING" data in "INSERT"，such as:

INSERT INTO "user" ("name", "phone") VALUES ('foo',  '12345678') RETURNING "id";

[jackc]

Run it with QueryRow instead of Exec.

[mvrhov]

@jackc This doesn't seem to work. I'm getting an error "Scan received the wrong number of arguments, got 12 but expected 1"
Looking at the row.fields with debugger There really is only one item in the fields The name of it is "row" and datatype is "record"

[mvrhov]

Hem. It seems that the pgx doesn't like it when the RETURNING is written as RETURNING (col1, col2) it only works with RETURNING col1, col2

[jackc]

Those two commands mean different things. RETURNING (col1, col2) returns rows with a single column of "record" type. RETURNING col1, col2 returns rows with two columns of col1 and col2.

The first error you got with pgx was because PostgreSQL returned a one column results set where you expected 12 columns.

See example below:

pgx_test=# insert into widgets (name, description, creation_time) values ('foo', 'bar', now()) returning (id, name, description);
      row      
───────────────
 (328,foo,bar)
(1 row)

INSERT 0 1
Time: 7.974 ms
pgx_test=# insert into widgets (name, description, creation_time) values ('foo', 'bar', now()) returning id, name, description;
 id  │ name │ description 
─────┼──────┼─────────────
 329 │ foo  │ bar
(1 row)

INSERT 0 1
Time: 7.542 ms

[mvrhov]

Thanks for the explanation @jackc I did figure it out. That's why I've written the 2nd post.
Maybe it wasn't clear enough but, you probably don't want to support the 2nd case right?

[jackc]

Well, it is supported, though I'm not sure when it would make sense to actually use it that way. You can scan into a single pgtype.Record struct.

You could do something like:

var row pgtype.Record
err := conn.QueryRow("insert ... returning (...)").Scan(&row)

There are a few limitation though. First, due to how PostgreSQL represents record types it will only work using the binary format. This is what pgx uses by default but if you were also forcing the text format for some reason (e.g. to reduce round trips to PG server) then row types are not readable. Secondly, you lose the names of the columns and have to access the data positionally. Third, you lose the ability to scan the record fields into specific or custom types.

So you can do it, but I think it would only make sense in rare circumstances.