doc/src/sgml/tcn.sgml

   1 <!-- doc/src/sgml/tcn.sgml -->
   2
   3 <sect1 id="tcn" xreflabel="tcn">
   4   <title>tcn &mdash; 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>