很早知道PostgreSQL有个创建规则功能，今天终于简单的做了下实验，简单地说，当向PostgreSQL 发出一条SQL时，可以创建一个规则，让PG去执行另外一条命令，举个例子，当创建一个表上的 update 规则时，可以让它什么都不做，当在一个表上创建一个 insert 规则时，也可以让它什么都不做，也可以创建一个查询规则，当查询表A时，可以让它去查询表B，这其实相当于实现了视图的功能，下面是两个测试。

创建 Insert 规则

创建表并插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12

skytf=> create table test_35 (id integer ,name varchar(32));  
CREATE TABLE
skytf=> insert into test_35 values (1,'a');  
INSERT 0 1
skytf=> insert into test_35 values (2,'b');  
INSERT 0 1
skytf=> select * From test_35;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

创建 insert 规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

skytf=> /h create rule  
Command: CREATE RULE  
Description: define a new rewrite rule  
Syntax:  
CREATE [ OR REPLACE ] RULE name AS ON event  
 TO table [ WHERE condition ]  
 DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

skytf=> create or replace rule r_ins_test_35 as on insert to test_35 do instead nothing;CREATE RULE

skytf=> /d test_35  
 Table "skytf.test_35"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Rules:  
 r_ins_test_35 AS  
 ON INSERT TO test_35 DO INSTEAD NOTHING

验证 Insert 规则

1
2
3
4
5
6
7
8

skytf=> insert into test_35 values (3,'c');  
INSERT 0 0
skytf=> select * From test_35;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

说明：从上面可以看出，当在表 test_35 上创建了 insert 规则后，向此表上插入数据无效。

创建 Update 规则

删除原来 insert 规则

1
2
3
4
5
6
7
8
9
10
11
12

skytf=> /d test_35;  
 Table "skytf.test_35"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Rules:  
 r_ins_test_35 AS  
 ON INSERT TO test_35 DO INSTEAD NOTHING

skytf=> drop rule r_ins_test_35 on test_35;  
DROP RULE

创建 update 规则

1
2
3
4
5
6
7
8
9
10
11
12

skytf=> create rule r_upd_test_35 as on update to test_35 do instead nothing;  
CREATE RULE

skytf=> /d test_35  
 Table "skytf.test_35"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Rules:  
 r_upd_test_35 AS  
 ON UPDATE TO test_35 DO INSTEAD NOTHING

验证 Update 规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

skytf=> select * from test_35;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

skytf=> update test_35 set name='francs' where id=1;  
UPDATE 0

skytf=> select * From test_35;  
id | name  
----+------  
 1 | a  
 2 | b  
(2 rows)

说明：从上面可以看出，当在表 test_35 上创建了 update 规则以后，更新表 test_35 数据时无效。

附一: Create Rule 语法

Name
CREATE RULE ― define a new rewrite rule
Synopsis
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command … ) }

name
The name of a rule to create. This must be distinct from the name of any other rule for the same table. Multiple rules on the same table and same event type are applied in alphabetical name order.

event
The event is one of SELECT, INSERT, UPDATE, or DELETE.

table
The name (optionally schema-qualified) of the table or view the rule applies to.

condition
Any SQL conditional expression (returning boolean). The condition expression cannot refer to any tables except NEW and OLD, and cannot contain aggregate functions.

INSTEAD
INSTEAD indicates that the commands should be executed instead of the original command.

ALSO
ALSO indicates that the commands should be executed in addition to the original command.
If neither ALSO nor INSTEAD is specified, ALSO is the default.
command
The command or commands that make up the rule action. Valid commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

附二: Drop Rule 语法

Name
DROP RULE ― remove a rewrite rule

Synopsis
DROP RULE [ IF EXISTS ] name ON relation [ CASCADE | RESTRICT ]
Description
DROP RULE drops a rewrite rule.
Parameters
IF EXISTS
Do not throw an error if the rule does not exist. A notice is issued in this case.

name
The name of the rule to drop.

relation
The name (optionally schema-qualified) of the table or view that the rule applies to.

CASCADE
Automatically drop objects that depend on the rule.

RESTRICT
Refuse to drop the rule if any objects depend on it. This is the default.

Examples
To drop the rewrite rule newrule:
DROP RULE newrule ON mytable;