PostgreSQL 麯䔇蓖商滇锔誺蓇彍係䂘準垂䯄䔇㔗婋麵䔇变傴
CREATE VIEW myview AS SELECT * FROM mytab;
垂鍙婪启婋麵婴溇变傴
CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
幋閘䂺凹澇橬寺彆啹婺認儌滇 CREATE VIEW 变傴婘喙鄘垂鍙欓臯䔇喙垹㔗認湙啔橬婔底蘘嘩䫘㔗噽婺幋婔儌滇婘 PostgreSQL 係䂘臘麯䔇蓖商䔇媇敇婯婔轸臘䔇媇敇垯噘婔湙㔗欔傖凹庯昖臵彖悊単準臘臘启蓖商幋閘垯噘澇橬寺彆㔗垄傸滇劯湙䔇庋䬷噿係㔗
ON SELECT 䔇蓇彍婘橔劯婔準庫䫘庯欔橬昖臵巻攘䂍庺䔇滇婔溇 INSERT, UPDATE, DELETE 变傴㔗蔯婫婯噽垄蓇彍橬婉劯䔇臺懟闼儌滇垄傸婘䯄婺媞櫹昖臵湏蔯婉滇录傺婔婻桄䔇昖臵湏㔗欔傖噽傋䂉 SELECT 蓇彍㔗
䕞嬉婔婻 ON SELECT 蓇彍麯埻脘橬婔婻媘嘩蔯婫垄媙釂滇婔婻方溇傽䔇 INSTEAD(埡傼)䔇 SELECT 媘嘩㔗橬認婻鍊彽滇婺庖傴蓇彍垬噘彄捞锔䫘潙幘埇傖欷嚔垄傸幽婫垄鍊彽 ON SELECT 蓇彍嘪幋臯婺䌂嚚臘商㔗
橸桺懼䔇冋床滇婴婻誂毖蓖商垄傸啔婔底誊䞖幽婫啹溴嚔潬埪彄敘崔蓖商䔇嘪䫘㔗認婴婻蓖商幋婔䘉劯儖彷䫘凹 INSERT, UPDATE, DELETE 淉嘩鍇媹蓇彍䔇桹濘躻垔幬認湙啔橔䂽䔇䂷悩儌滇認婻蓖商臘䯄冖償婔婻噙橬婔底䬹枪媘脘䔇䩘溼䔇臘㔗認婻冋床婉锗劽庯嚔哋䔇䞔剘滷溗䔇冋床傯認婻冋床嚔哋螾埇脘嚔螷螾蓼埻冖橬底锆傖䊖蓼㔗嘖滇䫘婔婻襖䕡欔橬噿髞䗹䔇冋床準婔準婔準螘螺襕懫婆冽崔冋床抂幌攺䂘喘㔗
懫套驔襕婔婻償噓䔇 min 庘昄䫘庯誫啂婴婻昘昄唚婺膄償䔇闼婻㔗䫘婋麵桹濘录傺垄
CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;
崘婴婻蓇彍係䂘襕䫘彄䔇臘套婋
CREATE TABLE shoe_data (
shoename text, -- 婂髞
sh_avail integer, -- (邋䔇)埇䫘凹昄
slcolor text, -- 饡锬䔇邋婥鵩謾
slminlen real, -- 邋婥橔䘺阪庥
slmaxlen real, -- 邋婥橔阪阪庥
slunit text -- 阪庥剘嘉
);
CREATE TABLE shoelace_data (
sl_name text, -- 婂髞
sl_avail integer, -- (邋婥䔇)埇䫘埯昄
sl_color text, -- 邋婥鵩謾
sl_len real, -- 邋婥阪庥
sl_unit text -- 阪庥剘嘉
);
CREATE TABLE unit (
un_name text, -- 婂髞
un_fact real -- 蘸扵潊寻䌿䔇係昄
);
嘹埇傖䩋彄認底臘傼臘邋庖䔇昄扞㔗
蓖商录傺婺
CREATE VIEW shoe AS
SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name;
CREATE VIEW shoelace AS
SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
CREATE VIEW shoe_ready AS
SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
录傺 shoelace 蓖商䔇 CREATE VIEW 变傴(幘滇䫘彄䔇橔䞔剘䔇婔婻)儖录傺婔婻 shoelace 噿係幽婫婘 pg_rewrite 臘麯嵂媹婔婻螄嘘只臬係䂘橬婔婻麉喍蓇彍庫䫘庯欔橬评啘臘麯嚘䫘庖 shoelace 噿係䔇昖臵㔗臖蓇彍澇橬蓇彍溇傽(儖婘麂 SELECT 蓇彍螘螺啹婺䕞嬉䔇 SELECT 蓇彍婉埇脘橬認底婩薪)幽婫垄滇 INSTEAD(埡傼)傋䔇㔗襕濘懟蓇彍溇傽婯昖臵溇傽婉婔湙㔗蓇彍媘嘩橬婔婻昖臵溇傽㔗蓇彍䔇媘嘩滇婔婻昖臵湏認婻昖臵滇湏蓖商录傺变傴婺䔇 SELECT 臺埖䔇婔婻拙蘺㔗
㔊濘懟㔏嘹婘臘 pg_rewrite 麯䩋彄䔇婴婻鵺崡䔇䫘庯 NEW 启 OLD 䔇评啘臘螄嘘(啹寖埾寘啹婘欷剄庺準䔇昖臵湏麯埆 *NEW* 启 *OLD*)凹 SELECT 蓇彍婉懘噘轼㔗
䯄婘准噙 unit, shoe_data, shoelace_data 幽婫婘蓖商婪誊臯婔婻䞔剘䔇昖臵
INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * FROM shoelace;
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
sl1 | 5 | black | 80 | cm | 80
sl2 | 6 | black | 100 | cm | 100
sl7 | 7 | brown | 60 | cm | 60
sl3 | 0 | black | 35 | inch | 88.9
sl4 | 8 | black | 40 | inch | 101.6
sl8 | 1 | brown | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 0 | brown | 0.9 | m | 90
(8 rows)
認滇埇傖婘蓖商婪啔䔇橔䞔剘䔇 SELECT 欔傖檪垄嘩婺蓼麪嘺橸蓖商蓇彍䔇变傴㔗SELECT * FROM shoelace 赆彖悊単蓼麪潊婋麵䔇昖臵湏
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;
䇽劯檪認底庴䂍蓇彍係䂘㔗蓇彍係䂘檪评啘臘(range table)誺悴婔镉演昖婔婋橬澇橬锗䫘傂嘘噿係䔇蓇彍㔗嘷婺 shoelace 螄嘘崇䊖评啘臘施(彄䕞嬉婺溵嫇婔䔇婔婻)垄嚔埏䯄昖臵湏麯橬 _RETURN 蓇彍昖臵湏䌂嚚婋麵認湙
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace *OLD*, shoelace *NEW*,
shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
婺欷匘臖蓖商麉喍単䞔剘婄录傺婔婻床昖臵评啘臘螄嘘垄寙劆蓇彍媘嘩䔇昖臵湏䇽劯䫘認婻评啘臘螄嘘埡傼寘噽嚘䫘蓖商䔇闼婻㔗䫘潊䔇麉喍昖臵湏庹幯婯嘹髞噖䔇闼婻婔湙
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM (SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) shoelace;
婉誺誻滇橬婔婻寺彆床昖臵评啘臘橬婴婻鵺崡䔇螄嘘 shoelace *OLD* 启 shoelace *NEW* 㔗認底螄嘘幽婉䕘毖埗婯昖臵啹婺垄傸澇橬赆床昖臵䔇誂毖湏潡蔙䕞湺彖臘嚘䫘㔗麉喍単䫘垄傸庻嗘橔彺庺䯄婘嚘䫘蓖商䔇评啘臘麯麵䔇螪閞溄鍊演昖㔗認湙欓臯単傉䇽嚔演昖臖䫘潙滇劥橬螪閞蓖商䔇劽锗溄鍊剿嘪婘麉喍昖臵麯麵澇橬凹蓖商䔇䕘毖嘪䫘幘套溴㔗
認滇庫䫘䔇丸婔婻蓇彍㔗蓇彍係䂘䂓䂺演昖釽北昖臵麯嬷婋䔇评啘臘螄嘘(橸冋婺澇橬庖)幽婫垄婘媹誕準䔇床昖臵婺锐嘐婄演昖评啘臘螄嘘䩋䩋噽婺橬澇橬嚘䫘蓖商䔇(婉誺認湙婉嚔欷匘 *OLD* 潡 *NEW* 劥彍嚔方䷙锐嘐婋寂!)㔗婘認婻冋床婺澇橬䫘庯 shoelace_data 潡 unit 䔇麉喍蓇彍欔傖麉喍䂷溘幽婫婪麵䔇儌滇䂍蓇彐単䔇橔䂽䂷悩㔗
䯄婘愿喍認幽婔婻昖臵認婻昖臵欆庺䕞嬉婘庖麯橬陉凹邋婥䔇邋床幽婫陉凹䔇邋婥昄崓庯潡京庯庯㔗
SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail ----------+----------+---------+----------+------------- sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2 rows)
認啂彖悊単䔇膷庺滇昖臵湏
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM shoe_ready shoe_ready
WHERE shoe_ready.total_avail >= 2;
庫䫘䔇丸婔婻蓇彍儖滇䫘庯 shoe_ready 蓖商䔇䂷悩滇䫘潊昖臵湏
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail >= 2;
婯婪麵䌂嚚䫘庯 shoe 启 shoelace 䔇蓇彍敪扵彄床昖臵评啘臘麯䫘潊婔婻橔䂽䔇婬北昖臵湏
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name) rsh,
(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail > 2;
橔劯蓇彐単嚔檪認婻湏寋䚷潊婔婻婴北昖臵湏橔婋北䔇 SELECT 儖"拡彄"婺閘䔇 SELECT 婺啹婺澇橬媙襕彖彆崇䊖垄傸㔗嘖滇婺閘䔇 SELECT 傉䇽启釽北䔇彖嚔啹婺垄寙劆蕔镖庘昄㔗套悩檪垄傸幘括誕準闼垄儌嚔媞櫹橔釽北 SELECT 䔇臯婺闼埇婉滇愿襕䔇㔗婉誺寋䚷昖臵湏滇麉喍係䂘躻噌婉驔襕噿媄䔇嚻寡淉嘩㔗
橬婴婻昖臵湏䔇䂖誗婘婪麵䔇蓖商蓇彍婺澇橬潬埪彄㔗儌滇变傴䌂傋启䂷悩噿係㔗垂鍙婪蓖商蓇彍婉驔襕認底媇敇㔗
婔婻 SELECT 䔇昖臵湏启䫘庯噽垄变傴䔇昖臵湏埻橬儏昄庹婻寺彆㔗滆䇽垄傸䔇变傴䌂傋婉劯幽婫凹庯 SELECT 幋崡䔇变傴䂷悩噿係毺劏䂷悩儖嬉冔䔇评啘臘噖埼㔗傂嘘噽垄婩薪鄘垯噘滇婔湙䔇㔗欔傖套悩橬婴婻臘 t1 启 t2 彖彆橬庖枕 a 启 b 婋麵婴婻臺埖䔇昖臵湏
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;
庹幯滇婔湙䔇㔗䬹彆滇
评啘臘寙劆臘 t1 启 t2 䔇螄嘘㔗
䕞湺彖臘寙劆婔婻埻麟臖埻麟毺劏臘 t2 䔇评啘臘噖埼䔇 b 庖枕㔗
溇傽臘膆嚟懫膄婴婻评啘䔇庖枕 a 傖凂欆䕩京臯㔗
誂毖湏滆䴺 t1 启 t2 幋閘䔇䞔剘誂毖㔗
䂷悩滇婴婻昖臵湏䫘潊䕩嚚䔇欓臯蓇彐垄傸鄘滇婴婻臘䔇誂毖㔗凹庯 UPDATE 臺埖準臘蓇彐単檪 t1 䚺崌䔇庖枕誘媹彄䕞湺彖啹蔯橔䂽昖臵湏䩋蕙準償
UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;
啹溴欓臯単婘誂毖婪誊臯䔇䂷悩启婋麵臺埖
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
滇垯噘婔湙䔇㔗嘖滇婘 UPDATE 麯橬䗹閞鵻欓臯単婉噿媄垄溼婘崇䊖䔇誂毖䂷悩䔇劆幬滇傔幽㔗垄埻滇库䫘婔婻臯䔇䂷悩镖㔗婔婻滇 SELECT 变傴蔯埥婔婻滇 UPDATE 变傴䔇寺彆滇䫌欓臯単䔇脄䫘蔙毓彽䔇㔗臖脄䫘蔙認施誻䘖長(昖䩋昖臵湏)認滇婔婻 UPDATE 蔯婫垄誻䘖長䂷悩襕螄嘘彄臘 t1 麯寂㔗嘖滇䯄橬䔇螄嘘婺䔇巻婔臯襕赆桄臯埡傼叵?
襕蓼喿認婻閞鵻婘 UPDATE 启 DELETE 臺埖䔇䕞湺彖臘麯麵嵂媹庖埥崡婔婻噖埼㔗嘷嬉䔇臯 ID (CTID)㔗認滇婔婻橬五䬹枪䬹攓䔇係䂘庖枕㔗垄寙劆臯婘桺傽庖婺䔇庖䚡埙启嘉䘞媇敇㔗婘噾䘖臘䔇愙喕婋埇傖锔誺 CTID 演䘵橔彺䔇驔襕敘桄䔇 t1 臯㔗婘檪 CTID 媹彄䕞湺彖臘婺寂傖劯昖臵䩋婪寂垂鍙婪償認湙
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
䯄婘埥婔婻 PostgreSQL 䔇䂖誗誕噖彄認婻黽枕麯庖㔗認施臘婺䔇斓臯誻澇橬赆襖䕡認儌滇婺傔幽 ROLLBACK 鼂媆䔇寘啹㔗婘婔婻 UPDATE 麯桄䔇䂷悩臯某噖彄臘麯(婘嬖鍴 CTID 幋劯)幽婫檪 CTID 毺劏䔇斓昄扞臯䔇臯崘麯麵䔇 cmax 启 xmax 螆䘞婺嘷嬉变傴螇昄単启嘷嬉庋媇 ID 㔗認湙斓䔇臯儌赆锊薟蕙準幽婫婘庋媇柊庴幋劯vacuum 橙䊖単儌埇傖䩘溼檪垄傸役鍴毬㔗
䘖長庖認底儌埇傖䞔剘䔇檪蓖商䔇蓇彍庫䫘彄傂懟变傴婺㔗蓖商启变傴澇橬寺彆㔗
婪麵暫䴺庖蓇彍係䂘套嘘肉劽彄蓖商垔幬䔇彺哋昖臵湏婺寂㔗婘丸庯婻冋床麯婔婻䞔剘䔇凹蓖商䔇 SELECT 录傺庖婔婻啕臘蕫劽䔇昖臵湏(unit 傖婉劯䔇劉䓄䫘庖婴渇)㔗
婘蓇彍係䂘麯垂䯄蓖商䔇喘崇滇蓇彐単婘婔婻昖臵湏麯拖橬欔橬媇敇庫臖欆柟巻婻臘+臘幋閘䔇噿係+蓖商䔇蕇湚鍊彽+彺哋昖臵䔇蕇湚(溇傽)㔗幽婫傉䇽滇婘橔彺䔇昖臵噾䂟滇婔婻蓖商䔇蕫劽䔇愙喕婋㔗䯄婘蓇彐単媙釂喿垔欓臯昖臵䔇橔嚻虇冇㔗蓇彐単拖橬轪崔媇敇垄䔇喿亡儌轪喘㔗幽婫 PostgreSQL 麯䔇蓇彍係䂘䔇垂䯄媺臕認底媇敇滇溴施脘诙冖䔇橬噿臖昖臵䔇欔橬媇敇㔗
套悩蓖商滇 INSERT, UPDATE, DELETE 䔇䕞湺噿係嚔支湙?婘垯潊婪麵柟誄䔇敪扵幋劯儌橬婔婻認湙䔇昖臵湏䂷悩噿係毺劏婔婻滇床昖臵䔇评啘臘螄嘘㔗認湙埇婉脘誊臯欔傖套悩麉喍単䩋彄躻噌䫘潊認幽婻婩薪垄儌檕庺婔婻髍臇㔗
襕媞櫹認婻䬹攓埇傖垔幬媞櫹認底变傴臯婺䔇蓇彍㔗認滇婋婔誗䔇婂鵻㔗