SQL-Ex blog

Создание правил

Правило можно создать при помощи следующего синтаксиса:

CREATE [ OR REPLACE ] RULE name AS ON event

    TO table_name [ WHERE condition ]

    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREATE OR REPLACE RULE будет создавать новое правило или заменять существующее правило с тем же именем для той же таблицы.

Параметры:

1. name: имя правила при создании или замене


2. event: одно из событий - SELECT, INSERT, UPDATE или DELETE


3. table_name: имя таблицы (или представления), к которой это правило применяется


4. condition: выражение SQL, указывающее, когда это правило должно применяться


5. INSTEAD: определяет альтернативный образ действий вместо данного


6. ALSO: определяет команды, которые должны применяться в дополнение к исходным командам. Если не упоминается ни Instead, ни Also, по умолчанию используется Also


7. command: альтернативное действие, которое требуется выполнить

В condition и command мы можем использовать специальные имена таблиц NEW и OLD для ссылок на значения в таблице, указанной в table_name. Имя таблицы NEW действительно в правилах ON INSERT и ON UPDATE для ссылки на новую строку - вставленную или обновленную. Имя таблицы OLD действительно в правилах ON UPDATE и ON DELETE для ссылок на существующую строку, обновляемую или удаляемую.

Создание нашего первого правила

Предположим, что нам требуется запретить обновление любой информации для сотрудника с именем John и фамилией Smith в таблице EMPLOYEE. Чтобы удовлетворить этому требованию с помощью правила, мы можем создать такое:

CREATE OR REPLACE RULE PROTECT_JOHN_SMITH 

AS ON UPDATE TO EMPLOYEE 

WHERE old.FIRST_NAME = 'JOHN' 

AND old.last_name = 'SMITH'

DO INSTEAD NOTHING;

Если мы выполним запрос на обновление:

UPDATE EMPLOYEE SET FIRST_NAME = 'ALIA' WHERE FIRST_NAME = 'JOHN';

то никакого обновления не произойдет.

Практические сценарии

Вот несколько общих сценариев, где правила оказываются полезными.

Случай использования: 1

Предположим, что нам требуется запретить всем обновлять любые данные в таблице employee. Для достижения этого мы можем написать такое правило:

CREATE RULE UPDATE_EMPLOYEE AS ON UPDATE TO EMPLOYEE 

    DO INSTEAD

    NOTHING;

Когда правило создано, попытайтесь выполнить запрос:

UPDATE EMPLOYEE SET EMPLOYEE_ID = 11111125 WHERE FIRST_NAME = 'JOHN';

Никакие строки не обновляются.

Случай использования: 2

Предположим, что требуется отслеживать любые обновления, сделанные в таблице EMPLOYEE, фиксируя их в другой таблице с именем EMPLOYEE_LOG. Для этого нам сначала потребуется наличие таблицы EMPLOYEE_LOG следующего вида:

CREATE TABLE EMPLOYEE_LOG (

    EMPLOYEE_NAME    TEXT, 

    EMPLOYEE_ID   INTEGER, 

    EMPLOYEE_USER    TEXT, 

    CREATE_DATETIME   TIMESTAMP 

);

Затем мы можем создать такое правило:

CREATE RULE LOG_EMPLOYEE AS ON UPDATE TO EMPLOYEE

    WHERE NEW.EMPLOYEE_ID <> OLD.EMPLOYEE_ID

    DO INSERT INTO EMPLOYEE_LOG VALUES (

                                    NEW.FIRST_NAME,

                                    NEW.EMPLOYEE_ID,

                                    CURRENT_USER,

                                    CURRENT_TIMESTAMP

                                );

Когда правило создано, мы можем теперь выполнить запрос на обновление, например:

UPDATE EMPLOYEE SET EMPLOYEE_ID = 6 WHERE FIRST_NAME = 'JOHN';

Этот запрос обновит соответствующую строку в таблице EMPLOYEE, а также сделает такую запись в EMPLOYEE_LOG:

Замечания

Следующие моменты следует иметь в виду при создании (или обновлении) правил в базе данных:

1. Вы должны быть владельцем таблицы, чтобы создавать или изменять правила для неё.


2. Важно следить, чтобы не возникали цикличные правила.

Вот пример цикличного правила:

CREATE RULE "RETURN_STAFF" AS

    ON SELECT TO STAFF

    DO INSTEAD

        SELECT * FROM DEPARTMENT;

CREATE RULE "RETURN_DEPARTMENT" AS

    ON SELECT TO DEPARTMENT

    DO INSTEAD

        SELECT * FROM STAFF;

Теперь давайте выполним запрос:

SELECT * FROM STAFF;

Второе правило выше противоположно первому, и создает циклическую зависимость. Мы указали, что команда SELECT на таблице STAFF вместо этого должна возвращать строки из таблицы DEPARTMENT. С другой стороны, команда SELECT на таблице DEPARTMENT должна вместо этого возвращать строки таблицы STAFF. Поэтому мы должны избегать таких ситуаций в своих проектах.

Заключение

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