update-returning.sql

-- Update goes into a CTE, the CTE will become the source of data to our insert into product_log
WITH logs AS (
    UPDATE product
    SET price = price * 1.1
      , date_updated = NOW()
    RETURNING product.id, product.price
)
INSERT INTO product_log (product_id, price)
SELECT id, price
FROM logs;

-- Looking at our data...
SELECT substring(id::text, 1, 5) || '...' AS id, name, to_char(price, '999D99') AS price, '... ' || substring(date_added::text, 12, 8) AS date_added, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product;
--     id    |         name         |  price  |  date_added  | date_updated
-- ----------+----------------------+---------+--------------+--------------
--  3da19... | feather on the stick |   14.64 | ... 16:53:59 | ... 16:54:01
--  156a6... | mouse on the stick   |   17.57 | ... 16:53:59 | ... 16:54:01
--  4ad03... | bed cavern           |  203.51 | ... 16:53:59 | ... 16:54:01
--  1612f... | scratch toy          |   42.46 | ... 16:53:59 | ... 16:54:01
--  b9803... | loud bell ball       |   11.71 | ... 16:53:59 | ... 16:54:01
-- (5 rows)

SELECT substring(product_id::text from 1 for 5) || '...' AS product_id, to_char(price, '999D99') AS price, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product_log;
--  product_id |  price  | date_updated
-- ------------+---------+--------------
--  3da19...   |   13.31 | ... 16:54:01
--  156a6...   |   15.97 | ... 16:54:01
--  4ad03...   |  185.01 | ... 16:54:01
--  1612f...   |   38.60 | ... 16:54:01
--  b9803...   |   10.65 | ... 16:54:01
--  3da19...   |   14.64 | ... 16:54:01
--  156a6...   |   17.57 | ... 16:54:01
--  4ad03...   |  203.51 | ... 16:54:01
--  1612f...   |   42.46 | ... 16:54:01
--  b9803...   |   11.71 | ... 16:54:01
-- (10 rows)