PostgreSQL 审计 - PostgreSQL 9.3 Allow OLD and NEW in multi-row VALUES within rules

背景

Allow OLD and NEW in multi-row VALUES within rules.  
Now that we have LATERAL, it's fairly painless to allow this case, which  
was left as a TODO in the original multi-row VALUES implementation.  

简单来说就是允许规则触发的sql中在多行输入时使用new和old关键字.

rule的详细用法参见 :

http://www.postgresql.org/docs/devel/static/sql-createrule.html

测试

取自regress test.

 937 --  
 938 -- check multi-rule VALUES in rules  
 939 --  
 940   
 941 create table rules_src(f1 int, f2 int);  
 942 create table rules_log(f1 int, f2 int, tag text);  
 943 insert into rules_src values(1,2), (11,12);  
 944 create rule r1 as on update to rules_src do also  
 945   insert into rules_log values(old.*, 'old'), (new.*, 'new');  
 946 update rules_src set f2 = f2 + 1;  
 947 update rules_src set f2 = f2 * 10;  
 948 select * from rules_src;  
 949 select * from rules_log;  
 950 create rule r2 as on update to rules_src do also  
 951   values(old.*, 'old'), (new.*, 'new');  
 952 update rules_src set f2 = f2 / 10;  
 953 select * from rules_src;  
 954 select * from rules_log;  
 955 \d+ rules_src  

测试结果 :

digoal=# create table rules_src(f1 int, f2 int);  
CREATE TABLE  
digoal=# create table rules_log(f1 int, f2 int, tag text);  
CREATE TABLE  
digoal=# insert into rules_src values(1,2), (11,12);  
INSERT 0 2  
digoal=# create rule r1 as on update to rules_src do also  
postgres-#   insert into rules_log values(old.*, 'old'), (new.*, 'new');  
CREATE RULE  
digoal=# update rules_src set f2 = f2 + 1;  
UPDATE 2  
digoal=# update rules_src set f2 = f2 * 10;  
UPDATE 2  
digoal=# select * from rules_src;  
 f1 | f2    
----+-----  
  1 |  30  
 11 | 130  
(2 rows)  
  
digoal=# select * from rules_log;  
 f1 | f2  | tag   
----+-----+-----  
  1 |   2 | old  
  1 |   3 | new  
 11 |  12 | old  
 11 |  13 | new  
  1 |   3 | old  
  1 |  30 | new  
 11 |  13 | old  
 11 | 130 | new  
(8 rows)  
  
digoal=# create rule r2 as on update to rules_src do also  
postgres-#   values(old.*, 'old'), (new.*, 'new');  
CREATE RULE  

这个有点意思, 相当于返回values中的内容. 类似update returning…

digoal=# update rules_src set f2 = f2 / 10;  
 column1 | column2 | column3   
---------+---------+---------  
       1 |      30 | old  
       1 |       3 | new  
      11 |     130 | old  
      11 |      13 | new  
(4 rows)  
  
UPDATE 2  
digoal=# select * from rules_src;  
 f1 | f2   
----+----  
  1 |  3  
 11 | 13  
(2 rows)  
  
digoal=# select * from rules_log;  
 f1 | f2  | tag   
----+-----+-----  
  1 |   2 | old  
  1 |   3 | new  
 11 |  12 | old  
 11 |  13 | new  
  1 |   3 | old  
  1 |  30 | new  
 11 |  13 | old  
 11 | 130 | new  
  1 |  30 | old  
  1 |   3 | new  
 11 | 130 | old  
 11 |  13 | new  
(12 rows)  
  
digoal=# \d+ rules_src  
                      Table "public.rules_src"  
 Column |  Type   | Modifiers | Storage | Stats target | Description   
--------+---------+-----------+---------+--------------+-------------  
 f1     | integer |           | plain   |              |   
 f2     | integer |           | plain   |              |   
Rules:  
    r1 AS  
    ON UPDATE TO rules_src DO  INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)  
    r2 AS  
    ON UPDATE TO rules_src DO  VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)  
Has OIDs: no  

参考

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=092d7ded29f36b0539046b23b81b9f0bf2d637f1

2. http://www.postgresql.org/docs/devel/static/sql-createrule.html

digoal’s 大量PostgreSQL文章入口