当前位置: 首页 > postgresql > 正文

PostgreSQL Rules 的简单介绍 – Making entries which can’t be altered

原文请猛点 这里,下面是经过本人翻译整理的版本
by Justin Clift

简介

玩过Postgresql的Rule规则吗?如果没有的话,看看下面的介绍,也许你会喜欢这种操作方式

postgresql的 rule 规则被用来在查询执行前改变查询的本来目的。比如:如果你有一张所有人都可以操作的表,但是:你又想让其中特定的三条记录不会被任何人误操作更改或者删除,你可以这样做:

1. 建立示例表:

foo=>CREATE TABLE gift_certificates (idnum serial, person varchar(20), amount float4);
NOTICE: CREATE TABLE will create implicit sequence 'gift_certificates_idnum_seq' for SERIAL column 'gift_certificates.idnum'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'gift_certificates_idnum_key' for table 'gift_certificates'
CREATE
2. 整点数据先:
foo=>insert into gift_certificates (person, amount) values ('Justin', 200);
INSERT 51564057 1
foo=>insert into gift_certificates (person, amount) values ('Tom', 200);
INSERT 51564059 1
foo=>insert into gift_certificates (person, amount) values ('Richard', 200);
INSERT 51564062 1
foo=>insert into gift_certificates (person, amount) values ('Peter', 200);
INSERT 51564065 1
foo=>insert into gift_certificates (person, amount) values ('Bruce', 200);
INSERT 51564066 1
foo=>insert into gift_certificates (person, amount) values ('Marc', 200);
INSERT 51564067 1
foo=>insert into gift_certificates (person, amount) values ('Vince', 200);

foo=>select * from gift_certificates;
 idnum |  person | amount
-------+---------+--------
     1 | Justin  | 200
     2 | Tom     | 200
     3 | Richard | 200
     4 | Peter   | 200
     5 | Bruce   | 200
     6 | Marc    | 200
     7 | Vince   | 200
(7 rows)
3. 接下来,建立如下两个规则:
foo=>CREATE RULE prot_gc_upd AS ON UPDATE TO gift_certificates WHERE old.idnum < 4 DO INSTEAD nothing;
CREATE
foo=>CREATE RULE prot_gc_del AS ON DELETE TO gift_certificates WHERE old.idnum < 4 DO INSTEAD nothing;
CREATE

Noting是一个神奇的pstgresql规则,可以将任何对于您想要保护的三条记录的UPDATE / DELETE操作无害化(DO NOTING INSTEAD);

放心:除了对于这三条记录的update/delete操作,其他任何对于该表的操作不受影响

4. OK, 接下来测试一把
foo=>update gift_certificates set person = 'Justin2' where idnum = 1;
UPDATE 0
foo=>update gift_certificates set person = 'Justin2' where idnum = 2;
UPDATE 0
foo=>update gift_certificates set person = 'Justin2' where idnum = 3;
UPDATE 0
foo=>update gift_certificates set person = 'Justin2' where idnum = 4;
UPDATE 1

瞅瞅,看有木有生效:

foo=>select * from gift_certificates;
 idnum |  person | amount
-------+---------+--------
     1 | Justin  | 200
     2 | Tom     | 200
     3 | Richard | 200
     5 | Bruce   | 200
     6 | Marc    | 200
     7 | Vince   | 200
     4 | Justin2 | 200
(7 rows)

foo=>

怎么样?再试试delete操作:

foo=>delete from gift_certificates;
DELETE 4
foo=>select * from gift_certificates;
 idnum |  person | amount
-------+---------+--------
     1 | Justin  | 200
     2 | Tom     | 200
     3 | Richard | 200
(3 rows)

foo=>

神奇吧?

希望这个功能可以对你有用

更多资料,参考《PostgreSQL Programmer’s Guide, “The Postgres Rule System”

    分享到:

本文固定链接: http://klwang.info/introduction-to-postgresql-rules-making-entries-which-cant-be-altered/ | 数据库|Linux|软件开发

该日志由 klwang 于2013年03月14日发表在 postgresql 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: PostgreSQL Rules 的简单介绍 – Making entries which can’t be altered | 数据库|Linux|软件开发

PostgreSQL Rules 的简单介绍 – Making entries which can’t be altered:等您坐沙发呢!

发表评论

*
快捷键:Ctrl+Enter