# PostgreSQL JSON 與 Trigger 應用
## 前言
一般trigger 做 audit,需要有一個紀錄變動之前或變動之後的table,會與原table類似,但是需要更多欄位,新舊,action(insert不一定會有,看需要,update或delete),再補個timestamp.在以前的例子中可以看到.
但是當原table的schema變動時,就很麻煩!需要做對應的變動,trigger也要改寫,雖然沒太大的技術性,但是需要很小心.若是欄位很多時,這個trigger 寫起來繁瑣又無趣.
## 搭配JSON使用
有了JSON以後,會更方便.雖然以前PG就有hstore了,但是hstore與其他DB或程式語言的整合度不比JSON.
### 範例資料
```sql!=
create table it230117 (
id int generated always as identity primary key
, gname text
, gprice int
);
create table it230117_audit (
id int generated always as identity primary key
, action text
, tid int
, oldval jsonb
, newval jsonb
);
create or replace function trifun_it230117()
returns trigger
as $_$
begin
if (TG_OP = 'INSERT') then
insert into it230117_audit(action, tid, newval)
values (TG_OP, NEW.id, to_jsonb(NEW.*));
elsif (TG_OP = 'DELETE') then
insert into it230117_audit(action, tid, oldval)
values (TG_OP, OLD.id, to_jsonb(OLD.*));
elsif (TG_OP = 'UPDATE') then
insert into it230117_audit(action, tid, oldval, newval)
values (TG_OP, OLD.id, to_jsonb(OLD.*), to_jsonb(NEW.*));
end if;
return null;
end;
$_$ language plpgsql;
create trigger tri_it230117
after insert or delete or update
on it230117
for each row execute function trifun_it230117();
```
### 測試一下
```sql!=
insert into it230117 (gname, gprice) values
('鉛筆', 5), ('原子筆', 7), ('簽字筆', 8);
select *
from it230117;
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 1 | 鉛筆 | 5 |
| 2 | 原子筆 | 7 |
| 3 | 簽字筆 | 8 |
+----+--------+--------+
(3 rows)
select *
from it230117_audit;
+----+--------+-----+--------+-------------------------------------------+
| id | action | tid | oldval | newval |
+----+--------+-----+--------+-------------------------------------------+
| 1 | INSERT | 1 | [NULL] | {"id": 1, "gname": "鉛筆", "gprice": 5} |
| 2 | INSERT | 2 | [NULL] | {"id": 2, "gname": "原子筆", "gprice": 7} |
| 3 | INSERT | 3 | [NULL] | {"id": 3, "gname": "簽字筆", "gprice": 8} |
+----+--------+-----+--------+-------------------------------------------+
(3 rows)
update it230117
set gprice = gprice + 1;
select *
from it230117_audit
where id > 3;
+----+--------+-----+-------------------------------------------+-------------------------------------------+
| id | action | tid | oldval | newval |
+----+--------+-----+-------------------------------------------+-------------------------------------------+
| 4 | UPDATE | 1 | {"id": 1, "gname": "鉛筆", "gprice": 5} | {"id": 1, "gname": "鉛筆", "gprice": 6} |
| 5 | UPDATE | 2 | {"id": 2, "gname": "原子筆", "gprice": 7} | {"id": 2, "gname": "原子筆", "gprice": 8} |
| 6 | UPDATE | 3 | {"id": 3, "gname": "簽字筆", "gprice": 8} | {"id": 3, "gname": "簽字筆", "gprice": 9} |
+----+--------+-----+-------------------------------------------+-------------------------------------------+
(3 rows)
delete
from it230117
where id = 3;
select *
from it230117_audit
where id > 6;
+----+--------+-----+-------------------------------------------+--------+
| id | action | tid | oldval | newval |
+----+--------+-----+-------------------------------------------+--------+
| 7 | DELETE | 3 | {"id": 3, "gname": "簽字筆", "gprice": 9} | [NULL] |
+----+--------+-----+-------------------------------------------+--------+
(1 row)
```
## 由JSON還原為record
雖然已經有紀錄起來了,但是還原為record,讓SQL語法可以應用,會更方便.
在此可以使用 jsonb_populate_record()
### 直接呼叫
```sql!=
select jsonb_populate_record(null::it230117, oldval)
from it230117_audit
where action = 'UPDATE';
+-----------------------+
| jsonb_populate_record |
+-----------------------+
| (1,鉛筆,5) |
| (2,原子筆,7) |
| (3,簽字筆,8) |
+-----------------------+
```
雖然還原為record了,但是是整個row.取用不便.
### 要用 join lateral
```sql!=
select r.*
from it230117_audit
cross join lateral jsonb_populate_record(null::it230117, oldval) r
where action = 'UPDATE';
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 1 | 鉛筆 | 5 |
| 2 | 原子筆 | 7 |
| 3 | 簽字筆 | 8 |
+----+--------+--------+
(3 rows)
select r.*
, '<-old new->' as "指示牌"
, s.*
from it230117_audit
cross join lateral jsonb_populate_record(null::it230117, oldval) r
cross join lateral jsonb_populate_record(null::it230117, newval) s
where action = 'UPDATE';
+----+--------+--------+-------------+----+--------+--------+
| id | gname | gprice | 指示牌 | id | gname | gprice |
+----+--------+--------+-------------+----+--------+--------+
| 1 | 鉛筆 | 5 | <-old new-> | 1 | 鉛筆 | 6 |
| 2 | 原子筆 | 7 | <-old new-> | 2 | 原子筆 | 8 |
| 3 | 簽字筆 | 8 | <-old new-> | 3 | 簽字筆 | 9 |
+----+--------+--------+-------------+----+--------+--------+
(3 rows)
select r.*
from it230117_audit
cross join lateral jsonb_populate_record(null::it230117, oldval) r
where action = 'DELETE';
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 3 | 簽字筆 | 9 |
+----+--------+--------+
(1 row)
select r.*
from it230117_audit
cross join lateral jsonb_populate_record(null::it230117, newval) r
where action = 'INSERT';
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 1 | 鉛筆 | 5 |
| 2 | 原子筆 | 7 |
| 3 | 簽字筆 | 8 |
+----+--------+--------+
(3 rows)
-- 再來點 update
select * from it230117;
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 1 | 鉛筆 | 6 |
| 2 | 原子筆 | 8 |
+----+--------+--------+
(2 rows)
-- 現在資料剩兩筆
update it230117
set gname = '2B鉛筆'
, gprice = 7
where id = 1;
select r.*
, '<-old new->' as "指示牌"
, s.*
from it230117_audit
cross join lateral jsonb_populate_record(null::it230117, oldval) r
cross join lateral jsonb_populate_record(null::it230117, newval) s
where action = 'UPDATE'
and r.gname like '%鉛筆%';
+----+-------+--------+-------------+----+--------+--------+
| id | gname | gprice | 指示牌 | id | gname | gprice |
+----+-------+--------+-------------+----+--------+--------+
| 1 | 鉛筆 | 5 | <-old new-> | 1 | 鉛筆 | 6 |
| 1 | 鉛筆 | 6 | <-old new-> | 1 | 2B鉛筆 | 7 |
+----+-------+--------+-------------+----+--------+--------+
(2 rows)
```
## 還有沒有進一步的用法?
上面的例子中,把 insert, update, delete 做了記錄,也可以取用為record了.但是對table的操作中,還有一個 truncate ！
### 改寫下trigger
```sql!=
create or replace function trif_trun_it230117()
returns trigger
as $_$
begin
insert into it230117_audit(action,oldval)
select 'TRUNCATE',jsonb_agg(to_jsonb(i.*))
from it230117 i;
return null;
end;
$_$ language plpgsql;
create trigger tri_trun_it230117
before truncate
on it230117
for each statement execute function trif_trun_it230117();
```
### truncate 與驗證
```sql!=
truncate table it230117
restart identity;
-- 檢查一下 it230117_audit
select oldval
from it230117_audit
where action = 'TRUNCATE';
+----------------------------------------------------------------------------------------+
| oldval |
+----------------------------------------------------------------------------------------+
| [{"id": 2, "gname": "原子筆", "gprice": 8}, {"id": 1, "gname": "2B鉛筆", "gprice": 7}] |
+----------------------------------------------------------------------------------------+
(1 row)
```
果然將資料存起來了,注意到存起來的是 JSONB ARRAY.
### 還原
多筆資料聚合,存成JSONB ARRAY,所以還原的方式,是呼叫不同的函數.
jsonb_populate_recordset()
```sql!=
select r.*
from it230117_audit
cross join lateral jsonb_populate_recordset(null::it230117, oldval) r
where action = 'TRUNCATE';
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 2 | 原子筆 | 8 |
| 1 | 2B鉛筆 | 7 |
+----+--------+--------+
(2 rows)
-- 當然可以排序
select r.*
, pg_typeof(r.id)
from it230117_audit
cross join lateral jsonb_populate_recordset(null::it230117, oldval) r
where action = 'TRUNCATE'
order by r.id;
+----+--------+--------+-----------+
| id | gname | gprice | pg_typeof |
+----+--------+--------+-----------+
| 1 | 2B鉛筆 | 7 | integer |
| 2 | 原子筆 | 8 | integer |
+----+--------+--------+-----------+
(2 rows)
```
### PostgreSQL 是怎樣知道對應的資料型態的?
可以觀察到,還原的資料,不是還原成JSON(B) 一些取用的運算子或是函數的text型態,我們後續使用還需要做轉型,而是直接與table對應的.
因為PostgreSQL在我們建立table時,會同時建立對應的type.
這樣我們就可以應用了!
在官網的文件中
https://www.postgresql.org/docs/current/functions-json.html
是另外建立了type, 透過以上的例子與官網資料,可以靈活的應用,除了還原為與原table相同的,也可以另外定義type,將資料轉為我們想要的型態,當然是要能夠正確轉換的.
附帶提一下,在前面truncate時,我加上了 restart identity,可以讓對應的identity 欄位重新計數,就不需要再去找table column 對應的 sequence 來做reset了.
### 復活吧 安蘇納姆
```sql!=
insert into it230117(gname, gprice)
select r.gname, r.gprice
from it230117_audit
cross join lateral jsonb_populate_recordset(null::it230117, oldval) r
where action = 'TRUNCATE'
order by r.id;
select *
from it230117;
+----+--------+--------+
| id | gname | gprice |
+----+--------+--------+
| 1 | 2B鉛筆 | 7 |
| 2 | 原子筆 | 8 |
+----+--------+--------+
(2 rows)
```
## 結語
PostgreSQL提供了許多強大的功能,與其他DB都在持續進步當中.
資訊科技進步日新月異,希望這個小小的心得分享,能夠帶給大家一些參考資訊.
Sign in with Wallet
Connect another wallet