This doesn't work:
,---- | (postmodern:query | (:select (:- :localtimestamp | (simple-date:encode-interval :hour 2 :minute 10)))) | | Database error 22007: invalid input syntax for type timestamp: "2 hours 10 minutes " | Query: (SELECT (localtimestamp - '2 hours 10 minutes ')) `----
This does work:
,---- | (postmodern:query | (:select (:- :localtimestamp (:raw "interval '2 hours 10 minutes'")))) `----
It was tested with PostgreSQL 8.0.13 and 8.2.4.
SQL-IZE methods for SIMPLE-DATE objects should return escaped strings like "interval '2 hours 10 minutes'", not just "2 hours 10 minutes", and second value of SQL-IZE must be NIL:
b ,---- | (:method ((arg simple-date:date)) | (multiple-value-bind (year month day) (simple-date:decode-date arg) | (format nil "date ~A" | (sql-escape-string (format nil "~4,'0d-~2,'0d-~2,'0d" year month day))))) `----
(Untested!)
Perhaps, sql-escape-string can be omitted: the output has no dangerous characters anyway.
,---- | (:method ((arg simple-date:date)) | (multiple-value-bind (year month day) (simple-date:decode-date arg) | (format nil "date '~4,'0d-~2,'0d-~2,'0d'" year month day))) `----
So far I always used these types like (:select (:type my-date-value date)), but explicitly putting 'date' in front of the value string seems to work (I had never seen it before). Only, there is a reason that sql-ize allows one to take the un-escaped version of a value -- when passing arguments to queries with $1-like 'holes' in them, you need to pass them as un-escaped strings, and something like "date '2000-01-01'" produces an error. I've modified sql-escape-string to take an optional second parameter, a prefix, and when sql-ize returns a string as its second value (rather than T or NIL), this now means that the value should be escaped, and that string can be prefixed as type info. This seems to work fine (and has been applied to the repository).
Cheers, Marijn
postmodern-devel@common-lisp.net
-
Ivan Boldyrev -
Marijn Haverbeke