F.42. tcn — a trigger function to notify listeners of changes to table content
Prev | Up | Appendix F. Additional Supplied Modules and Extensions | Home | Next

## F.42. tcn — a trigger function to notify listeners of changes to table content #

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`.

This module is considered “trusted”, that is, it can be installed by non-superusers who have `CREATE` privilege on the current database.

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.

Prev | Up | Next
F.41. tablefunc — functions that return tables (`crosstab` and others) | Home | F.43. test_decoding — SQL-based test/example module for WAL logical decoding