r10376: 03 Apr 2005 Kevin Rosenberg <kevin@rosenberg.net>

[clsql.git] / sql / generic-postgresql.lisp

diff --git a/sql/generic-postgresql.lisp b/sql/generic-postgresql.lisp
index b85da75fb073ea28016095ff97cd1bf1d2a47c97..ac01e7ba073ecdfe4d2dcb91039c12796f8d3706 100644 (file)
--- a/sql/generic-postgresql.lisp
+++ b/sql/generic-postgresql.lisp
@@ -32,7 +32,17 @@
   (declare (ignore database))
   (if args
       (format nil "CHAR(~A)" (car args))
-      "VARCHAR"))
+    "VARCHAR"))
+
+(defmethod database-get-type-specifier ((type (eql 'tinyint)) args database
+                                       (db-type (eql :postgresql)))
+  (declare (ignore args database))
+  "INT2")
+
+(defmethod database-get-type-specifier ((type (eql 'smallint)) args database
+                                       (db-type (eql :postgresql)))
+  (declare (ignore args database))
+  "INT2")
 
 (defmethod database-get-type-specifier ((type (eql 'wall-time)) args database
                                        (db-type (eql :postgresql)))
@@ -105,7 +115,7 @@
          (result
          (mapcar #'car
                  (database-query
-                  (format nil "SELECT attname FROM pg_class,pg_attribute WHERE pg_class.oid=attrelid AND relname='~A'~A"
+                  (format nil "SELECT attname FROM pg_class,pg_attribute WHERE pg_class.oid=attrelid AND attisdropped = FALSE AND relname='~A'~A"
                            (string-downcase table)
                            owner-clause)
                    database nil nil))))
@@ -178,7 +188,7 @@
    (parse-integer
     (caar
      (database-query
-      (concatenate 'string "SELECT LAST_VALUE ('" sequence-name "')")
+      (concatenate 'string "SELECT LAST_VALUE FROM " sequence-name)
       database nil nil)))))
 
 (defun postgresql-database-list (connection-spec type)
@@ -201,8 +211,12 @@
 (defmethod database-list (connection-spec (type (eql :postgresql-socket)))
   (postgresql-database-list connection-spec type))
 
-
+#+nil 
 (defmethod database-describe-table ((database generic-postgresql-database) table)
+  ;; MTP: LIST-ATTRIBUTE-TYPES currently executes separate queries for
+  ;; each attribute. It would be more efficient to have a single SQL
+  ;; query return the type data for all attributes. This code is
+  ;; retained as an example of how to do this for PostgreSQL.
   (database-query 
    (format nil "select a.attname, t.typname
                                from pg_class c, pg_attribute a, pg_type t
@@ -228,12 +242,16 @@
    (result-types :initarg :result-types :reader result-types)))
 
 (defun clsql-type->postgresql-type (type)
-  (case type
-    (:string "VARCHAR")
-    ((:int :integer) "INT4")
-    (:short "INT2")
-    ((:number :numeric :float) "NUMERIC")
-    (:bigint "INT8")))
+  (cond
+    ((in type :int :integer) "INT4")
+    ((in type :short) "INT2")
+    ((in type :bigint) "INT8")
+    ((in type :float :double :number) "NUMERIC")
+    ((and (consp type) (in (car type) :char :varchar)) "VARCHAR")
+    (t
+     (error 'sql-user-error 
+           :message 
+           (format nil "Unknown clsql type ~A." type)))))
 
 (defun prepared-sql-to-postgresql-sql (sql)
   ;; FIXME: Convert #\? to "$n". Don't convert within strings