How to use the cl-pg package?

Question

I have installed clisp and cl-pg on my computer (Raspberry PI 4B) to work with PostGres from a Lisp script.

# apt install clisp
# apt install cl-pg

The few tricks I have tried starting with the example code given here lead to nothing.

   (with-pg-connection (conn "testdb" "login" :host "dbhost" :password "secret")
      (with-pg-transaction conn
         (when (member "test_date" (pg-tables conn) :test #'string=)
            (pg-exec "DROP TABLE test_date"))
         (pg-exec conn "CREATE TABLE test_date(a timestamp, b abstime, c time, d date)")
         (pg-exec conn "INSERT INTO test_date VALUES "
                       "(current_timestamp, 'now', 'now', 'now')")))

Is there some sample code available somewhere showing how to use cl-pg, including any necessary (load ...) or whatever is needed to make use of this package ?

Answer 1

It is no wonder that it doesn't work, because you are at the wrong package. https://pg.common-lisp.dev/ is pg-dot-list. not cl-pg.

It is however to be recommended to use postmodern for this purpose (more modern library):

(ql:quickload "postmodern")

;; https://github.com/marijnh/Postmodern

;; connect by:
(postmodern:connect-toplevel "testdb" "foucault" "surveiller" "localhost" :port 5434)

;; query by:
(postmodern:query "SQL query string")

;; at the end disconnect
(postmodern:disconnect-toplevel)

The package has also a with-connection macro:

(postmodern:with-connection '("testdb" "foucault" "surveiller" "localhost")
 (postmodern:query "SQL query string")
 ...)