PostgreSQL tcn

tcn模塊提供一個觸發(fā)器函數(shù)，它通知監(jiān)聽者有關(guān)它所附著的任意表上的改變。它必須被用作一個行級AFTER觸發(fā)器。

這個模塊被認(rèn)為是“可信的”，也就是說，它可以由對當(dāng)前數(shù)據(jù)庫具有CREATE權(quán)限的非超級用戶安裝。

在一個CREATE TRIGGER語句中只可以為該函數(shù)提供一個參數(shù)，并且是可選的。如果提供該參數(shù)，它將被作為用于通知的頻道名。如果忽略它，頻道名將使用tcn。

通知的負(fù)載由表名、一個指示所執(zhí)行操作類型的字母以及用于主鍵列的列名/值對構(gòu)成。每一部分都用逗號與下一部分隔開。為了便于解析對正則表達式的使用，表和列名總是被包裹在雙引號內(nèi)，并且數(shù)據(jù)值總是被包裹在單引號內(nèi)。嵌入的引號都被雙寫。

下面是使用該擴展的簡單例子。

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.