Re: Simplified VALUES parameters

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.