ÊÓͼºÍ¹æÔòϵͳ

Postgres ÀïÊÓͼµÄʵÏÖ

Postgres ÀïµÄÊÓͼÊÇͨ¹ý¹æÔòϵͳÀ´ÊµÏֵģ®Êµ¼ÊÉÏÏÂÃæµÄÃüÁî
    CREATE VIEW myview AS SELECT * FROM mytab;
ºÍÏÂÃæÁ½ÌõÃüÁî
    CREATE TABLE myview (same attribute list as for mytab);
    CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD
        SELECT * FROM mytab;
Ö®¼ä¾ø¶ÔûÓÐÇø±ð£¬ÒòΪÕâ¾ÍÊÇ  CREATE VIEW ÃüÁîÔÚÄÚ²¿Êµ¼ÊÖ´ÐеÄÄÚÈÝ£®ÕâÑù×öÓÐһЩ¸º×÷Óã®ÆäÖÐÖ®Ò»¾ÍÊÇÔÚ Postgres ϵͳ±íÀïµÄÊÓͼµÄÐÅÏ¢ÓëÒ»°ã±íµÄÐÅÏ¢ÍêÈ«Ò»Ñù£®ËùÒÔ¶ÔÓÚ²éѯ·ÖÎöÆ÷À´Ëµ£¬±íºÍÊÓͼ֮¼äÍêȫûÓÐÇø±ð£®ËüÃÇÊÇͬÑùµÄÊÂÎ£­¹Øϵ£®Õâ¾ÍÊÇÄ¿Ç°ºÜÖØÒªµÄÒ»µã£®

SELECT ¹æÔòÈçºÎ¹¤×÷

ON SELECT µÄ¹æÔòÔÚ×îºóÒ»²½Ó¦ÓÃÓÚËùÓвéѯ£¬ÄÄŸø³öµÄÃüÁîÊÇÒ»Ìõ  INSERT£¬UPDATE »ò DELETE£®¶øÇÒÓëÆäËû£¨¹æÔò£©Óв»Í¬µÄÓïÒ⣬ÄǾÍÊÇËüÃÇÔÚʵµØÐ޸ķÖÎöÊ÷¶ø²»ÊÇ´´½¨Ò»¸öеģ¨·ÖÎöÊ÷£©£®ËùÒÔÎÒÃÇÏȽéÉÜ SELECT µÄ¹æÔò£®

Ä¿Ç°£¬ÕâÀïÖ»¿ÉÄÜ·¢ÉúÒ»¸ö¶¯×÷£¨action£©¶øÇÒËü±ØÐëÊÇÒ»¸ö INSTEAD £¨È¡´úÁË£©µÄ SELECT ¶¯×÷£®ÓÐÕâ¸öÏÞÖÆÊÇΪÁËÁî¹æÔò°²È«µ½ÆÕͨÓû§Ò²¿ÉÒÔ´ò¿ªËüÃÇ£¬²¢ÇÒËü¶ÔÕæÕýµÄÊÓͼ¹æÔò×ö ON SELECT ¹æÔòÏÞÖÆ£®

±¾ÎĵµµÄÀý×ÓÊÇÁ½¸öÁªºÏÊÓͼ£¬ËüÃÇ×öһЩÔËËã²¢ÇÒ»áÉæ¼°µ½¸ü¶àÊÓͼµÄʹÓã®ÕâÁ½¸öÊÓͼ֮һÉÔºó½«ÀûÓÃ¶Ô INSERT£¬UPDATE ºÍ DELETE ²Ù×÷¸½¼Ó¹æÔòµÄ·½·¨¿Í»§»¯£¬ÕâÑù×ö×îÖյĽá¹û¾Í»áÊÇÕâ¸öÊÓͼ±íÏÖµÃÏóÒ»¸ö¾ßÓÐһЩÌØÊ⹦ÄܵÄÕæÕýµÄ±í£®Õâ¿É²»ÊÇÒ»¸öÊʺÏÓÚ¿ªÊ¼µÄ¼òµ¥Ò׶®µÄÀý×Ó£¬´ÓÕâ¸öÀý×Ó¿ªÊ¼½²¿ÉÄÜ»áÈÃÎÒÃǵĽ²½â±äµÃÓÐЩÄÑÒÔÀí½â£®µ«ÊÇÎÒÃÇÈÏΪÓÃÒ»¸ö¸²¸ÇËùÓйؼüµãµÄÀý×ÓÀ´Ò»²½Ò»²½ÌÖÂÛÒª±È¾ÙºÜ¶àÀý×Ó¸ãÂÒ˼άºÃ¶àÁË£®

