Синтаксис:

CREATE [ OR REPLACE ] RULE name AS ON событие
    TO таблица [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | команда | ( команда ; команда ... ) }

в виду. Далее, правила обновления означает правила, определенные для INSERT, UPDATE, или DELETE.

Правила обновления применяются системой правил в тех случаях, когда результирующее отношение и тип команды дерева запроса совпадают с объектом и событием, указанными в CREATE RULE команде. Для правил обновления система правил создает список деревьев запросов. В исходном состоянии список деревьев запросов пуст. Количество действий может быть нулевым (с использованиемNOTHING ключевого слова), одно или несколько. Для упрощения рассмотрим правило с одним действием. Такое правило может иметь или не иметь условие (qualification), а также может быть INSTEAD или ALSO (по умолчанию).

Что представляет собой условие правила? Это ограничение, определяющее, в каких случаях действия правила должны быть выполнены. Данное условие может ссылаться только на псевдоотношения NEW и/или OLD, которые по сути представляют собой отношение, указанное в качестве объекта (но со специальным значением).

Таким образом, существует три случая, порождающих следующие деревья запросов для правила с одним действием.

Наконец, если правило определено как ALSO, неизмененное исходное дерево запроса добавляется в список. Поскольку только правила с условиями INSTEAD уже добавляют исходное дерево запроса, в итоге получается либо одно, либо два выходных дерева запроса для правила с одним действием.

Для ON INSERT правил исходный запрос (если он не подавлен параметром INSTEAD) выполняется перед любыми действиями, добавленными правилами. Это позволяет действиям видеть вставленные строки. Однако для ON UPDATE и ON DELETE правил исходный запрос выполняется после действий, добавленных правилами. Это гарантирует, что действия «видят» строки, подлежащие обновлению или удалению; в противном случае действия могут не выполнить никаких операций, так как не будет найдено строк, соответствующих их условиям.

Деревья запросов, созданные в результате выполнения действий правил, снова направляются в систему переписывания; при этом могут быть применены дополнительные правила, что приведет к увеличению или уменьшению количества деревьев запросов. Таким образом, действия правила должны иметь либо иной тип команды, либо иное результирующее отношение по сравнению с самим правилом, иначе этот рекурсивный процесс приведет к бесконечному циклу. (Рекурсивное развертывание правила обнаруживается и классифицируется как ошибка).

Деревья запросов, содержащиеся в действиях pg_rewrite системного каталога, являются лишь шаблонами. Поскольку они могут ссылаться на записи таблицы отношений для NEW и OLD, перед их использованием необходимо выполнить определенные подстановки. При любой ссылке на NEW, в целевом списке исходного запроса выполняется поиск соответствующего элемента. Если он найден, выражение этого элемента заменяет ссылку. В противном случае NEW означает то же самое, что и OLD (для UPDATE) или заменяется значением NULL (для INSERT). Любая ссылка на OLD заменяется ссылкой на элемент таблицы отношений, являющийся результирующим отношением.

После того как система завершит применение правил обновления, она применяет правила представлений к полученным деревьям запроса. Представления не могут добавлять новые действия по обновлению, поэтому применять правила обновления к результату перезаписи представлений не требуется.

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 | Ал      | Tue Oct 20 16:14:45 1998 MET DST
(1 строка)

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

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 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;

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 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';

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';

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';

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';

UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';

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';

UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';

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';

Простой способ защитить отношения-представления от упомянутой выше возможности выполнения команды 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, результирующий список деревьев запросов будет пустым, и весь запрос будет аннулирован, так как после завершения работы системы правил не останется ничего, что можно было бы оптимизировать или выполнить.

Более сложный способ использования системы правил заключается в создании правил, которые преобразуют дерево запроса в дерево, выполняющее требуемую операцию над реальными таблицами. Чтобы реализовать это для 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;

Если вы хотите обеспечить поддержку RETURNING запросов к представлению, необходимо включить в правила RETURNING предложения, вычисляющие строки представления. Обычно это довольно просто для представлений на базе одной таблицы, но может быть трудоёмким для представлений с соединением, таких как shoelace. Пример для случая со вставкой (INSERT):

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
    )
    RETURNING
           shoelace_data.*,
           (SELECT shoelace_data.sl_len * u.un_fact
            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);

Обратите внимание, что это правило поддерживает как INSERT и INSERT RETURNING запросы к представлению, так и — RETURNING предложение просто игнорируется для INSERT.

Теперь предположим, что время от времени в магазин поступает партия шнурков, а вместе с ней и большой список номенклатуры. Но вы не хотите вручную обновлять 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 строки)

Проверьте текущее состояние данных:

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 строк)

Теперь поместите поступившие шнурки в:

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 | черный    |     40 | дюйм    |     101.6
 sl3      |       10 | черный    |     35 | дюйм    |      88.9
 sl8      |       21 | коричневый    |     40 | дюйм    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | коричневый    |    0.9 | м       |        90
(8 строк)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when
---------+----------+--------+----------------------------------
 sl7     |        6 | Ал     | Вт Окт 20 19:14:45 1998 MET DST
 sl3     |       10 | Ал     | Вт Окт 20 19:25:16 1998 MET DST
 sl6     |       20 | Ал     | Вт Окт 20 19:25:16 1998 MET DST
 sl8     |       21 | Ал     | Вт Окт 20 19:25:16 1998 MET DST
(4 строки)

Этот путь от исходного варианта INSERT ... SELECT до данных результатов довольно велик. Описание преобразования дерева запроса будет последним разделом в этой главе. Сначала рассмотрим результат работы анализатора:

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;

и отбрасывает исходный запрос INSERT для shoelace_ok. Этот переписанный запрос снова передаётся системе правил, и второе применённое правило 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 , генерирующее дополнительное дерево запроса:

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;

После этого у системы правил заканчиваются правила, и она возвращает сформированные деревья запросов.

Таким образом, в итоге получаются два итоговых дерева запросов, эквивалентных SQL операторам:

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
              ->  Сортировка
                    ->  Seq Scan в s
        ->  Seq Scan
              ->  Сортировка
                    ->  Seq Scan в shoelace_arrive
  ->  Seq Scan в shoelace_data

в то время как отсутствие лишнего элемента таблицы отношений приведет к

Merge Join
  ->  Seq Scan
        ->  Сортировка
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Сортировка
              ->  Seq Scan on shoelace_arrive

что создает идентичные записи в таблице лога. Таким образом, система правил вызвала одно лишнее сканирование таблицы, shoelace_data которое совершенно не требуется. И то же самое избыточное сканирование выполняется еще раз в UPDATE. Однако реализация этой возможности была крайне сложной задачей.

В завершение продемонстрируем возможности Digital Q.DataBase системы правил. Предположим, вы добавляете в базу данных шнурки необычных цветов:

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

Теперь мы хотим настроить систему так, чтобы отсутствующие на складе шнурки, не имеющие соответствия по цвету, удалялись из базы данных. Чтобы немного усложнить задачу для Digital Q.DataBase, мы не будем удалять их напрямую. Вместо этого создадим ещё одно представление:

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 | коричневый    |    0.9 | м       |        90
(9 строк)

A DELETE для представления с условием в виде подзапроса, использующего в общей сложности 4 вложенных или соединенных представления (при этом одно из них само содержит условие в виде подзапроса с представлением, и в нем используются вычисляемые столбцы), переписывается в одно дерево запроса, которое удаляет данные из реальной таблицы.

Вероятно, в реальных условиях существует лишь несколько ситуаций, когда подобная конструкция действительно необходима. Однако это дает уверенность в том, что такой механизм работает корректно.