Previous: , Up: PostgreSQL Support   [Contents][Index]


61.3 SXEmacs PostgreSQL libpq Examples

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>

Previous: , Up: PostgreSQL Support   [Contents][Index]