ÔÚ±¾Àý×ÓÖÐÓõ½µÄÊý¾Ý¿âÃûÊÇ al_bundy£®ÄãºÜ¿ì¾Í»áÃ÷°×Ϊʲô½ÐÕâ¸öÃû×Ö£®¶øÇÒÕâ¸öÀý×ÓÐèÒª°²×°¹ý³ÌÓïÑÔ PL/pgSQL £¬ÒòΪÎÒÃÇÐèÒªÒ»¸öСÇÉµÄ min() º¯ÊýÓÃÓÚ·µ»ØÁ½¸öÕûÊýÖµÖеÄСµÄÄǸö£®ÎÒÃÇÓÃÏÂÃæ·½·¨´´½¨Ëü

    CREATE FUNCTION min(integer, integer) RETURNS integer AS
        'BEGIN
            IF $1 < $2 THEN
                RETURN $1;
            END IF;
            RETURN $2;
        END;'
    LANGUAGE 'plpgsql';
ÎÒÃÇÍ·Á½¸ö¹æÔòϵͳҪÓõ½µÄÕæʵµÄ±íµÄÃèÊöÈçÏ£º
    CREATE TABLE shoe_data (
        shoename   char(10),      -- primary key
        sh_avail   integer,       -- available # of pairs
        slcolor    char(10),      -- preferred shoelace color
        slminlen   float,         -- miminum shoelace length
        slmaxlen   float,         -- maximum shoelace length
        slunit     char(8)        -- length unit
    );

    CREATE TABLE shoelace_data (
        sl_name    char(10),      -- primary key
        sl_avail   integer,       -- available # of pairs
        sl_color   char(10),      -- shoelace color
        sl_len     float,         -- shoelace length
        sl_unit    char(8)        -- length unit
    );

    CREATE TABLE unit (
        un_name    char(8),       -- the primary key
        un_fact    float          -- factor to transform to cm
    );
ÎÒÏëÎÒÃǶ¼ÐèÒª´©Ð¬×Ó£¬Òò¶øÉÏÃæÕâЩÊý¾Ý¶¼ÊǺÜÓÐÓõÄÊý¾Ý£®µ±È»£¬ÓÐÄÇЩ²»ÐèҪЬ´øµÄЬ×Ó£¬µ«ÊDz»»áÈà AL µÄÉú»î±äµÃ¸üÇáËÉ£¬ËùÒÔÎÒÃǺöÂÔÖ®£®

ÊÓͼ´´½¨Îª

    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 ÃüÁҲÊÇÎÒÃÇÓõ½µÄ×î¼òµ¥µÄÒ»¸ö£© ½«´´½¨Ò»¸ö¹Øϵ/±í -- Ь´ø£¨relation shoelace £©²¢ÇÒÔÚ pg_rewrite ±íÀïÔö¼ÓÒ»¸ö¼Ç¼£¬¸æËßϵͳÓÐÒ»¸öÖØд¹æÔòÓ¦ÓÃÓÚËùÓÐË÷ÒýÁËЬ´ø¹Øϵ£¨relation shoelace£©µÄ²éѯ£®¸Ã¹æÔòûÓйæÔò×ʸñ£¨½«ÔÚ·Ç SELECT ¹æÔòÌÖÂÛ£¬ÒòΪĿǰµÄ SELECT ¹æÔò²»¿ÉÄÜÓÐÕâЩ¶«Î÷£©²¢ÇÒËüÊÇ INSTEAD £¨È¡´ú£©Ð͵ģ®Òª×¢Òâ¹æÔò×ʸñÓë²éѯ×ʸñ²»Ò»Ñù£¡Õâ¸ö¹æÔò¶¯×÷£¨action£©ÓÐÒ»¸ö×ʸñ£®

¹æÔò¶¯×÷£¨action£©ÊÇÒ»¸ö²éѯÊ÷£¬Êµ¼ÊÉÏÊÇÔÚ´´½¨ÊÓͼµÄÃüÁîÀïµÄ SELECT Óï¾äµÄÒ»¸ö¿½±´£®

×¢Ò⣺ÄãÔÚ±í pg_rewrite Àï¿´µ½µÄÁ½¸ö¶îÍâµÄÓÃÓÚ NEW ºÍ OLD ·¶Î§±íµÄ¼Ç¼£¨ÒòÀúÊ·Ô­Òò£¬ÔÚ´òÓ¡³öÀ´µÄ²éѯÊ÷Àï½Ð *NEW* ºÍ *CURRENT* £©¶Ô SELECT ¹æÔò²»¸ÐÐËȤ£®
ÏÖÔÚÎÒÃÇÌî³ä unit£¬shoe_data ºÍ shoelace_data ²¢ÇÒ Al ÉúƽµÚÒ»´Î¼üÈëÁË SELECT ÃüÁ
    al_bundy=> INSERT INTO unit VALUES ('cm', 1.0);
    al_bundy=> INSERT INTO unit VALUES ('m', 100.0);
    al_bundy=> INSERT INTO unit VALUES ('inch', 2.54);
    al_bundy=> 
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh1', 2, 'black', 70.0, 90.0, 'cm');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh2', 0, 'black', 30.0, 40.0, 'inch');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
    al_bundy=> INSERT INTO shoe_data VALUES 
    al_bundy->     ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
    al_bundy=> 
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl1', 5, 'black', 80.0, 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl2', 6, 'black', 100.0, 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl3', 0, 'black', 35.0 , 'inch');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl4', 8, 'black', 40.0 , 'inch');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl5', 4, 'brown', 1.0 , 'm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl6', 0, 'brown', 0.9 , 'm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl7', 7, 'brown', 60 , 'cm');
    al_bundy=> INSERT INTO shoelace_data VALUES 
    al_bundy->     ('sl8', 1, 'brown', 40 , 'inch');
    al_bundy=> 
    al_bundy=> 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)
