Dear list,
first of all, many thanks to Marijn and all the contributors for the excellent piece of code that is Postmodern.
In my project I'm using it to connect to a database that heavily uses some of the advanced PostgreSQL functionality, including PL/pgSQL functions taking arrays of records as arguments. I found S-SQL to be a little lacking in expressiveness for that, so I've quickly hacked some methods that let me pass Lisp vectors to SQL and QUERY (they will be rendered into PostgreSQL array literals):
;;; code starts here
(in-package :cl-postgres)
(defmethod to-sql-string ((arg vector)) (cond ((typep arg '(vector (unsigned-byte 8))) (values (escape-bytes arg) t)) (t (format nil "ARRAY[~{~A~^, ~}]" (mapcar #'s-sql::sql-ize (coerce arg 'list))))))
(defmethod to-sql-string ((arg list)) (format nil "(~{~A~^, ~})" (mapcar #'s-sql::sql-ize arg)))
(in-package :s-sql)
(def-sql-op :coerce (form type) `(,@(sql-expand form) "::" ,type))
;;; code ends here
I'm sending it along in case it is useful to anyone else. Or perhaps it might make its way to the main repo? For the latter, presumably a full support would be needed, but I didn't find time to dive into Postmodern's way of passing back PostgreSQL-returned data into Lisp values of proper types, as I currently don't need that.
Best regards, Daniel Janus
In my project I'm using it to connect to a database that heavily uses some of the advanced PostgreSQL functionality, including PL/pgSQL functions taking arrays of records as arguments. I found S-SQL to be a little lacking in expressiveness for that, so I've quickly hacked some methods that let me pass Lisp vectors to SQL and QUERY (they will be rendered into PostgreSQL array literals):
on a somewhat related note, i'm working on extending/reworking the binding mechanism so that users can add methods for lisp types that serialize lisp values directly into the socket stream.
for that i've added a serialize-for-postgres generic (that by default calls to-sql-string), but i need to spend more time to think through how this could be integrated better (e.g. i'd like to support serializers without an intermediate byte vector buffer). but i'm not happy with the current code yet... although inserting local-time:timestamp's without turning them into strings works already.
Hi Daniel,
Thanks for the code. However, :coerce is already there, named :type, and the way to-sql-string is currently used, it should A) not use S-SQL functions, since cl-postgres does not depend on S-SQL, and B) serialise to a format that can also be used to pass parameters to prepared queries. I'm not that knowledgeable about Postgres' textual formats, but it appears that a value like ARRAY[1, 2, 3] can not be written as a parameter:
Database error 22P02: array value must start with "{" or dimension information
If you feel like getting to the bottom of this, feel free to submit an alternative patch. If not, array serialisation will have to wait a bit more.
Best, Marijn
On Sat, Mar 6, 2010 at 9:40 AM, Marijn Haverbeke marijnh@gmail.com wrote:
Hi Daniel,
Thanks for the code. However, :coerce is already there, named :type, and the way to-sql-string is currently used, it should A) not use S-SQL functions, since cl-postgres does not depend on S-SQL, and B) serialise to a format that can also be used to pass parameters to prepared queries. I'm not that knowledgeable about Postgres' textual formats, but it appears that a value like ARRAY[1, 2, 3] can not be written as a parameter:
Database error 22P02: array value must start with "{" or dimension information
Pardon a vague response, but in my crack t postgres recently I do remember a minor aggravation with the two ways of saying "array". Probably just need to sort out which syntax to use when.
kt
Hi Marijn,
Thanks for the code. However, :coerce is already there, named :type,
Didn't notice it -- thanks!
and the way to-sql-string is currently used, it should A) not use S-SQL functions, since cl-postgres does not depend on S-SQL,
Then S-SQL could contain a method TO-SQL-STRING specializing on vectors and lists, much the same way as it now contains a specialization on symbols (see s-sql.lisp, lines 263-264).
, and B) serialise to a format that can also be used to pass parameters to prepared queries. I'm not that knowledgeable about Postgres' textual formats, but it appears that a value like ARRAY[1, 2, 3] can not be written as a parameter:
Database error 22P02: array value must start with "{" or dimensioninformation
Could you elaborate on how you are getting this error? I don't seem to have problems when passing arrays to prepared statements in this way:
weekword=# prepare testme(int[]) as select array_lower($1, 1) as lo, array_upper($1, 1) as hi; PREPARE weekword=# execute testme(array[1,2,3]); lo | hi ----+---- 1 | 3
If you feel like getting to the bottom of this, feel free to submit an alternative patch. If not, array serialisation will have to wait a bit more.
Thanks for the comments! I'll try to do my best to work this out in a correct way.
Best, Daniel
Then S-SQL could contain a method TO-SQL-STRING specializing on vectors and lists, much the same way as it now contains a specialization on symbols (see s-sql.lisp, lines 263-264).
Actually, there should probably be a separate method on the s-sql side, which calls to-sql-string on types it's not specialised on, and has its own specialisations for symbol. I'll look into this.
Could you elaborate on how you are getting this error? I don't seem to have problems when passing arrays to prepared statements in this way:
weekword=# prepare testme(int[]) as select array_lower($1, 1) as lo, array_upper($1, 1) as hi; PREPARE weekword=# execute testme(array[1,2,3]);
That's the client-side parser helping you out. What I did was prepare the query in Postmodern, and then trying to execute it with a vector as one of the arguments. This will cause cl-postgres to call to-sql-string on the parameter values before stuffing them into the socket. Apparently, at that level, only the {} syntax is supported.
Hey list,
I finally got around to implementing a proper serialisation for arrays, both as query text and as prepared-query parameters (which requires two wildly different formats --- ARRAY[E'a', E'b'] versus {"a", "b"}). They should now Just Work. Also added a :[] sql-op, so you can do (:[] 'myarr 1) and it'll expand to (myarr)[1] -- including superfluous parentheses, since in a lot of situations they are required.
Best, Marijn
Hi Marijn,
I finally got around to implementing a proper serialisation for arrays, both as query text and as prepared-query parameters (which requires two wildly different formats --- ARRAY[E'a', E'b'] versus {"a", "b"}). They should now Just Work. Also added a :[] sql-op, so you can do (:[] 'myarr 1) and it'll expand to (myarr)[1] -- including superfluous parentheses, since in a lot of situations they are required.
This is extremely great, thanks a million!
-Daniel
postmodern-devel@common-lisp.net
-
Attila Lendvai -
Daniel Janus -
Ken Tilton -
Marijn Haverbeke