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

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  Tom> I spent a fair amount of time cleaning this patch up to get it
>  Tom> into committable shape, but as I was working on the documentation
>  Tom> I started to lose enthusiasm for it, because I was having a hard
>  Tom> time coming up with compelling examples.

> One example that comes up occasionally (and that I've had to do myself
> more than once) is this: given a table "foo" and another with identical
> schema "reference_foo", apply appropriate inserts, updates and deletes
> to table "foo" to make the content of the two tables identical. This can
> be done these days with wCTEs:

> with
>   t_diff as (select o.id as o_id, n.id as n_id, o, n
>                from foo o full outer join reference_foo n on (o.id=n.id)
>               where (o.*) is distinct from (n.*)),
>   ins as (insert into foo select (n).* from t_diff where o_id is null),
>   del as (delete from foo
>            where id in (select o_id from t_diff where n_id is null)),
>   upd as (update foo
>              set (col1,col2,...) = ((n).col1,(n).col2,...)  -- XXX
>             from t_diff
>            where foo.id = n_id and o_id = n_id)
> select count(*) filter (where o_id is null) as num_ins,
>        count(*) filter (where o_id = n_id) as num_upd,
>        count(*) filter (where n_id is null) as num_del
>   from t_diff;

While I agree that the UPDATE part of that desperately needs improvement,
I don't agree that the INSERT part is entirely fine.  You're still relying
on a parse-time expansion of the (n).* notation, which is inefficient and
not at all robust against schema changes (the same problem as with the
patch's approach to UPDATE).  So if we're taking this as a motivating
example, I'd want to see a fix that allows both INSERT and UPDATE directly
from a composite value of proper rowtype, without any expansion to
individual columns at all.

Perhaps we could adopt some syntax likeINSERT INTO table (*) values-or-select
to represent the case that the values-or-select delivers a single
composite column of the appropriate type.

> Other examples arise from things one might want to do in plpgsql; for
> example to update a record from an hstore or json value, one can use
> [json_]populate_record to construct a record variable, but then it's
> back to naming all the columns in order to actually perform the update
> statement.

Sure, but the patch as given didn't work very well for that either,
at least not if you wanted to avoid multiple evaluation of the
composite-returning function.  You'd have to adopt some obscure syntax
like "UPDATE target SET (*) = (SELECT * FROM composite_function(...))".
With what I'm thinking about now you could doUPDATE target SET * = composite_function(...)
which is a good deal less notation, and with a bit of luck it would not
require disassembling and reassembling the function's output tuple.
        regards, tom lane