ÕâÊÇ Al ¿ÉÒÔÔÚÎÒÃǵÄÊÓͼÉÏ×öµÄ×î¼òµ¥µÄ 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;
È»ºó°ÑÕâЩ½»¸ø¹æÔòϵͳ£®¹æÔòϵͳ°Ñ¿ÉÅÅÁÐÔªËØ£¨rangetable£©¹ýÂËÒ»±é£¬¼ì²éÒ»ÏÂÔÚ pg_rewrite ±íÀïÃæÓÐûÓÐÊÊÓøùØϵµÄÈκιæÔò£®µ±Îª shoelace ´¦Àí¿ÉÅÅÁÐÔªËØʱ£¨µ½Ä¿Ç°ÎªÖ¹Î¨Ò»µÄÒ»¸ö£©£¬Ëü»á·¢ÏÖ·ÖÎöÊ÷ÀïÓйæÔò '_RETshoelace'
    SELECT s.sl_name, s.sl_avail,
           s.sl_color, s.sl_len, s.sl_unit,
           float8mul(s.sl_len, u.un_fact) AS sl_len_cm
      FROM shoelace *OLD*, shoelace *NEW*,
           shoelace_data s, unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
×¢Òâ·ÖÎöÆ÷ÒѾ­°Ñ£¨SQLÀïµÄ£©¼ÆËãºÍ×ʸñ»»³ÉÁËÏàÓ¦µÄº¯Êý£®µ«Êµ¼ÊÉÏÕâûÓиıäʲô£®ÖØдµÄµÚÒ»²½ÊÇ°ÑÁ½¸ö¿ÉÅÅÁÐÔªËع鲢ÔÚÒ»Æ𣮽á¹ûÉú³ÉµÄ·ÖÎöÊ÷ÊÇ
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u;
µÚ¶þ²½°Ñ×ʸñµÄ¹æÔò¶¯×÷×·¼Óµ½·ÖÎöÊ÷ÀïÃæÈ¥£¬½á¹ûÊÇ
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
µÚÈý²½°Ñ·ÖÎöÊ÷ÀïµÄËùÓбäÁ¿ÓùæÔò¶¯×÷Àï¶ÔÓ¦µÄÄ¿±êÁбí´ïʽÌæ»»µô£¬ÕâЩ±äÁ¿ÊÇÒýÓÃÁË¿ÉÅÅÁÐÔªËØ£¨Ä¿Ç°À´ËµÊÇÕýÔÚ´¦ÀíµÄ shoelace £©µÄ±äÁ¿£®Õâ¾ÍÉú³ÉÁË×îºóµÄ²éѯ
    SELECT s.sl_name, s.sl_avail, 
           s.sl_color, s.sl_len, 
           s.sl_unit, float8mul(s.sl_len, u.un_fact) AS sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
°ÑÕâЩת»»»ØÈËÀà¿ÉÄÜʹÓõĠSQL Óï¾ä
    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;
ÕâÊÇÓ¦ÓõĵÚÒ»¸ö¹æÔò£®µ±×öÍêÕâЩºó£¬¿ÉÅÅÁÐÔªËؾÍÔö¼ÓÁË£®ËùÒÔ¹æÔòϵͳ¼ÌÐø¼ì²é·¶Î§±íÈë¿Ú£®ÏÂÒ»¸öÊǵÚ2¸ö £¨shoelace *OLD*£©£® shoelace £¨Ð¬´ø£©¹ØϵÓÐÒ»¸ö¹æÔò£¬µ«Õâ¸ö¿ÉÅÅÁÐÔªËØûÓб»ÈκηÖÎöÊ÷ÀïµÄ±äÁ¿ÒýÓã¬ËùÒÔ±»ºöÂÔ£®ÒòΪËùÓÐʣϵĿÉÅÅÁÐÔªËØÈë¿ÚҪôÊÇÔÚ pg_rewrite ±íÀïÃæûÓмǼ£¬ÒªÃ´ÊÇûÓÐÒýÓã¬Òò¶øµ½´ïÖØÅÅÁÐÔªËؽáβ£®ËùÒÔÖØд½áÊø£¬Òò¶øÉÏÃæµÄ½á¹û¾ÍÊǸøÓÅ»¯Æ÷µÄ×îÖÕ½á¹û£®ÓÅ»¯Æ÷ºöÂÔÄÇЩÔÚ·ÖÎöÊ÷Àï¶àÓàµÄûÓб»±äÁ¿ÒýÓõĿÉÅÅÁÐÔªËØ£¬²¢ÇÒÓɹ滮Æ÷/ÓÅ»¯Æ÷Éú³ÉµÄ£¨ÔËÐУ©¹æ»®½«ºÍ Al ÔÚÉÏÃæ¼üÈëµÄ SELECT ²éѯһÑù£¬¶ø²»ÊÇÊÓͼѡÔñ£®

