在BackendTW社群中,有人問了這樣的問題:傳送門
簡言之就是兩筆record的一個欄位要交換.
通常一聽到交換,就很容易聯想到使用變數來中繼或者叫過橋.
我們使用的是資料庫,與程序性語言的方式是不同的.
可以這樣做
target: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)
source: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)
然後 update target
先來直接看我寫的測試, 也有傳送門:按我前往
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 是集合的操作,與單筆操作不同.
還是要感謝一下小島南與佐山愛.
or
By clicking below, you agree to our terms of service.
New to HackMD? Sign up