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.
https://github.com/lpsmith/postgresql-simple/issues/61
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.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM89609a0f349114af996fe2a2af897313f299c0f58f22a2b43ea353cf2e04170de159bada3e0dcd9c200aa44aa5629599@asav-2.01.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,
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers