xref: /xsrc/external/mit/xedit/dist/lisp/test/psql-2.lsp

;; Postgresql C library interface, example program 2, using the xedit
;; lisp interface

;;  Test of the asynchronous notification interface
;;
;; Start this program, then from psql in another window do
;;   NOTIFY TBL2;
;;
;; Or, if you want to get fancy, try this:
;; Populate a database with the following:
;;
;;   CREATE TABLE TBL1 (i int4);
;;
;;   CREATE TABLE TBL2 (i int4);
;;
;;   CREATE RULE r1 AS ON INSERT TO TBL1 DO
;;     (INSERT INTO TBL2 values (new.i); NOTIFY TBL2);
;;
;; and do
;;
;;   INSERT INTO TBL1 values (10);
(require "psql")

(defun exit-nicely (conn)
    (pq-finish conn)
    (quit 1)
)

;; begin, by setting the parameters for a backend connection if the
;; parameters are null, then the system will try to use reasonable
;; defaults by looking up environment variables or, failing that,
;; using hardwired constants
(setq pghost nil)		; host name of the backend server
(setq pgport nil)		; port of the backend server
(setq pgoptions nil)		; special options to start up the backend server
(setq pgtty nil)		; debugging tty for the backend server
(setq pgdbname "test")		; change this to the name of your test database
				;; XXX Note: getenv not yet implemented in the
				 ; lisp interpreter

;; make a connection to the database
(setq conn (pq-setdb pghost pgport pgoptions pgtty pgdbname))

;; check to see that the backend connection was successfully made
(when (= (pq-status conn) pg-connection-bad)
    (format t "Connection to database '~A' failed.~%" pgdbname)
    (format t "~A" (pq-error-message conn))
    (exit-nicely conn))

(setq res (pq-exec conn "LISTEN TBL2"))
(when (= (pq-status conn) pg-connection-bad)
    (format t "LISTEN command failed~%")
    (format t "~A" (pq-error-message conn))
    (exit-nicely conn))

;; Should PQclear PGresult whenever it is no longer needed to avoid memory leaks
(pq-clear res)

(loop
    ;; wait a little bit between checks; waiting with select()
    ;; would be more efficient.
	;; XXX Note: sleep not yet implemented in the lisp interpreter

    ;; collect any asynchronous backend messages
    (pq-consume-input conn)

    ;; check for asynchronous notify messages
    (when (setq notifies (pq-notifies conn))
	(format t "ASYNC NOTIFY of '~A' from backend pid '~D' received~%"
	 (pg-notify-relname notifies) (pg-notify-be-pid notifies))
    )
)

(pq-finish conn)