Re: [PATCH] Implement INSERT SET syntax

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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