13

Is it possible in postgres to return data from multiple tables after 1) An INSERT INTO, and 2) an UPDATE?

For example, supposing the following contrived example:

review             fit
------             ----
id                 id
fit_id             label
body

1) How can I insert into the review table and return the respective fit data joined with the review data in the RETURNING clause?

So, SQL to insert without accomplishing the desired result:

INSERT INTO review (id, fit_id, body) VALUES (5, 1, 'some copy') RETURNING *;

However, the desired result is:

id     | 5
fit_id | 1
body   | some copy
id     | 1
label  | some label

2) How can I update the review fit_id and return the new fit data joined with the review data in the RETURNING clause?

SQL to update the fit_id to 2 of review 5 without accomplishing the desired result:

UPDATE review SET fit_id = 2 WHERE review_id = 5 RETURNING *;

However, the desired result is:

id     | 5
fit_id | 2
body   | some copy
id     | 2
label  | some other label
craigmichaelmartin
  • 6,091
  • 1
  • 21
  • 25

1 Answers1

19
WITH inserted AS (
    INSERT INTO review VALUES (...) RETURNING *
)
SELECT inserted.*, fit.*
FROM inserted
INNER JOIN fit ON inserted.fit_id = fit.id

That will get you the output you want. Pretty sure you can't return stuff from another table in an UPDATE/INSERT ... RETURNING statement.

404
  • 8,022
  • 2
  • 27
  • 47