spi

The spi module provides several working examples of using the Server Programming Interface (SPI) and triggers. While these examples are valuable in their own right, they are even more useful as examples to modify for your own purposes. These functions are general enough to work with any table, but when creating a trigger, you must specify the table name and column names (as described below).

Each group of functions described below is provided as a separately installable extension.

1. refint — Functions for Implementing Referential Integrity

check_primary_key() and check_foreign_key() are used to check foreign key constraints (of course, this functionality was long ago superseded by the built-in foreign key mechanism, but this module can still serve as an example).

check_primary_key() checks the referencing table. The usage is to create a BEFORE INSERT OR UPDATE trigger on a table that references other tables using this function. Specify the trigger parameters as: the column names in the referencing table that constitute the foreign key, the referenced table name, and the column names in the referenced table that constitute the primary key/unique key. To handle multiple foreign keys, create a trigger for each reference.

check_foreign_key() checks the referenced table. The usage is to create a BEFORE DELETE OR UPDATE trigger on a table that is referenced by other tables using this function. Specify the trigger parameters as: the number of referencing tables the function must check, the action when a referencing key is found (cascade — delete referencing rows, restrict — abort the transaction if referencing keys exist, setnull — set the referencing key columns to null), the column names in the trigger's table that constitute the primary key/unique key, the referencing table names and column names (repeated for as many referencing tables as specified by the first parameter). Note: primary key/unique key columns should be marked as NOT NULL and should have a unique index.

Examples can be found in refint.example.

2. autoinc — Functions for Auto-incrementing Columns

autoinc() is a trigger that stores the next value of a sequence into an integer column. This overlaps somewhat with the built-in "serial column" feature, but it is not exactly the same: autoinc() will overwrite a different column value on insertion, and it can optionally increment the column on updates.

The usage is to create a BEFORE INSERT (or BEFORE INSERT OR UPDATE) trigger using this function. Specify two trigger parameters: the name of the integer column to be modified and the name of the sequence object that will provide the value (in fact, you can specify any number of such name pairs if you want to update more than one auto-increment column).

An example can be found in autoinc.example.

3. insert_username — Functions for Tracking Who Modified a Table

insert_username() is a trigger that stores the current user name into a text column. This is useful for tracking who last modified a particular row in a table.

The usage is to create a BEFORE INSERT and/or UPDATE trigger using this function. Specify one trigger parameter: the name of the text column to be modified.

An example can be found in insert_username.example.

moddatetime — Functions for Tracking Last Modification Time

moddatetime() is a trigger that stores the current time into a timestamp column. It is useful for tracking the last modification time of a particular row in a table.

The usage is to create a BEFORE UPDATE trigger using this function. Specify one trigger parameter: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

An example can be found in moddatetime.example.