Re: Support UPDATE table SET(*)=... - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Support UPDATE table SET(*)=...
Date
Msg-id 11264.1413558267@sss.pgh.pa.us
Whole thread Raw
In response to Re: Support UPDATE table SET(*)=...  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-hackers
Merlin Moncure <mmoncure@gmail.com> writes:
> On Wed, Oct 15, 2014 at 3:48 AM, Atri Sharma <atri.jiit@gmail.com> wrote:
>> Thanks for the links, but this patch only targets SET(*) case, which, if I
>> understand correctly, the patch you mentioned doesn't directly handle (If I
>> understand correctly, the target of the two patches is different).

> Yeah -- in fact, there was some discussion about this exact case.
> This patch solves a very important problem: when doing record
> operations to move data between databases with identical schema
> there's currently no way to 'update' in a generic way without building
> out the entire field list via complicated and nasty dynamic SQL.  I'm
> not sure about the proposed syntax though; it seems a little weird to
> me.  Any particular reason why you couldn't have just done:

> UPDATE table1 SET * = a,b,c, ...

> also,

> UPDATE table1 t SET t = (SELECT (a,b,c)::t FROM...);

> seems cleaner than the proposed syntax for row assignment.  Tom
> objected though IIRC.

That last proposal is no good because it would be ambiguous if the
table contains a column by that name.  The "(*)" idea actually seems
not bad, since it's shorthand for a parenthesized column list.

I'm not sure about the patch itself though --- in particular, it
doesn't seem to me that it should be touching transformTargetList,
since that doesn't have anything to do with expansion of multiassignments
today.  Probably this is a symptom of having chosen a poor representation
of the construct in the raw grammar output.  However, I've not exactly
wrapped my head around what that representation is ... the lack of any
comments explaining it doesn't help.

A larger question is whether it's appropriate to do the *-expansion
at parse time, or whether we'd need to postpone it to later in order
to handle reasonable use-cases.  Since we expand "SELECT *" at parse
time (and are mandated to do so by the SQL spec, I believe), it seems
consistent to do this at parse time as well; but perhaps there is a
counter argument.
        regards, tom lane



pgsql-hackers by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Support UPDATE table SET(*)=...
Next
From: Marko Tiikkaja
Date:
Subject: Re: Support UPDATE table SET(*)=...