Unsupported versions: 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

20.4. Examples

There are more complex examples in src/test/regress/regress.c and in contrib/spi.

Here is a very simple example of trigger usage. Function trigf reports the number of tuples in the triggered relation ttest and skips the operation if the query attempts to insert NULL into x (i.e - it acts as a NOT NULL constraint but doesn't abort the transaction).

#include "executor/spi.h"      /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* -"- and triggers */

extern Datum trigf(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(trigf);

Datum
trigf(PG_FUNCTION_ARGS)
{
        TriggerData    *trigdata = (TriggerData *) fcinfo->context;
        TupleDesc       tupdesc;
        HeapTuple       rettuple;
        char            *when;
        bool            checknull = false;
        bool            isnull;
        int             ret, i;

        /* Make sure trigdata is pointing at what I expect */
        if (!CALLED_AS_TRIGGER(fcinfo))
                elog(ERROR, "trigf: not fired by trigger manager");
        
        /* tuple to return to Executor */
        if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
                rettuple = trigdata->tg_newtuple;
        else
                rettuple = trigdata->tg_trigtuple;
        
        /* check for NULLs ? */
        if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) &&
                TRIGGER_FIRED_BEFORE(trigdata->tg_event))
                checknull = true;
        
        if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
                when = "before";
        else
                when = "after ";
        
        tupdesc = trigdata->tg_relation->rd_att;
        
        /* Connect to SPI manager */
        if ((ret = SPI_connect()) < 0)
                elog(NOTICE, "trigf (fired %s): SPI_connect returned %d", when, ret);
        
        /* Get number of tuples in relation */
        ret = SPI_exec("SELECT count(*) FROM ttest", 0);
        
        if (ret < 0)
                elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret);

        /* count(*) returns int8 as of PG 7.2, so be careful to convert */
        i = (int) DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
                                              SPI_tuptable->tupdesc,
                                              1,
                                              &isnull));
        
        elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);
        
        SPI_finish();
        
        if (checknull)
        {
                (void) SPI_getbinval(rettuple, tupdesc, 1, &isnull);
                if (isnull)
                        rettuple = NULL;
        }

        return PointerGetDatum(rettuple);
}
    

Now, compile and create the trigger function:

CREATE FUNCTION trigf () RETURNS OPAQUE AS 
'...path_to_so' LANGUAGE 'C';

CREATE TABLE ttest (x int4);
    
vac=> CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest 
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest 
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE
vac=> INSERT INTO ttest VALUES (NULL);
NOTICE:trigf (fired before): there are 0 tuples in ttest
INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

vac=> SELECT * FROM ttest;
x
-
(0 rows)

vac=> INSERT INTO ttest VALUES (1);
NOTICE:trigf (fired before): there are 0 tuples in ttest
NOTICE:trigf (fired after ): there are 1 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
-
1
(1 row)

vac=> INSERT INTO ttest SELECT x * 2 FROM ttest;
NOTICE:trigf (fired before): there are 1 tuples in ttest
NOTICE:trigf (fired after ): there are 2 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
INSERT 167794 1
vac=> SELECT * FROM ttest;
x
-
1
2
(2 rows)

vac=> UPDATE ttest SET x = null WHERE x = 2;
NOTICE:trigf (fired before): there are 2 tuples in ttest
UPDATE 0
vac=> UPDATE ttest SET x = 4 WHERE x = 2;
NOTICE:trigf (fired before): there are 2 tuples in ttest
NOTICE:trigf (fired after ): there are 2 tuples in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
x
-
1
4
(2 rows)

vac=> DELETE FROM ttest;
NOTICE:trigf (fired before): there are 2 tuples in ttest
NOTICE:trigf (fired after ): there are 1 tuples in ttest
NOTICE:trigf (fired before): there are 1 tuples in ttest
NOTICE:trigf (fired after ): there are 0 tuples in ttest
                                       ^^^^^^^^
                             remember what we said about visibility.
DELETE 2
vac=> SELECT * FROM ttest;
x
-
(0 rows)