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.




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.

Reply via email to