PostgreSQL RULE

前端之家收集整理的这篇文章主要介绍了PostgreSQL RULE前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

RULE语法:

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: 触发事件,可以是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.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.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,new_value)
  VALUES ('update'::character varying,'::text) || old.goods_id),'::text) || new.goods_id))
可以看到,rule不像function,触发器一样独立存在的,而是依附于表上,当你把表删了,相应的rule就一起被删了。

3. 测试:

> insert:

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:
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,102  | 2015-04-06 17:15:28.150866
  3 | update  | 1,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,201  | 2015-04-06 17:18:07.127828
  4 | delete  | 1,201  |           | 2015-04-06 17:19:20.672241
(4 rows)

完。

猜你在找的Postgre SQL相关文章