Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes:
> On 2020-03-24 18:57, Tom Lane wrote:
>> No doubt that's all fixable, but the realization that some cases of
>> this syntax are*not* just syntactic sugar for standards-compliant
>> syntax is giving me pause. Do we really want to get out front of
>> the SQL committee on extending INSERT in an incompatible way?
> What is the additional functionality that we are considering adding here?
> The thread started out proposing a more convenient syntax, but it seems
> to go deeper now and perhaps not everyone is following.
AIUI, the proposal is to allow INSERT commands to be written
using an UPDATE-like syntax, for example
INSERT INTO table SET col1 = value1, col2 = value2, ... [ FROM ... ]
where everything after FROM is the same as it is in SELECT. My initial
belief was that this was strictly equivalent to what you could do with
a target-column-names list in standard INSERT, viz
INSERT INTO table (col1, col2, ...) VALUES (value1, value2, ...);
or
INSERT INTO table (col1, col2, ...) SELECT value1, value2, ... FROM ...
but it's arguably more legible/convenient because the column names
are written next to their values.
However, that rewriting falls down for certain multiassignment cases
where you have a row source that can't be decomposed, such as my
example
INSERT INTO table SET (col1, col2) = (SELECT value1, value2 FROM ...),
... [ FROM ... ]
So, just as we found for UPDATE, multiassignment syntax is strictly
stronger than plain column-by-column assignment.
There are some secondary issues about which variants of this syntax
will allow a column value to be written as DEFAULT, and perhaps
about whether set-returning functions work. But the major point
right now is about whether its's possible to rewrite to standard
syntax.
regards, tom lane
