Thread: Simplified VALUES parameters
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.yFROM (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
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.FROM (VALUES (1,'hello'),(2,'world')) AS upd(x,y)WHERE tbl.x = upd.x
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.yFROM ? 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.
On 02/26/2014 10:47 AM, Leon Smith wrote: > 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: And thank you for writing that driver! I have no opinion about your request for VALUES() stuff, though. It looks fairly complex as far as grammar and libpq is concerned. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Wed, Feb 26, 2014 at 1:54 PM, Josh Berkus <josh@agliodbs.com> wrote:
And thank you for writing that driver!
You are welcome!
I have no opinion about your request for VALUES() stuff, though. It
looks fairly complex as far as grammar and libpq is concerned.
Actually, my suggestions wouldn't necessarily impact libpq at all. For better and worse, postgresql-simple does not currently support protocol-level parameters at all. While it's clear to me that I do eventually need to work on supporting protocol-level parameters and support for the binary formats, it's also become clear to me since I first wrote it that protocol-level parameters are not a total replacement either, and that postgresql-simple will still need to support direct parameter expansion in some cases. (e.g. for values parameters, for identifier parameters (which aren't yet supported due to the need to drop support for libpq 8.4), etc.)
Best,
Best,
Leon
Leon Smith wrote > 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. At a high-level I don't see how the nature of SQL would allow for either of these things to work. The only reason there even is (col type, col2 type) syntax is because record-returning functions have to have their return type defined during query construction. The result of processing a VALUES clause has to be a normal relation - the subsequent presence of AS simply provides column name aliases because in the common form each column is assigned a generic name during execution. Defining a generic empty-values expression has the same problem in that you have to define how many, with type and name, columns the VALUES expression needs to generate. From what I can see SQL is not going to readily allow for the construction of virtual tables via parameters. You need either make those tables non-virtual (even if temporary) or consolidate them into an ARRAY. In short you - the client library - probably can solve the virtual table problem but you will have to accommodate user-specified typing somehow in order to supply valid SQL to the server. The two common solutions for your specified use-case are either the user creates the needed temporary table and writes the update query to join against that OR they write the generic single-record update statement and then loop over all desired input values - ideally all done within a transaction. In your situation you should automate that by taking your desired syntax and construct a complete script that can then been sent to PostgreSQL. I don't imagine that the need for dynamically specified virtual tables is going to be strong enough for people to dedicate the amount of resources it would take to implement such a capability. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Simplified-VALUES-parameters-tp5793744p5793756.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.