Redrock Postgres 搜索 英文
版本: 9.3 / 9.4 / 9.5 / 9.6 / 10 / 11 / 12 / 13 / 14 / 15 / 16

F.37. spi

F.37.1. refint — 用于实现参照完整性的函数
F.37.2. timetravel — 实现时间旅行的函数
F.37.3. autoinc — 用于自增域的函数
F.37.4. insert_username — 用于跟踪谁修改了一个表的函数
F.37.5. moddatetime — 用于跟踪上一次修改时间的函数

spi模块提供了多个可工作的使用 SPI 和触发器的例子。尽管这些例子的价值只对它们自己合适,它们甚至更有助于作为例子来修改达到你自己的目的。这些函数足够普通,可以与任何表一起使用,但是在创建一个触发器时你必须指定表名和域名(如下所述) 。

下面描述的函数组中的每一个都作为一个独立可安装的扩展被提供。

F.37.1. refint — 用于实现参照完整性的函数

check_primary_key()check_foreign_key()被用来检查外键约束(当然,这个功能很早以前被内建的外建机制取代了,但是该模块还是可以用作一个例子)。

check_primary_key()检查引用表。用法是使用这个函数在一个引用其他表的表上创建一个BEFORE INSERT OR UPDATE触发器。指定该触发器的参数为:引用表中构成外键的列名、被引用表名称以及在被引用表中构成主键/唯一键的列名。要处理多个外键,请为每一个引用创建一个触发器。

check_foreign_key()检查被引用表。用法是使用这个函数在一个被其他表引用的表上创建一个BEFORE DELETE OR UPDATE触发器。指定该触发器的参数为:该函数必须对其执行检查的引用表数量、找到一个引用键后的动作(cascade — 删除引用行,restrict — 如果引用键存在则中断事务,setnull — 设置引用键域为空)、触发器所在表中构成主键/唯一键的列名、引用表名称和列名称(第一个参数指定多少个引用表就重复多少次)。注意主键/唯一键列应该被标记为 NOT NULL 并且应该有一个唯一索引。

refint.example中有一些例子。

F.37.2. timetravel — 实现时间旅行的函数

很久以前,PostgreSQL有一个内建的时间旅行特性,它为每一个元组保留插入和删除时间。这能够用这些函数模拟。要使用这些函数,你必须为一个表增加两个abstime类型的列来存储元组被插入的日期(start_date)以及被改变/删除的日期(stop_date):

CREATE TABLE mytab (
        ...             ...
        start_date      abstime,
        stop_date       abstime
        ...             ...
);

这些列可以以你喜欢的方式命名,但是在这次讨论中我们称它们为 start_date 和 stop_date。

当一个新行被插入时,start_date 通常应该被设置为当前时间,并且 stop_date 应当被设置为infinity。如果被插入的数据在这些列上包含空,触发器将自动替换这些值。通常,在这些列中插入显式非空数据的情况只有重新载入已转储数据。

等于infinity的元组现在是合法的,并且能够被修改。具有有限 stop_date 的元组不能再被修改 — 触发器将阻止修改(如果你需要这样做,你可以按照下面展示的关闭时间旅行)。

对于一个可修改的行,更新时只有被更新元组的 stop_date 将被改变(为当前时间)并且将会插入一个带有被修改数据的新元组。在这个新元组中的 start_date 将被设置为当前时间而 stop_date 被设置为infinity

一次删除并非真正地移除元组,而只是将它的 stop_date 设置为当前时间。

要查询当前有效的元组,在查询的 WHERE 条件中包括stop_date = 'infinity'(你可能希望在一个视图中使用它)。类似地,你可以用 start_date 和 stop_date 上合适的条件来查询在任何过去时刻有效的元组。

timetravel()是支持这种行为的通用触发器函数。使用这个函数在每一个需要时间旅行的表上创建一个BEFORE INSERT OR UPDATE OR DELETE触发器。指定两个触发器参数:start_date 和 stop_date 列的真实名称。可选地,你可以指定 1-3 个更多的参数,它们必须表示text类型的列。该触发器将会在 INSERT、UPDATE、DELETE 期间将当前用户名分别存储到第 1、2、3 列中。

set_timetravel()允许你在一个表上打开或关闭时间旅行。set_timetravel('mytab', 1)将为表mytab打开时间旅行。set_timetravel('mytab', 0)将为表mytab关闭时间旅行。在两种情况中都会报告旧的状态。当时间旅行被关闭时,你可以自由地修改 start_date 和 stop_date 列。注意开/关状态是对于当前数据库会话局部可见的 — 新会话开始时所有表上的时间旅行总是被打开的。

get_timetravel()返回一个表的时间旅行状态,但不会改变它。

timetravel.example中有一个例子。

F.37.3. autoinc — 用于自增域的函数

autoinc()是一个将序列的下一个值存储到一个整数域的触发器。这和内建的序数列特性有些重叠,但是它并不完全一样:autoinc()在插入时会覆盖掉给出的不同域值,并且它可被选择用来在更新时增加域。

用法是使用这个函数创建一个BEFORE INSERT(或者BEFORE INSERT OR UPDATE)触发器。指定两个触发器参数:要被修改的整数列名和将提供值的序列对象名(事实上,如果你想要更新多于一个自增列,你可以指定任意数量的这种名称对)。

autoinc.example中有一个例子。

F.37.4. insert_username — 用于跟踪谁修改了一个表的函数

insert_username()是存储当前用户名到一个文本域的触发器。这有助于跟踪是谁最后在一个表中修改了一个特定行。

用法是使用这个函数创建一个BEFORE INSERT以及/或者UPDATE触发器。指定一个触发器参数:要被修改的文本列名。

insert_username.example中有一个例子。

F.37.5. moddatetime — 用于跟踪上一次修改时间的函数

moddatetime()是一个存储当前时间到一个timestamp域的触发器。它有助于跟踪一个表中特定行最后一次的修改时间。

用法是使用这个函数创建一个BEFORE UPDATE触发器。指定一个触发器参数:要被修改的列名。该列必须是类型timestamp或者timestamp with time zone

moddatetime.example中有一个例子。