Postgresql - The Rules System

规则系统（更准确地说，查询重写规则系统）与存储过程和触发器完全不同。它修改查询以考虑规则，然后将修改后的查询传递给查询计划器进行计划和执行。它非常强大，可以用于许多事情，如查询语言过程、视图和版本。

1. Query Tree

规则系统位于解析器和规划器之间。它获取解析器的输出、一个查询树和用户定义的重写规则，这些规则也是带有一些额外信息的查询树，并创建零个或多个查询树。因此，它的输入和输出总是解析器本身可以产生的，因此，它看到的任何东西基本上都可以表示为SQL语句。

现在什么是查询树？它是一个SQL语句的内部表示，其中构建它的单个部分单独存储。如果设置了配置参数 debug_print_parse, debug_print_rewritten, or debug_print_plan ，则可以在服务器日志中显示这些查询树。规则操作也作为查询树存储在系统目录pg_rewrite中。它们的格式与日志输出不同，但包含完全相同的信息。

the command type

the range table

the result relation

the target list

the qualification

the join tree

the others

2. Views and the Rule System

mytest=# create table test_view (id int, col1 varchar(100));
CREATE TABLE
mytest=# create rule "_RETURN" as on select to test_view do instead select * from test;
CREATE RULE
mytest=# insert into test_view values (1,'a');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
mytest=# insert into test_view values (1001,'a');
INSERT 0 1
mytest=# select * from test where id = 1001;
  id  | col1
------+------
 1001 | a
(1 row)
# 查看relation，发现建的表变成视图
mytest=# \d
                         List of relations
 Schema |                 Name                 |   Type   |  Owner
--------+--------------------------------------+----------+---------
 public | test                                 | table    | dbadmin
 public | test_view                            | view     | dbadmin
.....

# 使用pg_dump导出schema，查看这个table 的创建语句变成 view
--
-- Name: test_view; Type: VIEW; Schema: public; Owner: dbadmin
--
CREATE VIEW public.test_view AS
 SELECT test.id,
    test.col1
   FROM public.test;

mytest=# select * from pg_views where viewname = 'test_view';
 schemaname | viewname  | viewowner |    definition
------------+-----------+-----------+------------------
 public     | test_view | dbadmin   |  SELECT test.id,+
            |           |           |     test.col1   +
            |           |           |    FROM test;
(1 row)

所以 rule 包括 view
           

所以 rule 包括 view

3.

rule 规则还包括 Materialized view，但无法通过create rule 创建 Materialized view

Materialized view 内容不在这里赘述。

4. Rules on INSERT, UPDATE, DELETE

Rules that are defined on INSERT, UPDATE, and DELETE are significantly different from the view rules described in the previous section. First, their CREATE RULE command allows more:

• They are allowed to have no action.
• They can have multiple actions.
• They can be INSTEAD or ALSO (the default).
• The pseudorelations NEW and OLD become useful.
• They can have rule qualifications.

Second, they don't modify the query tree in place. Instead they create zero or more new query trees and can throw away the original one.

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

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

mytest=# UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
UPDATE 1
mytest=# select * from shoelace_log;
 sl_name | sl_avail | log_who |          log_when
---------+----------+---------+----------------------------
 sl7     |        6 | dbadmin | 2019-05-24 05:39:30.889811
(1 row)

mytest=# UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
UPDATE 1
mytest=# select * from shoelace_log;
 sl_name | sl_avail | log_who |          log_when
---------+----------+---------+----------------------------
 sl7     |        6 | dbadmin | 2019-05-24 05:39:30.889811
(1 row)
# 更新两次的时候不记录，因为rule中写了 NEW.sl_avail <> OLD.sl_avail，当旧值不等于新值的时候才会记录。


mytest=# create rule test_ins_rule AS ON insert to test do instead insert into test0524 values (new.id, new.col1);
CREATE RULE
mytest=# insert into test values(99999,'aaaaa');
INSERT 0 1
mytest=# select * from test0524 where id > 90000;
  id   | col1  | col2 |           c_time
-------+-------+------+----------------------------
 99999 | aaaaa |      | 2019-05-24 06:52:41.558857
(1 row)

mytest=# select * from test where id > 90000;
 id | col1
----+------
(0 rows)
           

执行了后边代替所做的事情。