This is an example of one method of establishing an asynchronous connection.
(defun database-poller (P)
(message "%S before poll" (pq-pgconn P 'pq::status))
(pq-connect-poll P)
(message "%S after poll" (pq-pgconn P 'pq::status))
(if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
(message "Done!")
(add-timeout .1 'database-poller P)))
⇒ database-poller
(progn
(setq P (pq-connect-start ""))
(add-timeout .1 'database-poller P))
⇒ pg::connection-started before poll
⇒ pg::connection-made after poll
⇒ pg::connection-made before poll
⇒ pg::connection-awaiting-response after poll
⇒ pg::connection-awaiting-response before poll
⇒ pg::connection-auth-ok after poll
⇒ pg::connection-auth-ok before poll
⇒ pg::connection-setenv after poll
⇒ pg::connection-setenv before poll
⇒ pg::connection-ok after poll
⇒ Done!
P
⇒ #<PGconn kantdb:5432 freundt/freundt>
Here is an example of one method of doing an asynchronous reset.
(defun database-poller (P)
(let (PS)
(message "%S before poll" (pq-pgconn P 'pq::status))
(setq PS (pq-reset-poll P))
(message "%S after poll [%S]" (pq-pgconn P 'pq::status) PS)
(if (eq (pq-pgconn P 'pq::status) 'pg::connection-ok)
(message "Done!")
(add-timeout .1 'database-poller P))))
⇒ database-poller
(progn
(pq-reset-start P)
(add-timeout .1 'database-poller P))
⇒ pg::connection-started before poll
⇒ pg::connection-made after poll [pgres::polling-writing]
⇒ pg::connection-made before poll
⇒ pg::connection-awaiting-response after poll [pgres::polling-reading]
⇒ pg::connection-awaiting-response before poll
⇒ pg::connection-setenv after poll [pgres::polling-reading]
⇒ pg::connection-setenv before poll
⇒ pg::connection-ok after poll [pgres::polling-ok]
⇒ Done!
P
⇒ #<PGconn kantdb:5432 freundt/freundt>
And finally, an asynchronous query.
(defun database-poller (P)
(let (R)
(pq-consume-input P)
(if (pq-is-busy P)
(add-timeout .1 'database-poller P)
(setq R (pq-get-result P))
(if R
(progn
(push R result-list)
(add-timeout .1 'database-poller P))))))
⇒ database-poller
(when (pq-send-query P "SELECT * FROM sxemacs_codenames;")
(setq result-list nil)
(add-timeout .1 'database-poller P))
⇒ 1910971
;; wait a moment
result-list
⇒ (#<PGresult PGRES_TUPLES_OK[7] - SELECT>)
Here is an example showing how multiple SQL statements in a single query can have all their results collected.
;; Using the same database-poller function from the previous example
(when (pq-send-query P "SELECT * FROM sxemacs_codenames;
SELECT * FROM pg_database;
SELECT * FROM pg_user;")
(setq result-list nil)
(add-timeout .1 'database-poller P))
⇒ 1911150
;; wait a moment
result-list
⇒ (#<PGresult PGRES_TUPLES_OK[8] - SELECT> #<PGresult PGRES_TUPLES_OK[10] - SELECT> #<PGresult PGRES_TUPLES_OK[7] - SELECT>)
Here is an example which illustrates collecting all data from a query, including the field names.
(defun pg-util-query-results (results)
"Retrieve results of last SQL query into a list structure."
(let ((i (1- (pq-ntuples R)))
j l1 l2)
(while (>= i 0)
(setq j (1- (pq-nfields R)))
(setq l2 nil)
(while (>= j 0)
(push (pq-get-value R i j) l2)
(decf j))
(push l2 l1)
(decf i))
(setq j (1- (pq-nfields R)))
(setq l2 nil)
(while (>= j 0)
(push (pq-fname R j) l2)
(decf j))
(push l2 l1)
l1))
⇒ pg-util-query-results
(setq R (pq-exec P "SELECT * FROM sxemacs_codenames ORDER BY codename DESC;"))
⇒ #<PGresult PGRES_TUPLES_OK[7] - SELECT>
(pg-util-query-results R)
⇒ (("id" "version" "codename") ("8" "22.1.7" "Celica") ("7" "22.1.6" "Cadillac") ("6" "22.1.5" "Bugatti") ("4" "22.1.3" "BMW") ("3" "22.1.2" "Audi") ("2" "22.1.1" "Aston Martin") ("1" "22.1.0" "Alfa Romeo"))
Here is an example of a query that uses a database cursor.
(let (data R)
(setq R (pq-exec P "BEGIN;"))
(setq R (pq-exec P "DECLARE k_cursor CURSOR FOR SELECT * FROM sxemacs_codenames ORDER BY version DESC;"))
(setq R (pq-exec P "FETCH k_cursor;"))
(while (eq (pq-ntuples R) 1)
(push (list (pq-get-value R 0 0) (pq-get-value R 0 1)) data)
(setq R (pq-exec P "FETCH k_cursor;")))
(setq R (pq-exec P "END;"))
data)
⇒ (("1" "22.1.0") ("2" "22.1.1") ("3" "22.1.2") ("4" "22.1.3") ("6" "22.1.5") ("7" "22.1.6") ("8" "22.1.7"))
Here's another example of cursors, this time with a Lisp macro to implement a mapping function over a table.
(defmacro map-db (P table condition callout)
`(let (R)
(pq-exec ,P "BEGIN;")
(pq-exec ,P (concat "DECLARE k_cursor CURSOR FOR SELECT * FROM "
,table
" "
,condition
" ORDER BY codename DESC;"))
(setq R (pq-exec P "FETCH k_cursor;"))
(while (eq (pq-ntuples R) 1)
(,callout (pq-get-value R 0 0) (pq-get-value R 0 1))
(setq R (pq-exec P "FETCH k_cursor;")))
(pq-exec P "END;")))
⇒ map-db
(defun callback (arg1 arg2)
(message "arg1 = %s, arg2 = %s" arg1 arg2))
⇒ callback
(map-db P "sxemacs_codenames" "WHERE version > '22.1.0'" callback)
⇒ arg1 = 8, arg2 = 22.1.7
⇒ arg1 = 7, arg2 = 22.1.6
⇒ arg1 = 6, arg2 = 22.1.5
⇒ arg1 = 4, arg2 = 22.1.3
⇒ arg1 = 3, arg2 = 22.1.2
⇒ arg1 = 2, arg2 = 22.1.1
⇒ #<PGresult PGRES_COMMAND_OK - COMMIT>