ÏÖÔÚÎÒÃÇÈà Al Ãæ¶ÔÕâÑùÒ»¸öÎÊÌ⣺Blues Ðֵܵ½ÁËËûµÄЬµêÏëÂòһ˫ÐÂЬ£¬¶øÇÒ Blues ÐÖµÜÏëÂòÒ»ÑùµÄЬ×Ó£®²¢ÇÒÒªÁ¢¼´¾Í´©ÉÏ£¬ËùÒÔËûÃÇ»¹ÐèҪЬ´ø£®

Al ÐèÒªÖªµÀЬµêÀïÄ¿Ç°ÄÇÖÖЬÓкÏÊʵÄЬ´ø£¨ÑÕÉ«ºÍ³ß´ç£©ÒÔ¼°ÍêÈ«Ò»ÑùµÄÅäÖõĿâ´æÊÇ·ñ´óÓÚ»òµÈÓÚÁ½Ë«£®ÎÒÃǸæËßËûÈçºÎ×ö£¬È»ºóËûÎÊËûµÄÊý¾Ý¿â£º

    al_bundy=> 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)
Al ÊÇЬµÄר¼Ò£¬ÖªµÀÖ»ÓÐ sh1 µÄÀàÐÍ»áÊÊÓã¨sl7Ь´øÊÇ×ØÉ«µÄ£¬¶øÓë×ØÉ«µÄЬ´øÆ¥ÅäµÄЬ×ÓÊÇ Blues ÐֵܴÓÀ´²»´©µÄ£©£®

Õâ»Ø·ÖÎöÆ÷µÄÊä³öÊÇ·ÖÎöÊ÷

    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 int4ge(shoe_ready.total_avail, 2);
Ó¦ÓõĵÚÒ»¸ö¹æÔò½«ÊÇÓÃÓÚ shoe_ready ¹ØϵµÄ£¬½á¹ûÊÇÉú³É·ÖÎöÊ÷
    SELECT rsh.shoename, rsh.sh_avail,
           rsl.sl_name, rsl.sl_avail,
           min(rsh.sh_avail, rsl.sl_avail) AS total_avail
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl
     WHERE int4ge(min(rsh.sh_avail, rsl.sl_avail), 2)
       AND (bpchareq(rsl.sl_color, rsh.slcolor)
            AND float8ge(rsl.sl_len_cm, rsh.slminlen_cm)
            AND float8le(rsl.sl_len_cm, rsh.slmaxlen_cm)
           );
ʵ¼ÊÉÏ£¬×ʸñ/Ìõ¼þÀïµÄ AND ×Ӿ佫ÊÇÓµÓÐ×óÓÒ±í´ïʽµÄ²Ù×÷·û½Úµã£®µ«ÄÇÑù»á°Ñ¿É¶ÁÐÔ½µµÍ£¬¶øÇÒ»¹Óиü¶à¹æÔòÒª¸½¼Ó£®ËùÒÔÎÒÖ»ÊÇ°ÑËüÃÇ·ÅÔÚһЩԲÀ¨ºÅÀ½«ËüÃÇ°´³öÏÖ˳Ðò·Ö³ÉÂß¼­µ¥Ôª£¬È»ºóÎÒÃǼÌÐø¶Ô¸¶ÓÃÓÚ shoe £¨Ð¬£©¹ØϵµÄ¹æÔò£¬ÒòΪËüÊÇÒýÓÃÁ˵ÄÏÂÒ»¸ö¿ÉÅÅÁÐÔªËز¢ÇÒÓÐÒ»Ìõ¹æÔò£®Ó¦ÓùæÔòºóµÄ½á¹ûÊÇ
    SELECT sh.shoename, sh.sh_avail,
           rsl.sl_name, rsl.sl_avail,
           min(sh.sh_avail, rsl.sl_avail) AS total_avail,
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, shoe *OLD*,
           shoe *NEW*, shoe_data sh,
           unit un
     WHERE (int4ge(min(sh.sh_avail, rsl.sl_avail), 2)
            AND (bpchareq(rsl.sl_color, sh.slcolor)
                 AND float8ge(rsl.sl_len_cm, 
                              float8mul(sh.slminlen, un.un_fact))
                 AND float8le(rsl.sl_len_cm, 
                              float8mul(sh.slmaxlen, un.un_fact))
                )
           )
       AND bpchareq(sh.slunit, un.un_name);
