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

具体内容如下：要注意 new.attribute和old.attribute 与源表、视图属性之间的关系[root@me root]# su postgresbash-2.05b$ psql

具体内容如下：
要注意 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=# select * from president ;
sname | sadds | sphon | semail
--------+-------+--------------+------------------
金大中 | 汉城 | 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=# select * from president ;
sname | sadds | sphon | semail
--------+-------+--------------+------------------
金大中 | 汉城 | 050-12345678 | 金大中@韩国.汉
(1 行)

mydb1=# update v_president set email='金大中@汉城.韩国' where;
UPDATE 1
mydb1=# select * from v_president ;
name | adds | phon | email
--------+------+--------------+------------------
金大中 | 汉城 | 050-12345678 | 金大中@汉城.韩国
(1 行)

mydb1=# select * from president ;
sname | sadds | sphon | semail
--------+-------+--------------+------------------
金大中 | 汉城 | 050-12345678 | 金大中@汉城.韩国
(1 行)

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

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