Tom Lane <t...@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 - 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
