Em 02-10-2013 11:15, Jeremy Evans escreveu:
On Tuesday, October 1, 2013 2:37:49 PM UTC-7, Rodrigo Rosenfeld Rosas
wrote:
Are there any built-in API to allow batch updates with support for
PostgreSQL?
I'm looking for something like Dataset#import.
Here's how the batch update sql could look like:
UPDATE table_name SET col1 = v.c1, col2 = v.c2
FROM (VALUES (10, 'a', 200), (20, 'c', 300)) AS v (id, c1, c2)
WHERE table_name.id <http://table_name.id> = v.id <http://v.id>;
Sequel doesn't have direct support for PostgreSQL's VALUES clause, or
aliasing with renamed columns, so the best you could do currently is
something like this:
rows = [[10, 'a', 200], [20, 'c', 300]]
DB[:table_name, Sequel.lit('(VALUES ?) AS v(id, c1, c2))',
Sequel.lit(rows.map{|r| DB.literal(r)}.join(',
')))].where(:table_name__id=>:v__id).update(:col1=>:v__c1, :col2=>:v__c2)
Great! Thank you so much!
It's certainly possible to extend the DSL to allow something like this:
DB[:table_name, Sequel.pg_values(rows).as(:v, :cols=>[:id, :c1,
:c2])].where(:table_name__id=>:v__id).update(:col1=>:v__c1, :col2=>:v__c2)
Humm... I didn't know about this DB[a, b] trick yet :) And I wouldn't
even suspect it could be used for updates as well :)
I believe that for completion sake it would be interesting to have an
alternative for the Dataset#import method. Although I was thinking on
something more higher-level and less brain-confusing :P But then, after
trying to come up with an easier to digest API I figured out it would
probably be as (or more) complex as your example above, so maybe there
isn't really an obvious way of expressing our intention with batch
updates that would take care of all cases :)
Having said that, I'd be happy with the proposed DSL but I understand if
you prefer to leave this out since you showed up already that it's not
too much complicated to do it the "hard" way either...
As always, you've been of great help!
Thank you!
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
