Allow batch UPDATE statements with parameterised WHERE-clauses

[soenkehahn]

I'd like to use executeMany to perform UPDATE queries in batch. The queries have roughly the form:

UPDATE table SET someField = ? WHERE someOtherField = ?;

As I understand this is not possible due to the restrictions executeMany poses on query templates.

• Is there a way to work around this limitation?
• I guess this limitation is not imposed by postgresql but rather introduced on the Haskell side. Is that true?

[lpsmith]

I guess this limitation is not imposed by postgresql but rather introduced on the Haskell side. Is that true?

Depends on what kind of update you'd like to do. For example, you can do something like this:

UPDATE table SET count = count + 1 WHERE key IN (2,5,7,...)

Which you can handle just fine in postgresql-simple as well. (Check out the In type.)

On the other hand, if the update you want to perform isn't a function of other information already in the database, you can either create a temporary table and use it to perform your update, or use a literal table. Postgresql supports a special syntax for joins with updates, here's an example using a table literal:

UPDATE table SET someField = upd.y FROM (VALUES (key1, val1),(key2,val2)) as upd(x,y) WHERE table.someOtherField = upd.x;

This is a bit verbose, as you have to name the literal table (and it's columns) and then specify a join, but it should work, and you should even be able to use executeMany as-is, as long as the only thing you need to parameterize is the (VALUES ...) component.

Really, I should add a Values type constructor and associated FromField instance, not unlike the existing In type.

[lpsmith]

Oh, one caveat with the example above: if postgresql is unable to infer the types of the VALUES table, you need to specify the type on the first value (or all the values, although that would be more verbose.), which isn't very convenient to do with postgresql-simple.

See the documentation for UPDATE and VALUES for more details.

[ibotty]

regarding a potential Values constructor, what about a more generic comma-separated-values type?

given

newtype CommaSeparated a = CommaSeparated a

with appropriate ToField a => ToField (CommaSeparated [a]) instance, we can get

type Values a b = CommaSeparated [(a,b)]
type ColumnsToSet = CommaSeparated [Identifier]
type ValuesToSet = CommaSeparated [ExistentialToFieldWrapper]

where you could use the last two for

let xs :: undefined [(Identifier, ExistentialToFieldWrapper)]
    keys, vals = unzip xs
execute conn "UPDATE my_table SET (?) = (?);" (CommaSeparated keys, CommaSeparated vals)

[lpsmith]

The problem is that unfortunately, proper support for VALUES needs a bit more than that. See my comments on #61.

I'm highly inclined to go the data Values a = Values [ByteString] [a] route at this point in time, especially now that we have identifier escaping available. Or perhaps that should be QualifiedIdentifier instead of ByteString?

[lpsmith]

As for your proposal on its own merit, it sounds possibly useful. I'd have to think about it for a while to decide whether it'd be useful enough for inclusion.

Ultimately though, at what point does it make sense to stop messing around with tricks like these, and create a proper DSL for generating SQL? I really do need to dig into esqueleto, which seems like a good starting point. (Although I'd like to understand why persistent is a dependency, and what limitations (if any) that puts on my schema design. And as esqueleto is mostly aimed at MySQL, I'm sure there's a lot of postgresql-specific stuff that isn't particularly supported.)

[ibotty]

I have not looked into the inner workings of esqueletto, but last i checked they did not support calling functions with named parameters and no common table expressions. It's pretty hard to support when trying to be db agnostic, so i was not surprised.

I applaud the intent to do that though :). I'd be glad to help.

[lpsmith]

Well, if you are interested in creating a postgresql-specific variant of esqueleto, don't let me stop you, as it's a very low-priority project for me at the moment, and honestly I don't think my interest level is all that high.

Eventually, we could discuss the possibility of including it in postgresql-simple, or perhaps you'd prefer to release it as a separate package. You may also be interested in hssqlppp, which is a parser and pretty printer for a large subset of postgresql's syntax.

[lpsmith]

Ok, this particular use case is already covered by executeMany, and I just pushed a fix for more general use cases as talked about in #61, so I'm going to close this issue.