В некотором царстве, в некотором государстве... понадобилось мне добавить триггер в модель на pgModeler. Что сделать достаточно легко. А вот добавить триггерную функцию… Тоже легко, но пришлось немного поразбираться с параметрами, предлагаемыми для заполнения/выбора в интерфейсе.
pgModeler — это весьма неплохой инструмент для проектирования баз данных, который умеет генерировать sql-скрипты для PostgreSQL. Подробно об этом инструменте и его возможностях можно почитать на официальном сайте.
В качестве примера рассмотрим упрощённую модель с одной таблицей.
Добавим функцию в модель.
После этого откроется окно с различными доступными для редактирования параметрами, с которыми будет создана функция. При этом некоторые поля уже будут заполнены значениями по умолчанию.
Рассмотрим эти параметры подробней.
Думаю, с параметрами Name, Schema, Owner и Comment всё ясно — это название функции, схема базы данных, владелец и комментарий к этой функции соответственно.
Language — это название языка, на котором будет реализована функция. Если честно, то мне ещё ни разу не приходилось писать функции для PostgreSQL на чём-то кроме plpgsql. Поэтому именно это значение для параметра и было мною выбрано.
Return Method. Так как в триггерной функции ни таблицу (Table) ни множество (Set) значений нам возвращать не нужно, оставляем Simple.
С параметрами в блоке Data Type, в общем-то, тоже всё просто. Т.к. функция будет вызываться в триггере, то в поле Type указываем trigger (поле Format при этом будет заполнено автоматически значением trigger). Поле Dimension (единственное незадезабленное цифровое поле в этом блоке) нужно для указания размерности массива возвращаемого значения. Но так как нам нужно просто одно значение, а не массив, то оставляем в этом поле 0.
С оставшимися параметрами всё не так очевидно, по крайней мере для меня, т.к. никогда не приходилось над ними задумываться при обычном создании функции в PostgreSQL.
Function Type может принимать одно из трёх значений: IMMUTABLE, STABLE и VOLATILE. Из официальной документации PostgreSQL можно узнать, что эти аргументы информируют оптимизатор запросов о поведении функции.
Соответственно, если в триггерной функции требуется изменять базу данных, то IMMUTABLE не подходит. Функция с параметром STABLE не подходит для триггеров AFTER, желающих прочитать строки, изменённые текущей командой. Остаётся VOLATILE, у которого отсутствуют указанные выше проблемы. Он также будет указан по умолчанию, если при создании функции не указан ни один из перечисленных выше аргументов.
Security может принимать одно из двух значений: SECURITY DEFINER и SECURITY INVOKER и отвечает за то, с правами какого пользователя она будет вызвана.
По умолчанию используется SECURITY INVOKER, поэтому можно его и оставить.
Behavior может принимать одно из трёх значений: STRICT, RETURNS NULL ON NULL INPUT и CALLED ON NULL INPUT и показывает, как будет вести себя функция, если среди её аргументов окажутся значения NULL.
По умолчанию используется CALLED ON NULL INPUT. Поэтому, так же, можно его и оставить.
Rows Returned показывает число строк, которое будет ожидать планировщик. Значение указывается для функций, которые возвращают множества. Т.к. у нас функция возвращает одно значение, оставляем 0.
Execution Cost задаёт стоимость выполнения этой функции для планировщика. Для языка plpgsql по умолчанию она равна 100. Поэтому это значение и укажем.
Windown Func. означает, что будет создана оконная функция. В нашем случае, т.к. нам нужна триггерная функция, это значение указывать не нужно (ну и вообще, в самой документации пишут, что этот параметр имеет смысл указывать только для функций, написанных на C).
Leakproof показывает, что функция герметичная, т.е. что она не раскрывает информацию о своих аргументах (например, не выводит их значения в сообщении об ошибке), кроме как возвращая результат. Т.к. триггерная функция не принимает аргументов, то данный параметр не нужно указывать.
Итак, с параметрами функции покончено. Само тело функции можно написать в этом же окне на вкладке Definition. Переходим к созданию самого триггера.
После этого появится окно создания триггера.
Рассмотрим параметры, которые можно задать в этом окне.
С параметрами Name, Alias и Comment опять же всё понятно — это название триггера, алиас и комментарий к триггеру соответственно.
Excution показывает как будет выполняться данный триггер и может принимать одно из следующих значений: BEFORE, AFTER и INSTEAD OF, — которые означают, что функция будет выполняться до, после или вместо события.
FOR EACH ROW определяет, будет ли процедура триггера срабатывать один раз для каждой строки. Если не указать, то будет установлен параметр FOR EACH STATEMENT, который определяет, что процедура триггера срабатывает один раз для SQL-оператора.
Event определяет то, какие события необходимо обрабатывать в данном триггере. Можно указать несколько событий. События бывают следующих типов: INSERT, UPDATE, DELETE и TRUNCATE. Они возникают, когда будет вызвана соответствующая команда с одноимённым SQL-оператором.
Constraint указывает, что будет создан триггер ограничения. Триггеры ограничений используются для генерации исключений при нарушении ограничений. Подробнее о них можно почитать в официальной документации.
Для триггера ограничения можно указать Deferrable, который определяет время срабатывания триггера. Этот параметр может принимать одно из следующих значений: INITIALLY IMMEDIATE или INITIALLY DEFERRED.
Refer. Table — имя таблицы, на которую ссылается ограничение. Используется для ограничений внешнего ключа и допускается только для триггеров ограничений.
Condition — это условие, которое определяет, будет ли выполняться функция триггера. Для триггеров FOR EACH ROW в этом поле можно обращаться к старым и новым значениям через OLD и NEW соответственно (т.е. так же, как и в теле триггерной функции).
Arguments — список аргументов, которые будут переданы в триггерную функцию, когда сработает триггер. В качестве аргументов функции передаются строковые константы.
Columns — можно указать только для событий UPDATE. Триггер сработает только тогда, когда в списке столбцов, указанных в UPDATE, найдётся хотя бы один из указанных.
Вот, в общем-то, и всё. Надеюсь, это было интересно и будет кому-нибудь полезным.
При написании статьи, был использован pgModeler версии 0.9.2-alpha, собранный под Windows 7 x64. При использовании более старых/новых версий pgModeler возможны небольшие различия в интерфейсе.
Модель, использованную в статье, можно скачать по ссылке.
pgModeler — это весьма неплохой инструмент для проектирования баз данных, который умеет генерировать sql-скрипты для PostgreSQL. Подробно об этом инструменте и его возможностях можно почитать на официальном сайте.
В качестве примера рассмотрим упрощённую модель с одной таблицей.
Добавим функцию в модель.
После этого откроется окно с различными доступными для редактирования параметрами, с которыми будет создана функция. При этом некоторые поля уже будут заполнены значениями по умолчанию.
Рассмотрим эти параметры подробней.
Думаю, с параметрами Name, Schema, Owner и Comment всё ясно — это название функции, схема базы данных, владелец и комментарий к этой функции соответственно.
Language — это название языка, на котором будет реализована функция. Если честно, то мне ещё ни разу не приходилось писать функции для PostgreSQL на чём-то кроме plpgsql. Поэтому именно это значение для параметра и было мною выбрано.
Return Method. Так как в триггерной функции ни таблицу (Table) ни множество (Set) значений нам возвращать не нужно, оставляем Simple.
С параметрами в блоке Data Type, в общем-то, тоже всё просто. Т.к. функция будет вызываться в триггере, то в поле Type указываем trigger (поле Format при этом будет заполнено автоматически значением trigger). Поле Dimension (единственное незадезабленное цифровое поле в этом блоке) нужно для указания размерности массива возвращаемого значения. Но так как нам нужно просто одно значение, а не массив, то оставляем в этом поле 0.
С оставшимися параметрами всё не так очевидно, по крайней мере для меня, т.к. никогда не приходилось над ними задумываться при обычном создании функции в PostgreSQL.
Function Type может принимать одно из трёх значений: IMMUTABLE, STABLE и VOLATILE. Из официальной документации PostgreSQL можно узнать, что эти аргументы информируют оптимизатор запросов о поведении функции.
- IMMUTABLE означает, что функция не может модифицировать базу данных и всегда возвращает один и тот же результат при определённых значениях аргументов.
- STABLE означает, что функция не может модифицировать базу данных, и в рамках одного сканирования таблицы она всегда возвращает один и тот же результат для определённых значений аргументов.
- VOLATILE означает, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать.
Соответственно, если в триггерной функции требуется изменять базу данных, то IMMUTABLE не подходит. Функция с параметром STABLE не подходит для триггеров AFTER, желающих прочитать строки, изменённые текущей командой. Остаётся VOLATILE, у которого отсутствуют указанные выше проблемы. Он также будет указан по умолчанию, если при создании функции не указан ни один из перечисленных выше аргументов.
Security может принимать одно из двух значений: SECURITY DEFINER и SECURITY INVOKER и отвечает за то, с правами какого пользователя она будет вызвана.
- SECURITY DEFINER означает, что функция будет выполняться с правами пользователя, который ей владеет, т.е. того, кто был указан в Owner.
- SECURITY INVOKER означает, что функция будет выполняться с правами пользователя, который её вызвал.
По умолчанию используется SECURITY INVOKER, поэтому можно его и оставить.
Behavior может принимать одно из трёх значений: STRICT, RETURNS NULL ON NULL INPUT и CALLED ON NULL INPUT и показывает, как будет вести себя функция, если среди её аргументов окажутся значения NULL.
- RETURNS NULL ON NULL INPUT или STRICT означает, что функция всегда будет возвращать NULL, если хотя бы один из её аргументов NULL.
- CALLED ON NULL INPUT означает, что функция будет вызвана как обычно, даже если среди её аргументов будет NULL.
По умолчанию используется CALLED ON NULL INPUT. Поэтому, так же, можно его и оставить.
Rows Returned показывает число строк, которое будет ожидать планировщик. Значение указывается для функций, которые возвращают множества. Т.к. у нас функция возвращает одно значение, оставляем 0.
Execution Cost задаёт стоимость выполнения этой функции для планировщика. Для языка plpgsql по умолчанию она равна 100. Поэтому это значение и укажем.
Windown Func. означает, что будет создана оконная функция. В нашем случае, т.к. нам нужна триггерная функция, это значение указывать не нужно (ну и вообще, в самой документации пишут, что этот параметр имеет смысл указывать только для функций, написанных на C).
Leakproof показывает, что функция герметичная, т.е. что она не раскрывает информацию о своих аргументах (например, не выводит их значения в сообщении об ошибке), кроме как возвращая результат. Т.к. триггерная функция не принимает аргументов, то данный параметр не нужно указывать.
Итак, с параметрами функции покончено. Само тело функции можно написать в этом же окне на вкладке Definition. Переходим к созданию самого триггера.
После этого появится окно создания триггера.
Рассмотрим параметры, которые можно задать в этом окне.
С параметрами Name, Alias и Comment опять же всё понятно — это название триггера, алиас и комментарий к триггеру соответственно.
Excution показывает как будет выполняться данный триггер и может принимать одно из следующих значений: BEFORE, AFTER и INSTEAD OF, — которые означают, что функция будет выполняться до, после или вместо события.
FOR EACH ROW определяет, будет ли процедура триггера срабатывать один раз для каждой строки. Если не указать, то будет установлен параметр FOR EACH STATEMENT, который определяет, что процедура триггера срабатывает один раз для SQL-оператора.
Event определяет то, какие события необходимо обрабатывать в данном триггере. Можно указать несколько событий. События бывают следующих типов: INSERT, UPDATE, DELETE и TRUNCATE. Они возникают, когда будет вызвана соответствующая команда с одноимённым SQL-оператором.
Constraint указывает, что будет создан триггер ограничения. Триггеры ограничений используются для генерации исключений при нарушении ограничений. Подробнее о них можно почитать в официальной документации.
Для триггера ограничения можно указать Deferrable, который определяет время срабатывания триггера. Этот параметр может принимать одно из следующих значений: INITIALLY IMMEDIATE или INITIALLY DEFERRED.
- INITIALLY IMMEDIATE означает, что триггер будет срабатывать после каждого оператора.
- INITIALLY DEFERRED означает, что триггер будет срабатывать только в конце транзакции.
Refer. Table — имя таблицы, на которую ссылается ограничение. Используется для ограничений внешнего ключа и допускается только для триггеров ограничений.
Condition — это условие, которое определяет, будет ли выполняться функция триггера. Для триггеров FOR EACH ROW в этом поле можно обращаться к старым и новым значениям через OLD и NEW соответственно (т.е. так же, как и в теле триггерной функции).
Arguments — список аргументов, которые будут переданы в триггерную функцию, когда сработает триггер. В качестве аргументов функции передаются строковые константы.
Columns — можно указать только для событий UPDATE. Триггер сработает только тогда, когда в списке столбцов, указанных в UPDATE, найдётся хотя бы один из указанных.
Заключение
Вот, в общем-то, и всё. Надеюсь, это было интересно и будет кому-нибудь полезным.
При написании статьи, был использован pgModeler версии 0.9.2-alpha, собранный под Windows 7 x64. При использовании более старых/новых версий pgModeler возможны небольшие различия в интерфейсе.
Модель, использованную в статье, можно скачать по ссылке.