Hi again Marijn,
On Wed, 2008-11-12 at 00:42 +0100, Marijn Haverbeke wrote:
> This isn't currently supported by S-SQL. Feel free to propose a
> syntax. The current one (which tries to resemble UPDATE) is rather
> unsuitable for this. Maybe we could add a new operator.
> :insert-rows-into?
Creating a new operator sounds like a good idea. I've attached a patch
that implements the operator, let me know if it's good enough to apply
to your repo :)
It works like so:
(:insert-rows-into 'pg-table
:columns ('a 'b 'c)
:values ((1 2 3) (4 5 6)))
It does length-checking against the columns and values lists, allows
omission of the columns list, and includes RETURNING support.
> Yes, if you want the unwind-protect automatic cleanup you'll have to
> use with-transaction. You're free to just do (execute "begin"),
> (execute "commit") etcetera, of course.
Great, thanks :)
Regards,
Harry
> Cheers,
> Marijn
>
> _______________________________________________
> postmodern-devel mailing list
> [email protected]
> http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
--
Harry Bock
Software Developer, Package Maintainer
OSHEAN, Inc.
Email: [EMAIL PROTECTED]
PGP Key ID: 546CC353
--- old-postmodern/s-sql/s-sql.lisp 2008-11-11 22:37:00.000000000 -0500
+++ new-postmodern/s-sql/s-sql.lisp 2008-11-11 22:37:00.000000000 -0500
@@ -573,6 +573,27 @@
,@(when returning
`(" RETURNING " ,@(sql-expand-list returning))))))
+(def-sql-op :insert-rows-into (table &rest rest)
+ (split-on-keywords ((columns ?) (values *) (returning ? *)) rest
+ (let ((column-values-lists (if (null (car columns))
+ (car values)
+ (cons (car columns) (car values)))))
+ (unless (and (car values)
+ (every (lambda (list)
+ (eql (length list) (length (car column-values-lists))))
+ column-values-lists))
+ (error ":column and :values lists must agree in length, and the :values list must not be empty.")))
+ `("INSERT INTO " ,@(sql-expand table) " "
+ ,@(when (car columns)
+ `("(" ,@(sql-expand-list (car columns)) ") "))
+ "VALUES "
+ ,@(loop :for value-list :in (car values)
+ :for first = t :then nil
+ :append `(,@(if first () '(", "))
+ "(" ,@(sql-expand-list value-list) ")"))
+ ,@(when returning
+ `(" RETURNING " ,@(sql-expand-list returning))))))
+
(def-sql-op :update (table &rest args)
(split-on-keywords ((set *) (where ?) (returning ? *)) args
(when (oddp (length set))
_______________________________________________
postmodern-devel mailing list
[email protected]
http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
