F.57. tcn
Пред. НаверхПриложение F. Дополнительно поставляемые модули и расширения, поставляемые в postgrespro-std-13-contribНачало След.

F.57. tcn

Модуль tcn предлагает триггерную функцию, уведомляющую приёмники уведомлений об изменениях в любой таблице, к которой привязан триггер. Она должна использоваться в качестве триггера AFTER вида FOR EACH ROW.

Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE в текущей базе данных.

Этой функции в операторе CREATE TRIGGER передаётся только один параметр, и он не является обязательным. Если этот параметр присутствует, он задаёт имя канала для уведомлений. В случае его отсутствия именем канала будет tcn.

Сообщение уведомления включает имя таблицы, букву, обозначающую тип выполняемой операции и пары имя столбца/значение для столбца первичного ключа. Каждая часть сообщения отделяется от следующей запятой. Для упрощения разбора сообщения регулярными выражениями имена таблицы и столбцов всегда заключаются в двойные кавычки, а значения данных — в апострофы. Внутренние кавычки и апострофы дублируются.

Далее приведена краткая демонстрация использования расширения. 

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.
Пред. Наверх След.
F.56. tablefunc Начало F.58. test_decoding
32.21. Building libpq Programs
Prev UpChapter 32. libpq — C LibraryHome Next

32.21. Building libpq Programs #

To build (i.e., compile and link) a program using libpq you need to do all of the following things:

  • Include the libpq-fe.h header file: 

    #include <libpq-fe.h>

    If you failed to do that then you will normally get error messages from your compiler similar to: 

    foo.c: In function `main':
foo.c:34: `PGconn' undeclared (first use in this function)
foo.c:35: `PGresult' undeclared (first use in this function)
foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)

  • Point your compiler to the directory where the Postgres Pro header files were installed, by supplying the -Idirectory option to your compiler. (In some cases the compiler will look into the directory in question by default, so you can omit this option.) For instance, your compile command line could look like: 

    cc -c -I/usr/local/pgsql/include testprog.c

    If you are using makefiles then add the option to the CPPFLAGS variable: 

    CPPFLAGS += -I/usr/local/pgsql/include

    If there is any chance that your program might be compiled by other users then you should not hardcode the directory location like that. Instead, you can run the utility pg_config to find out where the header files are on the local system: 

    $ pg_config --includedir
/usr/local/include

    If you have pkg-config installed, you can run instead: 

    $ pkg-config --cflags libpq
-I/usr/local/include

    Note that this will already include the -I in front of the path.

    Failure to specify the correct option to the compiler will result in an error message such as: 

    testlibpq.c:8:22: libpq-fe.h: No such file or directory

  • When linking the final program, specify the option -lpq so that the libpq library gets pulled in, as well as the option -Ldirectory to point the compiler to the directory where the libpq library resides. (Again, the compiler will search some directories by default.) For maximum portability, put the -L option before the -lpq option. For example: 

    cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq

    You can find out the library directory using pg_config as well: 

    $ pg_config --libdir
/usr/local/pgsql/lib

    Or again use pkg-config: 

    $ pkg-config --libs libpq
-L/usr/local/pgsql/lib -lpq

    Note again that this prints the full options, not only the path.

    Error messages that point to problems in this area could look like the following: 

    testlibpq.o: In function `main':
testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
testlibpq.o(.text+0x71): undefined reference to `PQstatus'
testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'

    This means you forgot -lpq. 

    /usr/bin/ld: cannot find -lpq

    This means you forgot the -L option or did not specify the right directory.

Prev Up Next
32.20. Behavior in Threaded Programs Home 32.22. Example Programs