PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 33. ����ϵͳ | Fast Forward | Next |
������ INSERT��UPDATE �� DELETE �Ĺ�����ǰһ����������ͼ������ȫ��ͬ�� ���ȣ����ǵ�CREATE RULE����������ࣺ
�������ǿ���û�ж�����action����
���ǿ����ж��������action����
���ǿ����� INSTEAD ���� ALSO��ȱʡ����
α��ϵ NEW �� OLD ��������ˡ�
���ǿ����й����ʸ�������
�ڶ������Dz��Ǿ͵��IJ�ѯ���� �����Ǵ�����������²�ѯ�����ҿ��ܰ�ԭʼ���Ǹ��Ե���
�������
CREATE RULE rule_name AS ON event TO object [WHERE rule_qualification] DO [ALSO|INSTEAD] [action | (actions) | NOTHING];
���μ�ס������������� "update rules"(���¹���) ��˼�Ƕ����� INSERT��UPDATE �� DELETE �ϵĹ���
�����ѯ���Ľ����ϵ������������CREATE RULE ����������Ķ�����¼�һ���Ļ��� ����ϵͳ�ͰѸ��¹���Ӧ����ȥ�� ���ڸ��¹�����ϵͳ����һ����ѯ���б� һ��ʼ��ѯ���ǿյġ���������������NOTHING �ؼ��֣���һ������������ һ������������Ϊ������� �����ȿ�һ��һ��ֻ��һ��������action���Ĺ��� ������������һ��������û�в����������� INSTEAD �� ALSO ��ȱʡ����
��Ϊ��������������һ���������������߹�������action��ʲôʱ��Ҫ���� ʲôʱ������������ʸ�����������ֻ���� NEW ��/�� OLD α��ϵ�� ���ǻ������Ǵ����Զ�����ʽ�����Ļ�����ϵ�������������⺬�壩��
���ԣ��������������action���Ĺ������ɲ�ѯ�������������������
���Թ������IJ�ѯ����������ԭʼ��ѯ����������
���Թ������IJ�ѯ����������ԭʼ��ѯ����������
���Թ������Ĵ��й��������IJ�ѯ�����Ҹ�����ԭʼ��ѯ����������
���Թ��������й��������IJ�ѯ���Լ�ԭʼ��ѯ���������� �Լ��������෴����������ԭʼ��ѯ����
������������ ALSO�������δ�ĵIJ�ѯ�������뵽�б� ��Ϊֻ�кϸ��INSTEAD�����Ѿ��ڳ�ʼ�IJ�ѯ�����棬 ���Զ��ڵ����������������յõ�һ������������ѯ����
���� ON INSERT ���� ԭ���IJ�ѯ�����û�б� INSTEAD ȡ���������κι������ӵĶ���֮ǰ��ɵġ� ����������������������С����Ƕ��� ON UPDATE �� ON DELETE ����ԭ���IJ�ѯ���ڹ������ӵĶ���֮����ɵġ� ������ȷ���������Կ�����Ҫ���»��߽�Ҫɾ�����У�����������ʲôҲ��������Ϊ���Ƿ���û�з������ǵ�Ҫ����С�
�ӹ��������ɵIJ�ѯ�����ٴ��͵���дϵͳ�����ҿ��ܸ��Ӹ���Ĺ��� ����Ǹ���Ļ���ٵIJ�ѯ�������Թ�������IJ�ѯ����������һ���������ͻ���һ�������ϵ�� ���������ĵݹ���̾ͻ�û��û�ˡ� ������һ�� 100 ��Ĺ̶��ĵݹ�������ơ� ��� 100 �ε���֮������ҪӦ�õĸ��¹���update rules���� ����ϵͳ����Ϊ��һ���ڶ�������ϵ�ѭ���������һ������
�� pg_rewrite ϵͳ����� action ��IJ�ѯ��ֻ��ģ�塣 ��Ϊ���ǿ������÷�Χ��� NEW �� OLD�� ��ʹ������֮ǰ������һЩ�����������κζ� NEW �����ã� ��Ҫ���ڳ�ʼ��ѯ��Ŀ������������Ӧ����Ŀ������ҵ��� �Ѹ���Ŀ���ʽ�ŵ���������� NEW �� OLD �ĺ���һ�� ��UPDATEʱ�����߱� NULL �����INSERTʱ���� �κζ� OLD �����ö��ý����ϵ�ķ�Χ��������滻��
��ϵͳ��ɸ��¹���ĸ���֮�����ٸ�����ͼ�������ɵIJ�ѯ���ϡ� ��ͼ�������µĸ��¶���������û�б�Ҫ����ͼ��д��������Ӹ��¹���
��������ϣ������ shoelace_data ��ϵ�е� sl_avail �ֶΡ� ������������һ����־���һ��������������ÿ������UPDATE ���� shoelace_data ��ʱ��Ҫ�����ݿ���дһ����¼��
CREATE TABLE shoelace_log ( sl_name text, -- Ь���仯�� sl_avail integer, -- �µĿ�����ֵ log_who text, -- ˭�ɵ� log_when timestamp -- ʲôʱ�� ); CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE NEW.sl_avail <> OLD.sl_avail DO INSERT INTO shoelace_log VALUES ( NEW.sl_name, NEW.sl_avail, current_user, current_timestamp );
�������˼��룺
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
Ȼ�����ǿ�����־��
SELECT * FROM shoelace_log; sl_name | sl_avail | log_who | log_when ---------+----------+---------+---------------------------------- sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST (1 row)
����������Ҫ�ġ���˷������������¡� ������������ѯ��
UPDATE shoelace_data SET sl_avail = 6 FROM shoelace_data shoelace_data WHERE shoelace_data.sl_name = 'sl7';
������һ�������������ʽ�� ON UPDATE ���� log_shoelace
NEW.sl_avail <> OLD.sl_avail
�Ͷ���
INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*;
���������������е���֣���Ϊ�㲻��д INSERT ... VALUES ... FROM�� ����� FROM �Ӿ�ֻ�DZ�ʾ��ѯ���������� *NEW* �� *OLD* �ķ�Χ���¼�� ��Щ�����Ĵ�������Ϊ����һ�����ǾͿ��Ա� INSERT����IJ�ѯ����ı������á���
�ù�����һ���������� ALSO ���� ���Թ���ϵͳ���뷵��������ѯ���� ���Ĺ��Ĺ�������ԭʼ��ѯ���� �ڵ�һ���ԭʼ��ѯ�ķ�Χ���ɵ���������ѯ������ɣ�
INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data;
�ڶ����ѹ����������ӽ�ȥ�����Խ��������Ϊ sl_avail �ı��˵��С�
INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data WHERE *NEW*.sl_avail <> *OLD*.sl_avail;
�������������������֣���Ϊ INSERT ... VALUES Ҳû�� WHERE �Ӿ䣬 �����滮����ִ�����Դ˲������⡣���DZϾ���ҪΪ INSERT ... SELECT ֧�����ֹ��ܡ���
��������ԭʼ��ѯ���������ӽ�ȥ���ѽ������һ�����Ƴ�ֻ�б���ʼ��ѯ���ı���У�
INSERT INTO shoelace_log VALUES ( *NEW*.sl_name, *NEW*.sl_avail, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data WHERE *NEW*.sl_avail <> *OLD*.sl_avail AND shoelace_data.sl_name = 'sl7';
���IJ��� NEWNEW �����滻Ϊ��ԭʼ��ѯ����Ŀ�������Ļ�ӽ����ϵ������ƥ��ı������ã�
INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data WHERE 6 <> *OLD*.sl_avail AND shoelace_data.sl_name = 'sl7';
���岽�ý����ϵ���ð� OLD �����滻����
INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) FROM shoelace_data *NEW*, shoelace_data *OLD*, shoelace_data shoelace_data WHERE 6 <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7';
��ͳ��ˡ���Ϊ������ ALSO�����ǻ����ԭʼ��ѯ���� �����֮���ӹ���ϵͳ�������һ��������ѯ�����б������������ͬ��
INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, 6, current_user, current_timestamp ) FROM shoelace_data WHERE 6 <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7'; UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
�����ִ�е�˳���Լ�����Ҫ�������顣
�����滻���ӵ���������ȷ�����ԭʼ�IJ�ѯ����������
UPDATE shoelace_data SET sl_color = 'green' WHERE sl_name = 'sl7';
�Ͳ��������ڼ�¼д����� ��Ϊ���ԭʼ��ѯ���������й� sl_avail ��Ŀ���б� NEW.sl_avail ���� shoelace_data.sl_avail ���棬 ���ԣ��������ɵĶ��������ǣ�
INSERT INTO shoelace_log VALUES ( shoelace_data.sl_name, shoelace_data.sl_avail, current_user, current_timestamp ) FROM shoelace_data WHERE shoelace_data.sl_avail <> shoelace_data.sl_avail AND shoelace_data.sl_name = 'sl7';
������������Զ����������ֵ��
�������IJ�ѯ�Ķ���У���Ҳ�����С� �����������д����������
UPDATE shoelace_data SET sl_avail = 0 WHERE sl_color = 'black';
ʵ���������б����£�sl1��sl2��sl3 �� sl4���� �� sl3 �Ѿ��� sl_avail = 0����أ�ԭʼ�IJ�ѯ�������Ѿ���һ���ˣ�����ǹ�����������Ķ����ѯ��
INSERT INTO shoelace_log SELECT shoelace_data.sl_name, 0, current_user, current_timestamp FROM shoelace_data WHERE 0 <> shoelace_data.sl_avail AND shoelace_data.sl_color = 'black';
�����ѯ�����϶����������µ���־��¼����Ҳ����ȫ��ȷ�ġ�
���������Ǿ�����Ϊʲôԭʼ��ѯ�����ִ�зdz���Ҫ�� ���UPDATE���ȱ�ִ�У����е��ж��Ѿ���Ϊ�㣬���Լ���־�� INSERT�������ҵ��κ����Ƿ��� 0 <> shoelace_data.sl_avail �����ġ�
һ��������ͼ��ϵ�� ʹ������������ᵽ�����˿���������INSERT��UPDATE �� DELETE ���ɼ������ݵļ���������Щ��ѯ�������������Ǵ����������
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe DO INSTEAD NOTHING; CREATE RULE shoe_del_protect AS ON DELETE TO shoe DO INSTEAD NOTHING;
��������κ�����ͼ����ͼ��ϵ shoe��������κβ����� ����ϵͳ��Ӧ����Щ������Ϊ��Щ����û�ж���������INSTEAD�� ��������ɵIJ�ѯ�����ǿյIJ���������ѯ����ÿտ���Ҳ�� ��Ϊ��������ϵͳ�����û��ʲô����ʣ���������Ż���ִ���ˡ�
һ�������ӵ�ʹ�ù���ϵͳ�ķ������ù���ϵͳ����һ����д��ѯ���Ĺ��� ʹ��ѯ������ʵ�ı������ȷ�IJ�����Ҫ����ͼ shoelace ����������������Ǵ����������
CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data VALUES ( NEW.sl_name, NEW.sl_avail, NEW.sl_color, NEW.sl_len, NEW.sl_unit ); CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = NEW.sl_name, sl_avail = NEW.sl_avail, sl_color = NEW.sl_color, sl_len = NEW.sl_len, sl_unit = NEW.sl_unit WHERE sl_name = OLD.sl_name; CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE sl_name = OLD.sl_name;
������һ��Ь�������̵꣬��������һ��ʵ����� �������Dz���ÿ�ζ��ֹ����� shoelace ��ͼ�� ȡ����֮�������Ǵ���������С��һ�������ǿ��Դӵ����嵥�в��붫���� ��һ����һ������ļ��ɡ�������Щ���������£�
CREATE TABLE shoelace_arrive ( arr_name text, arr_quant integer ); CREATE TABLE shoelace_ok ( ok_name text, ok_quant integer ); CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = sl_avail + NEW.ok_quant WHERE sl_name = NEW.ok_name;
��������������Բ����б���������� shoelace_arrive ��
SELECT * FROM shoelace_arrive; arr_name | arr_quant ----------+----------- sl3 | 10 sl6 | 20 sl8 | 20 (3 rows)
������Ѹ�ٵؿ�һ�۵�ǰ�����ݣ�
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 | 6 | 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)
�ѵ���Ь���Ƶ���shoelace_ok����
INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
Ȼ������
SELECT * FROM shoelace ORDER BY sl_name; 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 | 6 | brown | 60 | cm | 60 sl4 | 8 | black | 40 | inch | 101.6 sl3 | 10 | black | 35 | inch | 88.9 sl8 | 21 | brown | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 (8 rows) SELECT * FROM shoelace_log; sl_name | sl_avail | log_who| log_when ---------+----------+--------+---------------------------------- sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST (4 rows)
�� INSERT ... SELECT ��䵽�����������˳�����һ�ι��̡� ���Ҷ������������DZ��ĵ���������������ɷ��������
INSERT INTO shoelace_ok SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
����Ӧ�õ�һ������ shoelace_ok_ins ����ת����
UPDATE shoelace SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok *OLD*, shoelace_ok *NEW*, shoelace shoelace WHERE shoelace.sl_name = shoelace_arrive.arr_name;
���Ұ�ԭʼ�Ķ� shoelace_ok ��INSERT�������� ������д��IJ�ѯ�ٴδ������ϵͳ���ҵڶ���Ӧ���˹��� shoelace_upd ����
UPDATE shoelace_data SET sl_name = shoelace.sl_name, sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant, sl_color = shoelace.sl_color, sl_len = shoelace.sl_len, sl_unit = shoelace.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok *OLD*, shoelace_ok *NEW*, shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data shoelace_data WHERE shoelace.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = shoelace.sl_name;
ͬ������һ�� INSTEAD ������ǰһ����ѯ������������ ע�������ѯ��Ȼ��ʹ����ͼ shoelace�� ���ǹ���ϵͳ��û�������һ���� ������������������Ӧ�ù��� _RETURN��Ȼ�����ǵõ�
UPDATE shoelace_data SET sl_name = s.sl_name, sl_avail = s.sl_avail + shoelace_arrive.arr_quant, sl_color = s.sl_color, sl_len = s.sl_len, sl_unit = s.sl_unit FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok *OLD*, shoelace_ok *NEW*, shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data shoelace_data, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name;
���Ӧ�ù���log_shoelace�����ɶ���IJ�ѯ��
INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok, shoelace_ok *OLD*, shoelace_ok *NEW*, shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data shoelace_data, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u, shoelace_data *OLD*, shoelace_data *NEW* shoelace_log shoelace_log WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail;
�������ڹ���ϵͳ�������еĹ�������ɵIJ�ѯ����
�����������յõ�������Ч������ SQL ���IJ�ѯ��
INSERT INTO shoelace_log SELECT s.sl_name, s.sl_avail + shoelace_arrive.arr_quant, current_user, current_timestamp FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.arr_name AND shoelace_data.sl_name = s.sl_name AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail; UPDATE shoelace_data SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data, shoelace_data s WHERE s.sl_name = shoelace_arrive.sl_name AND shoelace_data.sl_name = s.sl_name;
����Ǵ�һ����ϵ�������ݲ��뵽��һ���У����˵������б�ɸ��£� �ڵ����ĸ��б�ɸ��¼��ϼ���־������ڵ��������������Ϊ������ѯ��
��һ��Сϸ���е��������ܡ��������ɵ�������ѯ�� ���ǻᷢ�� shoelace_data ��ϵ�ڷ�Χ���г��������ζ�ʵ���Ͼ��Կ�����Ϊһ�Ρ� ��Ϊ�滮����������Щ�����ԶԹ���ϵͳ�����INSERT��ִ�й滮����
Nested Loop -> Merge Join -> Seq Scan -> Sort -> Seq Scan on s -> Seq Scan -> Sort -> Seq Scan on shoelace_arrive -> Seq Scan on shoelace_data
��ʡ�Զ���ķ�Χ���Ľ������
Merge Join -> Seq Scan -> Sort -> Seq Scan on s -> Seq Scan -> Sort -> Seq Scan on shoelace_arrive
��Ҳ������־��ϵ��������ȫһ���ļ�¼����ˣ�����ϵͳ���¶Ա� shoelace_data ��һ�ζ����ɨ�裬 ����ͬ�������ɨ�����UPDATE��Ҳһ������һ�Ρ� ����Ҫ�����Щ����ȥ����һ��̫���ѵĻ��ˡ�
�������� PostgreSQL ����ϵͳ���书����һ����ʾ�� ��������������ݿ������һЩ�ȽϺ�����Ь����
INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0); INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
���ǽ���һ����ͼ�������shoelace��¼����ɫ�� ���κ�Ь�Ӷ������䡣�����������ͼ��
CREATE VIEW shoelace_mismatch AS SELECT * FROM shoelace WHERE NOT EXISTS (SELECT shoename FROM shoe WHERE slcolor = sl_color);
���������
SELECT * FROM shoelace_mismatch; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ---------+----------+----------+--------+---------+----------- sl9 | 0 | pink | 35 | inch | 88.9 sl10 | 1000 | magenta | 40 | inch | 101.6
�����������������ã�û�п��IJ�ƥ���Ь���������ݿ���ɾ���� Ϊ�������¶� PostgreSQL �е��Ѷȣ����Dz�ֱ��ɾ�����ǡ�ȡ����֮���������ٴ���һ����ͼ
CREATE VIEW shoelace_can_delete AS SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
Ȼ�������淽������
DELETE FROM shoelace WHERE EXISTS (SELECT * FROM shoelace_can_delete WHERE sl_name = shoelace.sl_name);
���ԣ�
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 | 6 | brown | 60 | cm | 60 sl4 | 8 | black | 40 | inch | 101.6 sl3 | 10 | black | 35 | inch | 88.9 sl8 | 21 | brown | 40 | inch | 101.6 sl10 | 1000 | magenta | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 (9 rows)
��һ����ͼ�� DELETE�� �����ͼ����һ���ܹ�ʹ�����ĸ�Ƕ��/���ӵ���ͼ���Ӳ�ѯ������ ���ĸ���ͼ֮һ������һ��ӵ�ж�һ����ͼ���Ӳ�ѯ������ ����������ʹ�õ���ͼ���У������д����һ����ѯ���� �ò�ѯ����һ�������ı��������Ҫɾ��������ɾ����
��������ʵ������ֻ�к��ٵĻ�����Ҫ����������Ĺ��졣 ����Щ��������ת�϶����������