Re: [PATCH] Implement INSERT SET syntax

> On 26/03/2020, at 3:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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

Attached is v6 of the patch.

As per the suggestion the SET clause list is checked for any
MultiAssigmentRef nodes and to report an error if any are found.

For example, the rule definition that previously caused a parser crash
would now produce the following error:

vagrant=> create rule r1 as on insert to foo do instead
vagrant-> insert into bar set (f1,f2,f3) = (select f1,f2,f3 from foo);
ERROR: INSERT SET syntax does not support multi-assignment of columns.
LINE 2: insert into bar set (f1,f2,f3) = (select f1,f2,f3 from foo);
^
HINT: Specify the column assignments separately.

Requiring a FROM clause was a way to differentiate between an INSERT
with VALUES() which does allow DEFAULT and an INSERT with SELECT which
does not.

The idea was that it would help the user understand that they were writing
a different type of query and that DEFAULT would not be allowed in that
context.

To show what it would look like without that requirement I have removed
it from the v6 patch. In the first example works but the second one will
generate an error.

INSERT INTO t SET c1 = 1 WHERE true;
INSERT INTO t SET c1 = DEFAULT WHERE true;