13

Here's a minimal example of my real-world problem:

create table t(id serial primary key, rnd double precision);

of course you can return the inserted columns with a returning clause:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID |            RND |
|----|----------------|
|  9 | 0.203221440315 |
*/

you can also return a literal:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID |            RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 |     1 |
*/

but you can't return the source columns:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/

Is there any way I can get w.rnd out of the final returning clause?

db<>fiddle here

8

The documentation on the RETURNING clause says:

An expression to be computed and returned by the INSERT command after each row is inserted. The expression can use any column names of the table named by table_name. Write * to return all columns of the inserted row(s).

This clearly does not apply for columns from an other table.

Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:

WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
     x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
  FROM w, x;

That is, you can put more than one writable CTE to the beginning of a query. Please see this in action on dbfiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.