×îºó£¬ÎÒÃÇ°ÑÒѾ­ÊìÖªµÄÓÃÓÚ shoelace £¨Ð¬´ø£©µÄ¹æÔò¸½¼ÓÉÏÈ¥£¨Õâ»ØÎÒÃÇÔÚÒ»¸ö¸ü¸´ÔӵķÖÎöÊ÷ÉÏ£©µÃµ½
    SELECT sh.shoename, sh.sh_avail,
           s.sl_name, s.sl_avail,
           min(sh.sh_avail, s.sl_avail) AS total_avail
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, shoe *OLD*,
           shoe *NEW*, shoe_data sh,
           unit un, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE (    (int4ge(min(sh.sh_avail, s.sl_avail), 2)
                 AND (bpchareq(s.sl_color, sh.slcolor)
                      AND float8ge(float8mul(s.sl_len, u.un_fact), 
                                   float8mul(sh.slminlen, un.un_fact))
                      AND float8le(float8mul(s.sl_len, u.un_fact), 
                                   float8mul(sh.slmaxlen, un.un_fact))
                     )
                )
            AND bpchareq(sh.slunit, un.un_name)
           )
       AND bpchareq(s.sl_unit, u.un_name);
ͬÑù£¬ÎÒÃÇ°ÑËü¹é½áΪһ¸öÓë×îÖյĹæÔòϵͳÊä³öµÈЧµÄÕæʵ SQL Óï¾ä£º
    SELECT sh.shoename, sh.sh_avail,
           s.sl_name, s.sl_avail,
           min(sh.sh_avail, s.sl_avail) AS total_avail
      FROM shoe_data sh, shoelace_data s, unit u, unit un
     WHERE min(sh.sh_avail, s.sl_avail) >= 2
       AND s.sl_color = sh.slcolor
       AND s.sl_len * u.un_fact >= sh.slminlen * un.un_fact
       AND s.sl_len * u.un_fact <= sh.slmaxlen * un.un_fact
       AND sh.sl_unit = un.un_name
       AND s.sl_unit = u.un_name;
µÝ¹éµÄ´¦Àí¹æÔò½«°ÑÒ»¸ö´ÓÊÓͼµÄ SELECT ¸ÄдΪһ¸ö·ÖÎöÊ÷£¬ÕâÑù×öµÈЧÓÚÈç¹ûûÓÐÊÓͼ´æÔÚʱ Al ²»µÃ²»¼üÈëµÄ£¨SQL£©ÃüÁ
×¢Ò⣺ Ä¿Ç°¹æÔòϵͳÖÐûÓÐÓÃÓÚÊÓͼ¹æÔòµÝ¹éÖÕÖ¹»úÖÆ£¨Ö»ÓÐÓÃÓÚÆäËû¹æÔòµÄ£©£®ÕâÒ»µã²»»áÔì³ÉÌ«´óµÄË𺦣¬ÒòΪ°ÑÕâ¸ö£¨¹æÔò£©ÎÞÏÞÑ­»·£¨°Ñºó¶Ë´Ý»Ù£¬Ö±µ½ºÄ¾¡Äڴ棩µÄΨһ·½·¨ÊÇ´´½¨±íÈ»ºóÊÖ¹¤Óà CREATE RULE ÃüÁî´´½¨ÊÓͼ¹æÔò£¬Õâ¸ö¹æÔòÊÇÕâÑùµÄ£ºÒ»¸ö´ÓÆäËû£¨±í/ÊÓͼ£©Ñ¡Ôñ£¨select£©µÄÊÓͼѡÔñ£¨select£©ÁËËü×ÔÉí£®Èç¹ûʹÓÃÁË CREATE VIEW £¬ÕâÒ»µãÊÇÓÀÔ¶²»»á·¢ÉúµÄ£¬ÒòΪµÚ¶þ¸ö¹Øϵ²»´æÔÚÒò¶øµÚÒ»¸öÊÓͼ²»ÄÜ´ÓµÚ¶þ¸öÀïÃæÑ¡Ôñ£¨select£©£®

·Ç SELECT Óï¾äµÄÊÓͼ¹æÔò

ÓÐÁ½¸ö·ÖÎöÊ÷µÄϸ½ÚÎÒÃÇÔÚÉÏÃæµÄÊÓͼ¹æÔòÖÐûÓÐÉæ¼°µ½£®¾ÍÊÇÃüÁîÀàÐͺͽá¹û¹Øϵ£®Êµ¼ÊÉÏ£¬ÊÓͼ¹æÔò²»ÐèÒªÕâЩÐÅÏ¢£®

