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.

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.

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

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 .

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.