Функции 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.

В далёком прошлом в 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.

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

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

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

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

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

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

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

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

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

check_primary_key() and check_foreign_key() are used to check foreign key constraints. (This functionality is long since superseded by the built-in foreign key mechanism, of course, but the module is still useful as an example.)

check_primary_key() checks the referencing table. To use, create a BEFORE INSERT OR UPDATE trigger using this function on a table referencing another table. Specify as the trigger arguments: the referencing table's column name(s) which form the foreign key, the referenced table name, and the column names in the referenced table which form the primary/unique key. To handle multiple foreign keys, create a trigger for each reference.

check_foreign_key() checks the referenced table. To use, create a BEFORE DELETE OR UPDATE trigger using this function on a table referenced by other table(s). Specify as the trigger arguments: the number of referencing tables for which the function has to perform checking, the action if a referencing key is found (cascade — to delete the referencing row, restrict — to abort transaction if referencing keys exist, setnull — to set referencing key fields to null), the triggered table's column names which form the primary/unique key, then the referencing table name and column names (repeated for as many referencing tables as were specified by first argument). Note that the primary/unique key columns should be marked NOT NULL and should have a unique index.

There are examples in refint.example.

Long ago, PostgreSQL had a built-in time travel feature that kept the insert and delete times for each tuple. This can be emulated using these functions. To use these functions, you must add to a table two columns of abstime type to store the date when a tuple was inserted (start_date) and changed/deleted (stop_date):

CREATE TABLE mytab (
        ...             ...
        start_date      abstime,
        stop_date       abstime
        ...             ...
);

The columns can be named whatever you like, but in this discussion we'll call them start_date and stop_date.

When a new row is inserted, start_date should normally be set to current time, and stop_date to infinity. The trigger will automatically substitute these values if the inserted data contains nulls in these columns. Generally, inserting explicit non-null data in these columns should only be done when re-loading dumped data.

Tuples with stop_date equal to infinity are “valid now”, and can be modified. Tuples with a finite stop_date cannot be modified anymore — the trigger will prevent it. (If you need to do that, you can turn off time travel as shown below.)

For a modifiable row, on update only the stop_date in the tuple being updated will be changed (to current time) and a new tuple with the modified data will be inserted. Start_date in this new tuple will be set to current time and stop_date to infinity.

A delete does not actually remove the tuple but only sets its stop_date to current time.

To query for tuples “valid now”, include stop_date = 'infinity' in the query's WHERE condition. (You might wish to incorporate that in a view.) Similarly, you can query for tuples valid at any past time with suitable conditions on start_date and stop_date.

timetravel() is the general trigger function that supports this behavior. Create a BEFORE INSERT OR UPDATE OR DELETE trigger using this function on each time-traveled table. Specify two trigger arguments: the actual names of the start_date and stop_date columns. Optionally, you can specify one to three more arguments, which must refer to columns of type text. The trigger will store the name of the current user into the first of these columns during INSERT, the second column during UPDATE, and the third during DELETE.

set_timetravel() allows you to turn time-travel on or off for a table. set_timetravel('mytab', 1) will turn TT ON for table mytab. set_timetravel('mytab', 0) will turn TT OFF for table mytab. In both cases the old status is reported. While TT is off, you can modify the start_date and stop_date columns freely. Note that the on/off status is local to the current database session — fresh sessions will always start out with TT ON for all tables.

get_timetravel() returns the TT state for a table without changing it.

There is an example in timetravel.example.

autoinc() is a trigger that stores the next value of a sequence into an integer field. This has some overlap with the built-in “serial column” feature, but it is not the same: autoinc() will override attempts to substitute a different field value during inserts, and optionally it can be used to increment the field during updates, too.

To use, create a BEFORE INSERT (or optionally BEFORE INSERT OR UPDATE) trigger using this function. Specify two trigger arguments: the name of the integer column to be modified, and the name of the sequence object that will supply values. (Actually, you can specify any number of pairs of such names, if you'd like to update more than one autoincrementing column.)

There is an example in autoinc.example.

insert_username() is a trigger that stores the current user's name into a text field. This can be useful for tracking who last modified a particular row within a table.

To use, create a BEFORE INSERT and/or UPDATE trigger using this function. Specify a single trigger argument: the name of the text column to be modified.

There is an example in insert_username.example.

moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

There is an example in moddatetime.example.