Hi Tom, > On 19/08/2019, at 3:00 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Peter Eisentraut <peter.eisentr...@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
insert-set-v3.patch
Description: Binary data
