diff db_schema/stammdaten_schema.sql @ 866:d47e6b8f3897

Reorganise database setup scripts. First step towards better readability.
author Tom Gottfried <tom@intevation.de>
date Fri, 05 Feb 2016 17:01:28 +0100
parents
children 66069d69d6ee
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db_schema/stammdaten_schema.sql	Fri Feb 05 17:01:28 2016 +0100
@@ -0,0 +1,2167 @@
+\set ON_ERROR_STOP on
+
+BEGIN;
+
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+
+
+CREATE SCHEMA stammdaten;
+
+SET search_path = stammdaten, pg_catalog;
+
+
+CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
+    LANGUAGE plpgsql
+    AS $$
+declare
+  result character varying(100);
+  d00 smallint;
+  d01 smallint;
+  d02 smallint;
+  d03 smallint;
+begin
+  if media_desk like 'D: %' then
+    d00 := substring(media_desk,4,2);
+    d01 := substring(media_desk,7,2);
+    d02 := substring(media_desk,10,2);
+    d03 := substring(media_desk,13,2);
+    if d00 = '00' then
+      result := null;
+    else
+      if d01 = '00' then
+        select s00.beschreibung into result FROM stammdaten.deskriptoren s00
+        where s00.ebene = 0 and s00.sn = d00::smallint;
+      else
+        if d02 = '00' or d00 <> '01' then
+          select s01.beschreibung into result FROM stammdaten.deskriptoren s01
+          where s01.ebene = 1 and s01.sn = d01::smallint
+            and s01.vorgaenger =
+              (select s00.id FROM stammdaten.deskriptoren s00
+               where s00.ebene = 0 and s00.sn = d00::smallint);
+        else
+          if d03 = '00' then
+            select s02.beschreibung into result FROM stammdaten.deskriptoren s02
+            where s02.ebene = 2 and s02.sn = d02::smallint
+              and s02.vorgaenger =
+                (select s01.id FROM stammdaten.deskriptoren s01
+                 where s01.ebene = 1 and s01.sn = d01::smallint
+                   and s01.vorgaenger =
+                     (select s00.id FROM stammdaten.deskriptoren s00
+                      where s00.ebene = 0 and s00.sn = d00::smallint));
+          else
+            select s03.beschreibung into result FROM stammdaten.deskriptoren s03
+            where s03.ebene = 3 and s03.sn = d03::smallint
+              and s03.vorgaenger =
+              (select s02.id FROM stammdaten.deskriptoren s02
+              where s02.ebene = 2 and s02.sn = d02::smallint
+                and s02.vorgaenger =
+                  (select s01.id FROM stammdaten.deskriptoren s01
+                  where s01.ebene = 1 and s01.sn = d01::smallint
+                    and s01.vorgaenger =
+                      (select s00.id FROM stammdaten.deskriptoren s00
+                      where s00.ebene = 0 and s00.sn = d00::smallint)));
+          end if;
+        end if;
+      end if;
+    end if;
+  else
+    result := null;
+  end if;
+  return (result);
+end;
+$$;
+
+
+CREATE TABLE auth (
+    id integer NOT NULL,
+    ldap_group character varying(40) NOT NULL,
+    netzbetreiber_id character varying(2),
+    mst_id character varying(5),
+    labor_mst_id character varying(5),
+    funktion_id smallint
+);
+
+
+--
+-- Name: auth_funktion; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE auth_funktion (
+    id smallint NOT NULL,
+    funktion character varying(40)
+);
+
+
+--
+-- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE auth_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
+
+
+--
+-- Name: auth_id_seq1; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE auth_id_seq1
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: auth_id_seq1; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE auth_id_seq1 OWNED BY auth.id;
+
+
+--
+-- Name: auth_lst_umw; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE auth_lst_umw (
+    id integer NOT NULL,
+    lst_id character varying(5),
+    umw_id character varying(3)
+);
+
+
+--
+-- Name: auth_lst_umw_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE auth_lst_umw_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: auth_lst_umw_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
+
+
+--
+-- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE datenbasis (
+    id integer NOT NULL,
+    beschreibung character varying(30),
+    datenbasis character varying(6)
+);
+
+
+--
+-- Name: datenbasis_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE datenbasis_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: datenbasis_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
+
+
+--
+-- Name: datensatz_erzeuger; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE datensatz_erzeuger (
+    id integer NOT NULL,
+    netzbetreiber_id character varying(2),
+    da_erzeuger_id character varying(2),
+    mst_id character varying(5),
+    bezeichnung character varying(120),
+    letzte_aenderung timestamp without time zone
+);
+
+
+--
+-- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE datensatz_erzeuger_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
+
+
+--
+-- Name: de_vg; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE de_vg (
+    id integer NOT NULL,
+    use double precision,
+    rs character varying(12),
+    gf double precision,
+    rau_rs character varying(12),
+    gen character varying(50),
+    des character varying(75),
+    isn double precision,
+    bemerk character varying(75),
+    nambild character varying(16),
+    ags character varying(12),
+    rs_alt character varying(20),
+    wirksamkei date,
+    debkg_id character varying(16),
+    length numeric,
+    shape_area numeric,
+    geom public.geometry(MultiPolygon,4326)
+);
+
+
+--
+-- Name: de_vg_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE de_vg_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: de_vg_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
+
+
+--
+-- Name: deskriptor_umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE deskriptor_umwelt (
+    id integer NOT NULL,
+    s00 integer NOT NULL,
+    s01 integer NOT NULL,
+    s02 integer,
+    s03 integer,
+    s04 integer,
+    s05 integer,
+    s06 integer,
+    s07 integer,
+    s08 integer,
+    s09 integer,
+    s10 integer,
+    s11 integer,
+    s12 integer,
+    umw_id character varying(3) NOT NULL
+);
+
+
+--
+-- Name: deskriptoren; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE deskriptoren (
+    id integer NOT NULL,
+    vorgaenger integer,
+    ebene smallint,
+    s_xx integer,
+    sn smallint,
+    beschreibung character varying(100),
+    bedeutung character varying(300)
+);
+
+
+--
+-- Name: deskriptoren_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE deskriptoren_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: deskriptoren_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
+
+
+--
+-- Name: favorite; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE favorite (
+    id integer NOT NULL,
+    user_id integer NOT NULL,
+    query_id integer NOT NULL
+);
+
+
+--
+-- Name: favorite_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE favorite_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: favorite_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
+
+
+--
+-- Name: filter; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE filter (
+    id integer NOT NULL,
+    query_id integer NOT NULL,
+    data_index character varying(50) NOT NULL,
+    type character varying(10) NOT NULL,
+    label character varying(50) NOT NULL,
+    multiselect boolean
+);
+
+
+--
+-- Name: filter_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE filter_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: filter_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
+
+
+--
+-- Name: filter_value; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE filter_value (
+    id integer NOT NULL,
+    query_id integer NOT NULL,
+    user_id integer NOT NULL,
+    filter_id integer NOT NULL,
+    value text
+);
+
+
+--
+-- Name: filter_value_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE filter_value_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: filter_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
+
+
+--
+-- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE koordinaten_art (
+    id integer NOT NULL,
+    koordinatenart character varying(50),
+    idf_geo_key character varying(1)
+);
+
+
+--
+-- Name: koordinaten_art_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE koordinaten_art_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: koordinaten_art_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
+
+
+--
+-- Name: lada_user; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE lada_user (
+    id integer NOT NULL,
+    name character varying(80) NOT NULL
+);
+
+
+--
+-- Name: lada_user_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE lada_user_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: lada_user_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
+
+
+--
+-- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE mess_einheit (
+    id integer NOT NULL,
+    beschreibung character varying(50),
+    einheit character varying(12),
+    eudf_messeinheit_id character varying(8),
+    umrechnungs_faktor_eudf bigint
+);
+
+
+--
+-- Name: mess_einheit_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE mess_einheit_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: mess_einheit_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
+
+
+--
+-- Name: mess_methode; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE mess_methode (
+    id character varying(2) NOT NULL,
+    beschreibung character varying(300),
+    messmethode character varying(50)
+);
+
+
+--
+-- Name: mess_stelle; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE mess_stelle (
+    id character varying(5) NOT NULL,
+    netzbetreiber_id character varying(2),
+    beschreibung character varying(300),
+    mess_stelle character varying(60),
+    mst_typ character varying(1),
+    amtskennung character varying(6)
+);
+
+
+--
+-- Name: messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE messgroesse (
+    id integer NOT NULL,
+    beschreibung character varying(300),
+    messgroesse character varying(50) NOT NULL,
+    default_farbe character varying(9),
+    idf_nuklid_key character varying(6),
+    ist_leitnuklid boolean DEFAULT false,
+    eudf_nuklid_id bigint,
+    kennung_bvl character varying(7)
+);
+
+
+--
+-- Name: messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE messgroesse_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
+
+
+--
+-- Name: messgroessen_gruppe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE messgroessen_gruppe (
+    id integer NOT NULL,
+    bezeichnung character varying(80),
+    ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
+);
+
+
+--
+-- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE messgroessen_gruppe_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
+
+
+--
+-- Name: messprogramm_kategorie; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE messprogramm_kategorie (
+    id integer NOT NULL,
+    netzbetreiber_id character varying(2),
+    mpl_id character varying(3),
+    bezeichnung character varying(120),
+    letzte_aenderung timestamp without time zone
+);
+
+
+--
+-- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE messprogramm_kategorie_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE messprogramm_kategorie_id_seq OWNED BY messprogramm_kategorie.id;
+
+
+--
+-- Name: mg_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE mg_grp (
+    messgroessengruppe_id integer NOT NULL,
+    messgroesse_id integer NOT NULL
+);
+
+
+--
+-- Name: mmt_messgroesse_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE mmt_messgroesse_grp (
+    messgroessengruppe_id integer NOT NULL,
+    mmt_id character varying(2) NOT NULL
+);
+
+
+--
+-- Name: mmt_messgroesse; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW mmt_messgroesse AS
+ SELECT mmt_messgroesse_grp.mmt_id,
+    mg_grp.messgroesse_id
+   FROM mmt_messgroesse_grp,
+    mg_grp
+  WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
+
+
+--
+-- Name: netz_betreiber; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE netz_betreiber (
+    id character varying(2) NOT NULL,
+    netzbetreiber character varying(50),
+    idf_netzbetreiber character varying(1),
+    is_bmn boolean DEFAULT false,
+    mailverteiler character varying(512),
+    aktiv boolean DEFAULT false,
+    zust_mst_id character varying(5)
+);
+
+
+--
+-- Name: ort; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE ort (
+    id integer NOT NULL,
+    netzbetreiber_id character varying(2),
+    ort_id character varying(10),
+    langtext character varying(100),
+    staat_id smallint,
+    gem_id character varying(8),
+    unscharf character(1) DEFAULT NULL::bpchar,
+    nuts_code character varying(10),
+    kda_id integer,
+    koord_x_extern character varying(22),
+    koord_y_extern character varying(22),
+    hoehe_land real,
+    letzte_aenderung timestamp without time zone DEFAULT now(),
+    latitude double precision,
+    longitude double precision,
+    geom public.geometry(Point,4326),
+    shape public.geometry(MultiPolygon,4326),
+    ort_typ smallint,
+    kurztext character varying(15),
+    berichtstext character varying(70),
+    zone character varying(1),
+    sektor character varying(2),
+    zustaendigkeit character varying(10),
+    mp_art character varying(10),
+    aktiv character(1),
+    anlage_id integer,
+    oz_id integer
+);
+
+
+--
+-- Name: ort_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE ort_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: ort_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
+
+
+--
+-- Name: ort_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE ort_typ (
+    id smallint NOT NULL,
+    ort_typ character varying(60)
+);
+
+
+--
+-- Name: ortszuordnung_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE ortszuordnung_typ (
+    id character(1) NOT NULL,
+    ortstyp character varying(60)
+);
+
+
+--
+-- Name: pflicht_messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE pflicht_messgroesse (
+    id integer NOT NULL,
+    messgroesse_id integer,
+    mmt_id character varying(2),
+    umw_id character varying(3),
+    datenbasis_id smallint NOT NULL
+);
+
+
+--
+-- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE pflicht_messgroesse_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
+
+
+--
+-- Name: proben_zusatz; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE proben_zusatz (
+    id character varying(3) NOT NULL,
+    meh_id integer,
+    beschreibung character varying(50) NOT NULL,
+    zusatzwert character varying(7) NOT NULL,
+    eudf_keyword character varying(40)
+);
+
+
+--
+-- Name: probenart; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE probenart (
+    id integer NOT NULL,
+    beschreibung character varying(30),
+    probenart character varying(5) NOT NULL,
+    probenart_eudf_id character varying(1) NOT NULL
+);
+
+
+--
+-- Name: probenart_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE probenart_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: probenart_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
+
+
+--
+-- Name: probenehmer; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE probenehmer (
+    id integer NOT NULL,
+    netzbetreiber_id character varying(2),
+    prn_id character varying(9),
+    bearbeiter character varying(25),
+    bemerkung character varying(60),
+    betrieb character varying(80),
+    bezeichnung character varying(80),
+    kurz_bezeichnung character varying(10),
+    ort character varying(20),
+    plz character varying(5),
+    strasse character varying(30),
+    telefon character varying(20),
+    tp character varying(3),
+    typ character(1),
+    letzte_aenderung timestamp without time zone
+);
+
+
+--
+-- Name: probenehmer_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE probenehmer_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: probenehmer_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
+
+
+--
+-- Name: query; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE query (
+    id integer NOT NULL,
+    name character varying(80) NOT NULL,
+    type character varying(30) NOT NULL,
+    sql character varying(1500) NOT NULL,
+    description character varying(100)
+);
+
+
+--
+-- Name: query_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE query_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: query_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE query_id_seq OWNED BY query.id;
+
+
+--
+-- Name: result; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+CREATE TABLE result (
+    id integer NOT NULL,
+    query_id integer NOT NULL,
+    data_index character varying(50) NOT NULL,
+    header character varying(50) NOT NULL,
+    width integer,
+    flex boolean,
+    index integer
+);
+
+
+--
+-- Name: result_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE result_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: result_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE result_id_seq OWNED BY result.id;
+
+
+--
+-- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_00_view AS
+ SELECT deskriptoren.s_xx AS s00,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 0);
+
+
+--
+-- Name: s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s00,
+    d1.bedeutung,
+    d1.beschreibung,
+    d1.sn
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
+  WHERE (d1.ebene = 1);
+
+
+--
+-- Name: s_02_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_02_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s02
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 2);
+
+
+--
+-- Name: s_02_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_02_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s00,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 2);
+
+
+--
+-- Name: s_03_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_03_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s03
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
+
+
+--
+-- Name: s_03_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_03_view AS
+ SELECT d1.s_xx AS s03,
+    d2.s_xx AS s02,
+    d1.bedeutung,
+    d1.beschreibung,
+    d1.sn
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
+  WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
+UNION
+ SELECT d1.s_xx AS s03,
+    NULL::integer AS s02,
+    d1.bedeutung,
+    d1.beschreibung,
+    d1.sn
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
+  WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
+
+
+--
+-- Name: s_04_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_04_s_01_view AS
+ SELECT DISTINCT d1.s_xx AS s01,
+    d2.s_xx AS s04
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 4);
+
+
+--
+-- Name: s_04_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_04_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s04,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 4);
+
+
+--
+-- Name: s_05_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_05_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s05
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 5);
+
+
+--
+-- Name: s_05_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_05_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s05,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 5);
+
+
+--
+-- Name: s_06_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_06_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s06
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 6);
+
+
+--
+-- Name: s_06_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_06_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s06,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 6);
+
+
+--
+-- Name: s_07_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_07_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s07
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 7);
+
+
+--
+-- Name: s_07_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_07_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s07,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 7);
+
+
+--
+-- Name: s_08_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_08_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s08
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 8);
+
+
+--
+-- Name: s_08_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_08_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s08,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 8);
+
+
+--
+-- Name: s_09_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_09_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s09
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 9);
+
+
+--
+-- Name: s_09_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_09_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s09,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 9);
+
+
+--
+-- Name: s_10_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_10_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s10
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 10);
+
+
+--
+-- Name: s_10_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_10_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s10,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 10);
+
+
+--
+-- Name: s_11_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_11_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s11
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 11);
+
+
+--
+-- Name: s_11_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_11_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s11,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 11);
+
+
+--
+-- Name: s_12_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_12_s_01_view AS
+ SELECT d1.s_xx AS s01,
+    d2.s_xx AS s12
+   FROM (deskriptoren d1
+     JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
+  WHERE (d2.ebene = 12);
+
+
+--
+-- Name: s_12_view; Type: VIEW; Schema: stammdaten; Owner: -
+--
+
+CREATE VIEW s_12_view AS
+ SELECT DISTINCT deskriptoren.s_xx AS s12,
+    deskriptoren.bedeutung,
+    deskriptoren.beschreibung,
+    deskriptoren.sn
+   FROM deskriptoren
+  WHERE (deskriptoren.ebene = 12);
+
+
+--
+-- Name: staat; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE staat (
+    id integer NOT NULL,
+    staat character varying(50) NOT NULL,
+    hkl_id smallint NOT NULL,
+    staat_iso character varying(2) NOT NULL,
+    staat_kurz character varying(5),
+    eu character(1) DEFAULT NULL::bpchar,
+    koord_x_extern character varying(22),
+    koord_y_extern character varying(22),
+    kda_id integer
+);
+
+
+--
+-- Name: staat_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
+--
+
+CREATE SEQUENCE staat_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: staat_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
+--
+
+ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
+
+
+--
+-- Name: status_kombi; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE status_kombi (
+    id integer NOT NULL,
+    stufe_id integer,
+    wert_id integer
+);
+
+
+--
+-- Name: status_reihenfolge; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE status_reihenfolge (
+    id integer NOT NULL,
+    von_id integer,
+    zu_id integer
+);
+
+
+--
+-- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE status_stufe (
+    id integer NOT NULL,
+    stufe character varying(50)
+);
+
+
+--
+-- Name: status_wert; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE status_wert (
+    id integer NOT NULL,
+    wert character varying(50)
+);
+
+
+--
+-- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE umwelt (
+    id character varying(3) NOT NULL,
+    beschreibung character varying(300),
+    umwelt_bereich character varying(80) NOT NULL,
+    meh_id integer
+);
+
+
+--
+-- Name: verwaltungseinheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE TABLE verwaltungseinheit (
+    id character varying(8) NOT NULL,
+    bundesland character varying(8) NOT NULL,
+    kda_id integer,
+    kreis character varying(8),
+    nuts character varying(10),
+    regbezirk character varying(8),
+    bezeichnung character varying(80) NOT NULL,
+    is_bundesland character(1) NOT NULL,
+    is_gemeinde character(1) NOT NULL,
+    is_landkreis character(1) NOT NULL,
+    is_regbezirk character(1) NOT NULL,
+    koord_x_extern character varying(22),
+    koord_y_extern character varying(22),
+    plz character varying(6),
+    longitude double precision,
+    latitude double precision
+);
+
+--
+-- Name status_erreichbar; Type: VIEW; Schema: stammdaten; Owner: -;
+--
+CREATE VIEW status_erreichbar AS (
+    SELECT DISTINCT k.wert_id,
+        j.wert_id AS cur_wert,
+        j.stufe_id AS cur_stufe
+    FROM stammdaten.status_kombi k
+    JOIN (SELECT r.zu_id,
+              kom.wert_id,
+              kom.stufe_id
+          FROM stammdaten.status_reihenfolge r
+          JOIN stammdaten.status_kombi kom
+          ON kom.id = r.von_id) j
+    ON j.zu_id = k.id
+);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth ALTER COLUMN id SET DEFAULT nextval('auth_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY datenbasis ALTER COLUMN id SET DEFAULT nextval('datenbasis_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY datensatz_erzeuger ALTER COLUMN id SET DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY de_vg ALTER COLUMN id SET DEFAULT nextval('de_vg_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY deskriptoren ALTER COLUMN id SET DEFAULT nextval('deskriptoren_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY favorite ALTER COLUMN id SET DEFAULT nextval('favorite_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter ALTER COLUMN id SET DEFAULT nextval('filter_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter_value ALTER COLUMN id SET DEFAULT nextval('filter_value_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY lada_user ALTER COLUMN id SET DEFAULT nextval('lada_user_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY messgroesse ALTER COLUMN id SET DEFAULT nextval('messgroesse_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY messgroessen_gruppe ALTER COLUMN id SET DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm_kategorie ALTER COLUMN id SET DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('ort_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY pflicht_messgroesse ALTER COLUMN id SET DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY probenart ALTER COLUMN id SET DEFAULT nextval('probenart_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY probenehmer ALTER COLUMN id SET DEFAULT nextval('probenehmer_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY query ALTER COLUMN id SET DEFAULT nextval('query_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY result ALTER COLUMN id SET DEFAULT nextval('result_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass);
+
+
+--
+-- Name: auth_lst_umw_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY auth_lst_umw
+    ADD CONSTRAINT auth_lst_umw_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: auth_role_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY auth_funktion
+    ADD CONSTRAINT auth_role_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY datenbasis
+    ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: datensatz_erzeuger_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY datensatz_erzeuger
+    ADD CONSTRAINT datensatz_erzeuger_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: de_vg_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY de_vg
+    ADD CONSTRAINT de_vg_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: deskriptor_umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY deskriptor_umwelt
+    ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: favorite_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY favorite
+    ADD CONSTRAINT favorite_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: filter_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY filter
+    ADD CONSTRAINT filter_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: filter_value_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY filter_value
+    ADD CONSTRAINT filter_value_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY koordinaten_art
+    ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: lada_user_name_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY lada_user
+    ADD CONSTRAINT lada_user_name_key UNIQUE (name);
+
+
+--
+-- Name: lada_user_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY lada_user
+    ADD CONSTRAINT lada_user_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY mess_einheit
+    ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: mess_methode_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY mess_methode
+    ADD CONSTRAINT mess_methode_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: mess_stelle_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY mess_stelle
+    ADD CONSTRAINT mess_stelle_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY messgroesse
+    ADD CONSTRAINT messgroesse_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messgroessen_gruppe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY messgroessen_gruppe
+    ADD CONSTRAINT messgroessen_gruppe_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: messprogramm_kategorie_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY messprogramm_kategorie
+    ADD CONSTRAINT messprogramm_kategorie_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: mg_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY mg_grp
+    ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
+
+
+--
+-- Name: mmt_messgroesse_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY mmt_messgroesse_grp
+    ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
+
+
+--
+-- Name: netz_betreiber_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY netz_betreiber
+    ADD CONSTRAINT netz_betreiber_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ort_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ort_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY ort_typ
+    ADD CONSTRAINT ort_typ_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: ortszuordnung_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY ortszuordnung_typ
+    ADD CONSTRAINT ortszuordnung_typ_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: pflicht_messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY pflicht_messgroesse
+    ADD CONSTRAINT pflicht_messgroesse_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: pk_deskriptoren; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY deskriptoren
+    ADD CONSTRAINT pk_deskriptoren PRIMARY KEY (id);
+
+
+--
+-- Name: proben_zusatz_eudf_keyword_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY proben_zusatz
+    ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
+
+
+--
+-- Name: proben_zusatz_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY proben_zusatz
+    ADD CONSTRAINT proben_zusatz_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probenart_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY probenart
+    ADD CONSTRAINT probenart_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: probenehmer_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY probenehmer
+    ADD CONSTRAINT probenehmer_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: query_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY query
+    ADD CONSTRAINT query_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: result_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY result
+    ADD CONSTRAINT result_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY staat
+    ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_kombi_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY status_kombi
+    ADD CONSTRAINT status_kombi_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_reihenfolge_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY status_reihenfolge
+    ADD CONSTRAINT status_reihenfolge_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY status_stufe
+    ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: status_wert_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY status_wert
+    ADD CONSTRAINT status_wert_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY umwelt
+    ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: umwelt_umwelt_bereich_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY umwelt
+    ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
+
+
+--
+-- Name: verwaltungseinheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+ALTER TABLE ONLY verwaltungseinheit
+    ADD CONSTRAINT verwaltungseinheit_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: de_vg_geom_gist; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom);
+
+
+--
+-- Name: fts_stauts_kooin10001; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
+--
+
+CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
+
+
+--
+-- Name: auth_funktion_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
+
+
+--
+-- Name: auth_labor_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
+
+
+--
+-- Name: auth_lst_umw_lst_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth_lst_umw
+    ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
+
+
+--
+-- Name: auth_lst_umw_umw_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth_lst_umw
+    ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
+
+
+--
+-- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
+
+
+--
+-- Name: auth_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY auth
+    ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
+
+
+--
+-- Name: datensatz_erzeuger_mst_id_fkey1; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY datensatz_erzeuger
+    ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
+
+
+--
+-- Name: datensatz_erzeuger_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY datensatz_erzeuger
+    ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
+
+
+--
+-- Name: favorite_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY favorite
+    ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
+
+
+--
+-- Name: favorite_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY favorite
+    ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
+
+
+--
+-- Name: filter_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter
+    ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
+
+
+--
+-- Name: filter_value_filter_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter_value
+    ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
+
+
+--
+-- Name: filter_value_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter_value
+    ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
+
+
+--
+-- Name: filter_value_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY filter_value
+    ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
+
+
+--
+-- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY deskriptoren
+    ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
+
+
+--
+-- Name: messprogramm_kategorie_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY messprogramm_kategorie
+    ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
+
+
+--
+-- Name: ort_anlage_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
+
+
+--
+-- Name: ort_gem_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
+
+
+--
+-- Name: ort_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
+
+
+--
+-- Name: ort_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
+
+
+--
+-- Name: ort_ort_typ_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
+
+
+--
+-- Name: ort_oz_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
+
+
+--
+-- Name: ort_staat_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY ort
+    ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
+
+
+--
+-- Name: pflicht_messgroesse_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY pflicht_messgroesse
+    ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
+
+
+--
+-- Name: pflicht_messgroesse_mmt_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY pflicht_messgroesse
+    ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
+
+
+--
+-- Name: pflicht_messgroesse_umw_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY pflicht_messgroesse
+    ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
+
+
+--
+-- Name: proben_zusatz_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY proben_zusatz
+    ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
+
+
+--
+-- Name: probenehmer_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY probenehmer
+    ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
+
+
+--
+-- Name: result_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY result
+    ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
+
+
+--
+-- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY staat
+    ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
+
+
+--
+-- Name: status_kombi_stufe_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY status_kombi
+    ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id);
+
+
+--
+-- Name: status_kombi_wert_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY status_kombi
+    ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id);
+
+
+--
+-- Name: status_reihenfolge_von_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY status_reihenfolge
+    ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id);
+
+
+--
+-- Name: status_reihenfolge_zu_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY status_reihenfolge
+    ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id);
+
+
+--
+-- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY umwelt
+    ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
+
+
+--
+-- Name: verwaltungseinheit_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
+--
+
+ALTER TABLE ONLY verwaltungseinheit
+    ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
+
+
+COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)