On Wednesday, August 5, 2015 at 11:24:22 PM UTC-7, Steve H wrote:
>
> Hi all,
>
> I'm trying to write a prepared statement to insert multiple rows at once
> and having some trouble.
>
> Given the following code:
>
> query = <<-SQL
> WITH new_widgets (widget_id) AS (VALUES :widget_ids)
> INSERT INTO widgets (widget_group, widget_id)
> SELECT :widget_group, new_widgets.widget_id
> FROM new_widgets
> LEFT OUTER JOIN widgets ON widgets.widget_group = :widget_group AND
> new_widgets.widget_id = widgets.widget_id
> WHERE widgets.id IS NULL
> SQL
>
> insert_ds = DB[query, widget_group: "animal", widget_ids: widget_ids]
> insert_ds.insert
>
>
> Trying with a 2-dimensional array:
>
> widget_ids = [["cat"], ["dog"], ["fish"]]
> # => WITH new_widgets (widget_id) AS (VALUES (('cat'), ('dog'), ('fish')))
>
>
> Trying with a 1-dimensional array:
>
> widget_ids = ["cat", "dog", "fish"]
> # => WITH new_widgets (widget_id) AS (VALUES ('cat', 'dog', 'fish'))
>
>
> What I actually want:
>
> # => WITH new_widgets (widget_id) AS (VALUES ('cat'), ('dog'), ('fish'))
>
>
> Is there a way to bind the correct values in here?
>
Yes:
query = <<-SQL
WITH new_widgets (widget_id) AS :widget_ids
INSERT INTO widgets (widget_group, widget_id)
SELECT :widget_group, new_widgets.widget_id
FROM new_widgets
LEFT OUTER JOIN widgets ON widgets.widget_group = :widget_group AND
new_widgets.widget_id = widgets.widget_id
WHERE widgets.id IS NULL
SQL
widget_ids = ['cat', 'dog', 'fish']
insert_ds = DB[query, widget_group: "animal", widget_ids:
DB.values(widget_ids.map{|w| [w]})]
insert_ds.insert_sql
Thanks,
Jeremy
--
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/d/optout.
