Red Hat Database has various server-side function interfaces. Server-side functions can be written in SQL, PL/pgSQL, PL/TcL, or C; trigger functions can be written in any of these languages except SQL. This section describes how to write trigger functions in C. Refer to the other procedural language chapters for a description of how to write triggers in those languages. (Using procedural languages is considerably simpler than using C.)
Refer to the Red Hat Database SQL Guide and Reference for the syntax of the CREATE TRIGGER command.
The trigger function must be created before the trigger is created as a function taking no arguments and returning opaque.
If a trigger event occurs, the Trigger Manager (called by the Executor) sets up a TriggerData information structure (described below) and calls the trigger function to handle the event.
The arguments specified in the CREATE TRIGGER command are passed to the function in the TriggerData structure. The purpose of passing arguments to the function is to allow triggers for different events but with similar requirements to call the same function.
The same trigger function may also be used for triggering different relations (these functions are named as "general trigger functions").
As example of using both features above, there could be a general function that takes as its arguments two field names and puts the current user in one and the current timestamp in the other. This allows triggers to be written on INSERT events to automatically track creation of records in a transaction table for example. It could also be used as a "last updated" function if used in an UPDATE event.
If more than one trigger is defined for the same event or the same relation, the order of trigger firing is unpredictable. |
This section describes the low-level details of the interface to a trigger function. This information is needed only when writing a trigger function in C. If you are using a higher-level function language then these details are handled for you.
Trigger functions return a HeapTuple structure. This is ignored for triggers fired AFTER an INSERT, DELETE, or UPDATE operation but it allows BEFORE triggers to:
Return NULL to skip the operation for the current tuple (and so the tuple will not be inserted/updated/deleted).
Return a pointer to another tuple (INSERT and UPDATE only) which will be inserted (as the new version of the updated tuple if UPDATE) instead of the original tuple.
When a function is called by the trigger manager, it is not passed any normal parameters, but it is passed a "context" pointer pointing to a TriggerData structure. C functions can check whether they were called from the trigger manager or not by executing the macro CALLED_AS_TRIGGER(fcinfo), which expands to
((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData)) |
struct TriggerData is defined in src/include/commands/trigger.h:
typedef struct TriggerData { NodeTag type; TriggerEvent tg_event; Relation tg_relation; HeapTuple tg_trigtuple; HeapTuple tg_newtuple; Trigger *tg_trigger; } TriggerData; |
Always T_TriggerData if this is a trigger event.
Describes the event for which the function is called. You may use the following macros to examine tg_event:
Macro | Description |
---|---|
TRIGGER_FIRED_BEFORE(tg_event) | Returns TRUE if trigger fired BEFORE. |
TRIGGER_FIRED_AFTER(tg_event) | Returns TRUE if trigger fired AFTER. |
TRIGGER_FIRED_FOR_ROW(event) | Returns TRUE if trigger fired for a ROW-level event. |
TRIGGER_FIRED_FOR_STATEMENT(event) | Returns TRUE if trigger fired for STATEMENT-level event. |
TRIGGER_FIRED_BY_INSERT(event) | Returns TRUE if trigger fired by INSERT. |
TRIGGER_FIRED_BY_DELETE(event) | Returns TRUE if trigger fired by DELETE. |
TRIGGER_FIRED_BY_UPDATE(event) | Returns TRUE if trigger fired by UPDATE. |
is a pointer to structure describing the triggered relation. Look at src/include/utils/rel.h for details about this structure. The most interest things are tg_relation->rd_att (descriptor of the relation tuples) and tg_relation->rd_rel->relname (relation's name. This is not char*, but NameData. Use SPI_getrelname(tg_relation) to get char* if you need a copy of name).
is a pointer to the tuple for which the trigger is fired. This is the tuple being inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE). If INSERT/DELETE then this is what you are to return to Executor if you do not want to replace the tuple with another one (INSERT) or skip the operation.
is a pointer to the new version of tuple if UPDATE and NULL if this is for an INSERT or a DELETE. This is what you are to return to Executor if UPDATE and you do not want to replace this tuple with another one or skip the operation.
is pointer to structure Trigger defined in src/include/utils/rel.h:
typedef struct Trigger { Oid tgoid; char *tgname; Oid tgfoid; int16 tgtype; bool tgenabled; bool tgisconstraint; Oid tgconstrrelid; bool tgdeferrable; bool tginitdeferred; int16 tgnargs; int16 tgattr[FUNC_MAX_ARGS]; char **tgargs; } Trigger; |
There is currently no way to perform any initializations when the CREATE TRIGGER command is executed. The programmer of the trigger functions must ensure any initializations (if necessary) are done when the function is invoked for the first time. |
Data changes visibility rule: during a query execution, data changes made by the query itself (via SQL-function, SPI-function, triggers) are invisible to the query scan. For example, in the query:
INSERT INTO a SELECT * FROM a; |
Changes made by query Q are visible by queries that are started after query Q, no matter whether they are started inside Q (during the execution of Q) or after Q is done. |
This is true for triggers as well so, though a tuple being inserted (tg_trigtuple) is not visible to queries in a BEFORE trigger, this tuple (just inserted) is visible to queries in an AFTER trigger, and to queries in BEFORE/AFTER triggers fired after this!
This section presents 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 (that is, it acts as a NOT NULL constraint but does not 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); i = 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) { i = 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) |
There are more complex examples in src/test/regress/regress.c and in contrib/spi.
If a trigger function executes SQL queries (using SPI), then these queries may fire triggers again. This is known as "cascading triggers". There is no explicit limit to the number of cascading levels.
If a trigger is fired by INSERT and inserts a new tuple in the same relation, then this trigger will be fired again. Currently there is nothing provided to handle these cases, so the function must check for recursion. The following example uses some techniques to stop recursion (cascading) on itself.
#include "executor/spi.h" /* this is what you need to work with SPI */ #include "commands/trigger.h" /* -"- and triggers */ static TransactionId fd17b_xid = InvalidTransactionId; static TransactionId fd17a_xid = InvalidTransactionId; static int fd17b_level = 0; static int fd17a_level = 0; static bool fd17b_recursion = true; static bool fd17a_recursion = true; extern Datum funny_dup17(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(funny_dup17); Datum funny_dup17(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TransactionId *xid; int *level; bool *recursion; Relation rel; TupleDesc tupdesc; HeapTuple tuple; char *query, *fieldval, *fieldtype; char *when; int inserted; int selected = 0; int ret; if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "funny_dup17: not fired by trigger manager"); tuple = trigdata->tg_trigtuple; rel = trigdata->tg_relation; tupdesc = rel->rd_att; if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) { xid = &fd17b_xid; level = &fd17b_level; recursion = &fd17b_recursion; when = "BEFORE"; } else { xid = &fd17a_xid; level = &fd17a_level; recursion = &fd17a_recursion; when = "AFTER "; } if (!TransactionIdIsCurrentTransactionId(*xid)) { *xid = GetCurrentTransactionId(); *level = 0; *recursion = true; } if (*level == 17) { *recursion = false; return PointerGetDatum(tuple); } if (!(*recursion)) return PointerGetDatum(tuple); (*level)++; SPI_connect(); fieldval = SPI_getvalue(tuple, tupdesc, 1); fieldtype = SPI_gettype(tupdesc, 1); query = (char *) palloc(100 + NAMEDATALEN * 3 + strlen(fieldval) + strlen(fieldtype)); sprintf(query, "insert into %s select * from %s where %s = '%s'::%s", SPI_getrelname(rel), SPI_getrelname(rel), SPI_fname(tupdesc, 1), fieldval, fieldtype); if ((ret = SPI_exec(query, 0)) < 0) elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d", when, *level, ret); inserted = SPI_processed; sprintf(query, "select count (*) from %s where %s = '%s'::%s", SPI_getrelname(rel), SPI_fname(tupdesc, 1), fieldval, fieldtype); if ((ret = SPI_exec(query, 0)) < 0) elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d", when, *level, ret); if (SPI_processed > 0) { selected = DatumGetInt32(DirectFunctionCall1(int4in, CStringGetDatum(SPI_getvalue( SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1 )))); } elog(DEBUG, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected", when, *level, inserted, selected); SPI_finish(); (*level)--; if (*level == 0) *xid = InvalidTransactionId; return PointerGetDatum(tuple); } |