Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Assignment
ECommerce Website SQL Analysis by Iggy Zhao
loggin_trigger-audit
Select the latest id from last week from each different contact without repeating barcodes .....
Query for matching substring from text field in DB
fgf
create myStudents table
Studco
SQL social network practice by Han Wang 20200720 - 2
Mandelbrot