PostgreSQL RULE

来源：互联网发布：sysctl 优化编辑：程序博客网时间：2024/05/20 20:19

RULE语法：

postgres=# \h create ruleCommand:     CREATE RULEDescription: define a new rewrite ruleSyntax:CREATE [ OR REPLACE ] RULE name AS ON event    TO table_name [ WHERE condition ]    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

event：触发事件，可以是insert，update，delete等
ALSO：比如，被触发的表插入一条数据的数据，触发在另一个表里面也插入一条（日志记录）。

INSTEAD：比如，向被触发的表插入一条数据的时候，用插入到其他表来代替（分区表）。

实例

1. 建表（订单表+订单日志表）

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

2. create rule on orders table(rule的作用是将对orders表的操作以日志的方式插入到orders_log表中)
> 记录insert操作的rule：

postgres=# 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);CREATE RULE

> 记录update操作的rule：

postgres=# 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);CREATE RULE

> 记录delete操作的rule：

postgres=# 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);CREATE RULE

查看orders表结构：

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 DO  INSERT 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 DO  INSERT 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 DO  INSERT 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))

可以看到，rule不像function，触发器一样独立存在的，而是依附于表上，当你把表删了，相应的rule就一起被删了。

3. 测试：

>　insert：

postgres=# insert into orders(name,goods_id) values('aa',101);INSERT 0 1postgres=# 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：

postgres=# update orders set name ='cc',goods_id=201 where id=1;UPDATE 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：

postgres=# delete from orders where id=1;DELETE 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)

完。

0 0