The spi module provides
several workable examples of using SPI and triggers. While these
functions are of some value in their own right, they are even
more useful as examples to modify for your own purposes. The
functions are general enough to be used with any table, but you
have to specify table and field names (as described below) while
creating a trigger.

Each of the groups of functions described below is provided as
a separately-installable extension.

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.

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):

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.

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.)