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

On Fri, May 2, 2014 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> 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.

Presumably it'd follow similar rules to SELECT -- resolve the column
name in the face of ambiguity.

merlin