spi — Server Programming Interface features/examples

The spi module provides several workable examples of using the Server Programming Interface (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.

refint — Functions for Implementing Referential Integrity

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.

Example:

-- Column ID of table A is primary key:

CREATE TABLE A (
   ID  int4 not null
);
CREATE UNIQUE INDEX AI ON A (ID);

-- Columns REFB of table B and REFC of C are foreign keys referencing ID of A:

CREATE TABLE B (
   REFB    int4
);
CREATE INDEX BI ON B (REFB);

CREATE TABLE C (
   REFC    int4
);
CREATE INDEX CI ON C (REFC);

-- Trigger for table A:

CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
/*
2       - means that check must be performed for foreign keys of 2 tables.
cascade - defines that corresponding keys must be deleted.
ID      - name of primary key column in triggered table (A). You may
          use as many columns as you need.
B       - name of (first) table with foreign keys.
REFB    - name of foreign key column in this table. You may use as many
          columns as you need, but number of key columns in referenced
          table (A) must be the same.
C       - name of second table with foreign keys.
REFC    - name of foreign key column in this table.
*/

-- Trigger for table B:

CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key ('REFB', 'A', 'ID');

/*
REFB - name of foreign key column in triggered (B) table. You may use as
       many columns as you need, but number of key columns in referenced
       table must be the same.
A    - referenced table name.
ID   - name of primary key column in referenced table.
*/

-- Trigger for table C:

CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key ('REFC', 'A', 'ID');

-- Now try

INSERT INTO A VALUES (10);
INSERT INTO A VALUES (20);
INSERT INTO A VALUES (30);
INSERT INTO A VALUES (40);
INSERT INTO A VALUES (50);

INSERT INTO B VALUES (1);   -- invalid reference
INSERT INTO B VALUES (10);
INSERT INTO B VALUES (30);
INSERT INTO B VALUES (30);

INSERT INTO C VALUES (11);  -- invalid reference
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (30);

DELETE FROM A WHERE ID = 10;
DELETE FROM A WHERE ID = 20;
DELETE FROM A WHERE ID = 30;

SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;

autoinc — Functions for Autoincrementing Fields

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

Example:

DROP SEQUENCE next_id;
DROP TABLE ids;

CREATE SEQUENCE next_id START -2 MINVALUE -2;

CREATE TABLE ids (
   id      int4,
   idesc       text
);

CREATE TRIGGER ids_nextid
   BEFORE INSERT OR UPDATE ON ids
   FOR EACH ROW
   EXECUTE PROCEDURE autoinc (id, next_id);

INSERT INTO ids VALUES (0, 'first (-2 ?)');
INSERT INTO ids VALUES (null, 'second (-1 ?)');
INSERT INTO ids(idesc) VALUES ('third (1 ?!)');

SELECT * FROM ids;

UPDATE ids SET id = null, idesc = 'first: -2 --> 2'
   WHERE idesc = 'first (-2 ?)';
UPDATE ids SET id = 0, idesc = 'second: -1 --> 3'
   WHERE id = -1;
UPDATE ids SET id = 4, idesc = 'third: 1 --> 4'
   WHERE id = 1;

SELECT * FROM ids;

SELECT 'Wasn''t it 4 ?' as nextval, nextval ('next_id') as value;

insert into ids (idesc) select textcat (idesc, '. Copy.') from ids;

SELECT * FROM ids;

insert_username — Functions for Tracking Who Changed a Table

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.

Example:

DROP TABLE username_test;

CREATE TABLE username_test (
   name        text,
   username    text not null
);

CREATE TRIGGER insert_usernames
   BEFORE INSERT OR UPDATE ON username_test
   FOR EACH ROW
   EXECUTE PROCEDURE insert_username (username);

INSERT INTO username_test VALUES ('nothing');
INSERT INTO username_test VALUES ('null', null);
INSERT INTO username_test VALUES ('empty string', '');
INSERT INTO username_test VALUES ('space', ' ');
INSERT INTO username_test VALUES ('tab', '  ');
INSERT INTO username_test VALUES ('name', 'name');

SELECT * FROM username_test;

moddatetime — Functions for Tracking Last Modification Time

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.

Example:

DROP TABLE mdt;

CREATE TABLE mdt (
   id      int4,
   idesc       text,
   moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
   BEFORE UPDATE ON mdt
   FOR EACH ROW
   EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
   WHERE id = 1;
UPDATE mdt SET id = 5
   WHERE id = 2;
UPDATE mdt SET id = 6
   WHERE id = 3;

SELECT * FROM mdt;