Trigger procedures can be written in PL/R. PostgreSQL requires that a procedure that is to be called as a trigger must be declared as a function with no arguments and a return type of trigger.
The information from the trigger manager is passed to the procedure body in the following variables:
The name of the trigger from the CREATE TRIGGER statement.
The object ID of the table that caused the trigger procedure to be invoked.
The name of the table that caused the trigger procedure to be invoked.
The string BEFORE or AFTER depending on the type of trigger call.
The string ROW or STATEMENT depending on the type of trigger call.
The string INSERT, UPDATE, or DELETE depending on the type of trigger call.
When the trigger is defined FOR EACH ROW, a data.frame containing the values of the new table row for INSERT or UPDATE actions. For triggers defined FOR EACH STATEMENT and for DELETE actions, set to NULL. The atribute names are the table's column names. Columns that are null will be represented as NA.
When the trigger is defined FOR EACH ROW, a data.frame containing the values of the old table row for DELETE or UPDATE actions. For triggers defined FOR EACH STATEMENT and for INSERT actions, set to NULL. The atribute names are the table's column names. Columns that are null will be represented as NA.
A vector of the arguments to the procedure as given in the CREATE TRIGGER statement.
The return value from a trigger procedure can be NULL or a one row data.frame matching the number and type of columns in the trigger table. NULL tells the trigger manager to silently suppress the operation for this row. If a one row data.frame is returned, it tells PL/R to return a possibly modified row to the trigger manager that will be inserted instead of the one given in pg.tg.new. This works for INSERT and UPDATE only. Needless to say that all this is only meaningful when the trigger is BEFORE and FOR EACH ROW; otherwise the return value is ignored.
Here's a little example trigger procedure that forces an integer value in a table to keep track of the number of updates that are performed on the row. For new rows inserted, the value is initialized to 0 and then incremented on every update operation.
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '
if (pg.tg.op == "INSERT")
{
retval <- pg.tg.new
retval[pg.tg.args[1]] <- 0
}
if (pg.tg.op == "UPDATE")
{
retval <- pg.tg.new
retval[pg.tg.args[1]] <- pg.tg.old[pg.tg.args[1]] + 1
}
if (pg.tg.op == "DELETE")
retval <- pg.tg.old
return(retval)
' LANGUAGE plr;
CREATE TABLE mytab (num integer, description text, modcnt integer);
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');Notice that the trigger procedure itself does not know the column name; that's supplied from the trigger arguments. This lets the trigger procedure be reused with different tables.