postgresql通过创建规则（RULE)实现表记录

1、创建原表mytabcreate table mytab(id int primary key,note text);创建记录表mytab_logcreate 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 1tian=# insert into mytab2 values(2,'2222');INSERT 0 1tian=# insert into mytab2 values(3,'2222');INSERT 0 1tian=# 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 1tian=# delete from mytab2 where id =3;DELETE 1tian=# 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)日志紧凑了许多

本人原博客连接：http://blog.csdn.net/oraclesand/article/details/57083432

BY 海无涯