> Could you please point me to some resource explaining how to achieve implicit updatable mode with some simple example?
> Maybe i'm overlooking something important.
We currently require that the insert/update modifies only a single target and that the target is key-preserved.
If you think that your scenario should be updatable or there is a bug, can you provide more of the details here or in an issue?
I read whole documentation. However i didn't find any example with updatable view with joins.
Could you please explain "single target and that the target is key-preserved. " with example in normal SQL92?
what he is saying is if the view represents a single table that has PK/Unique key on it. If a transformation is more complex the intention of the update can not fully be guessed as there may be duplicate columns across the join tables etc. It is best if you define explicit update procedures.
I understand the single table scenario. However i'm interested in view with joins scenarios. According to the docs it should be possible, but there is no simple example:
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.
Using an example like:
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;
You could issue "insert into denormalized (fk_col, child_name) values (1, 'a')", which targets a single key-preserved table, but not "insert into denormalized (name) values ('a')" as it issue an insert that is not visible to the view.
However the logic is limited. You may be doing something like "insert into denormalized (pk_col, child_name) values (1, 'a')" with a view that is defined using the p.pk_col - unfortunately the logic doesn't yet consider the equivalency of the key values. It also for example doesn't consider left out join scenarios as a way of preserving the parent_table key. If you need a scenario like that to work, please log an issue.
I can also update the docs with more information on this topic - could you log an issue for that?
Thanks for your and Ramesh's explanation. Now i understand it. I will try to profit from described automatic behaviour and generate update triggers where needed. If it was problematic, i would log an issue with concrete scenario.