Re: [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 - 1
WHERE 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's PostgreSQL training!