[postmodern-devel] Multirow inserts and transactions in postmodern
Hi, I'm wonder what the proper :insert-into syntax would be to insert multiple rows simultaneously, like so: "INSERT INTO my_table (col1, col2, col3) VALUES ('1', '2', '3'), ('4', '5', '6'), ...;" As this tends to be much faster when inserting large data sets. Also, the transaction functionality of postmodern doesn't seem to be well-documented. Is the only method of working with them to wrap calls in the with-transaction macro? Thanks, Harry -- Harry Bock Software Developer, Package Maintainer OSHEAN, Inc. Email: harry@oshean.org PGP Key ID: 546CC353
Hello Harry,
I'm wonder what the proper :insert-into syntax would be to insert multiple rows simultaneously, like so:
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?
Also, the transaction functionality of postmodern doesn't seem to be well-documented. Is the only method of working with them to wrap calls in the with-transaction macro?
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. Cheers, Marijn
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 postmodern-devel@common-lisp.net http://common-lisp.net/cgi-bin/mailman/listinfo/postmodern-devel
-- Harry Bock Software Developer, Package Maintainer OSHEAN, Inc. Email: harry@oshean.org PGP Key ID: 546CC353
Hi Harry, I've pushed a version of the operator where the list given as :values is evaluated and checked at run-time -- I suppose you'll rarely know exactly how many rows you are inserting at compile-time, so doing the length checks and expansion then seems unpractical. Also (for consistency with out operators) removed the parentheses around the :columns list. Your example would now look like this:
(:insert-rows-into 'pg-table :columns 'a 'b 'c :values '((1 2 3) (4 5 6)))
See if this works for you. Best, Marijn
participants (2)
-
Harry Bock -
Marijn Haverbeke