1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
<!-- doc/src/sgml/tcn.sgml -->
<sect1 id="tcn" xreflabel="tcn">
<title>tcn — a trigger function to notify listeners of changes to table content</title>
<indexterm zone="tcn">
<primary>tcn</primary>
</indexterm>
<indexterm zone="tcn">
<primary>triggered_change_notification</primary>
</indexterm>
<para>
The <filename>tcn</filename> module provides a trigger function that notifies
listeners of changes to any table on which it is attached. It must be
used as an <literal>AFTER</literal> trigger <literal>FOR EACH ROW</literal>.
</para>
<para>
This module is considered <quote>trusted</quote>, that is, it can be
installed by non-superusers who have <literal>CREATE</literal> privilege
on the current database.
</para>
<para>
Only one parameter may be supplied to the function in a
<literal>CREATE TRIGGER</literal> statement, and that is optional. If supplied
it will be used for the channel name for the notifications. If omitted
<literal>tcn</literal> will be used for the channel name.
</para>
<para>
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.
</para>
<para>
A brief example of using the extension follows.
<programlisting>
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.
</programlisting>
</para>
</sect1>
|
