Higher level ECS modules without custom module/mappers

[depombo]

Some research is necessary to figure out if it makes more sense to use a VIEWs or perhaps RULEs. Whatever format we pick will set the stage for advanced IaSQL users. It is common within eng orgs using IaC to create higher level modules built on top of the ones provided e.g. the DevOps/infra person in a 10-20 person eng team creates a backend/microservice abstraction that the software engineers use to spin up their services. This higher level abstraction will for example use a k8 pod with a set number of instances, an RDS instance, load balancer, etc. The software engineer can overwrite the module, but mostly they take it off the shelf and provide a few input params. Ideally we can offer our users a way to do this without having to write a custom IaSQL module, but simply do it with SQL functionality like an updatable VIEW

[aguillenv]

I was taking a look and found earlier this week an example of updateable views

could be tricky though

[depombo]

I think updatable views do not work across multiple tables. Perhaps the way forward would be with two-way triggers or somehow with rules hmm

[depombo]

so a two-way trigger would need a trigger depth check or it would recurse forever, but come think of it a one-way trigger from the high level table is probably a good enough start?

[depombo]

Proof of concept of two-way trigger working using a very simple example!

drop table if exists example;
drop table if exists example_simplified;

create table example_simplified(id integer UNIQUE NOT NULL, description text);
create table example(id integer UNIQUE NOT NULL, description text, serial integer);

create or replace function trigger_on_example_simplified()
returns trigger language plpgsql as $$
begin
    INSERT INTO example (id, description) VALUES (new.id, new.description)
    ON CONFLICT (id)
    WHERE id = new.id
    DO UPDATE SET description = new.description;
    RETURN new;
end
$$;

create trigger trigger_on_example_simplified
after insert or update on example_simplified
for each row when (pg_trigger_depth() = 0)
execute procedure trigger_on_example_simplified();

create or replace function trigger_on_example()
returns trigger language plpgsql as $$
begin
    INSERT INTO example_simplified (id, description) VALUES (new.id, new.description)
    ON CONFLICT (id)
    WHERE id = new.id
    DO UPDATE SET description = new.description;
    RETURN new;
end
$$;

create trigger trigger_on_example
after insert or update on example
for each row when (pg_trigger_depth() = 0)
execute procedure trigger_on_example();

insert into example_simplified (id, description)
values (1, 'John');

select *
from example;

[aguillenv]

I like this, but wouldn't they be custom mappers at SQL level?

[depombo]

I like this, but wouldn't they be custom mappers at SQL level?

https://github.com/iasql/iasql-engine/pull/1145#issue-1346856081