1;; Postgresql C library interface, example program 2, using the xedit
2;; lisp interface
3
4;;  Test of the asynchronous notification interface
5;;
6;; Start this program, then from psql in another window do
7;;   NOTIFY TBL2;
8;;
9;; Or, if you want to get fancy, try this:
10;; Populate a database with the following:
11;;
12;;   CREATE TABLE TBL1 (i int4);
13;;
14;;   CREATE TABLE TBL2 (i int4);
15;;
16;;   CREATE RULE r1 AS ON INSERT TO TBL1 DO
17;;     (INSERT INTO TBL2 values (new.i); NOTIFY TBL2);
18;;
19;; and do
20;;
21;;   INSERT INTO TBL1 values (10);
22(require "psql")
23
24(defun exit-nicely (conn)
25    (pq-finish conn)
26    (quit 1)
27)
28
29;; begin, by setting the parameters for a backend connection if the
30;; parameters are null, then the system will try to use reasonable
31;; defaults by looking up environment variables or, failing that,
32;; using hardwired constants
33(setq pghost nil)		; host name of the backend server
34(setq pgport nil)		; port of the backend server
35(setq pgoptions nil)		; special options to start up the backend server
36(setq pgtty nil)		; debugging tty for the backend server
37(setq pgdbname "test")		; change this to the name of your test database
38				;; XXX Note: getenv not yet implemented in the
39				 ; lisp interpreter
40
41;; make a connection to the database
42(setq conn (pq-setdb pghost pgport pgoptions pgtty pgdbname))
43
44;; check to see that the backend connection was successfully made
45(when (= (pq-status conn) pg-connection-bad)
46    (format t "Connection to database '~A' failed.~%" pgdbname)
47    (format t "~A" (pq-error-message conn))
48    (exit-nicely conn))
49
50(setq res (pq-exec conn "LISTEN TBL2"))
51(when (= (pq-status conn) pg-connection-bad)
52    (format t "LISTEN command failed~%")
53    (format t "~A" (pq-error-message conn))
54    (exit-nicely conn))
55
56;; Should PQclear PGresult whenever it is no longer needed to avoid memory leaks
57(pq-clear res)
58
59(loop
60    ;; wait a little bit between checks; waiting with select()
61    ;; would be more efficient.
62	;; XXX Note: sleep not yet implemented in the lisp interpreter
63
64    ;; collect any asynchronous backend messages
65    (pq-consume-input conn)
66
67    ;; check for asynchronous notify messages
68    (when (setq notifies (pq-notifies conn))
69	(format t "ASYNC NOTIFY of '~A' from backend pid '~D' received~%"
70	 (pg-notify-relname notifies) (pg-notify-be-pid notifies))
71    )
72)
73
74(pq-finish conn)
75