33.8. PostgreSQL Database Access

This package offers an FFI-based interface to PostgreSQL.

The package SQL (nicknamed POSTGRES and POSTGRESQL) is :CASE-SENSITIVE, so you would write (sql:PQconnectdb ...) when you need to call PQconnectdb.

When this module is present, *FEATURES* contains the symbol :POSTGRESQL.

See modules/postgresql/test.tst for sample usage.

Additionally, some higher level functionality is available (defined in modules/postgresql/sql.lisp):

(sql:pq-finish connection)
PQfinish the connection and mark it as invalid
(sql:pq-clear result)
PQclear the result and mark it as invalid
(sql:sql-error connection result format-string &REST arguments)
finalize connection and result and SIGNAL an appropriate ERROR
(sql:sql-connect &KEY host port options tty name login password)
call PQsetdbLogin and return the connection
(sql:with-sql-connection (variable &REST options &KEY log &ALLOW-OTHER-KEYS) &BODY body)
  1. bind *sql-log* to the log argument

  2. call sql:sql-connect on options and bind variable to the result

  3. execute body

  4. call sql:pq-finish on variable

(sql:sql-transaction connection command status &OPTIONAL (clear-p T))
execute the command via connection; if the status does not match status, ERROR is SIGNALed; if clear-p is non-NIL sql:pq-clear the result; otherwise return it
(sql:with-sql-transaction (result connection command status) &BODY body)
execure the body on the result of command, then sql:pq-clear the result
the default login argument to sql:sql-connect (initially set to "postgres")
the default password argument to sql:sql-connect (initially set to "postgres")
when non-NIL, should be a STREAM; sql:sql-connect and sql:sql-transaction will write to it (initially set to NIL)


Since PQfinish and PQclear cannot be called on the same pointer twice, one needs to track their validity (sql:sql-connect and sql:sql-transaction take care of that). See Example 32.11, “Controlling validity of resources”.

For PQtrace, see Section 33.1.13, “Standard file input and output”.

These notes document CLISP version 2.49Last modified: 2010-07-07