Format error in an UPDATE statement

[CGenie]

Hello,

I have this query:

[sql| UPDATE x
                    SET a = ?
                    WHERE b = ? AND c = ? AND d IN (SELECT id FROM y WHERE A = ?) |]

This works perfectly fine when I substitute some values and execute as a raw SQL statement.

However when I try to Database.PostgreSQL.Simple.execute this query with some values, I'm getting this error:

*** Exception: FormatError {fmtMessage = "syntax error in multi-row template", fmtQuery = "UPDATE x SET a = ? WHERE b = ? AND c = ? AND d IN (SELECT id FROM y WHERE A = ?)", fmtParams = []}

Apparently the internal parser for SQL for postgresql-simple can't handle this query properly.

[phadej]

are you sure you are calling execute and not executeMany?

A standalone reproducer using formatQuery would be great.

[CGenie]

Yes, it's executeMany but I thought they're just like a map over execute and I wanted to simplify my example.

[phadej]

I thought they're just like a map over execute and I wanted to simplify my example.

it's not. It expands VALUS (?,?) into many tuples, so only one statement is executed.

from formatMany doc:

-- The query string must contain exactly one substitution group,
-- identified by the SQL keyword \"@VALUES@\" (case insensitive)
-- followed by an \"@(@\" character, a series of one or more \"@?@\"
-- characters separated by commas, and a \"@)@\" character. White
-- space in a substitution group is permitted.

[CGenie]

Ah OK I get it. So I should do mapM (execute c query) params in my case instead of executeMany directly?

[CGenie]

Yeah, mapM worked for me. Thanks for solving this!