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

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: 
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:

Reply via email to