pgsql 创建日志表（创建规则）

create table mytab(id int primary key, note text);
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);

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);

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);

SELECT * from mytab_log;

insert into mytab values(1, '1111111111111');

update mytab set note='aaaaaaaaaa' WHERE id = 1;

delete from mytab;