tcn

The tcn module provides a trigger function that notifies listeners about changes on any table to which it is attached. It must be used as a row-level AFTER trigger.

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

Only a single argument may be supplied to the function in a CREATE TRIGGER statement, and it is optional. If supplied, it will be used as the channel name for notifications. If omitted, the channel name will default to tcn.

The payload of the notification consists of the table name, a letter indicating the type of operation performed, and column name/value pairs for the 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.

Here is a simple example using this extension.

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.