Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

SQLFiddle

share|improve this question
    
In MS SQL Server only the MERGE statement allows for additional columns to be returned. Maybe that will work for postgres too. –  Sebastian Meine Sep 28 '13 at 15:01
    
I solved a similar problem for UPDATE in this related answer on SO, but this won't work for INSERT. –  Erwin Brandstetter Oct 2 '13 at 0:48
add comment

1 Answer 1

up vote 4 down vote accepted

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 do 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 note that for some obscure reason this does not produce the expected output on SQLFiddle.

share|improve this answer
2  
The reason: SQLfiddle cannot currently handle duplicate column names. Consider this updated fiddle. –  Erwin Brandstetter Oct 2 '13 at 0:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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