PostgreSQL update 案例

前言

在BackendTW社群中,有人問了這樣的問題:傳送門
簡言之就是兩筆record的一個欄位要交換.

SQL的作法

通常一聽到交換,就很容易聯想到使用變數來中繼或者叫過橋.
我們使用的是資料庫,與程序性語言的方式是不同的.
可以這樣做

target: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)

source: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)

然後 update target 

後面還有大大提出是不是要做log

先來直接看我寫的測試, 也有傳送門:按我前往

create table t240108a (
  id int not null primary key
, player text
, cash bigint
, Monologue text
);

insert into t240108a values
(1, '勇者', random() * 1e7, '喜歡邊喝咖啡,抽著煙,寫Prolog,Haskell,SQL.'),
(2, '某董', random() * 1e13, '不要羨慕我,不要嫉妒我.'),
(3, '小島南', null, '突然召喚我出來,你壞壞捏.等一下請吃鼎泰豐喔.'),
(4, '佐山愛', null, '聽說有我的分享按讚數都不錯.等一下請吃鼎泰豐喔.');

create table t240108b (
  id bigint generated always as identity primary key
, card int
, detail jsonb
, ts timestamptz default clock_timestamp()
);

---

create or replace procedure magicCard99(a int, b int)
language sql
as $code$
with t1 as (
update t240108a
   set cash = oldt.cash
  from t240108a as oldt
 where (t240108a.id, oldt.id) in ((a,b), (b,a))
returning t240108a.id, t240108a.cash, oldt.id as oldid, oldt.cash as oldcash
)
insert into t240108b(card, detail)
select 99
     , jsonb_build_object('交換之前', json_agg(json_build_object(oldid, oldcash)))
    || jsonb_build_object('交換之後', json_agg(json_build_object(id, cash)))
  from t1;
$code$;

call magicCard99(1,2);

select *
  from t240108a
 order by id;

 id | player |     cash      |                   monologue                    
----+--------+---------------+------------------------------------------------
  1 | 勇者   | 9540297216777 | 喜歡邊喝咖啡,抽著煙,寫Prolog,Haskell,SQL.
  2 | 某董   |       5674863 | 不要羨慕我,不要嫉妒我.
  3 | 小島南 |          NULL | 突然召喚我出來,你壞壞捏.等一下請吃鼎泰豐喔.
  4 | 佐山愛 |          NULL | 聽說有我的分享按讚數都不錯.等一下請吃鼎泰豐喔.
(4 rows)

select *
  from t240108b;
  
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------
id     | 1
card   | 99
detail | {"交換之前": [{"2": 9540297216777}, {"1": 5674863}], "交換之後": [{"1": 9540297216777}, {"2": 5674863}]}
ts     | 2024-01-08 12:35:49.991599+08

update 之後再利用returning, 然後組合成json, insert into logtable.

結語

方法有很多,不一定哪種好.SQL 是集合的操作,與單筆操作不同.
還是要感謝一下小島南與佐山愛.