| F.64. tcn — a trigger function to notify listeners of changes to table content | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions Shipped inpostgrespro-std-17-contrib | Home | Next |
F.64. tcn — a trigger function to notify listeners of changes to table content#
Thetcn module provides a trigger function that notifies listeners of changes to any table on which it is attached. It must be used as anAFTER triggerFOR EACH ROW.
This module is considered“trusted”, that is, it can be installed by non-superusers who haveCREATE privilege on the current database.
Only one parameter may be supplied to the function in aCREATE TRIGGER statement, and that is optional. If supplied it will be used for the channel name for the notifications. If omittedtcn 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 tcndatatest-# (test(# a int not null,test(# b date not null,test(# c text,test(# primary key (a, b)test(# );CREATE TABLEtest=# create trigger tcndata_tcn_triggertest-# after insert or update or delete on tcndatatest-# for each row execute function triggered_change_notification();CREATE TRIGGERtest=# listen tcn;LISTENtest=# 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 3Asynchronous 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 2Asynchronous 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 1Asynchronous notification "tcn" with payload ""tcndata",D,"a"='1',"b"='2012-12-22'" received from server process with PID 22770.