[postmodern-devel] Using postgresql's default option for column values

[Drew Crampsie]

On 10/02/2008, Peter Eddy <peter.eddy at gmail.com> wrote:
> Hi, new postmodern user here.
>
> I'd like to be able to use postgresql's 'default' column value feature
> instead of an :initform in deftable. Is that possible? I haven't had
> any luck getting it to work.

I use a trick to do this in some older software. It would be quite
trivial to make in work in postmodern. Basically, the idea is to get
the actual default value expression from the   system tables and run a
SELECT on it. This code is postrgresql, but it should be trivial to
modify for postmodern.

(defun set-default-values (model)
  (labels ((sym->sql (sym)
             (string-downcase (substitute #\_ #\- (string sym))))
           (get-def (slot)
             (caar (query
                    (format nil "
SELECT DISTINCT adsrc
FROM pg_attrdef join pg_attribute on attnum = adnum
WHERE adrelid = (select oid from pg_class where relname = '~A')
AND attname = '~A'"
                            (sym->sql (class-name (class-of model)))
(sym->sql slot)))))

           (get-default-value (slot)
             (let ((def (get-def slot)))
               (if def
                   (caar (query (format nil "SELECT ~A" def)))))))
    (dolist (slot (list-slots model))
      (when (and (primary-key-p model slot)
                 (or (not (slot-boundp model slot))
                     (equal (slot-value model slot) nil)))
        (setf (slot-value model slot) (get-default-value slot))
        (when (and (primary-key-p model slot)
                         (not (slot-value model slot))
          (error "No default value for primary key : ~A" slot))))))
  model)

 have used this without problems for over 4 years.

Cheers,

drewc




>
> I have a couple of reasons for wanting to do this, first I have
> several tables that get their primary keys from a single postgresql
> sequence, e.g.:
>
> create table fooA ( id int8 default nextval( 'id_source' ) primary key, ... )
>
> create table fooB ( id int8 default nextval( 'id_source' ) primary key, ... )
>
>
> Additionally, other table columns use the 'default' option to create
> creation time stamps and so forth.
>
> I can't remove these options from the table definitions because that
> would break the other applications that use the database.
>
> So, what I'm really after is the ability to define fields with
> deftable so that those fields are not initialized by (make-instance)
> but read into the dao after creation, similarly to how auto-id appears
> to work.
>
> I looked through the archives but didn't see anything that looked
> related to this.
>
> thanks,
> Peter
> _______________________________________________
> postmodern-devel mailing list
> postmodern-devel at common-lisp.net
> http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
>