Re: Support UPDATE table SET(*)=...
От | Andrew Gierth |
---|---|
Тема | Re: Support UPDATE table SET(*)=... |
Дата | |
Msg-id | 87vbh6oe1h.fsf@news-spur.riddles.org.uk обсуждение исходный текст |
Ответ на | Re: Support UPDATE table SET(*)=... (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Support UPDATE table SET(*)=...
|
Список | pgsql-hackers |
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: >> 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_foon 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 wheren_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; Tom> While I agree that the UPDATE part of that desperately needsTom> improvement, I don't agree that the INSERT part isentirely fine.Tom> You're still relying on a parse-time expansion of the (n).*Tom> notation, which is inefficient Not in my experience a huge deal given what else is going on... Tom> and not at all robust against schema changes (the same problem asTom> with the patch's approach to UPDATE). Now this I think is wrong; I think it's just as robust against schema changes as using the composite value directly would be. Consider the case where foo and reference_foo match with the exception of dropped columns; the code as it is should just work, while a variant that used the composite values would have to explicitly deal with that. (When I've used this kind of operation in practice, reference_foo has just been created using CREATE TEMP TABLE reference_foo (LIKE foo); and then populated via COPY from an external data source. Even if reference_foo were a non-temp table, the logic of the situation requires it to have the same schema as foo as far as SQL statements are concerned.) Tom> So if we're taking this as a motivating example, I'd want to see aTom> fix that allows both INSERT and UPDATE directlyfrom a compositeTom> value of proper rowtype, without any expansion to individualTom> columns at all. I would argue that this is a case of the perfect being the enemy of the good. Tom> Perhaps we could adopt some syntax likeTom> INSERT INTO table (*) values-or-selectTom> to represent the case thatthe values-or-select delivers a singleTom> composite column of the appropriate type. We could, but I think in all practical cases it'll be nothing more than a small performance optimization rather than something that really benefits people in terms of enhanced functionality. >> Other examples arise from things one might want to do in plpgsql; for>> example to update a record from an hstore or jsonvalue, one can use>> [json_]populate_record to construct a record variable, but then it's>> back to naming all the columnsin order to actually perform the update>> statement. Tom> Sure, but the patch as given didn't work very well for thatTom> either, Partly that's a limitation resulting from how much can be done with the existing SET (...) = syntax and implementation without ripping it all out and starting over. An incremental improvement seemed to be a more readily achievable goal. In practice it would indeed probably look like: declare new_id integer; new_values hstore; begin /* do stuff */ update foo set (*) = (select * from populate_record(foo,new_values)) where foo.id = new_id; A agree that it would be nicer to do update foo set (*) = populate_record(foo, new_values) where foo.id = new_id; but that would be a substantially larger project. The alternative of set * = populate_record(foo, new_values) is less satisfactory since it introduces inconsistencies with the case where you _do_ want to specify explicit columns, unless you also allow set (a,b) = row_value which is required by the spec for T641 but which we don't currently have. -- Andrew (irc:RhodiumToad)
В списке pgsql-hackers по дате отправления: