doc/src/sgml/tcn.sgml - master - githubmirror/postgres

doc/src/sgml/tcn.sgml
77 строк3 KB
David Rowley
Doc: use uppercase keywords in SQLs
06 ноя 2025, 06:03
06 ноя 2025, 06:0349d43fa
<!-- doc/src/sgml/tcn.sgml -->

<sect1 id="tcn" xreflabel="tcn">
  <title>tcn &mdash; 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>