- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

6.4 A Complete Example

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

The function trigf reports the number of rows in the table ttest and skips the actual operation if the command attempts to insert a null value into the column x. (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 "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 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(INFO, "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(NOTICE, "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, 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 PROCEDURE trigf();

CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
    FOR EACH ROW EXECUTE PROCEDURE trigf();

Now you can test the operation of the trigger:

=> INSERT INTO ttest VALUES (NULL);
INFO:  trigf (fired before): there are 0 rows in ttest
INSERT 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 ttest
INFO:  trigf (fired after ): there are 1 rows in ttest
                                       ^^^^^^^^
        remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
 x
---
 1
(1 row)

=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO:  trigf (fired before): there are 1 rows in ttest
INFO:  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 ttest
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO:  trigf (fired before): there are 2 rows in ttest
INFO:  trigf (fired after ): there are 2 rows in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
 x
---
 1
 4
(2 rows)

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

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

ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition