只是順便 RETURNING

許多人會在變更資料之後，緊接著查詢資料庫，以確認資料變更的結果。其實你可以試著加上「RETURNING」，在執行資料變更後，立即回傳需要的資料。

PostgreSQL 提供了便利的語法，來做點練習吧！

6.4. Returning Data from Modified Rows

以下範例也發佈在 DB fiddle:
https://www.db-fiddle.com/f/vc3t9XcVWPQ3qvQNuTyXqG/0

CREATE TABLE

建立一個測試的 TABLE，主要包含一個會自動產生 UUID 的欄位。常見的情況是更新資料後可能需要再依其 uuid 進行其他的資料處理。

CREATE TABLE pg_returning(
   uuid UUID DEFAULT gen_random_uuid(),
   value TEXT
);
INSERT INTO pg_returning(value) VALUES('abc');
SELECT * FROM pg_returning;
                 uuid                 | value
--------------------------------------+-------
 c65c0a0b-d331-417f-bc5a-7cdf693e89d9 | abc
(1 row)

INSERT RETURNING

INSERT 完之後可以直接取得新產生的 uuid。

INSERT INTO pg_returning(value) VALUES ('123') RETURNING *;
                 uuid                 | value
--------------------------------------+-------
 b877724a-8ef8-4906-abbd-5f085d10c0dd | 123
(1 row)

UPDATE RETURNING

UPDATE 之後可以立即確認變更的結果。

UPDATE pg_returning SET value='def' WHERE value='abc' RETURNING *;
                 uuid                 | value
--------------------------------------+-------
 c65c0a0b-d331-417f-bc5a-7cdf693e89d9 | def
(1 row)

DELETE RETURNING

DELETE 之後可以列出剛剛被刪除的資料有哪些，就不用先 SELECT 再 DELETE。

DELETE FROM pg_returning WHERE value='def' RETURNING *;
                 uuid                 | value
--------------------------------------+-------
 c65c0a0b-d331-417f-bc5a-7cdf693e89d9 | def
(1 row)

UPDATE……OLD and NEW

在 UPDATE 的時候，如果想要列出變更前後的值該怎麼做呢？

先列出目前 TABLE 裡的資料。

INSERT INTO pg_returning(value) VALUES ('456');
SELECT * FROM pg_returning;
                 uuid                 | value
--------------------------------------+-------
 b877724a-8ef8-4906-abbd-5f085d10c0dd | 123
 6eb10004-cb1a-45fa-a437-93f74573fdea | 456
(2 rows)

以下範例語法用於整表更新：

• 利用每一筆資料都有 ctid，來對應新舊資料列（更新完後 ctid 會改變）。
• FROM 子句是最先被執行的，記得要加上 FOR UPDATE，才能鎖住即將要交易的資料。old 裡的值在此時被暫存下來。
• WHERE 子句中，除了你的 UPDATE 條件之外，需要再 AND old.ctid = new.ctid，將新舊資料的內容 JOIN 起來。
• RETURNING 的時候交易已經結束，可以依需求列出需要回傳的欄位了。

UPDATE pg_returning new 
SET value='abc' 
FROM (SELECT ctid,* FROM pg_returning FOR UPDATE) old 
WHERE old.ctid = new.ctid  
RETURNING old.uuid, old.value old_value, new.value new_value;
                 uuid                 | old_value | new_value
--------------------------------------+-----------+-----------
 b877724a-8ef8-4906-abbd-5f085d10c0dd | 123       | abc
 6eb10004-cb1a-45fa-a437-93f74573fdea | 456       | abc
(2 rows)

接下來，就換你動手做囉！

https://flic.kr/p/qCaWte