On 1/14/14, 6:15 PM, Tom Lane wrote:
Marko Tiikkaja <ma...@joh.to> writes:
How about:
(a) = SELECT 1;
(a, b) = SELECT 1, 2;
(a, b) = INSERT INTO foo RETURNING col1, col2;
Same semantics: TOO_MANY_ROWS on rows > 1, sets FOUND and row_count.
AFAICT this can be parsed unambiguously, too, and we don't need to look
at the query string because this is new syntax.
The idea of inventing new syntax along this line seems like a positive
direction to pursue. Since assignment already rejects multiple rows
from the source expression, this wouldn't be weirdly inconsistent.
It might be worth thinking about the <multiple column assignment> UPDATE
syntax that's in recent versions of the SQL standard:
UPDATE targettab SET (a, b, c) = row-valued-expression [ , ... ] [
WHERE ... ]
We don't actually implement this in PG yet, except for trivial cases, but
it will certainly happen eventually. I think your sketch above deviates
unnecessarily from what the standard says for UPDATE. In particular
I think it'd be better to write things like
(a, b) = ROW(1, 2);
(a, b, c) = (SELECT x, y, z FROM foo WHERE id = 42);
which would exactly match what you'd write in a multiple-assignment
UPDATE, and it has the same rejects-multiple-rows semantics too.
Hmm. That's a fair point I did not consider.
Also note that the trivial cases we do already implement in UPDATE
look like
UPDATE targettab SET (a, b, c) = (1, 2, 3) [ WHERE ... ]
that is, we allow a row constructor where the optional keyword ROW has
been omitted. I think people would expect to be able to write this in
plpgsql:
(a, b) = (1, 2);
Now, this doesn't provide any guidance for INSERT/UPDATE/DELETE RETURNING,
but frankly I don't feel any need to invent new syntax for those, since
RETURNING INTO already works the way you want.
Yeah, I don't feel strongly about having to support them with this
syntax. The inconsistency is a bit ugly, but it's not the end of the world.
I'm not too sure what it'd take to make this work. Right now,
SELECT (SELECT x, y FROM foo WHERE id = 42);
would generate "ERROR: subquery must return only one column", but
I think it's mostly a historical artifact that it does that rather than
returning a composite value (of an anonymous record type). If we were
willing to make that change then it seems like it'd be pretty
straightforward to teach plpgsql to handle
(a, b, ...) = row-valued-expression
where there wouldn't actually be any need to parse the RHS any differently
from the way plpgsql parses an assignment RHS right now. Which would be
a good thing IMO. If we don't generalize the behavior of scalar
subqueries then plpgsql would have to jump through a lot of hoops to
support the subselect case.
You can already do the equivalent of (a,b,c) = (1,2,3) with SELECT ..
INTO. Would you oppose to starting the work on this by only supporting
the subquery syntax, with the implementation being similar to how we
currently handle SELECT .. INTO? If we could also not flat out reject
including that into 9.4, I would be quite happy.
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
