create foreign table parent_table (pk_col integer primary key, name string) options (updatable true);
create foreign table child_table (pk_col integer primary key, name string, fk_col integer, foreign key (fk_col) references parent_table (pk_col)) options (updatable true);
create view denormalized options (updatable true) as select c.fk_col, c.name as child_name, p.name from parent_table as p, child_table as c where p.pk_col = c.fk_col;Updatable Views
Any view may be marked as updatable. In many circumstances the view definition may allow the view to be inherently updatable without the need to manually define a trigger to handle INSERT/UPDATE/DELETE operations.
An inherently updatable view cannot be defined with a query that has:
-
A set operation (INTERSECT, EXCEPT, UNION).
-
SELECT DISTINCT
-
Aggregation (aggregate functions, GROUP BY, HAVING)
-
A LIMIT clause
A UNION ALL can define an inherently updatable view only if each of the UNION branches is itself inherently updatable. A view defined by a UNION ALL can support inherent INSERTs if it is a Federated Optimizations#Partitioned Union and the INSERT specifies values that belong to a single partition.
Any view column that is not mapped directly to a column is not updatable and cannot be targeted by an UPDATE set clause or be an INSERT column.
If a view is defined by a join query or has a WITH clause it may still be inherently updatable. However in these situations there are further restrictions and the resulting query plan may execute multiple statements. For a non-simple query to be updatable, it is required:
-
An INSERT/UPDATE can only modify a single Key-preserved Table.
-
To allow DELETE operations there must be only a single Key-preserved Table.
If the default handling is not available or you wish to have an alternative implementation of an INSERT/UPDATE/DELETE, then you may use Update Procedures (Triggers) to define procedures to handle the respective operations.
Consider the following example of an inherently updatable denormalized view:
A query such as "insert into denormalized (fk_col, child_name) values (1, 'a')" would succeed against this view as it targets a single key-preserved table - child_table. However "insert into denormalized (name) values ('a')" would fail as it maps to a parent_table which is not key preserved as there can be multiple rows for each parent_table key. Also an insert against just parent_table may not be visible to the view - as there may be no child entities associated either.
Not all scenarios will work. Referencing the above example, an "insert into denormalized (pk_col, child_name) values (1, 'a')" with a view that is defined using the p.pk_col will fail as the logic doesn’t yet consider the equivalency of the key values. If you encounter a scenario that needs support, please log an issue.