Proposal: INSERT ... BY NAME

Hi,

I wanted to take a stab at implementing INSERT ... BY NAME, which has been
accepted into the SQL draft standard. I could not find an existing patch
for
PostgreSQL; apologies if this has already been proposed. I learned about
the
standardization update from Peter's recent blog post.

Attached is a draft patch for INSERT ... BY NAME. It matches the result
columns of a source query to target columns by name instead of by position,
which is useful when the source and target columns are written in different
orders. Similar syntax exists in DuckDB [1] and Oracle [2]:

INSERT INTO t1 (c1, c2)
BY NAME
SELECT c1 * 10 AS c2, c2 + 5 AS c1 FROM t2;

Here c1 gets c2 + 5 and c2 gets c1 * 10. BY POSITION remains the default
and can also be written explicitly.

Behavior that the patch exhibits:

- Each source column must match exactly one target column. Unknown source
names and duplicate matches are errors.

- Target columns not named by the query get their default values. An
explicit target column list narrows the candidate target columns;
without
one, all table columns are candidates.

- BY NAME requires a query source and is rejected for VALUES and DEFAULT
VALUES. BY POSITION is accepted with DEFAULT VALUES as a no-op.

- BY NAME is rejected for target lists that assign to subfields or array
elements, since matching on the base column name would be ambiguous.

The implementation resolves BY NAME during parse analysis by reordering the
target column/attribute-number lists to match the source column order.
After
that, the query tree is an ordinary positional INSERT, so no rule/view
deparsing changes were needed in the patch.

For comparison, DuckDB [1] and Oracle 23ai [2] implement the same core
behavior: source names are matched against target columns, unmatched target
columns default, and unknown source names are errors.

It's still a WIP patch, I'm trying out different queries, but it would be
great if I
could get some reviews on the direction.

Thoughts?

[1]
https://duckdb.org/docs/current/sql/statements/insert#insert-into--by-name
[2]
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/INSERT.html

Regards,
Ayush