PL/pgSQL can be used to define trigger procedures. They are created with the usual CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE.
There are some Postgres specific details in functions used as trigger procedures.
First they have some special variables created automatically in the top-level blocks declaration section. They are
Data type RECORD; variable holding the new database row on INSERT/UPDATE operations on ROW level triggers.
Data type RECORD; variable holding the old database row on UPDATE/DELETE operations on ROW level triggers.
Data type name; variable that contains the name of the trigger actually fired.
Data type text; a string of either BEFORE or AFTER depending on the triggers definition.
Data type text; a string of either ROW or STATEMENT depending on the triggers definition.
Data type text; a string of INSERT, UPDATE or DELETE telling for which operation the trigger is actually fired.
Data type oid; the object ID of the table that caused the trigger invocation.
Data type name; the name of the table that caused the trigger invocation.
Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.
Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0 and can be given as an expression. Invalid indices (< 0 or >= tg_nargs) result in a NULL value.
Second they must return either NULL or a record/row containing exactly the structure of the table the trigger was fired for. Triggers fired AFTER might always return a NULL value with no effect. Triggers fired BEFORE signal the trigger manager to skip the operation for this actual row when returning NULL. Otherwise, the returned record/row replaces the inserted/updated row in the operation. It is possible to replace single values directly in NEW and return that or to build a complete new record/row to return.
Example 24-1. A PL/pgSQL Trigger Procedure Example
This trigger ensures, that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it ensures that an employees name is given and that the salary is a positive value.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();