(blog ‘lucindo)

Brincando um pouco com Postmodern:

(eval-when (:compile-toplevel :load-toplevel :execute)
  (require :postmodern))

(defpackage :pg-test
  (:use :cl :postmodern))

(in-package :pg-test)

(defparameter *db-name* “lucindo”)
(defparameter *db-user* “lucindo”)
(defparameter *db-pass* “”)
(defparameter *db-host* “localhost”)

;; global connection
(eval-when (:execute)
  (connect-toplevel *db-name* *db-user* *db-pass* *db-host*))

;; defines a table (and a CLOS class)
(deftable user ()
  ((id :type integer
       :initarg :id
       :accessor id)
   (name :type (or string db-null)
         :initarg :name
         :initform :null
         :accessor name)
   (points :type (numeric 10 3)
           :initarg :points
           :accessor points))
  (:indices id points)
  (:class-name user))

;; creates the table if not exists
(defun setup ()
  (when (not (table-exists-p :user))
    (create-table ‘user)))

(defun create-update-user (id name &optional (points 0.0))
  (save-dao (make-instance ‘user :id id :name name :points points)))

(defun get-user-by-id (id)
  (get-dao ‘user id))

Se você precisar escrever SQL pode usar o mapeamento para expressões Lisp, como:

(:select ‘relname
         :from ‘pg-catalog.pg-class
         :inner-join ‘pg-catalog.pg-namespace
         :on (:= ‘relnamespace ‘pg-namespace.oid)
         :where (:and (:= ‘relkind “r”)
                      (:not-in ‘nspname (:set “pg_catalog” “pg_toast”))
                      (:pg-catalog.pg-table-is-visible ‘pg-class.oid)))

A lib é bem completa, com connection pools, prepared statements, … e 100% Common Lisp (você não precisa ter nada do PostgreSQL instalado na máquina cliente).

Update: deftable não tem suporte a foreign keys