PostgreSQL 9.3.1 中文手册 | ||||
---|---|---|---|---|
上一页 | 上一级 | 附录 F. 额外提供的模块 | 下一页 |
tcn模块提供了一个触发器函数,通知监听器它附着的任何表有修改。 它必须作为一个AFTER触发器FOR EACH ROW使用。
在一个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 procedure 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.