注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: introduction of creating rule  

2011-05-09 11:32:15|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

  

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

一创建 insert 规则
--1 创建表并插入测试数据
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)

--2 创建 insert 规则
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

--3 验证:插入数据测试。
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 规则
--1 删除原来 insert 规则
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

--2 创建 update 规则
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

--3 验证 update 规则
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;


 

  评论这张
 
阅读(25991)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016