tcn

tcn模块提供一个触发器函数，它通知监听者有关它所附着的任意表上的改变。它必须被用作一个行级AFTER触发器。

这个模块被认为是“可信的”，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

在一个CREATE TRIGGER语句中只可以为该函数提供一个参数，并且是可选的。如果提供该参数，它将被作为用于通知的频道名。如果忽略它，频道名将使用tcn。

通知的负载由表名、一个指示所执行操作类型的字母以及用于主键列的列名/值对构成。每一部分都用逗号与下一部分隔开。为了便于解析对正则表达式的使用，表和列名总是被包裹在双引号内，并且数据值总是被包裹在单引号内。嵌入的引号都被双写。

下面是使用该扩展的简单例子。

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.