Postgresql 中文操作指南
F.44. tcn — a trigger function to notify listeners of changes to table content #
tcn 模块提供了一个触发器函数，用于向侦听器通知其所连接的任何表的变化。它必须用作 AFTER 触发器 FOR EACH ROW。
The tcn module provides a trigger function that notifies listeners of changes to any table on which it is attached. It must be used as an AFTER trigger FOR EACH ROW.
此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。
This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.
在 CREATE TRIGGER 语句中只能向函数提供一个参数，并且该参数是可选的。如果提供，它将被用作通知的通道名称。如果省略，tcn 将被用作通道名称。
Only one parameter may be supplied to the function in a CREATE TRIGGER statement, and that is optional. If supplied it will be used for the channel name for the notifications. If omitted tcn will be used for the channel name.
通知的有效负载包括表名、指示执行了哪种类型操作的字母，以及主键列的列名/值对。每个部分由逗号分隔。为了使用正则表达式轻松进行解析，表名和列名始终用双引号括起来，而数据值始终用单引号括起来。嵌入的引号加倍。
The payload of the notifications consists of the table name, a letter to indicate which type of operation was performed, and column name/value pairs for primary key columns. Each part is separated from the next by a comma. For ease of parsing using regular expressions, table and column names are always wrapped in double quotes, and data values are always wrapped in single quotes. Embedded quotes are doubled.
以下是使用此扩展名的一个简要示例。
A brief example of using the extension follows.
test=# create table tcndata
test-# (
test(# a int not null,
test(# b date not null,
test(# c text,
test(# primary key (a, b)
test(# );
CREATE TABLE
test=# create trigger tcndata_tcn_trigger
test-# after insert or update or delete on tcndata
test-# for each row execute function triggered_change_notification();
CREATE TRIGGER
test=# listen tcn;
LISTEN
test=# insert into tcndata values (1, date '2012-12-22', 'one'),
test-# (1, date '2012-12-23', 'another'),
test-# (2, date '2012-12-23', 'two');
INSERT 0 3
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-23'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='2',"b"='2012-12-23'" received from server process with PID 22770.
test=# update tcndata set c = 'uno' where a = 1;
UPDATE 2
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-23'" received from server process with PID 22770.
test=# delete from tcndata where a = 1 and b = date '2012-12-22';
DELETE 1
Asynchronous notification "tcn" with payload ""tcndata",D,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.