语法
postgres=# \h create rule
Command: CREATE RULE
Description: define a new rewrite rule
Syntax:
CREATE [ OR REPLACE ] RULE name AS ON event
TO table_name [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
event： 触发事件
SELECT：当 SQL 的查询计划中存在查询表的操作时会重写查询计划。
INSERT：当 SQL 的查询计划中存在向表中插入数据的操作时会重写查询计划。
UPDATE：当 SQL 的查询计划中存在向表中更新数据的操作时会重写查询计划
DELETE：当 SQL 的查询计划中存在向表中数据删除的操作时会重写查询计划
ALSO：被触发的表插入一条数据的数据，触发在另一个表里面也插入一条（日志记录）。
INSTEAD：向被触发的表插入一条数据的时候，用插入到其他表来代替（分区表）。
NOTHING：表示什么都不执行
权限
规则是从属于表或试图的。如果一张表属于一个用户，则这张表上的所有规则都属于这个用户。
对比
规则系统是通过查询重写来实现的，修改查询或生成额外的查询不容易理解
触发器是为每一行都触发执行一次，但从概念上比规则的方法简单，更容易让新手掌握
实例
准备
建表（订单表+订单日志表）
postgres=# create table orders(id serial,name character varying,goods_id integer);
CREATE TABLE
postgres=# \d orders
Table "public.orders"
Column | Type|Modifiers
----------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('orders_id_seq'::regclass)
name | character varying |
goods_id | integer
postgres=# create table orders_log(id serial,do_type character varying,old_value character varying,new_value character varying,do_time timestamp without time zone not null default now());
CREATE TABLE
postgres=# \d orders_log
Table "public.orders_log"
Column |Type |Modifiers
-----------+-----------------------------+---------------------------------------------------------
id| integer | not null default nextval('orders_log_id_seq'::regclass)
do_type | character varying |
old_value | character varying |
new_value | character varying |
do_time | timestamp without time zone | not null default now()
实验
create rule on orders table(rule的作用是将对orders表的操作以日志的方式插入到 orders_log 表中)
记录 insert 操作的 rule
create or replace rule rule_orders_insert_log as on insert to orders
do also insert into orders_log(do_type,new_value)
values('insert',new.id||','||new.name||','||new.goods_id);
记录 update 操作的 rule
create or replace rule rule_orders_update_log as on update to orders
do also insert into orders_log(do_type,old_value,new_value)
values('update',old.id||','||old.name||','||old.goods_id,new.id||','||new.name||','||new.goods_id);
记录 delete 操作的 rule
create or replace rule rule_orders_delete_log as on delete to orders
do also insert into orders_log(do_type,old_value)
values('delete',old.id||','||old.name||','||old.goods_id);
rule 不像 function，触发器一样独立存在的，而是依附于表上，当你把表删了，相应的rule就一起被删了。
postgres=# \d orders
Table "public.orders"
Column| Type|Modifiers
----------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('orders_id_seq'::regclass)
name | character varying |
goods_id | integer |
Rules:
rule_orders_delete_log AS
ON DELETE TO orders DOINSERT INTO orders_log (do_type, old_value)
VALUES ('delete'::character varying, ((((old.id || ','::text) || old.name::text) || ','::text) || old.goods_id))
rule_orders_insert_log AS
ON INSERT TO orders DOINSERT INTO orders_log (do_type, new_value)
VALUES ('insert'::character varying, ((((new.id || ','::text) || new.name::text) || ','::text) || new.goods_id))
rule_orders_update_log AS
ON UPDATE TO orders DOINSERT INTO orders_log (do_type, old_value, new_value)
VALUES ('update'::character varying, ((((old.id || ','::text) || old.name::text) || ','::text) || old.goods_id), ((((new.id || ','::text) || new.name::text) || ','::text) || new.goods_id))
使用验证
插入验证
postgres=# insert into orders(name,goods_id) values('aa',101);
INSERT 0 1
postgres=# insert into orders(name,goods_id) values('bb',102);
INSERT 0 1
查看 orders 表和 orders_log 中的数据
postgres=# select * from orders; id | name | goods_id ----+------+---------- 1 | aa |101 3 | bb |102 (2 rows) postgres=# select * from orders_log; id | do_type | old_value | new_value |do_time ----+---------+-----------+-----------+---------------------------- 1 | insert| | 2,aa,101| 2015-04-06 17:15:20.088412 2 | insert| | 4,bb,102| 2015-04-06 17:15:28.150866 (2 rows)
修改验证
update orders set name ='cc',goods_id=201 where id=1;
查看orders表和orders_log中的数据：
postgres=# select * from orders; id | name | goods_id ---+------+---------- 3 | bb |102 1 | cc |201 (2 rows) postgres=# select * from orders_log; id | do_type | old_value | new_value |do_time ----+---------+-----------+-----------+---------------------------- 1 | insert| | 2,aa,101| 2015-04-06 17:15:20.088412 2 | insert| | 4,bb,102| 2015-04-06 17:15:28.150866 3 | update| 1,aa,101| 1,cc,201| 2015-04-06 17:18:07.127828 (3 rows)
删除验证
delete from orders where id=1;
查看orders表和orders_log中的数据
postgres=# select * from orders; id | name | goods_id ----+------+---------- 3 | bb |102 (1 row) postgres=# select * from orders_log; id | do_type | old_value | new_value |do_time ----+---------+-----------+-----------+---------------------------- 1 | insert| | 2,aa,101| 2015-04-06 17:15:20.088412 2 | insert| | 4,bb,102| 2015-04-06 17:15:28.150866 3 | update| 1,aa,101| 1,cc,201| 2015-04-06 17:18:07.127828 4 | delete| 1,cc,201| | 2015-04-06 17:19:20.672241 (4 rows)
大部分内容转自：https://blog.csdn.net/luojinbai/article/details/44903589
