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
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
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
postmodern-devel@common-lisp.net
-
Harry Bock -
Marijn Haverbeke