It is desirable to avoid using database functions. They are black boxes which gift only night horrors to unwary developers. A system is difficult to understand, maintain and debug when chunks of it lurk unseen in the DB.

Despite this – for certain features – using them does make sense. Provided the database function’s code is source controlled and huge lumps of comments referring to both the location and function of said code is spread evenly throughout the associated application code.

For example, in order to bill Ekaya agents accurately we needed a log of show house status changes, from upcoming to active to past or cancelled. Most of these changes were implemented in sweeping SQL statements that, while efficient at their own task, made it difficult to track individual changes.

Implementing this in the application code would also mean that any new features that modified the show house status would require someone to remember to add business essential hooks to update the audit table. In the end it seemed simpler to create a trigger in the DB which watched the show house table and updated an audit table accordingly.

PostgreSQL functions

Triggers trigger functions. In order to have a trigger you must first have an existing function to trigger.

PostgreSQL functions can be written in a number of languages, including Python, but I found PL/pgSQL worked fine for me.

Functions are created using the CREATE FUNCTION command. I recommend using CREATE OR REPLACE FUNCTION as this will handily update the function when initially mucking about with it.

Examples of functions can be found in the CREATE FUNCTION page as well as the Trigger Procedures page. At the top of the Trigger Procedures page is also a list of special variables you can use in a trigger function. NEW and OLD are especially helpful.

In my experience I have found it prudent to ensure your trigger function always culminates on a RETURN statement of some sort. Otherwise triggers reguritate error codes at you. In my case making the last line of the function code RETURN NEW sufficed.

Once your function has been created you can view it in the DB using the following command:

SELECT proname, prosrc FROM pg_proc WHERE proname = 'function_name';

Should you desire to remove the function, you can do so in this fashion (note the brackets after the function name):

DROP FUNCTION IF EXISTS function_name() CASCADE;

NB running the above statement with CASCADE will cause any associated triggers to be dropped as well.

PostgreSQL triggers

Once your function has been created you can create your trigger to … trigger it. I recommend reading Overview of Trigger Behaviour and CREATE TRIGGER but in a nutshell:

• Triggers are attached to a TABLE or VIEW
• They are fired BEFORE, AFTER or INSTEAD OF an INSERT, UPDATE, DELETE or TRUNCATE statement that acts on their TABLE/VIEW
• A statement trigger will always be triggered, but only once for each statement. A row trigger will be triggered only for each row that is affected by the statement.

Once created you can view a trigger like so:

SELECT * FROM pg_trigger WHERE tgname = 'trigger_name';

And they can be removed like so:

DROP TRIGGER IF EXISTS trigger_name ON table_name CASCADE;