Ò»¸ö SELECT µÄ·ÖÎöÊ÷ºÍÓÃÓÚÆäËûÃüÁîµÄ·ÖÎöÊ÷Ö»ÓÐÉÙÊý¼¸¸öÇø±ð£®ÏÔÈ»ËüÃÇÓÐÁíÒ»¸öÃüÁîÀàÐͲ¢ÇÒÕâ»Ø½á¹û¹ØϵָÏòÉú³É½á¹ûµÄ¿ÉÅÅÁÐÔªËØÈë¿Ú£®ÈκÎÆ䶫Î÷¶¼ÍêÈ«ÊÇÒ»ÑùµÄ£®ËùÒÔÈç¹ûÓÐÁ½¸ö±í t1 ºÍ t2 ·Ö±ðÓÐ×ֶΠa ºÍ b £¬ÏÂÃæÁ½¸öÓï¾äµÄ·ÖÎöÊ÷

    SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

    UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
¼¸ºõÊÇÒ»ÑùµÄ£® ½á¹ûÊÇ£¬Á½¸ö·ÖÎöÊ÷Éú³ÉÏàËƵÄÖ´Ðй滮£®ËüÃǶ¼ÊÇÁ½¸ö±íµÄÁªºÏ£®¶ÔÓÚ 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 ÀïÈ¥£®µ«ÊÇÏÖÓеÄ666ÐмǼÖеÄÄÄÒ»ÐÐÒª±»ÐÂÐÐÈ¡´úÄØ£¿±»Ö´Ðеģ¨²éѯ£©¹æ»®ÊÇÒ»¸ö´øÓÐ×ʸñ£¨Ìõ¼þ£©µÄÁªºÏ£¬¸ÃÁªºÏ¿ÉÄÜÒÔδ֪˳ÐòÉú³É 0 µ½ 666 ¼äÈÎÒâÊýÁ¿µÄÐУ®

Òª½â¾öÕâ¸öÎÊÌ⣬ÔÚ UPDATE ºÍ DELETE Óï¾äµÄÄ¿±êÁбíÀïÃæÔö¼ÓÁËÁíÍâÒ»¸öÈë¿Ú£®µ±Ç°µÄ¼Ç¼ ID£¨ctid£©£®ÕâÊÇÒ»¸öÓÐ×ÅÌØÊâÌØÐÔµÄϵͳ×ֶΣ®Ëü°üº¬ÐÐÔÚ£¨´æ´¢£©¿éÖеģ¨´æ´¢£©¿éÊýºÍλÖÃÐÅÏ¢£®ÔÚÒÑÖª±íµÄÇé¿öÏ£¬ctid ¿ÉÒÔͨ¹ý¼òµ¥µØ²éÕÒijһÊý¾Ý¿éÔÚÒ»¸ö 1.5GB ´óСµÄ°üº¬³É°ÙÍòÌõ¼Ç¼µÄ±íÀïÃæ²éÕÒijһÌض¨ÐУ®ÔÚ°Ñ ctid ¼Óµ½Ä¿±êÁбíÖÐÈ¥ÒÔºó£¬×îÖյĽá¹û¿ÉÒÔ¶¨ÒåΪ

    SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
ÏÖÔÚ£¬ÁíÒ»¸ö Postgres µÄϸ½Ú½øÈëµ½Õâ¸ö½×¶ÎÀïÁË£®Õâʱ£¬±íµÄÐл¹Ã»Óб»¸²¸Ç£¬Õâ¾ÍÊÇΪʲô ABORT TRANSACTION ËٶȿìµÄÔ­Òò£®ÔÚÒ»¸ö UPDATE ÀеĽá¹ûÐвåÈëµ½±íÀÔÚͨ¹ý ctid ²éÕÒÖ®ºó£©²¢ÇÒ°Ñ ctid Ö¸ÏòµÄ cmax ºÍ xmax Èë¿ÚµÄÐеļǼͷÉèÖÃΪµ±Ç°ÃüÁî¼ÆÊýÆ÷ºÍµ±Ç°½»Ò×ID£®ÕâÑù¾ÉµÄÐоͱ»Òþ²ØÆðÀ´²¢ÇÒÔÚÊÂÎñÌá½»Ö®ºó"Îü³¾Æ÷"£¨vacumm cleaner£©¾Í¿ÉÒÔÕæÕý°ÑËüÃÇɾ³ýµô£®

ÖªµÀÁËÕâЩ£¬ÎÒÃǾͿÉÒÔ¼òµ¥µÄ°ÑÊÓͼµÄ¹æÔòÓ¦Óõ½ÈÎÒâÃüÁîÖУ®ËüÃÇ£¨ÊÓͼºÍÃüÁûÓÐÇø±ð£®

