Practical PostgreSQL

PL/pgSQL and Triggers

Trigger functions can be created with PL/pgSQL and referenced within a PostgreSQL trigger definition. The term “trigger function” is simply a way of referring to a function that is intended to be invoked by a trigger. Triggers define operations that are performed when a specific event occurs within the database. A PL/pgSQL trigger function can be referenced by a trigger as the operation to be performed when the trigger’s event occurs.

The definition of a trigger and the definition of its associated trigger function are two different things. A trigger is defined with the SQL CREATE TRIGGER command, whereas trigger functions are defined using the SQL CREATE FUNCTION command. Trigger definitions are explained in detail in Chapter 7.

A trigger function should be defined as accepting no arguments, and returns a value of the special opaque data type. The CREATE FUNCTION syntax for defining a PL/pgSQL trigger function is shown in Example 11-52.

Example 11-52. Creating trigger functions

CREATE FUNCTION function_identifier ( ) RETURNS opaque AS '
  DECLARE
    declarations;
    [...]
  BEGIN
    statements;
    [...]
  END;
' LANGUAGE 'plpgsql';

Trigger functions have access to a variety of special variables that exist to provide information about the calling trigger, and to allow the trigger function to manipulate table data. Each special trigger function variable is listed in Table 11-2.

Table 11-2. Trigger function variables

Name	Data type	Description
NEW	RECORD	Contains the new database ...