Movatterモバイル変換

Here is a very simple example of a trigger function written in C. (Examples of triggers written in procedural languages can be found in the documentation of the procedural languages.)

The functiontrigf reports the number of rows in the tablettest and skips the actual operation if the command attempts to insert a null value into the columnx. (So the trigger acts as a not-null constraint but doesn't abort the transaction.)

First, the table definition:

CREATE TABLE ttest (    x integer);

This is the source code of the trigger function:

#include "postgres.h"#include "fmgr.h"#include "executor/spi.h"       /* this is what you need to work with SPI */#include "commands/trigger.h"   /* ... triggers ... */#include "utils/rel.h"          /* ... and relations */PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(trigf);Datumtrigf(PG_FUNCTION_ARGS){    TriggerData *trigdata = (TriggerData *) fcinfo->context;    TupleDesc   tupdesc;    HeapTuple   rettuple;    char       *when;    bool        checknull = false;    bool        isnull;    int         ret, i;    /* make sure it's called as a trigger at all */    if (!CALLED_AS_TRIGGER(fcinfo))        elog(ERROR, "trigf: not called 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(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);    /* get number of rows in table */    ret = SPI_exec("SELECT count(*) FROM ttest", 0);    if (ret < 0)        elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);    /* count(*) returns int8, so be careful to convert */    i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],                                    SPI_tuptable->tupdesc,                                    1,                                    &isnull));    elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);    SPI_finish();    if (checknull)    {        SPI_getbinval(rettuple, tupdesc, 1, &isnull);        if (isnull)            rettuple = NULL;    }    return PointerGetDatum(rettuple);}

After you have compiled the source code (seeSection 36.10.5), declare the function and the triggers:

CREATE FUNCTION trigf() RETURNS trigger    AS 'filename'    LANGUAGE C;CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest    FOR EACH ROW EXECUTE FUNCTION trigf();CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest    FOR EACH ROW EXECUTE FUNCTION trigf();

Now you can test the operation of the trigger:

=> INSERT INTO ttest VALUES (NULL);INFO:  trigf (fired before): there are 0 rows in ttestINSERT 0 0-- Insertion skipped and AFTER trigger is not fired=> SELECT * FROM ttest; x---(0 rows)=> INSERT INTO ttest VALUES (1);INFO:  trigf (fired before): there are 0 rows in ttestINFO:  trigf (fired after ): there are 1 rows in ttest                                       ^^^^^^^^                             remember what we said about visibility.INSERT 167793 1vac=> SELECT * FROM ttest; x--- 1(1 row)=> INSERT INTO ttest SELECT x * 2 FROM ttest;INFO:  trigf (fired before): there are 1 rows in ttestINFO:  trigf (fired after ): there are 2 rows in ttest                                       ^^^^^^                             remember what we said about visibility.INSERT 167794 1=> SELECT * FROM ttest; x--- 1 2(2 rows)=> UPDATE ttest SET x = NULL WHERE x = 2;INFO:  trigf (fired before): there are 2 rows in ttestUPDATE 0=> UPDATE ttest SET x = 4 WHERE x = 2;INFO:  trigf (fired before): there are 2 rows in ttestINFO:  trigf (fired after ): there are 2 rows in ttestUPDATE 1vac=> SELECT * FROM ttest; x--- 1 4(2 rows)=> DELETE FROM ttest;INFO:  trigf (fired before): there are 2 rows in ttestINFO:  trigf (fired before): there are 1 rows in ttestINFO:  trigf (fired after ): there are 0 rows in ttestINFO:  trigf (fired after ): there are 0 rows in ttest                                       ^^^^^^                             remember what we said about visibility.DELETE 2=> SELECT * FROM ttest; x---(0 rows)