Hi Tom,
> On 19/08/2019, at 3:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> What I don't like about the syntax is that it kind of breaks the
>> notional processing model of INSERT in a fundamental way.
>
> Agreed. I really don't like that this only works for a VALUES-like case
> (and only the one-row form at that). It's hard to see it as anything
> but a wart pasted onto the syntax.
>
>> Let's think about how we can achieve this using existing concepts in
>> SQL. What we really need here at a fundamental level is an option to
>> match $target to $table_source by column *name* rather than column
>> *position*. There is existing syntax in SQL for that, namely
>> a UNION b
>> vs
>> a UNION CORRESPONDING b
>
> A potential issue here --- and something that applies to Vik's question
> as well, now that I think about it --- is that CORRESPONDING breaks down
> in the face of ALTER TABLE RENAME COLUMN. Something that had been a
> legal query before the rename might be invalid, or mean something quite
> different, afterwards. This is really nasty for stored views/rules,
> because we have neither a mechanism for forbidding input-table renames
> nor a mechanism for revalidating views/rules afterwards. Maybe we could
> make it go by resolving CORRESPONDING in the rewriter or planner, rather
> than in parse analysis; but that seems quite unpleasant as well.
> Changing our conclusions about the data types coming out of a UNION
> really shouldn't happen later than parse analysis.
>
> The SET-style syntax doesn't have that problem, since it's explicit
> about which values go into which columns.
>
> Perhaps the way to resolve Peter's objection is to make the syntax
> more fully like UPDATE:
>
> INSERT INTO target SET c1 = x, c2 = y+z, ... FROM tables-providing-x-y-z
>
> (with the patch as-submitted corresponding to the case with an empty
> FROM clause, hence no variables in the expressions-to-be-assigned).
>
> Of course, this is not functionally distinct from
>
> INSERT INTO target(c1,c2,...) SELECT x, y+z, ... FROM tables-providing-x-y-z
>
> and it's fair to question whether it's worth supporting a nonstandard
> syntax just to allow the target column names to be written closer to
> the expressions-to-be-assigned.
Thanks for the feedback. Attached is version 3 of the patch that makes
the syntax work more like an UPDATE statement when a FROM clause is used.
So, an updated summary of the new syntax is:
1. Equivalent to VALUES(...):
INSERT INTO t SET c1 = x, c2 = y, c3 = z;
2. Equivalent to INSERT INTO ... SELECT ...:
INSERT INTO t SET c1 = sum(x.c1) FROM x WHERE x.c1 < y AND x.c2 != z
GROUP BY x.c3 ORDER BY x.c4 ASC LIMIT a OFFSET b;
Gareth
> regards, tom lane
