Re: Follow-up on INSERT INTO ... SET ...

Sven Berkvens-Matthijsse <sven(at)postgresql(dot)berkvens(dot)net> writes:
> In 2016, a thread was started about implementing INSERT INTO ... SET ...
> that included a patch and was basically ready for inclusion in
> PostgreSQL. However, it seems as though it stagnated for some reason.
> Does anybody remember this and is there perhaps someone who knows what
> the current status is? If nobody is working on this any longer, I'd be
> willing to try to revive the patch for the current code base.
> The thread that I'm talking about can be found at:
> https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61(at)joh(dot)to

Looking at the thread, it seems like Marko lost interest for some
reason, and never submitted a revised patch.

I'm not really sure whether we'd want to support a nonstandard
syntax for this. I can see that it'd have some usefulness for wide
tables, but is that enough of an argument to risk incompatibility
with future SQL-spec extensions?

Looking at the patch itself, I'd raise two major complaints:

* It looks like the only supported syntax is "INSERT ... SET
set_clause_list", which I guess is meant to be functionally
the same as INSERT ... VALUES with a single values list.
That's not terribly compelling. I'd expect to be able to
use this syntax for multiple inserted rows. Maybe allow
something like INSERT ... SET ... FROM ..., where the set-list
entries can use variables emitted by the FROM clause?

* If I'm reading it right, it blows off multiple-assignment
syntax -- that is, "SET (a,b,c) = row-valued-expr" -- with
the comment

+ * This is different from set_clause_list used in UPDATE because the SelectStmt
+ * syntax already does everything you might want to do in an in INSERT.

I'm unimpressed with that reasoning, because the SQL-standard
syntax already does everything you might want to do with this.

Since this patch was originally submitted, we sweated pretty
hard to upgrade our support of UPDATE's multiple-assignment
syntax so that it handles all interesting cases; so I'd want
INSERT ... SET to be fully on par with UPDATE ... SET if we
do it at all.

regards, tom lane