Postgres ÀïÊÓͼµÄÇ¿´óÄÜÁ¦

ÉÏÃæÑÝʾÁ˹æÔòϵͳÈçºÎÈںϵ½ÊÓͼ¶¨ÒåµÄ³õʼ·ÖÎöÊ÷ÖÐÈ¥£®ÔÚµÚ¶þ¸öÀý×ÓÀһ¸ö¼òµ¥µÄ¶ÔÊÓͼµÄ SELECT ´´½¨ÁËÒ»¸ö4¸ö±íÁªºÏµÄ·ÖÎöÊ÷£¨unit ÒÔ²»Í¬µÄÃû³ÆÓÃÁËÁ½´Î£©£®

Òæ´¦

ÔÚ¹æÔòϵͳÀïʵÏÖÊÓͼµÄºÃ´¦ÊÇÓÅ»¯Æ÷ÔÚÒ»¸ö·ÖÎöÊ÷ÀïÓµÓÐËùÓÐÐÅÏ¢£ºÓ¦¸ÃɨÃèÄĸö±í + ±íÖ®¼äµÄ¹Øϵ + ÊÓͼµÄ×ʸñÏÞÖÆ + ³õʼ²éѯµÄ×ʸñ£¨Ìõ¼þ£©£®²¢ÇÒÒÀÈ»ÊÇÔÚ×î³õµÄ²éѯÒѾ­ÊÇÒ»¸öÊÓͼµÄÁªºÏµÄÇé¿öÏ£®ÏÖÔÚÓÅ»¯Æ÷±ØÐë¾ö¶¨Ö´ÐвéѯµÄ×îÓÅ·¾¶£®ÓÅ»¯Æ÷ÓµÓÐÔ½¶àÐÅÏ¢£¬ËüµÄ¾ö²ß¾ÍÔ½ºÃ£®²¢ÇÒ Postgres ÀïµÄ¹æÔòϵͳµÄʵÏÖ±£Ö¤ÕâЩÐÅÏ¢ÊÇÄ¿Ç°ÄÜ»ñµÃµÄËùÓÐÐÅÏ¢£®

¿¼ÂÇ

ºÜ³¤Ò»¶Îʱ¼äÀPostgres ¹æÔòϵͳ±»ÈÏΪÊÇÓÐÎÊÌâµÄ£®¹æÔòµÄʹÓÃÊDz»±»ÍƼöµÄ¶øÇÒÄܹ¤×÷µÄ²¿·ÖÖ»ÓÐÊÓͼ¹æÔò£®¶øÇÒÕâЩÊÓͼ¹æÔò»¹³£³£·¸Ã«²¡£¬ÒòΪ¹æÔòϵͳ²»ÄÜÔÚ³ý SELECT Óï¾äÍâµÄÓï¾äÀïÓ¦ÓÃËüÃÇ£¨ÀýÈçÓôÓÒ»¸öÊÓͼÀ´µÄÊý¾Ý UPDATE ¾Í²»Äܹ¤×÷£©£®

ÔÚÄǶÎʱ¼äÀ¿ª·¢¹¤×÷¼ÌÐø½øÐУ¬Ðí¶àÐÂÌØÐÔ¼ÓÈëµ½·ÖÎöÆ÷ºÍÓÅ»¯Æ÷À¹æÔòϵͳµÄ¹¦ÄÜÔ½À´Ô½³Â¾É¶øÇÒÔ½À´Ô½ÄÑÒÔÐÞ¸´ËüÃÇ£®

´Ó 6.4 Æð£¬Ä³¸öÈË£¨Òë×¢£º¸Ðл Jan £©¹ØÆðÃÅ£¬ÉîÎüÒ»¿ÚÆø°ÑËùÓÐÕâЩÀö«Î÷³¹µ×ÐÞÀíÁËÒ»±ã£®½á¹û¾ÍÊDZ¾ÕÂÃèÊöµÄ¹æÔòϵͳ£®µ«ÊÇ»¹ÓÐһЩÎÞ·¨´¦ÀíµÄ¹¹ÔìºÍһЩʧЧµÄµØ·½£¬Ö÷ÒªÔ­ÒòÊÇÕâЩ¶«Î÷ÏÖÔÚ²»±» Postgres ²éѯÓÅ»¯Æ÷Ö§³Ö£®

    ´ø¾Û¼¯×ֶεÄÊÓͼÓкܴóÎÊÌ⣮ÔÚ×ʸñÁÐÀïµÄ¾Û¼¯±í´ïʽ±ØÐëͨ¹ý×Ó²éѯʹÓã®Ä¿Ç°²»¿ÉÄÜ×öÁ½¸öÊÓͼµÄÁªºÏ²éѯ -- ÿ¸ö¶¼ÓÐÒ»¸ö¾Û¼¯×ֶΣ¬²¢ÇÒÓë×ʸñÁÐÀïµÄÁ½¸ö¾Û¼¯µÄ½á¹û½øÐбȽϣ®µ«ÎÒÃÇ¿ÉÄÜ°ÑÕâЩ¾Û¼¯±í´ïʽ·Åµ½º¯ÊýÀͨ¹ýºÏÊʵIJÎÊýÔÚÊÓͼ¶¨ÒåÖÐʹÓÃËüÃÇ£®

    ÁªºÏ£¨union£©µÄÊÓͼµ±Ç°²»±»Ö§³Ö£®¾¡¹ÜÎÒÃǺÜÈÝÒ×°ÑÒ»¸ö¼òµ¥µÄSELECT ÖØд³ÉÁªºÏ£¨union£©£®µ«ÊÇÈç¹û¸ÃÊÓͼÊÇÒ»¸öÕýÔÚ×ö¸üеÄÁªºÏµÄÒ»²¿·Öʱ¾Í»áÓÐÂé·³£®

    ÊÓͼÀïµÄ ORDER BY ×Ӿ䲻±»Ö§³Ö£®

    ÊÓͼÀïµÄ DISTINCT ²»±»Ö§³Ö£®
