Hi, I'm the maintainer and a primary author of a postgresql client library for Haskell, called postgresql-simple, and I recently investigated improving support for VALUES expressions in this library. As a result, I'd like to suggest two changes to postgresql:
1. Allow type specifications inside AS clauses, for example
(VALUES (1,'hello'),(2,'world')) AS update(x int, y text)
2. Have an explicit syntax for representing VALUES expressions which contain no rows, such as VALUES (). (although the precise syntax isn't important to me.)
My claim is that these changes would make it simpler for client libraries to properly support parameterized VALUES expressions. If you care, I've included a postscript including a brief background, and a link to my analysis and motivations.
Best,
Leon
P.S.
Not entirely unlike many other client libraries, such as psycopg2, postgresql generates queries
by expanding values of particular Haskell types into fragments of SQL syntax. So for example, you can currently write:
executeMany conn [sql|
UPDATE tbl SET tbl.y = upd.y
FROM (VALUES (?,?)) AS upd(x,y)
WHERE tbl.x = upd.x
|] [(1,"hello"),(2,"world")]
Which will issue the query:
UPDATE tbl SET tbl.y = upd.y
FROM (VALUES (1,'hello'),(2,'world')) AS upd(x,y)
WHERE tbl.x = upd.x
The issue however is that postgresql-simple cannot currently parameterize more complex queries that have multiple VALUES expressions, or a VALUES expression alongside other parameters, as might occur with a Writable CTE or complex query.
Also, when presented with a empty list of arguments, executeMany does not issue a query at all and simply returns 0, which is (usually?) the right thing to do given it's intended use cases, but is not the right thing to do in more general settings.
So, what I'd like to do is to be able to write something like:
execute conn [sql|
UPDATE tbl SET tbl.y = upd.y
FROM ? AS upd(x,y)
WHERE tbl.x = upd.x
AND tbl.z = ?
|] ( Values [(1,"hello"),(2,"world")], False )
and issue a similar query. However, the problems with this approach is specifying the postgresql types and handling the zero-row case properly.