Postgresql学习笔记之——规则系统简述

最新推荐文章于 2024-04-13 13:23:08 发布

Major_ZYH

最新推荐文章于 2024-04-13 13:23:08 发布

阅读量1.4k

点赞数 2

分类专栏： Postgresql 文章标签： postgresql

本文链接：https://blog.youkuaiyun.com/qq_32838955/article/details/105456319

版权

Postgresql 专栏收录该内容

32 篇文章

订阅专栏

本文深入探讨了PostgreSQL中的规则系统，包括SELECT规则用于视图创建，更新规则用于记录操作日志，以及规则与权限、触发器之间的区别。通过实例展示了如何使用规则系统增强数据库功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

规则系统，准确的说就是查询重写规则的系统。从使用上说，规则系统上的一些功能也可以通过函数或者触发器来实现，但是规则与触发器完全不同，他在执行前把用户发过来的SQL语句通过内部的规则定义改变成另一个SQL后再执行的一种方式。

1.select规则

Postgresql的视图是通过select规则来实现的。
创建视图的命令如下：

postgres=# select * from student ;
 student_no | student_name | age | sex 
------------+--------------+-----+-----
          1 | 李四         |  16 | 
          2 | 张三         |  16 | 
          3 | 王二3        |  19 | 
          4 | scott        |  22 | f
          5 | Lily         |  18 | t
          6 | smith        |  20 | f
          7 | Tom          |  19 | f
          8 | Jack         |  20 | f
          9 | kate         |  17 | t
         10 | Finity       |  22 | t
(10 rows)

postgres=# create view v_stu as select * from student ;
CREATE VIEW

创建视图的语句与下面的两个命令时等同的：

create table v_stu (same column list as student);
create rule "_RETURN" AS ON SELECT TO v_stu DO INSTEAD select * from student;

由此可见，视图实际上是一张表，不过是在这张表上加了一个SELECT规则。

SELECT规则使用方法：
先创建一个测试表，如下：

postgres=# create table mytab1(id int,note varchar(40));
CREATE TABLE
postgres=# create table mytab2(id int,note varchar(40));
CREATE TABLE
postgres=# create rule myrule as on select to mytab2 do instead select * from mytab1;
ERROR:  view rule for "mytab2" must be named "_RETURN"

在mytab2上创建SELECT规则，只能是**“INSTEAD SELECT”，即只能用另一个SELECT语句取代。
而且使用自定义的规则名称myrule会提示报错，因为SELECT规则只能使用“_RETURN”**这个名称来命名规则：

postgres=# create rule "_RETURN" as on select to mytab2 do instead select * from mytab1;
CREATE RULE

SELECT规则创建成功后你会发现创建的表mytab2变成了视图！！

2.更新规则

更新规则是指在插入、更新或删除上定义的规则。更新规则与上面的SELECT规则完全不同，它允许用户有更多的操作。
创建更新规则的语法如下：

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

语法中的**“event”**可以取下列值：

（1）SELECT：上述介绍过的SELECT规则，当SQL的查询计划中存在查询表的操作时会重写查询计划。
（2）INSERT：当SQL的查询计划中存在向表中插入数据的操作时会重写查询计划。
（3）UPDATE：当SQL的查询计划中存在向表中更新数据的操作时会重写查询计划。
（4）DELETE：当SQL的查询计划中存在将表中数据删除的操作时会重写查询计划。

而更新规则就包括了INSERT、UPDATE、DELETE。

语法中的**“ALSO”、“INSTEAD”**说明如下：

（1）ALSO：除了执行原本操作外，还执行一些附加操作，这些附加操作由后面的 “command” 指定。
（2）INSTEAD：用后面的 “command” 操作代替原操作。

语法中的**“NOTHING”**表示什么都不执行。

示例
创建一张测试表，然后创建对这张表操作记录的日志表：

postgres=# create table mytab(id int primary key,note varchar(40));
CREATE TABLE
postgres=# create table mytab_log(seq bigserial primary key,
postgres(# oprtype char(1),
postgres(# oprtime timestamp,
postgres(# old_id int,
postgres(# new_id int,
postgres(# old_note text,
postgres(# new_note text);
CREATE TABLE

日志表中有一个序列字段seq作为主键，这样可以记录操作表mytab的先后执行顺序，oprtype记录操作的类型（insert为‘i’、update为‘u’、delete为‘d’），old_id、old_note、new_id、new_note分别记录表mytab中id和note字段的操作过程中的旧值和新值。

创建三个规则来实现日志记录操作的功能：

postgres=# 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
postgres=# CREATE RULE rule_mytab_update AS ON UPDATE TO mytab 
DO ALSO INSERT INTO mytab_log (oprtype, oprtime, old_id, old_note, new_id, new_note)
VALUES('u', now(), old.id, old.note, new.id, new.note);
CREATE RULE
postgres=# 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);
CREATE RULE

然后对表mytab插入、更新和删除数据，看看效果：

postgres=# insert into mytab values(1,'zhang');
INSERT 0 1
postgres=# insert into mytab values(2,'ya');
INSERT 0 1
postgres=# insert into mytab values(3,'hui');
INSERT 0 1
postgres=# update mytab set note='1111' where id=1;
UPDATE 1
postgres=# update mytab set note='2222' where id=2;
UPDATE 1
postgres=# delete from mytab where id='3';
DELETE 1
postgres=# select * from mytab_log;
 seq | oprtype |          oprtime           | old_id | new_id | old_note | new_note 
-----+---------+----------------------------+--------+--------+----------+----------
   1 | i       | 2020-04-12 11:10:45.192234 |        |      1 |          | zhang
   2 | i       | 2020-04-12 11:10:52.378097 |        |      2 |          | ya
   3 | i       | 2020-04-12 11:10:58.722165 |        |      3 |          | hui
   4 | u       | 2020-04-12 11:11:35.044123 |      1 |      1 | zhang    | 1111
   5 | u       | 2020-04-12 11:11:47.06058  |      2 |      2 | ya       | 2222
   6 | d       | 2020-04-12 11:11:58.420889 |      3 |        | hui      | 
(6 rows)

以上结果看出规则的效果，实际上上面日志表中插入操作，旧值总是空的，删除操作新值总是空的，会显得日志表结构不紧凑，可以优化一下，将更新操作拆解为删除旧值然后插入新值的操作，这样日志表就可以省略新旧值的区分了，只需要更改UPDATE规则即可：

postgres=# CREATE RULE rule_mytab_update AS ON UPDATE TO mytab 
DO ALSO (
INSERT INTO mytab_log (oprtype, oprtime, id, ote)
VALUES('d', now(), old.id, old.note);
INSERT INTO mytab_log (oprtype, oprtime, id, note)
VALUES('i', now(),  new.id, new.note);
)
CREATE RULE