ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL:RULE

PostgreSQL:RULE

PostgreSQL 作者:Ryan_Bai 时间:2020-12-18 20:11:47 0 删除 编辑

语法

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31490526/viewspace-2743531/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PostgreSQL:EXPLAIN
请登录后发表评论 登录
全部评论
Oracle ACE Associate; OCMU 用户组成员; Oracle 10g OCE、OCA、OCP; Oracle 11g OCP、OCM; MySQL 5.6 OCP; Oracle 11g OCP讲师; PostgreSQL PGCE 获得者;

注册时间:2017-09-18

  • 博文量
    226
  • 访问量
    261369