`formatMany` doesn't seem to handle type signatures for ? parameters

tchoutri commented 2 years ago

Reading the documentation for formatMany:

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.

which means that this kind of query is not permitted:

Exception: FormatError {fmtMessage = "syntax error in multi-row template",
fmtQuery = "INSERT INTO \"blogposts\" (\"blogpost_id\", \"author_id\", \"uuid_list\",
\"title\", \"content\", \"created_at\") VALUES (?, ?, ?::uuid[], ?, ?, ?)",
fmtParams = []}

However, this is perfectly valid SQL, and without the type signature for the uuid_list field, PostgreSQL is going to trip and fail to infer the proper type (this is also the case for lists of enums):

INSERT INTO "blogposts" ("blogpost_id", "author_id", "uuid_list", "title", "content",
 "created_at") VALUES ('78b01048-ba6b-4d34-ea90-77348e632ce9', '2ab72008-aea5-4dd8-14
6f-b82d9e35910d', ARRAY['371a73f0-8cbd-e9aa-49c0-928ea3e86efa','22af1de8-569f-993b-88
af-bcda68953c8a','8e3b78d4-8de7-4f7f-57ca-00e2e4c2db33','e711dfd2-d33e-6ffa-fbbe-b2f1
7f272edf'], 'Testing unescaped single quotes '' :)', '򯮷󲚭􄛪򇐓溴𲺥񇨳򮩕󂷌񎝙򡵔', '2017-12-26 23:
11:43Z')

FAIL
      Exception: SqlError {sqlState = "42804", sqlExecStatus = FatalError, sqlErrorMsg =
 "column \"uuid_list\" is of type uuid[] but expression is of type text[]", sqlErr
orDetail = "", sqlErrorHint = "You will need to rewrite or cast the expression."}

@phadej Do you see a way out of this? Would you endorse a change to the parser to allow for this?

phadej commented 2 years ago

A workaround is to make the type of uuid_list format as .... :: uuid[], i.e. include the type-annotation in ToField of that type. (That will save you from writing the type annotation elsewhere as well, but you may need to pay a price of newtype).

Changing parser won't be enough, as formatMany duplicates the VALUES clause, we would need to preserve the extras, and that is a big change. As there is relatively cheap workaround, I'd rather not do it.

tchoutri commented 2 years ago

@phadej okay I've implemented this and I consider my problem fixed. Thanks a lot!

haskellari / postgresql-simple

`formatMany` doesn't seem to handle type signatures for ? parameters #86