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:

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:

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;

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.

results matching ""

    No results matching ""