用规则在PostgreSQL中创建可查询插入修改删除的表视图

文章由LinuxBoy分享于2019-03-30 06:03:49热评（379）

用规则在PostgreSQL中创建可查询插入修改删除的表视图

具体内容如下：
要注意 new.attribute和old.attribute 与源表、视图属性之间的关系
[root@me root]# su postgres
bash-2.05b$ psql -E -h me.linux.edu.cn mydb1
********* QUERY **********
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'postgres'; COMMIT
**************************

欢迎来到 psql 7.3.2, PostgreSQL 的交互终端.

键入: \copyright 获取发布信息
       \h 获取 SQL 命令的帮助
       \? 获取内部反斜扛命令的帮助
       \g 或者以分号结尾执行查询
       \q 退出

mydb1=# \help
Available help:
ABORT                     CREATE TABLE              EXECUTE
ALTER DATABASE            CREATE TABLE AS           EXPLAIN
ALTER GROUP               CREATE TRIGGER            FETCH
ALTER TABLE               CREATE TYPE               GRANT
ALTER TRIGGER             CREATE USER               INSERT
ALTER USER                CREATE VIEW               LISTEN
ANALYZE                   DEALLOCATE                LOAD
BEGIN                     DECLARE                   LOCK
CHECKPOINT                DELETE                    MOVE
CLOSE                     DROP AGGREGATE            NOTIFY
CLUSTER                   DROP CAST                 PREPARE
COMMENT                   DROP CONVERSION           REINDEX
COMMIT                    DROP DATABASE             RESET
COPY                      DROP DOMAIN               REVOKE
CREATE AGGREGATE          DROP FUNCTION             ROLLBACK
CREATE CAST               DROP GROUP                SELECT
CREATE CONSTRAINT TRIGGER DROP INDEX                SELECT INTO
CREATE CONVERSION         DROP LANGUAGE             SET
CREATE DATABASE           DROP OPERATOR CLASS       SET CONSTRAINTS
CREATE DOMAIN             DROP OPERATOR             SET SESSION AUTHORIZATION
CREATE FUNCTION           DROP RULE                 SET TRANSACTION
CREATE GROUP              DROP SCHEMA               SHOW
CREATE INDEX              DROP SEQUENCE             START TRANSACTION
CREATE LANGUAGE           DROP TABLE                TRUNCATE
CREATE OPERATOR CLASS     DROP TRIGGER              UNLISTEN
CREATE OPERATOR           DROP TYPE                 UPDATE
CREATE RULE               DROP USER                 VACUUM
CREATE SCHEMA             DROP VIEW
CREATE SEQUENCE           END
mydb1=# select * from table_tamplate ;
sname |    sadds     |    sphon     |      semail
--------+--------------+--------------+------------------
小布时 | 纽约州纽约镇 | 010-84452484 | 小布时@美国.纽约
普京   | 莫斯科镇     | 020-12345678 | 普京@俄国.莫斯科
布莱尔 | 伦敦镇       | 030-12345678 | 布莱尔@英国.伦敦
希拉克 | 巴黎镇       | 040-12345678 | 希拉克@法国.巴黎
(4 行)

mydb1=# create table president() inherits(table_tamplate);
CREATE TABLE
mydb1=# select * from president ;
sname | sadds | sphon | semail
-------+-------+-------+--------
(0 行)

mydb1=# \help create view
命令: CREATE VIEW
描述: define a new view
语法:
CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query

mydb1=# create view v_president(name,adds,phon,email) as select p.sname,p.sadds,p.sphon,p.semail from president p;
CREATE VIEW
mydb1=# select * from v_president ;
name | adds | phon | email
------+------+------+-------
(0 行)

mydb1=# insert into v_president values ('金大中','汉城','050-12345678','金大中@韩国.汉城');
错误: Cannot insert into a view
You need an unconditional ON INSERT DO INSTEAD rule
mydb1=# 呵呵，看来视图目前是不能插入数据的，那么，只好用规则了！;

mydb1=# create rule r_insert_v_president AS ON insert TO v_president do instead
mydb1-# insert into president(sname,sadds,sphon,semail) values(new.name,new.adds,new.phon,email);
错误: Attribute "email" not found
mydb1=# create rule r_insert_v_president AS ON insert TO v_president do instead
mydb1-# insert into president(sname,sadds,sphon,semail) values(new.name,new.adds,new.phon,new.email);
CREATE RULE
mydb1=# insert into v_president values ('金大中','汉城','050-12345678','金大中@韩国.汉城');
INSERT 17287 1
mydb1=# select * from v_president ;
name | adds | phon | email
--------+------+--------------+------------------
金大中 | 汉城 | 050-12345678 | 金大中@韩国.汉城
(1 行)

mydb1=# 呵呵，现在可以插入数据了！再来改写、删除！

mydb1=# create rule r_delete_v_president AS ON delete TO v_president do instead
mydb1-# delete from president where sname=old.name;
CREATE RULE
mydb1=# delete from v_president where name ~ '^金';
DELETE 1
mydb1=# select * from v_president ;
name | adds | phon | email
------+------+------+-------
(0 行)

mydb1=# select * from president ;
sname | sadds | sphon | semail
-------+-------+-------+--------
(0 行)

mydb1=# 看来删除是没有问题了！来更新!
mydb1=# create rule r_update_v_president AS ON update TO v_president do instead
mydb1-# update president set sname=new.name,sadds=new.adds,sphon=new.phon,semail=new.email
mydb1-# where sname=new.name;
CREATE RULE
mydb1=# insert into v_president values ('金大中','汉城','050-12345678','金大中@韩国.汉城');
INSERT 17290 1
mydb1=# select * from v_president ;
name | adds | phon | email
--------+------+--------------+------------------
金大中 | 汉城 | 050-12345678 | 金大中@韩国.汉
(1 行)

mydb1=# 好了，现在我们的视图 v

mydb1=# 好了，现在我们的视图可以查询、插入、修改、删除数据了！用规则还可以做触发器！;
mydb1=#

推荐文章：

用规则在PostgreSQL中创建可查询插入修改删除的表视图