Tom Lane <tgl@sss.pgh.pa.us> writes:
> ON UPDATE DO INSTEAD SUBSTITUTE base_table [ (base_column_name, ...) ]
> [ WHERE ... ]
>
> ON UPDATE DO INSTEAD
> UPDATE base_table SET base_col_1 = new.derived_col_1, base_col_2 ...
> WHERE CURRENT OF VIEW;
What would happen with these if the view is defined with "SELECT *" and I add
a new column or drop columns from the table? It seems like the former with the
optional list of columns would magically apply to the new columns which would
make it behave differently from the normal select rule. Or would you expand an
ommitted column list like we do with "select *"
In any case the fact that the latter allows you to extend things with computed
values seems pretty attractive. We could always allow shortcuts like "SET *
WHERE CURRENT OF VIEW" analogous to "SELECT *" for manually created views. We
could also allow the rhs of the expressions to be skipped so you could do
UPDATE base_table SET col1, col2, col, base_col = new.derived_col - 1WHERE CURRENT OF VIEW
This same machinery isn't present in the normal executor is it? I mean, if I
can update a view then ISTM I should be able to update a view written inline
in the query like:
UPDATE (select * from a where x=1) set y=2
just like I can with SELECTs. This does incidentally work in Oracle and is its
way of doing what we do with UPDATE...FROM. It's the only way AFAIK to get
merge join update plans out of it.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!