| 1 |
<!-- doc/src/sgml/tcn.sgml --> |
| 2 |
|
| 3 |
<sect1 id="tcn" xreflabel="tcn"> |
| 4 |
<title>tcn — a trigger function to notify listeners of changes to table content</title> |
| 5 |
|
| 6 |
<indexterm zone="tcn"> |
| 7 |
<primary>tcn</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<indexterm zone="tcn"> |
| 11 |
<primary>triggered_change_notification</primary> |
| 12 |
</indexterm> |
| 13 |
|
| 14 |
<para> |
| 15 |
The <filename>tcn</filename> module provides a trigger function that notifies |
| 16 |
listeners of changes to any table on which it is attached. It must be |
| 17 |
used as an <literal>AFTER</literal> trigger <literal>FOR EACH ROW</literal>. |
| 18 |
</para> |
| 19 |
|
| 20 |
<para> |
| 21 |
This module is considered <quote>trusted</quote>, that is, it can be |
| 22 |
installed by non-superusers who have <literal>CREATE</literal> privilege |
| 23 |
on the current database. |
| 24 |
</para> |
| 25 |
|
| 26 |
<para> |
| 27 |
Only one parameter may be supplied to the function in a |
| 28 |
<literal>CREATE TRIGGER</literal> statement, and that is optional. If supplied |
| 29 |
it will be used for the channel name for the notifications. If omitted |
| 30 |
<literal>tcn</literal> will be used for the channel name. |
| 31 |
</para> |
| 32 |
|
| 33 |
<para> |
| 34 |
The payload of the notifications consists of the table name, a letter to |
| 35 |
indicate which type of operation was performed, and column name/value pairs |
| 36 |
for primary key columns. Each part is separated from the next by a comma. |
| 37 |
For ease of parsing using regular expressions, table and column names are |
| 38 |
always wrapped in double quotes, and data values are always wrapped in |
| 39 |
single quotes. Embedded quotes are doubled. |
| 40 |
</para> |
| 41 |
|
| 42 |
<para> |
| 43 |
A brief example of using the extension follows. |
| 44 |
|
| 45 |
<programlisting> |
| 46 |
test=# create table tcndata |
| 47 |
test-# ( |
| 48 |
test(# a int not null, |
| 49 |
test(# b date not null, |
| 50 |
test(# c text, |
| 51 |
test(# primary key (a, b) |
| 52 |
test(# ); |
| 53 |
CREATE TABLE |
| 54 |
test=# create trigger tcndata_tcn_trigger |
| 55 |
test-# after insert or update or delete on tcndata |
| 56 |
test-# for each row execute function triggered_change_notification(); |
| 57 |
CREATE TRIGGER |
| 58 |
test=# listen tcn; |
| 59 |
LISTEN |
| 60 |
test=# insert into tcndata values (1, date '2012-12-22', 'one'), |
| 61 |
test-# (1, date '2012-12-23', 'another'), |
| 62 |
test-# (2, date '2012-12-23', 'two'); |
| 63 |
INSERT 0 3 |
| 64 |
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-22'" received from server process with PID 22770. |
| 65 |
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='1',"b"='2012-12-23'" received from server process with PID 22770. |
| 66 |
Asynchronous notification "tcn" with payload ""tcndata",I,"a"='2',"b"='2012-12-23'" received from server process with PID 22770. |
| 67 |
test=# update tcndata set c = 'uno' where a = 1; |
| 68 |
UPDATE 2 |
| 69 |
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-22'" received from server process with PID 22770. |
| 70 |
Asynchronous notification "tcn" with payload ""tcndata",U,"a"='1',"b"='2012-12-23'" received from server process with PID 22770. |
| 71 |
test=# delete from tcndata where a = 1 and b = date '2012-12-22'; |
| 72 |
DELETE 1 |
| 73 |
Asynchronous notification "tcn" with payload ""tcndata",D,"a"='1',"b"='2012-12-22'" received from server process with PID 22770. |
| 74 |
</programlisting> |
| 75 |
</para> |
| 76 |
|
| 77 |
</sect1> |