tom@866: \set ON_ERROR_STOP on tom@866: tom@866: BEGIN; tom@866: tom@866: SET statement_timeout = 0; tom@866: SET lock_timeout = 0; tom@866: SET client_encoding = 'UTF8'; tom@866: SET standard_conforming_strings = on; tom@866: SET check_function_bodies = false; tom@866: SET client_min_messages = warning; tom@866: tom@866: tom@866: CREATE SCHEMA stammdaten; tom@866: tom@866: SET search_path = stammdaten, pg_catalog; tom@866: raimund@926: CREATE FUNCTION update_letzte_aenderung() RETURNS trigger raimund@926: LANGUAGE plpgsql raimund@926: AS $$ raimund@926: BEGIN raimund@926: NEW.letzte_aenderung = now(); raimund@926: RETURN NEW; raimund@926: END; raimund@926: $$; tom@866: tom@866: CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying tom@866: LANGUAGE plpgsql tom@866: AS $$ tom@866: declare tom@866: result character varying(100); tom@866: d00 smallint; tom@866: d01 smallint; tom@866: d02 smallint; tom@866: d03 smallint; tom@866: begin tom@866: if media_desk like 'D: %' then tom@866: d00 := substring(media_desk,4,2); tom@866: d01 := substring(media_desk,7,2); tom@866: d02 := substring(media_desk,10,2); tom@866: d03 := substring(media_desk,13,2); tom@866: if d00 = '00' then tom@866: result := null; tom@866: else tom@866: if d01 = '00' then tom@866: select s00.beschreibung into result FROM stammdaten.deskriptoren s00 tom@866: where s00.ebene = 0 and s00.sn = d00::smallint; tom@866: else tom@866: if d02 = '00' or d00 <> '01' then tom@866: select s01.beschreibung into result FROM stammdaten.deskriptoren s01 tom@866: where s01.ebene = 1 and s01.sn = d01::smallint tom@866: and s01.vorgaenger = tom@866: (select s00.id FROM stammdaten.deskriptoren s00 tom@866: where s00.ebene = 0 and s00.sn = d00::smallint); tom@866: else tom@866: if d03 = '00' then tom@866: select s02.beschreibung into result FROM stammdaten.deskriptoren s02 tom@866: where s02.ebene = 2 and s02.sn = d02::smallint tom@866: and s02.vorgaenger = tom@866: (select s01.id FROM stammdaten.deskriptoren s01 tom@866: where s01.ebene = 1 and s01.sn = d01::smallint tom@866: and s01.vorgaenger = tom@866: (select s00.id FROM stammdaten.deskriptoren s00 tom@866: where s00.ebene = 0 and s00.sn = d00::smallint)); tom@866: else tom@866: select s03.beschreibung into result FROM stammdaten.deskriptoren s03 tom@866: where s03.ebene = 3 and s03.sn = d03::smallint tom@866: and s03.vorgaenger = tom@866: (select s02.id FROM stammdaten.deskriptoren s02 tom@866: where s02.ebene = 2 and s02.sn = d02::smallint tom@866: and s02.vorgaenger = tom@866: (select s01.id FROM stammdaten.deskriptoren s01 tom@866: where s01.ebene = 1 and s01.sn = d01::smallint tom@866: and s01.vorgaenger = tom@866: (select s00.id FROM stammdaten.deskriptoren s00 tom@866: where s00.ebene = 0 and s00.sn = d00::smallint))); tom@866: end if; tom@866: end if; tom@866: end if; tom@866: end if; tom@866: else tom@866: result := null; tom@866: end if; tom@866: return (result); tom@866: end; tom@866: $$; tom@866: tom@866: raimund@1015: CREATE TABLE koordinaten_art ( raimund@1015: id serial PRIMARY KEY, raimund@1015: koordinatenart character varying(50), raimund@1015: idf_geo_key character varying(1) raimund@1015: ); raimund@1015: raimund@1015: raimund@1015: CREATE TABLE mess_einheit ( raimund@1015: id serial PRIMARY KEY, raimund@1015: beschreibung character varying(50), raimund@1015: einheit character varying(12), raimund@1015: eudf_messeinheit_id character varying(8), raimund@1015: umrechnungs_faktor_eudf bigint raimund@1015: ); raimund@1015: raimund@1015: raimund@1015: CREATE TABLE umwelt ( raimund@1015: id character varying(3) PRIMARY KEY, raimund@1015: beschreibung character varying(300), raimund@1015: umwelt_bereich character varying(80) NOT NULL, raimund@1018: meh_id integer REFERENCES mess_einheit, raimund@1018: UNIQUE (umwelt_bereich) raimund@1015: ); raimund@1015: raimund@1015: raimund@1025: CREATE TABLE betriebsart ( raimund@1025: id serial PRIMARY KEY, raimund@1025: name character varying(30) NOT NULL raimund@1025: ); raimund@1025: INSERT INTO betriebsart VALUES(0, 'Normal-/Routinebetrieb'); raimund@1025: INSERT INTO betriebsart VALUES(1, 'Störfall-/Intensivbetrieb'); raimund@1025: raimund@1025: tom@902: CREATE TABLE staat ( raimund@1014: id serial PRIMARY KEY, tom@902: staat character varying(50) NOT NULL, tom@902: hkl_id smallint NOT NULL, tom@902: staat_iso character varying(2) NOT NULL, tom@902: staat_kurz character varying(5), tom@902: eu character(1) DEFAULT NULL::bpchar, tom@902: koord_x_extern character varying(22), tom@902: koord_y_extern character varying(22), raimund@1016: kda_id integer REFERENCES koordinaten_art tom@902: ); tom@902: tom@902: tom@902: CREATE TABLE verwaltungseinheit ( tom@902: id character varying(8) PRIMARY KEY, tom@902: bundesland character varying(8) NOT NULL, raimund@1016: kda_id integer REFERENCES koordinaten_art, tom@902: kreis character varying(8), tom@902: nuts character varying(10), tom@902: regbezirk character varying(8), tom@902: bezeichnung character varying(80) NOT NULL, tom@902: is_bundesland character(1) NOT NULL, tom@902: is_gemeinde character(1) NOT NULL, tom@902: is_landkreis character(1) NOT NULL, tom@902: is_regbezirk character(1) NOT NULL, tom@902: koord_x_extern character varying(22), tom@902: koord_y_extern character varying(22), tom@902: plz character varying(6), tom@902: longitude double precision, tom@902: latitude double precision tom@902: ); tom@902: tom@902: tom@902: CREATE TABLE netz_betreiber ( tom@902: id character varying(2) PRIMARY KEY, tom@902: netzbetreiber character varying(50), tom@902: idf_netzbetreiber character varying(1), tom@902: is_bmn boolean DEFAULT false, tom@902: mailverteiler character varying(512), raimund@1012: aktiv boolean DEFAULT false tom@902: ); tom@902: tom@902: tom@902: CREATE TABLE mess_stelle ( tom@902: id character varying(5) PRIMARY KEY, tom@968: netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, tom@902: beschreibung character varying(300), tom@902: mess_stelle character varying(60), tom@902: mst_typ character varying(1), tom@902: amtskennung character varying(6) tom@902: ); tom@902: tom@902: tom@868: CREATE TABLE auth_funktion ( tom@868: id smallint PRIMARY KEY, tom@872: funktion character varying(40) UNIQUE NOT NULL tom@866: ); tom@869: INSERT INTO auth_funktion VALUES (0, 'Erfasser'); tom@869: INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser'); tom@869: INSERT INTO auth_funktion VALUES (2, 'Status-Land'); tom@869: INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); tom@869: INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); tom@866: tom@870: raimund@1015: CREATE TABLE auth ( raimund@1015: id serial PRIMARY KEY, raimund@1015: ldap_group character varying(40) NOT NULL, raimund@1015: netzbetreiber_id character varying(2) REFERENCES netz_betreiber, raimund@1015: mst_id character varying(5) REFERENCES mess_stelle, raimund@1015: labor_mst_id character varying(5) REFERENCES mess_stelle, raimund@1015: funktion_id smallint REFERENCES auth_funktion raimund@1015: ); raimund@1015: raimund@1015: tom@868: CREATE TABLE auth_lst_umw ( raimund@1014: id serial PRIMARY KEY, raimund@1016: mst_id character varying(5) REFERENCES mess_stelle, raimund@1016: umw_id character varying(3) REFERENCES umwelt tom@868: ); tom@866: tom@866: tom@868: CREATE TABLE datenbasis ( raimund@1014: id serial PRIMARY KEY, tom@868: beschreibung character varying(30), tom@868: datenbasis character varying(6) tom@868: ); tom@866: tom@866: tom@868: CREATE TABLE datensatz_erzeuger ( raimund@1014: id serial PRIMARY KEY, tom@904: netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, raimund@1011: datensatz_erzeuger_id character varying(2) NOT NULL, tom@904: mst_id character varying(5) NOT NULL REFERENCES mess_stelle, tom@904: bezeichnung character varying(120) NOT NULL, tom@905: letzte_aenderung timestamp without time zone, raimund@1012: UNIQUE(datensatz_erzeuger_id, netzbetreiber_id) tom@868: ); raimund@926: CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@866: tom@866: tom@866: CREATE TABLE de_vg ( raimund@1014: id serial PRIMARY KEY, tom@866: use double precision, tom@866: rs character varying(12), tom@866: gf double precision, tom@866: rau_rs character varying(12), tom@866: gen character varying(50), tom@866: des character varying(75), tom@866: isn double precision, tom@866: bemerk character varying(75), tom@866: nambild character varying(16), tom@866: ags character varying(12), tom@866: rs_alt character varying(20), tom@866: wirksamkei date, tom@866: debkg_id character varying(16), tom@866: length numeric, tom@866: shape_area numeric, tom@866: geom public.geometry(MultiPolygon,4326) tom@866: ); tom@866: tom@866: tom@866: CREATE TABLE deskriptor_umwelt ( tom@868: id integer PRIMARY KEY, tom@866: s00 integer NOT NULL, tom@866: s01 integer NOT NULL, tom@866: s02 integer, tom@866: s03 integer, tom@866: s04 integer, tom@866: s05 integer, tom@866: s06 integer, tom@866: s07 integer, tom@866: s08 integer, tom@866: s09 integer, tom@866: s10 integer, tom@866: s11 integer, tom@866: s12 integer, raimund@1016: umw_id character varying(3) NOT NULL REFERENCES umwelt tom@866: ); tom@866: tom@866: tom@866: CREATE TABLE deskriptoren ( raimund@1014: id serial PRIMARY KEY, raimund@1016: vorgaenger integer REFERENCES deskriptoren, tom@866: ebene smallint, tom@866: s_xx integer, tom@866: sn smallint, tom@866: beschreibung character varying(100), tom@866: bedeutung character varying(300) tom@866: ); tom@866: tom@954: tom@954: CREATE TABLE lada_user ( raimund@1014: id serial PRIMARY KEY, raimund@1018: name character varying(80) NOT NULL, raimund@1018: UNIQUE (name) tom@954: ); tom@954: tom@954: raimund@1022: CREATE TABLE query_type ( raimund@1022: id serial PRIMARY KEY, raimund@1022: type character varying(30) NOT NULL raimund@1029: ); raimund@1022: INSERT INTO query_type VALUES(0, 'probe'); raimund@1022: INSERT INTO query_type VALUES(1, 'messung'); raimund@1022: INSERT INTO query_type VALUES(2, 'messprogramm'); raimund@1022: INSERT INTO query_type VALUES(3, 'ort'); raimund@1022: INSERT INTO query_type VALUES(4, 'probenehmer'); raimund@1023: INSERT INTO query_type VALUES(5, 'datensatzerzeuger'); raimund@1023: INSERT INTO query_type VALUES(6, 'messprogrammkategorie'); raimund@1022: tom@866: tom@954: CREATE TABLE query ( raimund@1014: id serial PRIMARY KEY, tom@954: name character varying(80) NOT NULL, raimund@1029: type integer NOT NULL REFERENCES query_type, raimund@1032: sql character varying(2500) NOT NULL, tom@954: description character varying(100) tom@954: ); tom@954: tom@866: tom@868: CREATE TABLE favorite ( raimund@1014: id serial PRIMARY KEY, tom@954: user_id integer NOT NULL REFERENCES lada_user, tom@954: query_id integer NOT NULL REFERENCES query ON DELETE CASCADE tom@868: ); tom@866: tom@866: raimund@1029: CREATE TABLE filter_type ( raimund@1029: id serial PRIMARY KEY, raimund@1029: type character varying(10) NOT NULL raimund@1029: ); raimund@1029: INSERT INTO filter_type VALUES(0, 'text'); raimund@1029: INSERT INTO filter_type VALUES(1, 'listmst'); raimund@1029: INSERT INTO filter_type VALUES(2, 'listnetz'); raimund@1029: INSERT INTO filter_type VALUES(3, 'listumw'); raimund@1029: INSERT INTO filter_type VALUES(4, 'liststatus'); raimund@1029: INSERT INTO filter_type VALUES(5, 'number'); raimund@1029: raimund@1029: tom@868: CREATE TABLE filter ( raimund@1014: id serial PRIMARY KEY, tom@954: query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, tom@868: data_index character varying(50) NOT NULL, raimund@1022: type integer NOT NULL REFERENCES filter_type, tom@868: label character varying(50) NOT NULL, tom@868: multiselect boolean tom@868: ); tom@866: tom@866: tom@868: CREATE TABLE filter_value ( raimund@1014: id serial PRIMARY KEY, tom@954: user_id integer NOT NULL REFERENCES lada_user, tom@954: filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE, tom@868: value text tom@868: ); tom@866: tom@866: tom@866: CREATE TABLE mess_methode ( tom@868: id character varying(2) PRIMARY KEY, tom@866: beschreibung character varying(300), tom@866: messmethode character varying(50) tom@866: ); tom@866: tom@866: tom@866: CREATE TABLE messgroesse ( raimund@1014: id serial PRIMARY KEY, tom@866: beschreibung character varying(300), tom@866: messgroesse character varying(50) NOT NULL, tom@866: default_farbe character varying(9), tom@866: idf_nuklid_key character varying(6), tom@866: ist_leitnuklid boolean DEFAULT false, tom@866: eudf_nuklid_id bigint, tom@866: kennung_bvl character varying(7) tom@866: ); tom@866: tom@866: tom@868: CREATE TABLE messgroessen_gruppe ( raimund@1014: id serial PRIMARY KEY, tom@868: bezeichnung character varying(80), tom@868: ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar tom@868: ); tom@866: tom@866: tom@868: CREATE TABLE messprogramm_kategorie ( raimund@1014: id serial PRIMARY KEY, tom@904: netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, raimund@1011: code character varying(3) NOT NULL, tom@904: bezeichnung character varying(120) NOT NULL, tom@905: letzte_aenderung timestamp without time zone, raimund@1013: UNIQUE(code, netzbetreiber_id) tom@868: ); raimund@926: CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@866: tom@866: tom@866: CREATE TABLE mg_grp ( raimund@1016: messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, raimund@1016: messgroesse_id integer NOT NULL REFERENCES messgroesse tom@866: ); raimund@1017: ALTER TABLE ONLY mg_grp raimund@1017: ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); tom@866: tom@866: tom@866: CREATE TABLE mmt_messgroesse_grp ( raimund@1016: messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, raimund@1016: mmt_id character varying(2) NOT NULL REFERENCES mess_methode tom@866: ); raimund@1017: ALTER TABLE ONLY mmt_messgroesse_grp raimund@1017: ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id); raimund@1017: raimund@1017: tom@866: tom@866: tom@866: CREATE VIEW mmt_messgroesse AS tom@866: SELECT mmt_messgroesse_grp.mmt_id, tom@866: mg_grp.messgroesse_id tom@866: FROM mmt_messgroesse_grp, tom@866: mg_grp tom@866: WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); tom@866: tom@866: tom@902: CREATE TABLE ort_typ ( tom@902: id smallint PRIMARY KEY, tom@902: ort_typ character varying(60) tom@866: ); tom@866: mstanko@1020: CREATE TABLE kta ( mstanko@1020: id serial NOT NULL, mstanko@1020: code character varying(7), mstanko@1020: bezeichnung character varying(80), mstanko@1020: CONSTRAINT kta_pkey PRIMARY KEY (id) mstanko@1020: ); mstanko@1020: COMMENT ON TABLE kta mstanko@1020: IS 'kernteschnische Anlagen'; mstanko@1020: mstanko@1020: CREATE TABLE ortszusatz ( mstanko@1020: id serial NOT NULL, mstanko@1020: code character varying(7), mstanko@1020: bezeichnung character varying(80), mstanko@1020: CONSTRAINT ortszusatz_pkey PRIMARY KEY (id) mstanko@1020: ); tom@866: tom@866: CREATE TABLE ort ( raimund@1014: id serial PRIMARY KEY, tom@912: netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, tom@904: ort_id character varying(10) NOT NULL, tom@904: langtext character varying(100) NOT NULL, tom@904: staat_id smallint NOT NULL REFERENCES staat, tom@902: gem_id character varying(8) REFERENCES verwaltungseinheit, tom@866: unscharf character(1) DEFAULT NULL::bpchar, tom@866: nuts_code character varying(10), tom@904: kda_id integer NOT NULL REFERENCES koordinaten_art, tom@904: koord_x_extern character varying(22) NOT NULL, tom@904: koord_y_extern character varying(22) NOT NULL, tom@866: hoehe_land real, tom@866: letzte_aenderung timestamp without time zone DEFAULT now(), tom@866: latitude double precision, tom@866: longitude double precision, tom@866: geom public.geometry(Point,4326), tom@866: shape public.geometry(MultiPolygon,4326), tom@902: ort_typ smallint REFERENCES ort_typ, tom@904: kurztext character varying(15) NOT NULL, tom@866: berichtstext character varying(70), tom@866: zone character varying(1), tom@866: sektor character varying(2), tom@866: zustaendigkeit character varying(10), tom@866: mp_art character varying(10), tom@866: aktiv character(1), tom@866: anlage_id integer, tom@905: oz_id integer, mstanko@1020: hoehe_ueber_nn real, tom@905: UNIQUE(ort_id, netzbetreiber_id) tom@866: ); mstanko@1020: raimund@926: CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@866: tom@902: ALTER TABLE ONLY ort mstanko@1020: ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id); tom@866: tom@902: ALTER TABLE ONLY ort tom@902: ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); tom@902: tom@866: tom@866: CREATE TABLE ortszuordnung_typ ( tom@868: id character(1) PRIMARY KEY, tom@866: ortstyp character varying(60) tom@866: ); tom@866: tom@866: tom@868: CREATE TABLE pflicht_messgroesse ( raimund@1014: id serial PRIMARY KEY, tom@868: messgroesse_id integer, raimund@1016: mmt_id character varying(2) REFERENCES mess_methode, raimund@1016: umw_id character varying(3) REFERENCES umwelt, raimund@1016: datenbasis_id smallint NOT NULL REFERENCES datenbasis tom@868: ); tom@866: tom@866: tom@866: CREATE TABLE proben_zusatz ( tom@868: id character varying(3) PRIMARY KEY, raimund@1016: meh_id integer REFERENCES mess_einheit, tom@866: beschreibung character varying(50) NOT NULL, tom@866: zusatzwert character varying(7) NOT NULL, raimund@1018: eudf_keyword character varying(40), raimund@1018: UNIQUE (eudf_keyword) tom@866: ); tom@866: tom@866: tom@868: CREATE TABLE probenart ( raimund@1014: id serial PRIMARY KEY, tom@868: beschreibung character varying(30), tom@868: probenart character varying(5) NOT NULL, tom@868: probenart_eudf_id character varying(1) NOT NULL tom@868: ); tom@866: tom@866: tom@866: CREATE TABLE probenehmer ( raimund@1014: id serial PRIMARY KEY, tom@904: netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, tom@904: prn_id character varying(9) NOT NULL, tom@866: bearbeiter character varying(25), tom@866: bemerkung character varying(60), tom@866: betrieb character varying(80), tom@904: bezeichnung character varying(80) NOT NULL, tom@904: kurz_bezeichnung character varying(10) NOT NULL, tom@866: ort character varying(20), tom@866: plz character varying(5), tom@866: strasse character varying(30), tom@866: telefon character varying(20), tom@866: tp character varying(3), tom@866: typ character(1), tom@905: letzte_aenderung timestamp without time zone, tom@905: UNIQUE(prn_id, netzbetreiber_id) tom@866: ); raimund@926: CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); tom@866: tom@866: tom@866: CREATE TABLE result ( raimund@1014: id serial PRIMARY KEY, tom@954: query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, tom@866: data_index character varying(50) NOT NULL, tom@866: header character varying(50) NOT NULL, tom@866: width integer, tom@866: flex boolean, tom@988: index integer, tom@988: UNIQUE (query_id, data_index) tom@866: ); tom@866: tom@866: tom@870: -- Status workflow tom@866: CREATE TABLE status_stufe ( tom@868: id integer PRIMARY KEY, tom@872: stufe character varying(50) UNIQUE NOT NULL tom@866: ); tom@871: INSERT INTO status_stufe VALUES (1, 'MST'); tom@871: INSERT INTO status_stufe VALUES (2, 'LAND'); tom@871: INSERT INTO status_stufe VALUES (3, 'LST'); tom@871: tom@866: tom@866: CREATE TABLE status_wert ( tom@868: id integer PRIMARY KEY, tom@872: wert character varying(50) UNIQUE NOT NULL tom@866: ); tom@871: INSERT INTO status_wert VALUES (0, 'nicht vergeben'); tom@871: INSERT INTO status_wert VALUES (1, 'plausibel'); tom@871: INSERT INTO status_wert VALUES (2, 'nicht repräsentativ'); tom@871: INSERT INTO status_wert VALUES (3, 'nicht plausibel'); tom@871: INSERT INTO status_wert VALUES (4, 'Rückfrage'); tom@871: INSERT INTO status_wert VALUES (7, 'nicht lieferbar'); tom@871: INSERT INTO status_wert VALUES (8, 'zurückgesetzt'); tom@871: tom@866: tom@870: CREATE TABLE status_kombi ( tom@870: id integer PRIMARY KEY, tom@872: stufe_id integer REFERENCES status_stufe NOT NULL, tom@872: wert_id integer REFERENCES status_wert NOT NULL, tom@872: UNIQUE(stufe_id, wert_id) tom@870: ); tom@871: -- 'zurückgesetzt' is left out here deliberately! tom@871: INSERT INTO status_kombi VALUES (1, 1, 0); tom@871: INSERT INTO status_kombi VALUES (2, 1, 1); tom@871: INSERT INTO status_kombi VALUES (3, 1, 2); tom@871: INSERT INTO status_kombi VALUES (4, 1, 3); tom@871: INSERT INTO status_kombi VALUES (5, 1, 7); tom@871: INSERT INTO status_kombi VALUES (6, 2, 1); tom@871: INSERT INTO status_kombi VALUES (7, 2, 2); tom@871: INSERT INTO status_kombi VALUES (8, 2, 3); tom@871: INSERT INTO status_kombi VALUES (9, 2, 4); tom@871: INSERT INTO status_kombi VALUES (10, 3, 1); tom@871: INSERT INTO status_kombi VALUES (11, 3, 2); tom@871: INSERT INTO status_kombi VALUES (12, 3, 3); tom@871: INSERT INTO status_kombi VALUES (13, 3, 4); tom@870: tom@871: tom@870: CREATE TABLE status_reihenfolge ( raimund@1014: id serial PRIMARY KEY, tom@872: von_id integer REFERENCES status_kombi NOT NULL, tom@872: zu_id integer REFERENCES status_kombi NOT NULL, tom@872: UNIQUE(von_id, zu_id) tom@870: ); tom@870: tom@871: CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$ tom@871: DECLARE kombi_from RECORD; tom@871: DECLARE s_from integer; tom@871: DECLARE w_from integer; tom@871: DECLARE kombi_to RECORD; tom@871: DECLARE s_to integer; tom@871: DECLARE w_to integer; tom@871: tom@871: BEGIN tom@871: FOR kombi_from IN SELECT * FROM status_kombi LOOP tom@871: s_from := kombi_from.stufe_id; tom@871: w_from := kombi_from.wert_id; tom@871: tom@871: FOR kombi_to IN SELECT * FROM status_kombi LOOP tom@871: s_to := kombi_to.stufe_id; tom@871: w_to := kombi_to.wert_id; tom@871: tom@884: IF s_from = s_to AND w_to <> 0 THEN tom@871: -- At the same 'stufe', all permutations occur, tom@871: -- but 'nicht vergeben' is only allowed for von_id tom@871: INSERT INTO status_reihenfolge (von_id, zu_id) tom@871: VALUES (kombi_from.id, kombi_to.id); tom@871: tom@871: ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN tom@871: -- Going to the next 'stufe' all available status_kombi are allowed tom@871: -- in case current wert is not 'nicht vergeben' or 'Rückfrage' tom@871: INSERT INTO status_reihenfolge (von_id, zu_id) tom@871: VALUES (kombi_from.id, kombi_to.id); tom@871: tom@871: ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN tom@871: -- After 'Rückfrage' follows 'MST' with tom@871: -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ' tom@871: INSERT INTO status_reihenfolge (von_id, zu_id) tom@871: VALUES (kombi_from.id, kombi_to.id); tom@871: tom@871: END IF; tom@871: END LOOP; tom@871: END LOOP; tom@871: END; tom@871: $$ LANGUAGE plpgsql; tom@871: tom@871: SELECT populate_status_reihenfolge(); tom@871: DROP FUNCTION populate_status_reihenfolge(); tom@871: ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT; tom@871: DROP SEQUENCE status_reihenfolge_id_seq; tom@871: tom@871: tom@870: CREATE VIEW status_erreichbar AS ( tom@879: SELECT r.id, tom@879: zu.wert_id, tom@879: zu.stufe_id, tom@879: von.wert_id AS cur_wert, tom@879: von.stufe_id AS cur_stufe tom@879: FROM stammdaten.status_reihenfolge r tom@879: JOIN stammdaten.status_kombi von tom@879: ON von.id = r.von_id tom@879: JOIN stammdaten.status_kombi zu tom@879: ON zu.id = r.zu_id tom@870: ); tom@870: -- Status workflow tom@870: tom@866: tom@866: CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom); tom@866: tom@866: tom@866: CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); tom@866: tom@866: tom@866: COMMIT;