PL/pgSQL and Triggers

Trigger functions can be created with PL/pgSQL and referenced within a PostgreSQL trigger definition. The term "trigger function" is a 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';

Every trigger function created has access to a number of special variables that exist to provide information about the calling trigger, and to allow the trigger function to manipulate table data. All special trigger function variables are listed in Table 11-2.

Table 11-2. Trigger function variables

NameData typeDescription

NEW

RECORD

Contains the new database row created after INSERT and UPDATE operations run by ROW level triggers. Use this variable to make modifications to the new row.

OLD

RECORD

Contains the old database row left after UPDATE AND DELETE operations performed by ROW level triggers.

TG_NAME

name

Contains the name of the fired trigger.

TG_WHEN

text

Contains either a BEFORE or AFTER string, depending on whether the trigger was defined as running after or before its specified event.

TG_LEVEL

text

Contains either a ROW or STATEMENT string, depending on the defined level of the trigger.

TG_OP

text

Contains an INSERT, UPDATE, or DELETE string that indicates the operation the trigger is invoked on.

TG_RELID

oid

Contains the object ID of the table that invoked the trigger.

TG_RELNAME

name

Contains the name of the table for which the trigger was invoked.

TG_NARGS

integer

Contains the number of arguments the trigger's definition specifies the trigger function as having.

TG_ARGV[ ]

array of text

Contains the arguments specified by the CREATE TRIGGER statement. The array index begins at zero.

Example 11-53 illustrates the definition of a PL/pgSQL trigger function and demonstrates the usage of the previously listed special variables. The check_shipment_addition trigger function is called after an INSERT or UPDATE operation is performed upon the shipments table.

The check_shipment_addition() function checks to make sure each added shipment contains a valid customer ID number and a valid ISBN for the book specified. It then subtracts one from the total amount of stock in the stock table for the specified book if the calling SQL operation is an INSERT statement (but not an UPDATE statement).

Example 11-53. The check_shipment_addition() PL/pgSQL trigger function

CREATE FUNCTION check_shipment_addition () RETURNS opaque AS '
  DECLARE
    -- Declare a variable to hold the customer ID.
    id_number INTEGER;
    
    -- Declare a variable to hold the ISBN.
    book_isbn TEXT;
  BEGIN
    
    -- If there is an ID number that matches the customer ID in
    -- the new table, retrieve it from the customers table.
    SELECT INTO id_number id FROM customers WHERE id = NEW.customer_id;
    
    -- If there was no matching ID number, raise an exception.
    IF NOT FOUND THEN
      RAISE EXCEPTION ''Invalid customer ID number.'';
    END IF;
    
    -- If there is an ISBN that matches the ISBN specified in the
    -- new table, retrieve it from the editions table.
    SELECT INTO book_isbn isbn FROM editions WHERE isbn = NEW.isbn;
    
    -- If there is no matching ISBN, raise an exception.
    IF NOT FOUND THEN
      RAISE EXCEPTION ''Invalid ISBN.'';
    END IF;
    
    -- If the previous checks succeeded, update the stock amount
    -- for INSERT commands.
    IF TG_OP = ''INSERT'' THEN
       UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn;
    END IF;
    
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

Once the check_shipment_addition() function has been created, a trigger may be set on the shipments table to call it. Example 11-54 shows the syntax to create the check_shipment trigger in the booktown database from within psql.

Example 11-54. The check_shipment trigger

booktown=# CREATE TRIGGER check_shipment
booktown-#                BEFORE INSERT OR UPDATE
booktown-#                ON shipments FOR EACH ROW
booktown-#                EXECUTE PROCEDURE check_shipment_addition();
CREATE

Note that the check_shipment_addition trigger function must be defined within the booktown database before its associated trigger is defined. Always define trigger functions before defining the triggers that reference them.

See Chapter 7 for more in-depth information on triggers.