Postgresql 中文操作指南

44.6. Trigger Functions in PL/Tcl #

触发器函数可以用 PL/Tcl 编写。PostgreSQL 要求作为触发器调用的函数必须被声明为不带参数且返回类型为 trigger 的函数。

Trigger functions can be written in PL/Tcl. PostgreSQL requires that a function 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 function body in the following variables:

  • $TG_name

    • The name of the trigger from the CREATE TRIGGER statement.

  • $TG_relid

    • The object ID of the table that caused the trigger function to be invoked.

  • $TG_table_name

    • The name of the table that caused the trigger function to be invoked.

  • $TG_table_schema

    • The schema of the table that caused the trigger function to be invoked.

  • $TG_relatts

    • A Tcl list of the table column names, prefixed with an empty list element. So looking up a column name in the list with Tcl’s lsearch command returns the element’s number starting with 1 for the first column, the same way the columns are customarily numbered in PostgreSQL. (Empty list elements also appear in the positions of columns that have been dropped, so that the attribute numbering is correct for columns to their right.)

  • $TG_when

    • The string BEFORE, AFTER, or INSTEAD OF, depending on the type of trigger event.

  • $TG_level

    • The string ROW or STATEMENT depending on the type of trigger event.

  • $TG_op

    • The string INSERT, UPDATE, DELETE, or TRUNCATE depending on the type of trigger event.

  • $NEW

    • An associative array containing the values of the new table row for INSERT or UPDATE actions, or empty for DELETE. The array is indexed by column name. Columns that are null will not appear in the array. This is not set for statement-level triggers.

  • $OLD

    • An associative array containing the values of the old table row for UPDATE or DELETE actions, or empty for INSERT. The array is indexed by column name. Columns that are null will not appear in the array. This is not set for statement-level triggers.

  • $args

    • A Tcl list of the arguments to the function as given in the CREATE TRIGGER statement. These arguments are also accessible as $1 …​ $_n_ in the function body.

触发器函数的返回值可以是字符串_OK_或_SKIP_，或者一个列名/值对列表。如果返回值是_OK_，触发触发器的操作（INSERT/UPDATE/DELETE）将正常进行。SKIP_告诉触发器管理器静默抑制该行的操作。如果返回一个列表，它告诉PL/Tcl向触发器管理器返回一个修改后的行；修改后的行的内容由列表中的列名和值指定。列表中未提及的任何列均设为null。仅当返回修改后的行对于行级_BEFORE INSERT_或_UPDATE_触发器有意义，修改后的行将被插入而不是$NEW_中给出的行；或对于行级_INSTEAD OF_ INSERT_或_UPDATE_触发器，其中返回的行用作_INSERT RETURNING_或_UPDATE RETURNING_子句的源数据。在行级_BEFORE DELETE_或_INSTEAD OF _DELETE_触发器中，返回修改后的行的效果与返回_OK_相同，即操作进行。对于所有其他类型的触发器，都忽略触发器返回值。

The return value from a trigger function can be one of the strings OK or SKIP, or a list of column name/value pairs. If the return value is OK, the operation (INSERT/UPDATE/DELETE) that fired the trigger will proceed normally. SKIP tells the trigger manager to silently suppress the operation for this row. If a list is returned, it tells PL/Tcl to return a modified row to the trigger manager; the contents of the modified row are specified by the column names and values in the list. Any columns not mentioned in the list are set to null. Returning a modified row is only meaningful for row-level BEFORE INSERT or UPDATE triggers, for which the modified row will be inserted instead of the one given in $NEW; or for row-level INSTEAD OF INSERT or UPDATE triggers where the returned row is used as the source data for INSERT RETURNING or UPDATE RETURNING clauses. In row-level BEFORE DELETE or INSTEAD OF DELETE triggers, returning a modified row has the same effect as returning OK, that is the operation proceeds. The trigger return value is ignored for all other types of triggers.

Tip

结果列表可以用使用_array get_Tcl命令的修改后的元组的数组表示法生成。

The result list can be made from an array representation of the modified tuple with the array get Tcl command.

这是一个小型的示例触发器函数，它强制表中的整型值跟踪对该行执行的更新次数。对于插入的新行，该值初始化为0，然后在每次更新操作时递增。

Here’s a little example trigger function 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 $$
    switch $TG_op {
        INSERT {
            set NEW($1) 0
        }
        UPDATE {
            set NEW($1) $OLD($1)
            incr NEW($1)
        }
        default {
            return OK
        }
    }
    return [array get NEW]
$$ LANGUAGE pltcl;

CREATE TABLE mytab (num integer, description text, modcnt integer);

CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
    FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt');

请注意，触发器函数本身不知道列名；这是从触发器参数中提供的。这允许触发器函数与不同的表重复使用。

Notice that the trigger function itself does not know the column name; that’s supplied from the trigger arguments. This lets the trigger function be reused with different tables.