PostgreSQL: Returning of an Update into variable/temp table

Question

I have an update statement with returning statement.

What I want is to put the result into a variable or temp table.

update my_table
set something = 'x'
where id ...
returning *;

I tried to that using the execute statement and e.g. returning row_to_json(my_table.*) but the variable, off cause, only contains the first row returned from the update.

Marth · Accepted Answer · 2018-03-14 12:10:11Z

3

Use a CTE with the UPDATE-RETURNING statement in it, then use that in the INSERT:

CREATE TEMP TABLE t (i int, j int);
CREATE TEMP TABLE u (i int, j int);
INSERT INTO u VALUES (1, 1), (2, 2), (3, 3);

WITH updated AS (
  UPDATE u
  SET i = i * 10
  WHERE i < 3
  RETURNING *
)
INSERT INTO t
SELECT *
FROM updated;

SQLFiddle

answered Mar 14, 2018 at 12:10

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Returning of an Update into variable/temp table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related