E.35. spi

E.35.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.

E.35.2. timetravel — функции для реализации перемещений во времени

В далёком прошлом в PostgreSQL была встроенная возможность перемещений во времени, для которой фиксировалось время добавления и удаления каждого кортежа. Эти функции позволяют её имитировать. Чтобы использовать их, вы должны добавить в таблицу две колонки типа 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.

E.35.3. autoinc — функции для автоувеличения полей

Функция autoinc() реализует код триггера, сохраняющего следующее значение последовательности в целочисленном поле. Это в некоторой степени пересекается со встроенной функциональностью колонки "serial", но есть и отличия: autoinc() препятствует попыткам вставить другое значение поля при добавлении строк и может увеличивать значение поля при изменениях.

Чтобы использовать её, создайте триггер BEFORE INSERT (или BEFORE INSERT OR UPDATE) с этой функцией. Передайте триггеру два аргумента: имя целочисленной колонки, которая будет меняться, и имя объекта последовательности, который будет поставлять значения. (Вообще вы можете задать любое число пар таких имён, если хотите поддерживать несколько автоувеличивающихся колонок.)

Пример приведён в autoinc.example.

E.35.4. insert_username — функции для отслеживания пользователя, вносящего изменения

Функция insert_username() реализует код триггера, сохраняющего имя текущего пользователя в текстовом поле. Это может быть полезно для отслеживания пользователя, изменившего конкретную строку таблицы последним.

Чтобы использовать её, создайте триггер BEFORE INSERT и/или UPDATE с этой функцией. Передайте триггеру один аргумент: имя целевой текстовой колонки.

Пример приведён в insert_username.example.

E.35.5. moddatetime — функции для отслеживания времени последнего изменения

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

Чтобы использовать её, создайте триггер BEFORE UPDATE с этой функцией. Передайте триггеру один аргумент: имя целевой колонки. Колонка должна иметь тип timestamp или timestamp with time zone.

Пример приведён в moddatetime.example.