PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 33. ����ϵͳ | Fast Forward | Next |
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�� �����������������в�ͬ�����⣬ �Ǿ����������ֳ��IJ�ѯ�������Ǵ���һ���µģ���ѯ������ ���������Ƚ���SELECT�Ĺ���
Ŀǰ��һ�� ON SELECT ������ֻ����һ��������action���� ������������һ���������� 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 ��ϵ�IJ�ѯ���ù���û�й������������ڷ� SELECT �������ۣ���ΪĿǰ�� SELECT ����������Щ�������������� INSTEAD��ȡ�����͵ġ� Ҫע������������ѯ������һ�������ǵĹ�������action����һ����ѯ������ ����Ķ�����һ����ѯ���������ѯ������ͼ���������е� SELECT ����һ��������
ע��: ���ڱ� pg_rewrite ������������������ NEW �� OLD �ķ�Χ���¼������ʷԭ�� �ڴ�ӡ�����IJ�ѯ����� *NEW* �� *CURRENT* �� �� SELECT ������Ȥ��
����������� unit��shoe_data �� shoelace_data����������ͼ������һ���IJ�ѯ��
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 �����������ͳ�����IJ�ѯ��
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace;
Ȼ�����Щ��������ϵͳ������ϵͳ�ѷ�Χ��rangetable������һ�飬 ���һ������û�������κι�ϵ���������Ϊ 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;
Ϊ��չ����ͼ����д���ش���һ���Ӳ�ѯ��Χ���¼�� �������������IJ�ѯ����Ȼ���������Χ���¼ȡ��ԭ��������ͼ���Ǹ��� ���ɵ���д��ѯ���������������Ǹ�һ��
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)
��ط�����������Dz�ѯ��
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 ����Ϊ���ǿɲ���������Ҫ�ġ� ������ѹ����ѯ������дϵͳ�Լ�����Ҫ���ĵ��Ż�������
ע��: Ŀǰ����ϵͳ��û��������ͼ����ݹ���ֹ���ƣ�ֻ��������������ģ��� ��һ�㲻�����̫����� ��Ϊ���������������ѭ�����������ͺ�ˣ�ֱ���ľ��ڴ棩��Ψһ�����Ǵ�����Ȼ����ֹ���CREATE RULE�������ͼ���� ��������������ģ�һ���������ط�����ѡ��select��ѡ��select���������ͼ�� ���ʹ����CREATE VIEW����һ������Զ���ᷢ���ģ� ��Ϊ�ڶ�����ϵ�����ڣ����Ե�һ��CREATE VIEW ���ܴӵڶ�����ͼ����ѡ��select����
��������ѯ����ϸ���������������ͼ������û���漰���� �����������ͺͽ����ϵ��ʵ���ϣ���ͼ������Ҫ��Щ��Ϣ��
һ��SELECT�IJ�ѯ����������������IJ�ѯ��ֻ�������������� ��Ȼ�����Dz�ͬ���������Ͳ��Ҷ���SELECT֮������ �����ϵָ������ǰ���ķ�Χ����ڡ��κ�������������ȫ��һ���ġ� ��������������� t1 �� t2 �ֱ����ֶ� a �� b�� �����������IJ�ѯ��
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
������һ���ġ��ر��ǣ�
��Χ������� t1 �� t2 �ļ�¼��
Ŀ���а���һ��ָ��Χ�� t2 ����ֶ� b �ı�����
�������ʽ�Ƚ�������Χ���ֶ� a ��Ѱ����ȣ��У���
��������jointree����ʾ t1 �� t2 ֮��ļ����ӡ�
����ǣ�������ѯ���������Ƶ�ִ�й滮�����Ƕ�������������ӡ� ����UPDATE�����˵�� �滮���� t1 ȱʧ���ֶ��ӵ�Ŀ����������ղ�ѯ����������
UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a;
���ִ���������������еĽ�����������
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
����ȫһ���ġ�������UPDATE���е����⣺ ִ���������������ڴ���Ĵ����ӳ����Ľ���ĺ�����ʲô�� ��ֻ�Dz���һ���еĽ������ һ����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 �������Ϳ�������������ɾ������
֪������Щ�����ǾͿ��Լİ���ͼ�Ĺ���Ӧ�õ����������С� ���ǣ���ͼ�����û������
������ʾ�˹���ϵͳ����ںϵ���ͼ����ij�ʼ��ѯ����ȥ�� �ڵڶ��������һ���Ķ���ͼ��SELECT ������һ�� 4 �������ϵIJ�ѯ����unit �Բ�ͬ�������������Σ���
�ڹ���ϵͳ��ʵ����ͼ�ĺô��ǣ��滮����һ����ѯ����ӵ��������Ϣ�� Ӧ��ɨ���ĸ��� + ��֮��Ĺ�ϵ+ ��ͼ���ʸ����� + ��ʼ��ѯ���ʸ��������� ������Ȼ��������IJ�ѯ�Ѿ���һ����ͼ�����ϵ�����¡� ���ڹ滮���������ִ�в�ѯ������·���� �滮��ӵ��Խ����Ϣ�����ľ��߾�Խ�á����� PostgreSQL ��Ĺ���ϵͳ��ʵ�ֱ�֤��Щ��Ϣ�Ǵ�ʱ�ܻ�õ��йظò�ѯ��������Ϣ��
�����ͼ��INSERT��UPDATE������ DELETE ��Ŀ���ϵ�������� ��������������������滻֮�� ���Ǿ���һ�������IJ�ѯ���������ϵָ��һ�����Ӳ�ѯ�ķ�Χ���¼�� �����ɲ������У����������д�������Լ�������ôһ�������� �����׳�һ������
Ҫ��������ԣ����ǿ��Զ�������Щ������Ϊ�Ĺ��� ������һ�ڵ����⡣