Examples

PostgreSQL 7.3.4 Documentation
Prev | Chapter 16. Triggers | Next

# 16.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 a null value 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 null values */
        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(INFO, "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 TRIGGER 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);
    WARNING:  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);
    INFO:  trigf (fired before): there are 0 tuples in ttest
    INFO:  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;
    INFO:  trigf (fired before): there are 1 tuples in ttest
    INFO:  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;
    INFO:  trigf (fired before): there are 2 tuples in ttest
    UPDATE 0
    vac=> UPDATE ttest SET x = 4 WHERE x = 2;
    INFO:  trigf (fired before): there are 2 tuples in ttest
    INFO:  trigf (fired after ): there are 2 tuples in ttest
    UPDATE 1
    vac=> SELECT * FROM ttest;
     x
    ---
     1
     4
    (2 rows)

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

Prev | Home | Next
Visibility of Data Changes | Up | Server Programming Interface