Attributvariation 2, PostgreSQL

Attributvariation 2

-- Tabelle komponente mit unterschiedlichen Typen ------------------------------
-- Jeder Typ kann ganz unterschiedliche Attribute besitzen ---------------------
create table komponente (id int, typ text, properties json);
insert into komponente values
(1, 'weg', '{"laenge":19.11, "breite":3}'),
(2, 'weg', '{"laenge":99.97, "breite":2}'),
(3, 'mast', '{"hoehe":13.5, "spreizung":5.2, "bemerkung":"bla bla"}'),
(4, 'mast', '{"hoehe":15.7, "spreizung":7.2, "bemerkung":"bla"}');

-- Für jeden Typ einen Postgres Datentyp erzeugen (kann man dynamich erzeugen --
create type wegtyp as(
    laenge numeric(5,2),
    breite numeric(5,2)
);
create type masttyp as(
    hoehe numeric(5,2),
    spreizung numeric(5,2),
    bemerkung text
);

-- View für Wege --------------------------------------------------------------
create view weg as
select k.id, k.typ, r.* 
from komponente k, 
lateral json_populate_record(null::wegtyp, properties) r
where k.typ = 'weg';

CREATE OR REPLACE RULE insert_weg AS
  ON INSERT TO weg
  DO INSTEAD
     INSERT INTO komponente (id, typ, properties)
     VALUES (NEW.id, NEW.typ, row_to_json(NEW));
 
CREATE OR REPLACE RULE update_weg AS
  ON UPDATE TO weg
  DO INSTEAD
     UPDATE komponente SET typ = NEW.typ, properties = row_to_json(NEW)::JSONB -'id' -'typ' WHERE id = NEW.id;

CREATE OR REPLACE RULE delete_weg AS 
  ON DELETE TO weg DO INSTEAD 
  DELETE FROM komponente WHERE id = OLD.id;
     
-- View für Maste ----------------------------------------------------------
create view mast as 
select k.id, k.typ, r.* 
from komponente k, 
lateral json_populate_record(null::masttyp, properties) r
where k.typ = 'mast';

CREATE OR REPLACE RULE insert_mast AS
  ON INSERT TO mast
  DO INSTEAD
     INSERT INTO komponente (id, typ, properties)
     VALUES (NEW.id, NEW.typ, row_to_json(NEW));
     
CREATE OR REPLACE RULE update_mast AS
  ON UPDATE TO mast
  DO INSTEAD
     UPDATE komponente SET typ = NEW.typ, properties = row_to_json(NEW)::JSONB -'id' -'typ' WHERE id = NEW.id;

CREATE OR REPLACE RULE delete_mast AS 
  ON DELETE TO mast DO INSTEAD 
  DELETE FROM komponente WHERE id = OLD.id;

-- Neue Datensatz über View anlegen (z.B SQL oder WFS) -----------------------
insert into weg (id, typ, laenge, breite) values (5, 'weg', 55.1, 5.0);
insert into weg (id, typ, laenge, breite) values (6, 'weg', 22.6, 2.0);
insert into mast (id, typ, hoehe, spreizung) values (7, 'mast', 8.1, 4.5);
insert into mast (id, typ, hoehe, spreizung) values (8, 'mast', 10.4, 5.2);

-- Einen Datensatz über View updaten (z.B SQL oder WFS) -----------------------
update weg set breite = 99.99 where id = 2;
update mast set hoehe = 99.99 where id = 2;

-- Einen Datensatz über View löschen (z.B SQL oder WFS) -----------------------
delete from weg where id = 1;
delete from mast where id = 8;

select * from weg order by id;
select * from mast order by id;
select * from komponente order by id;


run \| edit \| history \| help	0