Хабрахабр

Создание триггерной функции в pgModeler

В некотором царстве, в некотором государстве... понадобилось мне добавить триггер в модель на pgModeler. Что сделать достаточно легко. А вот добавить триггерную функцию… Тоже легко, но пришлось немного поразбираться с параметрами, предлагаемыми для заполнения/выбора в интерфейсе.

Подробно об этом инструменте и его возможностях можно почитать на официальном сайте.
В качестве примера рассмотрим упрощённую модель с одной таблицей. pgModeler — это весьма неплохой инструмент для проектирования баз данных, который умеет генерировать sql-скрипты для PostgreSQL.

Добавим функцию в модель.

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

Рассмотрим эти параметры подробней.

Думаю, с параметрами Name, Schema, Owner и Comment всё ясно — это название функции, схема базы данных, владелец и комментарий к этой функции соответственно.

Если честно, то мне ещё ни разу не приходилось писать функции для PostgreSQL на чём-то кроме plpgsql. Language — это название языка, на котором будет реализована функция. Поэтому именно это значение для параметра и было мною выбрано.

Так как в триггерной функции ни таблицу (Table) ни множество (Set) значений нам возвращать не нужно, оставляем Simple. Return Method.

Т.к. С параметрами в блоке Data Type, в общем-то, тоже всё просто. Поле Dimension (единственное незадезабленное цифровое поле в этом блоке) нужно для указания размерности массива возвращаемого значения. функция будет вызываться в триггере, то в поле Type указываем trigger (поле Format при этом будет заполнено автоматически значением trigger). Но так как нам нужно просто одно значение, а не массив, то оставляем в этом поле 0.

никогда не приходилось над ними задумываться при обычном создании функции в PostgreSQL. С оставшимися параметрами всё не так очевидно, по крайней мере для меня, т.к.

Из официальной документации PostgreSQL можно узнать, что эти аргументы информируют оптимизатор запросов о поведении функции. Function Type может принимать одно из трёх значений: IMMUTABLE, STABLE и VOLATILE.

  • 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. Т.к.

Для языка plpgsql по умолчанию она равна 100. Execution Cost задаёт стоимость выполнения этой функции для планировщика. Поэтому это значение и укажем.

В нашем случае, т.к. Windown Func. означает, что будет создана оконная функция. нам нужна триггерная функция, это значение указывать не нужно (ну и вообще, в самой документации пишут, что этот параметр имеет смысл указывать только для функций, написанных на C).

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

Само тело функции можно написать в этом же окне на вкладке Definition. Итак, с параметрами функции покончено. Переходим к созданию самого триггера.

После этого появится окно создания триггера.

Рассмотрим параметры, которые можно задать в этом окне.

С параметрами Name, Alias и Comment опять же всё понятно — это название триггера, алиас и комментарий к триггеру соответственно.

Excution показывает как будет выполняться данный триггер и может принимать одно из следующих значений: BEFORE, AFTER и INSTEAD OF, — которые означают, что функция будет выполняться до, после или вместо события.

Если не указать, то будет установлен параметр FOR EACH STATEMENT, который определяет, что процедура триггера срабатывает один раз для SQL-оператора. FOR EACH ROW определяет, будет ли процедура триггера срабатывать один раз для каждой строки.

Можно указать несколько событий. Event определяет то, какие события необходимо обрабатывать в данном триггере. Они возникают, когда будет вызвана соответствующая команда с одноимённым SQL-оператором. События бывают следующих типов: INSERT, UPDATE, DELETE и TRUNCATE.

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

Этот параметр может принимать одно из следующих значений: INITIALLY IMMEDIATE или INITIALLY DEFERRED. Для триггера ограничения можно указать Deferrable, который определяет время срабатывания триггера.

  • INITIALLY IMMEDIATE означает, что триггер будет срабатывать после каждого оператора.
  • INITIALLY DEFERRED означает, что триггер будет срабатывать только в конце транзакции.

Table — имя таблицы, на которую ссылается ограничение. Refer. Используется для ограничений внешнего ключа и допускается только для триггеров ограничений.

Для триггеров FOR EACH ROW в этом поле можно обращаться к старым и новым значениям через OLD и NEW соответственно (т.е. Condition — это условие, которое определяет, будет ли выполняться функция триггера. так же, как и в теле триггерной функции).

В качестве аргументов функции передаются строковые константы. Arguments — список аргументов, которые будут переданы в триггерную функцию, когда сработает триггер.

Триггер сработает только тогда, когда в списке столбцов, указанных в UPDATE, найдётся хотя бы один из указанных. Columns — можно указать только для событий UPDATE.

Заключение

Вот, в общем-то, и всё. Надеюсь, это было интересно и будет кому-нибудь полезным.
При написании статьи, был использован pgModeler версии 0.9.2-alpha, собранный под Windows 7 x64. При использовании более старых/новых версий pgModeler возможны небольшие различия в интерфейсе.

Модель, использованную в статье, можно скачать по ссылке.

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть