postgresql通過建立規則（RULE)實現表記錄

1、建立原表mytab
create table mytab(id int primary key,note text);
建立記錄表mytab_log
create table mytab_log(seq bigserial primary key,
oprtype char(1),
oprtime timestamp,
old_id int,
new_id int,
old_note text,
new_note text);


2、建立規則
 create rule rule_mytab_insert as on insert
 to mytab
 do also insert into mytab_log(oprtype,oprtime,new_id,new_note) values('i',now(),new.id,new.note);
 
 create rule rule_mytab_update as on update
 to mytab
 do also insert into mytab_log(oprtype,oprtime,old_id,new_id,old_note,new_note) values('u',now(),old.id,new.id,old.note,new.note);
  （更新資料時實現記錄舊資料和新資料，型別為”u“）
 create rule rule_mytab_delete as on delete
 to mytab
 do also insert into mytab_log(oprtype,oprtime,old_id,old_note) values('d',now(),old.id,old.note);


insert into mytab values(1,'abc');
insert into mytab values(2,'bac');
insert into mytab values(3,'cab');


tian=# select * from mytab;
 id | note 
----+------
  1 | abc
  2 | bac
  3 | cab
(3 rows)


update mytab set note='ccc' where id=3;
delete from mytab where id=3;


tian=# select * from mytab;
 id | note 
----+------
  1 | abc
  2 | bac
(2 rows)
3、檢視資料記錄
tian=# select * from mytab_log;
 seq | oprtype |          oprtime           | old_id | new_id | old_note | new_note 
-----+---------+----------------------------+--------+--------+----------+----------
   1 | i       | 2017-02-25 15:19:20.932179 |        |      1 |          | abc
   2 | i       | 2017-02-25 15:19:26.350333 |        |      2 |          | bac
   3 | i       | 2017-02-25 15:19:31.822498 |        |      3 |          | cab
   4 | u       | 2017-02-25 15:19:50.297173 |      3 |      3 | cab      | ccc
   5 | d       | 2017-02-25 15:19:56.557354 |      3 |        | ccc      | 
(5 rows)


方式二：【改變更新記錄資料的方式】
create table mytab2(id int primary key,note text);


create table mytab_log2(
seq bigserial primary key,
oprtype char(1),
oprtime timestamp,
id int,
note text);


create rule rule_mytab_insert2 as on insert
 to mytab2
 do also insert into mytab_log2(oprtype,oprtime,id,note) values('i',now(),new.id,new.note);
 
 create rule rule_mytab_update2 as on update
 to mytab2
 do also (insert into mytab_log2(oprtype,oprtime,id, note) values('d',now(),old.id,old.note);
 insert into mytab_log2(oprtype,oprtime,id, note) values('i',now(),new.id,new.note));
 （更新資料時通過分解為刪除和插入兩部分實現記錄舊資料和新資料，型別為”d“和”i“）
 create rule rule_mytab_delete2 as on delete
 to mytab2
 do also insert into mytab_log2(oprtype,oprtime,id,note) values('d',now(),old.id,old.note);
 
 
tian=# insert into mytab2 values(1,'1111');
INSERT 0 1
tian=# insert into mytab2 values(2,'2222');
INSERT 0 1
tian=# insert into mytab2 values(3,'2222');
INSERT 0 1
tian=# select * from mytab_log2;
 seq | oprtype |          oprtime           | id | note 
-----+---------+----------------------------+----+------
   1 | i       | 2017-02-25 15:42:07.852126 |  1 | 1111
   2 | i       | 2017-02-25 15:42:15.638697 |  2 | 2222
   3 | i       | 2017-02-25 15:42:19.748022 |  3 | 2222
   
   
tian=# update mytab2 set note='3333' where id = 3;
UPDATE 1
tian=# delete from mytab2 where id =3;
DELETE 1
tian=# select * from mytab_log2;
 seq | oprtype |          oprtime           | id | note 
-----+---------+----------------------------+----+------
   1 | i       | 2017-02-25 15:42:07.852126 |  1 | 1111
   2 | i       | 2017-02-25 15:42:15.638697 |  2 | 2222
   3 | i       | 2017-02-25 15:42:19.748022 |  3 | 2222
   4 | d       | 2017-02-25 15:43:18.741909 |  3 | 2222
   5 | i       | 2017-02-25 15:43:18.741909 |  3 | 3333
   6 | d       | 2017-02-25 15:44:14.097896 |  3 | 3333
(6 rows)
日誌緊湊了許多

BY  海無涯