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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
