Dynamic Model view updates

[philayres]

Scenario

It may be useful to have a view that can be "edited" by a user, so that save triggers can be fired on the records. If the view has more than a single table, it is not updatable without an INSTEAD OF trigger.

To facilitate this, we need to implement the following changes.

DB migration to provide a dummy trigger function:

create or replace function ml_app.view_skip_updates()
returns trigger 
language plpgsql
as $function$
  begin
    return new;
  end
$function$;

Then in the _configurations section of the dynamic model options, add an option view_skip_updates with value true or false. This must only appear if the view_sql is set.

When view_skip_updates is true, after every update of the view

create or replace trigger view_skip_updates_trig
instead of insert or update on <schema name>.<view name>
for each row execute procedure ml_app.view_skip_updates();

This will give the appearance that the update has happened, allowing save triggers to be used to actually perform the required actions.