Postgresql 中文操作指南

F.41. spi — Server Programming Interface features/examples #

spi 模块提供了一些有关如何使用 Server Programming Interface (SPI) 和触发器的可行的示例。虽然这些函数本身有价值，但它们作为供修改以用于你自己的目的的示例更为有用。这些函数很通用，可用于任何表，但你必须在创建触发器时指定表和字段名称（如下所述）。

The spi module provides several workable examples of using the Server Programming Interface (SPI) and triggers. While these functions are of some value in their own right, they are even more useful as examples to modify for your own purposes. The functions are general enough to be used with any table, but you have to specify table and field names (as described below) while creating a trigger.

下面描述的每个函数组作为单独可安装的扩展提供。

Each of the groups of functions described below is provided as a separately-installable extension.

F.41.1. refint — Functions for Implementing Referential Integrity #

check_primary_key() 和 check_foreign_key() 用于检查外键约束。（当然，此功能早已被内置外键机制所取代，但该模块仍可作为示例使用。）

check_primary_key() and check_foreign_key() are used to check foreign key constraints. (This functionality is long since superseded by the built-in foreign key mechanism, of course, but the module is still useful as an example.)

check_primary_key() 检查引用表。要使用，请使用此函数在引用另一个表的表上创建 BEFORE INSERT OR UPDATE 触发器。指定为触发器参数：外键的引用表的列名称、引用的表名称和引用的表中形成主键/唯一键的列名称。要处理多个外键，请为每个引用创建触发器。

check_primary_key() checks the referencing table. To use, create a BEFORE INSERT OR UPDATE trigger using this function on a table referencing another table. Specify as the trigger arguments: the referencing table’s column name(s) which form the foreign key, the referenced table name, and the column names in the referenced table which form the primary/unique key. To handle multiple foreign keys, create a trigger for each reference.

check_foreign_key() 检查引用的表。要使用，请使用此函数在由另一个表（或多个表）引用的表上创建 BEFORE DELETE OR UPDATE 触发器。指定为触发器参数：函数必须为其执行检查的引用表数、如果找到引用键则执行的操作（cascade — 删除引用行、restrict — 如果存在引用键则中止事务、setnull — 将引用键字段设置为 null）、形成主键/唯一键的触发表列名称，然后形成引用表名称和列名称（重复指定的数量与第一个参数指定的引用表数量相同）。注意，主键/唯一键列应标记为 NOT NULL，并且应具有唯一索引。

check_foreign_key() checks the referenced table. To use, create a BEFORE DELETE OR UPDATE trigger using this function on a table referenced by other table(s). Specify as the trigger arguments: the number of referencing tables for which the function has to perform checking, the action if a referencing key is found (cascade — to delete the referencing row, restrict — to abort transaction if referencing keys exist, setnull — to set referencing key fields to null), the triggered table’s column names which form the primary/unique key, then the referencing table name and column names (repeated for as many referencing tables as were specified by first argument). Note that the primary/unique key columns should be marked NOT NULL and should have a unique index.

refint.example 中有示例。

There are examples in refint.example.

F.41.2. autoinc — Functions for Autoincrementing Fields #

autoinc() 是一种触发器，它将序列的下一个值存储到整数字段中。这与内置的“序列列”功能有一些重叠，但它不是相同的：autoinc() 将覆盖在插入过程中用其他字段值替换的尝试，并且可以选择在更新过程中使用它来增加该字段。

autoinc() is a trigger that stores the next value of a sequence into an integer field. This has some overlap with the built-in “serial column” feature, but it is not the same: autoinc() will override attempts to substitute a different field value during inserts, and optionally it can be used to increment the field during updates, too.

要使用，请使用此函数创建 BEFORE INSERT（或选择 BEFORE INSERT OR UPDATE）触发器。指定两个触发器参数：要修改的整数列的名称和将提供值的序列对象的名称。（实际上，如果你想更新多个自动增量列，你可以指定任意数量的此类名称对。）

To use, create a BEFORE INSERT (or optionally BEFORE INSERT OR UPDATE) trigger using this function. Specify two trigger arguments: the name of the integer column to be modified, and the name of the sequence object that will supply values. (Actually, you can specify any number of pairs of such names, if you’d like to update more than one autoincrementing column.)

autoinc.example 中有一个示例。

There is an example in autoinc.example.

F.41.3. insert_username — Functions for Tracking Who Changed a Table #

insert_username() 是一个触发器，用于将当前用户的名称存储到文本字段中。这可用于跟踪谁最后修改了表中的特定行。

insert_username() is a trigger that stores the current user’s name into a text field. This can be useful for tracking who last modified a particular row within a table.

要使用，请使用此函数创建 BEFORE INSERT 和/或 UPDATE 触发器。指定单个触发器参数：要修改的文本列的名称。

To use, create a BEFORE INSERT and/or UPDATE trigger using this function. Specify a single trigger argument: the name of the text column to be modified.

insert_username.example 中有一个示例。

There is an example in insert_username.example.

F.41.4. moddatetime — Functions for Tracking Last Modification Time #

moddatetime() 是一个触发器，用于将当前时间存储到 timestamp 字段中。这可用于跟踪表中特定行的最后修改时间。

moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

要使用，请使用此函数创建 BEFORE UPDATE 触发器。指定单个触发器参数：要修改的列的名称。列必须为 timestamp 或 timestamp with time zone 类型。

To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

moddatetime.example 中有一个示例。

There is an example in moddatetime.example.