CODEWITHSUNDEEP
sqlpostgresqlinsert-intosql-returning
Attilio
Attilio

Reputation: 1722

How to SELECT the result of RETURNING

How to put the result of a RETURNING clause into a SELECT in PostgreSQL? I.e., I would like the SELECT statement to return whatever RETURNING returns.

I have tried the following two syntaxes, none of them seem to work:

(1) SELECT (INSERT INTO ... RETURNING *)

(2) SELECT * FROM (INSERT INTO ... RETURNING *) ...

You might ask, why I'm trying to do this, in spite of RETURNING already giving me the desired result? It's because I have a structure using two WITH clauses, and the insertion would be in the second one, but I also need the results of the first one. (See related question.)

Remark: unfortunately, I cannot provide the concrete SQL query (because it is in a closed source product), but I think the question is generic enough to be answerable without this concrete query.

EDIT Added example:

Suppose we have this table:

CREATE TABLE test(id INT, name VARCHAR)

This works:

INSERT INTO test VALUES (1, 'Joe') RETURNING *

What I'm trying to do, but does not work:

SELECT INSERT INTO test VALUES (1, 'Joe') RETURNING *

SELECT * FROM INSERT INTO test VALUES (1, 'Joe') RETURNING *

Upvotes: 0

Views: 4335

Answers (1)

user330315
user330315

Reputation:

insert ... returning * will already return a result set, there is no need to add a SELECT statement to it.

But if you insist, you could do

with i as (
   INSERT INTO test VALUES (1, 'Joe') 
   RETURNING *
)
select *
from i;

But that returns exactly the same thing as:

INSERT INTO test VALUES (1, 'Joe') 
RETURNING *

Upvotes: 1

Related Questions