Obtain data from modified rows while they are being manipulated

returning_example.sql

-- https://www.postgresql.org/docs/current/dml-returning.html

-- Use of RETURNING avoids performing an extra database query to collect the
-- data, and is especially valuable when it would otherwise be difficult to
-- identify the modified rows reliably. The allowed contents of a RETURNING 
-- clause are the same as a SELECT command's output list (see Section 7.3). 
-- It can contain column names of the command's target table, or value 
-- expressions using those columns. A common shorthand is RETURNING *, which
-- selects all columns of the target table in order.

-- In an INSERT, the data available to RETURNING is the row as it was inserted.
-- This is not so useful in trivial inserts, since it would just repeat the data
-- provided by the client. But it can be very handy when relying on computed
-- default values. For example, when using a serial column to provide unique
-- identifiers, RETURNING can return the ID assigned to a new row:

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

-- In an UPDATE, the data available to RETURNING is the new content of the
-- modified row. For example:

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;
  
-- In a DELETE, the data available to RETURNING is the content of the deleted
-- row. For example:

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;