下边是一个PostgreSQL视图更新功能的例子,主要思想是通过在视图上创建Rule来完成对底层表的更新.-- 创建人员表 -- CREATE table people ( person_id SERIAL PRIMARY KEY, inits text, fname text ); -- 创建国家表 CREATE table states ( state text PRIMARY KEY, state_long text );-- 创建地址表 CREATE table addresses ( person_id int REFERENCES people (person_id) ON DELETE CASCADE, city text, state text REFERENCES states (state), country text, zip text );-- -- 创建我们将要更新的视图 -- CREATE VIEW people_full AS SELECT p.person_id, p.inits, p.fname, a.city, a.state, s.state_long, a.country, a.zip FROM people p JOIN addresses a USING (person_id) JOIN states s USING (state);-- 创建视图会自动创建如下的Rule -- -- -- CREATE RULE "_RETURN" AS ON -- SELECT TO people_full DO INSTEAD -- SELECT p.person_id, p.inits, p.fname, a.city, a.state, s.state_long, -- a.country, a.zip FROM -- ((people p JOIN addresses a USING (person_id)) JOIN states s USING (state));-- -- 创建简单的Rule,它只更新People表 -- CREATE RULE "upd_people_full" AS ON UPDATE TO people_full DO INSTEAD UPDATE people set inits=NEW.inits, fname=NEW.fname WHERE person_id=OLD.person_id; CREATE RULE "ins_people_full" AS ON INSERT TO people_full DO INSTEAD INSERT INTO people (person_id, inits, fname) VALUES (NEW.person_id, NEW.inits, NEW.fname); CREATE RULE "del_people_full" AS ON DELETE TO people_full DO INSTEAD DELETE FROM people WHERE person_id=OLD.person_id; -- -- 删除刚才创建的Rule -- DROP RULE "upd_people_full" on people_full; DROP RULE "ins_people_full" on people_full;-- -- 重新创建Rule,它可以删除地址表. -- CREATE RULE "upd_people_full" as ON UPDATE TO people_full DO INSTEAD ( UPDATE people set inits=NEW.inits, fname=NEW.fname WHERE person_id=OLD.person_id; UPDATE addresses set city=NEW.city, state=NEW.state, zip=NEW.zip WHERE person_id=OLD.person_id; );CREATE RULE "ins_people_full" as ON INSERT TO people_full DO INSTEAD ( INSERT INTO people (person_id, inits, fname) VALUES (nextval('people_person_id_seq'),NEW.inits, NEW.fname); INSERT INTO addresses (person_id,city, state, zip) VALUES (currval('people_person_id_seq'), NEW.city, NEW.state, NEW.zip ); );
http://bbs.pgsqldb.com/index.php?t=thread&frm_id=5&rid=&S=e580e616c52793f11417ca1c0283ecae
--
CREATE table people (
person_id SERIAL PRIMARY KEY,
inits text,
fname text
);
-- 创建国家表
CREATE table states (
state text PRIMARY KEY,
state_long text
);-- 创建地址表
CREATE table addresses (
person_id int REFERENCES people (person_id)
ON DELETE CASCADE,
city text,
state text REFERENCES states (state),
country text,
zip text
);--
-- 创建我们将要更新的视图
--
CREATE VIEW people_full AS
SELECT p.person_id, p.inits, p.fname, a.city,
a.state, s.state_long, a.country, a.zip
FROM people p JOIN addresses a USING (person_id)
JOIN states s USING (state);-- 创建视图会自动创建如下的Rule
--
--
-- CREATE RULE "_RETURN" AS ON
-- SELECT TO people_full DO INSTEAD
-- SELECT p.person_id, p.inits, p.fname, a.city, a.state, s.state_long,
-- a.country, a.zip FROM
-- ((people p JOIN addresses a USING (person_id)) JOIN states s USING (state));--
-- 创建简单的Rule,它只更新People表
--
CREATE RULE "upd_people_full" AS ON
UPDATE TO people_full DO INSTEAD
UPDATE people set inits=NEW.inits, fname=NEW.fname
WHERE person_id=OLD.person_id; CREATE RULE "ins_people_full" AS ON
INSERT TO people_full DO INSTEAD
INSERT INTO people (person_id, inits, fname)
VALUES (NEW.person_id, NEW.inits, NEW.fname);
CREATE RULE "del_people_full" AS ON
DELETE TO people_full DO INSTEAD
DELETE FROM people WHERE person_id=OLD.person_id;
--
-- 删除刚才创建的Rule
--
DROP RULE "upd_people_full" on people_full;
DROP RULE "ins_people_full" on people_full;--
-- 重新创建Rule,它可以删除地址表.
--
CREATE RULE "upd_people_full" as ON
UPDATE TO people_full DO INSTEAD
(
UPDATE people set inits=NEW.inits, fname=NEW.fname
WHERE person_id=OLD.person_id;
UPDATE addresses set city=NEW.city, state=NEW.state, zip=NEW.zip
WHERE person_id=OLD.person_id;
);CREATE RULE "ins_people_full" as ON
INSERT TO people_full DO INSTEAD
(
INSERT INTO people (person_id, inits, fname)
VALUES (nextval('people_person_id_seq'),NEW.inits, NEW.fname);
INSERT INTO addresses (person_id,city, state, zip)
VALUES (currval('people_person_id_seq'), NEW.city, NEW.state, NEW.zip );
);