F.11. spi
- F.11.1. refint — функции для реализации ссылочной целостности
- F.11.2. timetravel — функции для реализации перемещений во времени
- F.11.3. autoinc — функции для автоувеличения полей
- F.11.4. insert_username — функции для отслеживания пользователя, вносящего изменения
- F.11.5. moddatetime — функции для отслеживания времени последнего изменения
- F.11.2. timetravel — функции для реализации перемещений во времени
Модуль spi предоставляет несколько рабочих примеров использования Интерфейса программирования сервера (Server Programming Interface, SPI) и триггеров. Хотя эти функции имеют некоторую ценность сами по себе, они ещё более полезны как заготовки, которые можно приспособить под собственные нужды. Эти функции достаточно общие, чтобы работать с любой таблицей, но вы должны явно указать имена таблицы и полей (как описано ниже) при создании триггера.
Каждая группа функций, описанная ниже, представлена в виде отдельно устанавливаемого расширения.
F.11.1. refint — функции для реализации ссылочной целостности
Функции check_primary_key()
и check_foreign_key()
применяются для проверки ограничений внешних ключей. (Эта функциональность уже давно вытеснена встроенным механизмом внешних ключей, но этот модуль всё ещё полезен в качестве примера.)
Функция check_primary_key()
проверяет ссылающуюся таблицу. Чтобы воспользоваться ей, создайте триггер BEFORE INSERT OR UPDATE
с этой функцией для таблицы, ссылающейся на другую. Укажите в аргументах триггера: имена столбцов ссылающейся таблицы, образующих внешний ключ, имя целевой таблицы и имена столбцов в ней, образующих первичный/уникальный ключ. Чтобы контролировать несколько внешних ключей, создайте триггер для каждой такой ссылки.
Функция check_foreign_key()
проверяет целевую таблицу. Чтобы использовать её, создайте триггер BEFORE DELETE OR UPDATE
с этой функцией для таблицы, на которую ссылаются другие. Укажите в аргументах триггера: число ссылающихся таблиц, для которых функция должна выполнить проверки, действие в случае обнаружения ссылающегося ключа (cascade
— удалить ссылающуюся строку, restrict
— прервать транзакцию, setnull
— установить в ссылающихся полях значения NULL), имена столбцов целевой таблицы, образующих первичный/уникальный ключ, а затем имена таблиц и столбцов (в количестве, задаваемом первым аргументом). Заметьте, что поля первичных/уникальных столбцов должны иметь пометку NOT NULL и по ним должен быть создан уникальный индекс.
Примеры приведены в refint.example
.
F.11.2. timetravel — функции для реализации перемещений во времени
В далёком прошлом в Postgres Pro была встроенная возможность перемещений во времени, для которой фиксировалось время добавления и удаления каждого кортежа. Эти функции позволяют её имитировать. Чтобы использовать их, вы должны добавить в таблицу два столбца типа abstime
, в которых будет храниться дата/время, когда кортеж был вставлен (start_date) и когда изменён/удалён (stop_date):
CREATE TABLE mytab ( ... ... start_date abstime, stop_date abstime ... ... );
Эти столбцы могут называться как угодно, но в данном описании они называются start_date и stop_date.
Когда вставляется новая строка, в start_date обычно устанавливается текущее время, а в stop_date — infinity
(бесконечность). Триггер автоматически подставит эти значения, если добавляемая строка содержит NULL в этих столбцах. Обычно не NULL в этих столбцах может оказаться только при загрузке в базу выгруженных данных.
Кортежи, в которых поле stop_date равно infinity
, считаются «актуальными сейчас» и могут быть изменены. Кортежи с определённой датой stop_date больше не могут быть изменены — триггер будет препятствовать этому. (Если вам нужно сделать это, вы можете отключить машину времени как показано ниже.)
Если кортеж является изменяемым, при модификации в нём меняется только stop_date (на текущее время), но в таблицу вставляется новый кортеж с модифицированными данными. В поле start_date в этом новом кортеже записывается текущее время, а в stop_date записывается infinity
.
При удалении кортеж на самом деле не удаляется; в нём только записывается текущее время в stop_date.
Чтобы запросить кортежи «актуальные сейчас», добавьте stop_date = 'infinity'
в условие WHERE вашего запроса. (Возможно, вы захотите завернуть это условие в представление.) Аналогичным образом вы можете запрашивать кортежи, которые были актуальны в любой момент в прошлом, задав подходящие условия для start_date и stop_date.
Функция timetravel()
реализует код универсального триггера, поддерживающего это поведение. Чтобы использовать её, создайте триггер BEFORE INSERT OR UPDATE OR DELETE
с этой функцией для каждой таблицы, перемещающейся во времени. Передайте триггеру два аргумента: фактические имена столбцов start_date и stop_date. Вы также можете дополнительно передать от одного до трёх аргументов, задающих имена столбцов типа text
. Данный триггер сохранит имя текущего пользователя в первый из этих столбцов при INSERT, во второй — при UPDATE, и в третий — при DELETE.
Функция set_timetravel()
позволяет включить или отключить машину времени для таблицы. Вызов set_timetravel('mytab', 1)
включает машину времени для таблицы mytab
, а set_timetravel('mytab', 0)
— отключает её для таблицы mytab
. В обоих случаях возвращается прежнее состояние. Когда машина времени выключена, вы можете свободно модифицировать столбцы start_date и stop_date. Заметьте, что состояние активности машины является локальным для текущего сеанса базы данных — в новых сеансах машина времени всегда включена для всех таблиц.
Функция get_timetravel()
возвращает состояние перемещения во времени для таблицы, не меняя его.
Пример приведён в timetravel.example
.
F.11.3. autoinc — функции для автоувеличения полей
Функция autoinc()
реализует код триггера, сохраняющего следующее значение последовательности в целочисленном поле. Это в некоторой степени пересекается со встроенной функциональностью столбца «serial», но есть и отличия: autoinc()
препятствует попыткам вставить другое значение поля при добавлении строк и может увеличивать значение поля при изменениях.
Чтобы использовать её, создайте триггер BEFORE INSERT
(или BEFORE INSERT OR UPDATE
) с этой функцией. Передайте триггеру два аргумента: имя целочисленного столбца, который будет меняться, и имя объекта последовательности, который будет поставлять значения. (Вообще вы можете задать любое число пар таких имён, если хотите поддерживать несколько автоувеличивающихся столбцов.)
Пример приведён в autoinc.example
.
F.11.4. insert_username — функции для отслеживания пользователя, вносящего изменения
Функция insert_username()
реализует код триггера, сохраняющего имя текущего пользователя в текстовом поле. Это может быть полезно для отслеживания пользователя, изменившего конкретную строку таблицы последним.
Чтобы использовать её, создайте триггер BEFORE INSERT
и/или UPDATE
с этой функцией. Передайте триггеру один аргумент: имя целевого текстового столбца.
Пример приведён в insert_username.example
.
F.11.5. moddatetime — функции для отслеживания времени последнего изменения
Функция moddatetime()
реализует код триггера, сохраняющего текущее время в поле типа timestamp
. Это может быть полезно для отслеживания времени последней модификации конкретной строки таблицы.
Чтобы использовать её, создайте триггер BEFORE UPDATE
с этой функцией. Передайте триггеру один аргумент: имя целевого столбца. Столбец должен иметь тип timestamp
или timestamp with time zone
.
Пример приведён в moddatetime.example
.