Re: Supporting multiple column assignment in UPDATE (9.5 project)

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Fri, May 2, 2014 at 1:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I've been thinking about how we might implement the <multiple column
>> assignment> UPDATE syntax that was introduced in SQL:2003. This feature
>> allows you to do
>> UPDATE table SET ..., (column, column, ...) = <row-valued expression>, ...

> Couple quick questions:
> 1) how does this interplay with RETURNING? I guess it probably
> doesn't change, but I imagine there's be no way to reference the
> composite result in the RETURNING statement?

Not as such; obviously you could reference the assigned-to columns
in RETURNING and thereby reconstruct the composite value.

> 2) I often wish that you could reference the table (or it's alias)
> directly as the field list.

> UPDATE foo f set f = (...)::foo;
> or even
> UPDATE foo SET foo = foo;

Hm. You could get there with this syntax as long as you didn't mind
writing out the field list explicitly. Arguments why you should
want to do that are the same as for avoiding "SELECT *", with maybe
a bit more urgency since at least SELECT * won't trash your data
if you get it wrong. However, assuming that that argument doesn't
impress you ...

My draft copy of SQL99 mentions a syntax

UPDATE table SET ROW = <row-valued expression> [ WHERE ... ]

which does not appear in later editions of the spec, and probably wasn't
in SQL99 final either (since SQL:2003 does not mention it as a removed
feature). I'm not sure we'd want to implement that; it would require
making ROW into a fully-reserved word, which it is not today, and that
seems rather a high price for implementing a not-per-spec feature.
But I don't think your suggestions of the table name or alias work;
they could conflict with an actual column name.

regards, tom lane