ΪʲôÓÅ»¯Æ÷²»ÄÜ´¦ÀíÄÇЩ·ÖÎöÆ÷Òò SQL Óï·¨ÏÞÖÆÉú³ÉµÄ"²»Ó¦¸ÃÉú³É"·ÖÎöÊ÷£¿ÎÒÃÇҲûÓгä·ÖµÄÀíÓÉ£®×÷ÕßÏ£ÍûÕâЩÎÊÌâÔÚ½«À´»áÏûʧ£®

Èç´ËʵÏֵĸ±×÷ÓÃ

ʹÓÃÉÏÃæÃèÊöµÄ¹æÔòϵͳÀ´ÊµÏÖÊÓͼÓÐ×ÅÓÐȤµÄ¸±×÷Óã®ÏÂÃæµÄ£¨ÃüÁ¿´ÆðÀ´²»»á¹¤×÷£º
    al_bundy=> INSERT INTO shoe (shoename, sh_avail, slcolor)
    al_bundy->     VALUES ('sh5', 0, 'black');
    INSERT 20128 1
    al_bundy=> SELECT shoename, sh_avail, slcolor FROM shoe_data;
    shoename  |sh_avail|slcolor   
    ----------+--------+----------
    sh1       |       2|black     
    sh3       |       4|brown     
    sh2       |       0|black     
    sh4       |       3|brown     
    (4 rows)
ÓÐȤµÄÊÂÇéÊÇ INSERT µÄ·µ»ØÂë¸øÎÒÃÇÒ»¸ö¶ÔÏó±êʶ£¨OID£©²¢ÇÒ¸æËßÎÒÃDzåÈëÁËÒ»ÐУ®µ«¸ÃÐÐûÓÐÔÚ shoe_data Àï³öÏÖ£®ÍùÊý¾Ý¿âĿ¼À￴ʱÎÒÃÇ¿ÉÒÔ·¢ÏÖ£¬ÓÃÓÚÊÓͼ¹Øϵ shoe µÄÊý¾Ý¿âÎļþ¿´À´ÏÖÔÚÓÐÁËÊý¾Ý¿é£®Êµ¼ÊÇé¿öÕýÊÇÈç´Ë£®

ÎÒÃÇ»¹¿ÉÒÔʹÓÃÒ»Ìõ DELETE ÃüÁÈç¹û¸ÃÃüÁîûÓУ¨×ʸñ£©Ìõ¼þ£¬Ëü»á¸æËßÎÒÃÇÓÐÒ»Ðб»É¾³ýÁ˲¢ÇÒÏÂÒ»´ÎÇåÀíʱ½«°ÑÎļþ¸´Î»ÎªÁã³ß´ç£®

ÕâÖÖÏÖÏóµÄÔ­ÒòÊÇ INSERT Éú³ÉµÄ·ÖÎöÊ÷ûÓÐÔÚÈκαäÁ¿ÀïÒýÓà shoe £¨Ð¬£©¹Øϵ£®Ä¿±êÁбíÖ»°üº¬³£Á¿Öµ£®ËùÒÔ²»»á¸½¼ÓÈκιæÔò£¬²éѯ²»¼ÓÐ޸ĵؽøÈëÖ´ÐвåÈë¸ÃÐУ® DELETE ʱÍêÈ«Ò»Ñù£®

Òª¸Ä±äÕâЩÎÊÌ⣬ÎÒÃÇ¿ÉÒÔ¶¨ÒåһЩ¹æÔòÓÃÒÔ¸Ä±ä ·Ç£­SELECT ²éѯµÄÌØÐÔ£®ÕâÊÇÏÂÒ»ÕµÄÄÚÈÝ£®

Ò