Postgresql 中文操作指南

9.28. Trigger Functions #

虽然触发器的许多用途都涉及用户编写的触发器函数，但 PostgreSQL 提供了几个可以直接在用户自定义触发器中使用的内置触发器函数。这些函数已在 Table 9.103 中汇总。（存在其他内置触发器函数，它们可实现外来键约束和暂缓索引约束。这里未记录这些函数，因为用户无需直接使用它们。）

While many uses of triggers involve user-written trigger functions, PostgreSQL provides a few built-in trigger functions that can be used directly in user-defined triggers. These are summarized in Table 9.103. (Additional built-in trigger functions exist, which implement foreign key constraints and deferred index constraints. Those are not documented here since users need not use them directly.)

有关创建触发器的详细信息，请参阅 CREATE TRIGGER 。

For more information about creating triggers, see CREATE TRIGGER.

Table 9.103. Built-In Trigger Functions

Function

Description

Example Usage

suppress_redundant_updates_trigger ( ) → trigger

Suppresses do-nothing update operations. See below for details.

CREATE TRIGGER … suppress_redundant_updates_trigger()

tsvector_update_trigger ( ) → trigger

Automatically updates a tsvector column from associated plain-text document column(s). The text search configuration to use is specified by name as a trigger argument. See Section 12.4.3 for details.

CREATE TRIGGER … tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)

tsvector_update_trigger_column ( ) → trigger

Automatically updates a tsvector column from associated plain-text document column(s). The text search configuration to use is taken from a regconfig column of the table. See Section 12.4.3 for details.

CREATE TRIGGER … tsvector_update_trigger_column(tsvcol, tsconfigcol, title, body)

suppress_redundant_updates_trigger 函数在作为行级 BEFORE UPDATE 触发器应用时，将会禁止执行任何不会实际更改行中数据的更新。这会覆盖始终执行物理行更新的正常行为，而不管数据是否已更改。（此正常行为会使更新运行得更快，因为无需检查，并且在某些情况下也很有用。）

The suppress_redundant_updates_trigger function, when applied as a row-level BEFORE UPDATE trigger, will prevent any update that does not actually change the data in the row from taking place. This overrides the normal behavior which always performs a physical row update regardless of whether or not the data has changed. (This normal behavior makes updates run faster, since no checking is required, and is also useful in certain cases.)

理想情况下，你应该避免运行实际上不会更改记录中数据的更新。冗余更新会花费大量不必要的时间，尤其是在有很多要更改的索引时，以及在最终必须进行真空处理的无效行中花费的空间时。然而，在客户端代码中检测到此类情况并不总是容易的，甚至有可能，并且编写表达式来检测它们容易出错。一种替代方法是使用 suppress_redundant_updates_trigger，它将跳过不会更改数据的更新。但是，你应该谨慎使用此功能。触发器为每条记录花费一些时间，虽然不多，但如果实际上受到更新影响的大部分记录都发生变化，使用此触发器将使更新平均运行得更慢。

Ideally, you should avoid running updates that don’t actually change the data in the record. Redundant updates can cost considerable unnecessary time, especially if there are lots of indexes to alter, and space in dead rows that will eventually have to be vacuumed. However, detecting such situations in client code is not always easy, or even possible, and writing expressions to detect them can be error-prone. An alternative is to use suppress_redundant_updates_trigger, which will skip updates that don’t change the data. You should use this with care, however. The trigger takes a small but non-trivial time for each record, so if most of the records affected by updates do actually change, use of this trigger will make updates run slower on average.

suppress_redundant_updates_trigger 函数可以像这样添加到表中：

The suppress_redundant_updates_trigger function can be added to a table like this:

CREATE TRIGGER z_min_update
BEFORE UPDATE ON tablename
FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();

在大多数情况下，你需要对每个行的触发器最后触发，以便它不会覆盖可能希望更改行的其他触发器。请记住，触发器按名称顺序触发，因此你将选择一个触发器名称，该名称在表上可能有的任何其他触发器的名称之后。（因此示例中的“z”前缀。）

In most cases, you need to fire this trigger last for each row, so that it does not override other triggers that might wish to alter the row. Bearing in mind that triggers fire in name order, you would therefore choose a trigger name that comes after the name of any other trigger you might have on the table. (Hence the “z” prefix in the example.)