# HG changeset patch # User Raimund Renkert # Date 1467387552 -7200 # Node ID 9002ebe2a8af38db491c0e36aca0a8f18bd3a9b1 # Parent ff07474b65e08c664583bf68df6971b3fdfadd7f Removed schema 'bund'. diff -r ff07474b65e0 -r 9002ebe2a8af db_schema/lada_schema.sql --- a/db_schema/lada_schema.sql Fri Jul 01 16:55:16 2016 +0200 +++ b/db_schema/lada_schema.sql Fri Jul 01 17:39:12 2016 +0200 @@ -11,23 +11,15 @@ SET client_min_messages = warning; -- --- Name: bund; Type: SCHEMA; Schema: -; Owner: - --- - -CREATE SCHEMA bund; - - --- -- Name: land; Type: SCHEMA; Schema: -; Owner: - -- CREATE SCHEMA land; - -SET search_path = bund, pg_catalog; +SET search_path = land, pg_catalog; -- --- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: - +-- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: - -- CREATE FUNCTION update_time_status() RETURNS trigger @@ -48,16 +40,6 @@ END; $$; -SET search_path = land, pg_catalog; - -CREATE FUNCTION update_letzte_aenderung() RETURNS trigger - LANGUAGE plpgsql - AS $$ - BEGIN - NEW.letzte_aenderung = now(); - RETURN NEW; - END; -$$; -- -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - @@ -85,7 +67,7 @@ RAISE NOTICE 'messung is %',NEW.id; NEW.tree_modified = now(); UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id; - UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id; + UPDATE land.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id; RETURN NEW; END; $$; @@ -152,10 +134,8 @@ $$; -SET search_path = bund, pg_catalog; - -- --- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: kommentar_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE kommentar_id_seq @@ -170,84 +150,9 @@ SET default_with_oids = false; --- --- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE kommentar ( - id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, - erzeuger character varying(5) NOT NULL, - datum timestamp without time zone DEFAULT now(), - text character varying(1024) -); - - --- --- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE kommentar_m ( - messungs_id integer NOT NULL -) -INHERITS (kommentar); - - --- --- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE kommentar_p ( - probe_id integer NOT NULL -) -INHERITS (kommentar); - -- --- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE list ( - id integer NOT NULL, - typ character(1) NOT NULL, - bezeichnung character varying(20) NOT NULL, - beschreibuing character varying(512), - letzte_aenderung timestamp with time zone NOT NULL, - gueltig_bis timestamp with time zone -); -CREATE TRIGGER letzte_aenderung_list BEFORE UPDATE ON list FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - - --- --- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: - --- - -CREATE SEQUENCE list_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: - --- - -ALTER SEQUENCE list_id_seq OWNED BY list.id; - - --- --- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE list_zuordnung ( - list_id integer NOT NULL, - probe_id integer -); - - --- --- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: messung_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE messung_id_seq @@ -259,25 +164,7 @@ -- --- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE messung ( - id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, - probe_id integer NOT NULL, - nebenproben_nr character varying(10), - mmt_id character varying(2) NOT NULL, - messdauer integer, - messzeitpunkt timestamp with time zone, - fertig boolean DEFAULT false NOT NULL, - status integer, - letzte_aenderung timestamp without time zone DEFAULT now() -); -CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - - --- --- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE messung_messung_id_alt_seq @@ -289,7 +176,7 @@ -- --- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE messung_messungs_id_seq @@ -301,7 +188,7 @@ -- --- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: messwert_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE messwert_id_seq @@ -313,26 +200,7 @@ -- --- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE messwert ( - id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL, - messungs_id integer NOT NULL, - messgroesse_id integer NOT NULL, - messwert_nwg character varying(1), - messwert double precision NOT NULL, - messfehler real, - nwg_zu_messwert double precision, - meh_id smallint NOT NULL, - grenzwertueberschreitung boolean DEFAULT false, - letzte_aenderung timestamp without time zone DEFAULT now() -); -CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - - --- --- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: ort_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE ort_id_seq @@ -343,30 +211,9 @@ CACHE 1; --- --- Name: ortszuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE ortszuordnung ( - id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL, - probe_id integer NOT NULL, - ort_id bigint NOT NULL, - ortszuordnung_typ character varying(1), - ortszusatztext character varying(100), - letzte_aenderung timestamp without time zone DEFAULT now() -); -CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - -- --- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz'; - - --- --- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE probe_id_seq @@ -378,105 +225,7 @@ -- --- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE probe ( - id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), - test boolean DEFAULT false NOT NULL, - netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, - mst_id character varying(5) NOT NULL - REFERENCES stammdaten.mess_stelle, - labor_mst_id character varying(5) NOT NULL - REFERENCES stammdaten.mess_stelle, - hauptproben_nr character varying(20), - datenbasis_id smallint REFERENCES stammdaten.datenbasis, - ba_id character varying(1), - probenart_id smallint NOT NULL REFERENCES stammdaten.probenart, - media_desk character varying(100), - media character varying(100), - umw_id character varying(3) REFERENCES stammdaten.umwelt, - probeentnahme_beginn timestamp with time zone, - probeentnahme_ende timestamp with time zone, - mittelungsdauer bigint, - letzte_aenderung timestamp without time zone DEFAULT now(), - UNIQUE (mst_id, hauptproben_nr) -); - -CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - --- --- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; - - --- --- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; - - --- --- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle'; - - --- --- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor'; - - --- --- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel'; - - --- --- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)'; - - --- --- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)'; - - --- --- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)'; - - --- --- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; - - --- --- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: - --- - -COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; - - --- --- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE probe_probe_id_seq @@ -488,19 +237,7 @@ -- --- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE status_protokoll ( - status_stufe integer NOT NULL, - status_wert integer NOT NULL, - tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (kommentar_m); - - --- --- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: - +-- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: land; Owner: - -- CREATE SEQUENCE zusatz_wert_id_seq @@ -510,32 +247,17 @@ NO MAXVALUE CACHE 1; - --- --- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace: --- - -CREATE TABLE zusatz_wert ( - id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL, - probe_id integer NOT NULL, - pzs_id character varying(3) NOT NULL, - messwert_pzs double precision, - messfehler real, - letzte_aenderung timestamp without time zone DEFAULT now(), - nwg_zu_messwert double precision -); -CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); - - -SET search_path = land, pg_catalog; - -- -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: -- CREATE TABLE kommentar_m ( -) -INHERITS (bund.kommentar_m); + id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, + erzeuger character varying(5) NOT NULL, + datum timestamp without time zone DEFAULT now(), + text character varying(1024), + messungs_id integer NOT NULL +); -- @@ -543,8 +265,12 @@ -- CREATE TABLE kommentar_p ( -) -INHERITS (bund.kommentar_p); + id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, + erzeuger character varying(5) NOT NULL, + datum timestamp without time zone DEFAULT now(), + text character varying(1024), + probe_id integer NOT NULL +); -- @@ -643,10 +369,18 @@ -- CREATE TABLE messung ( + id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, + probe_id integer NOT NULL, + nebenproben_nr character varying(10), + mmt_id character varying(2) NOT NULL, + messdauer integer, + messzeitpunkt timestamp with time zone, + fertig boolean DEFAULT false NOT NULL, + status integer, + letzte_aenderung timestamp without time zone DEFAULT now(), geplant boolean DEFAULT false NOT NULL, tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (bund.messung); +); -- @@ -656,7 +390,7 @@ CREATE TABLE messung_translation ( id integer NOT NULL, messungs_id integer NOT NULL, - messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL + messungs_id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL ); @@ -684,9 +418,19 @@ -- CREATE TABLE messwert ( + id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL, + messungs_id integer NOT NULL, + messgroesse_id integer NOT NULL, + messwert_nwg character varying(1), + messwert double precision NOT NULL, + messfehler real, + nwg_zu_messwert double precision, + meh_id smallint NOT NULL, + grenzwertueberschreitung boolean DEFAULT false, + letzte_aenderung timestamp without time zone DEFAULT now(), tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (bund.messwert); +); +CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -- @@ -694,9 +438,21 @@ -- CREATE TABLE ortszuordnung ( + id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL, + probe_id integer NOT NULL, + ort_id bigint NOT NULL, + ortszuordnung_typ character varying(1), + ortszusatztext character varying(100), + letzte_aenderung timestamp without time zone DEFAULT now(), tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (bund.ortszuordnung); +); +CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); + +-- +-- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz'; -- @@ -704,6 +460,24 @@ -- CREATE TABLE probe ( + id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), + test boolean DEFAULT false NOT NULL, + netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, + mst_id character varying(5) NOT NULL + REFERENCES stammdaten.mess_stelle, + labor_mst_id character varying(5) NOT NULL + REFERENCES stammdaten.mess_stelle, + hauptproben_nr character varying(20), + datenbasis_id smallint REFERENCES stammdaten.datenbasis, + ba_id character varying(1), + probenart_id smallint NOT NULL REFERENCES stammdaten.probenart, + media_desk character varying(100), + media character varying(100), + umw_id character varying(3) REFERENCES stammdaten.umwelt, + probeentnahme_beginn timestamp with time zone, + probeentnahme_ende timestamp with time zone, + mittelungsdauer bigint, + letzte_aenderung timestamp without time zone DEFAULT now(), erzeuger_id integer, probe_nehmer_id integer, mp_kat character(1), @@ -711,36 +485,80 @@ mpr_id integer, solldatum_beginn timestamp without time zone, solldatum_ende timestamp without time zone, - tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (bund.probe); - -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_pkey PRIMARY KEY (id); - -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_datenbasis_id_fkey - FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id); - -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_mst_id_fkey - FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id); + tree_modified timestamp without time zone DEFAULT now(), + UNIQUE (mst_id, hauptproben_nr) +); +CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_labor_mst_id_fkey - FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id); +-- +-- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: - +-- -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_netzbetreiber_id_fkey - FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id); +COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_probenart_id_fkey - FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id); -ALTER TABLE ONLY probe - ADD CONSTRAINT probe_umw_id_fkey - FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id); +-- +-- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; + + +-- +-- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle'; + + +-- +-- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor'; + + +-- +-- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel'; + + +-- +-- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)'; + + +-- +-- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)'; + + +-- +-- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)'; + + +-- +-- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; + + +-- +-- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: - +-- + +COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; + -- @@ -750,7 +568,7 @@ CREATE TABLE probe_translation ( id integer NOT NULL, probe_id integer NOT NULL, - probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('bund.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL + probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL ); @@ -778,8 +596,15 @@ -- CREATE TABLE status_protokoll ( -) -INHERITS (bund.status_protokoll); + id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, + erzeuger character varying(5) NOT NULL, + datum timestamp without time zone DEFAULT now(), + text character varying(1024), + messungs_id integer NOT NULL, + status_stufe integer NOT NULL, + status_wert integer NOT NULL, + tree_modified timestamp without time zone DEFAULT now() +); -- @@ -787,57 +612,27 @@ -- CREATE TABLE zusatz_wert ( + id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL, + probe_id integer NOT NULL, + pzs_id character varying(3) NOT NULL, + messwert_pzs double precision, + messfehler real, + letzte_aenderung timestamp without time zone DEFAULT now(), + nwg_zu_messwert double precision, tree_modified timestamp without time zone DEFAULT now() -) -INHERITS (bund.zusatz_wert); - - -SET search_path = bund, pg_catalog; - --- --- Name: id; Type: DEFAULT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); - - --- --- Name: datum; Type: DEFAULT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now(); +); +CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -- --- Name: id; Type: DEFAULT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); - - --- --- Name: datum; Type: DEFAULT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now(); - - --- --- Name: id; Type: DEFAULT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass); - - --- --- Name: id; Type: DEFAULT; Schema: bund; Owner: - +-- Name: id; Type: DEFAULT; Schema: land; Owner: - -- ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); -- --- Name: datum; Type: DEFAULT; Schema: bund; Owner: - +-- Name: datum; Type: DEFAULT; Schema: land; Owner: - -- ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); @@ -849,7 +644,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); +ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); -- @@ -863,7 +658,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); +ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); -- @@ -884,7 +679,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass); +ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('land.messung_id_seq'::regclass); -- @@ -912,7 +707,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass); +ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('land.messwert_id_seq'::regclass); -- @@ -933,7 +728,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass); +ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('land.ort_id_seq'::regclass); -- @@ -954,7 +749,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); +ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); -- @@ -975,7 +770,7 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass); +ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('land.zusatz_wert_id_seq'::regclass); -- @@ -985,82 +780,6 @@ ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now(); -SET search_path = bund, pg_catalog; - --- --- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY kommentar - ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id); - - --- --- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY list - ADD CONSTRAINT list_pkey PRIMARY KEY (id); - - --- --- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY messung - ADD CONSTRAINT messung_pkey PRIMARY KEY (id); - - --- --- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY messwert - ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id); - - --- --- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY messwert - ADD CONSTRAINT messwert_pkey PRIMARY KEY (id); - - --- --- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY ortszuordnung - ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); - - --- --- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY status_protokoll - ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); - - --- --- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY zusatz_wert - ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id); - - --- --- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: --- - -ALTER TABLE ONLY zusatz_wert - ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); - - -SET search_path = land, pg_catalog; - -- -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: -- @@ -1173,22 +892,6 @@ ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); -SET search_path = bund, pg_catalog; - --- --- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace: --- - -CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id); - - --- --- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace: --- - -CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id); - - SET search_path = land, pg_catalog; -- @@ -1226,10 +929,8 @@ CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); -SET search_path = bund, pg_catalog; - -- --- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: - +-- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: - -- CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status(); @@ -1272,98 +973,8 @@ CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); -SET search_path = bund, pg_catalog; - --- --- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_m - ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); - - --- --- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY kommentar_p - ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); - - --- --- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY messung - ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id); - - --- --- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY messung - ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); - - --- --- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY messung - ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); - - --- --- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - -- - -ALTER TABLE ONLY messwert - ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); - - --- --- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY messwert - ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id); - - --- --- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY messwert - ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); - - --- --- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY ortszuordnung - ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id); - - --- --- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY ortszuordnung - ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id); - - --- --- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY ortszuordnung - ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); - - --- --- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - +-- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - -- ALTER TABLE ONLY status_protokoll @@ -1371,7 +982,7 @@ -- --- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - +-- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - -- ALTER TABLE ONLY status_protokoll @@ -1379,24 +990,6 @@ -- --- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY zusatz_wert - ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); - - --- --- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - --- - -ALTER TABLE ONLY zusatz_wert - ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); - - -SET search_path = land, pg_catalog; - --- -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - --