Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 741:c998673c6d1e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add initial DB schema script. Generated with pg_dump -Os -f lada_schema.sql $db_name from a database that was set up from Rev 0978a9c668ee of Intevations internal db scripts repository.
author Tom Gottfried <tom@intevation.de>
date Mon, 26 Oct 2015 15:44:15 +0100
parents
children 37a8f4d157c7
comparison
equal deleted inserted replaced
740:b79afbdadbf7 741:c998673c6d1e
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET statement_timeout = 0;
6 SET lock_timeout = 0;
7 SET client_encoding = 'UTF8';
8 SET standard_conforming_strings = on;
9 SET check_function_bodies = false;
10 SET client_min_messages = warning;
11
12 --
13 -- Name: bund; Type: SCHEMA; Schema: -; Owner: -
14 --
15
16 CREATE SCHEMA bund;
17
18
19 --
20 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
21 --
22
23 CREATE SCHEMA land;
24
25
26 --
27 -- Name: stammdaten; Type: SCHEMA; Schema: -; Owner: -
28 --
29
30 CREATE SCHEMA stammdaten;
31
32
33 --
34 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
35 --
36
37 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
38
39
40 --
41 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
42 --
43
44 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
45
46
47 --
48 -- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
49 --
50
51 CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
52
53
54 --
55 -- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
56 --
57
58 COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';
59
60
61 SET search_path = bund, pg_catalog;
62
63 --
64 -- Name: is_kommentar_unique(); Type: FUNCTION; Schema: bund; Owner: -
65 --
66
67 CREATE FUNCTION is_kommentar_unique() RETURNS trigger
68 LANGUAGE plpgsql
69 AS $$
70 BEGIN
71 -- Check that empname and salary are given
72 IF NEW.id IN (SELECT id from bund.kommentar) THEN
73 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
74 ELSE
75 RETURN NEW;
76 END IF;
77 END;
78 $$;
79
80
81 --
82 -- Name: is_messung_unique(); Type: FUNCTION; Schema: bund; Owner: -
83 --
84
85 CREATE FUNCTION is_messung_unique() RETURNS trigger
86 LANGUAGE plpgsql
87 AS $$
88 BEGIN
89 -- Check that empname and salary are given
90 IF NEW.id IN (SELECT id from bund.messung) THEN
91 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messung', NEW.id;
92 ELSE
93 RETURN NEW;
94 END IF;
95 END;
96 $$;
97
98
99 --
100 -- Name: is_messwert_unique(); Type: FUNCTION; Schema: bund; Owner: -
101 --
102
103 CREATE FUNCTION is_messwert_unique() RETURNS trigger
104 LANGUAGE plpgsql
105 AS $$
106 BEGIN
107 -- Check that empname and salary are given
108 IF NEW.id IN (SELECT id from bund.messwert) THEN
109 RAISE EXCEPTION 'Key (id)=(%) already present in bund.messwert', NEW.id;
110 ELSE
111 RETURN NEW;
112 END IF;
113 END;
114 $$;
115
116
117 --
118 -- Name: is_ort_unique(); Type: FUNCTION; Schema: bund; Owner: -
119 --
120
121 CREATE FUNCTION is_ort_unique() RETURNS trigger
122 LANGUAGE plpgsql
123 AS $$
124 BEGIN
125 -- Check that empname and salary are given
126 IF NEW.id IN (SELECT id from bund.ort) THEN
127 RAISE EXCEPTION 'Key (id)=(%) already present in bund.ort', NEW.id;
128 ELSE
129 RETURN NEW;
130 END IF;
131 END;
132 $$;
133
134
135 --
136 -- Name: is_probe_unique(); Type: FUNCTION; Schema: bund; Owner: -
137 --
138
139 CREATE FUNCTION is_probe_unique() RETURNS trigger
140 LANGUAGE plpgsql
141 AS $$
142 BEGIN
143 -- Check that empname and salary are given
144 IF NEW.id IN (SELECT id from bund.probe) THEN
145 RAISE EXCEPTION 'Key (id)=(%) already present in bund.probe', NEW.id;
146 ELSE
147 RETURN NEW;
148 END IF;
149 END;
150 $$;
151
152
153 --
154 -- Name: is_status_unique(); Type: FUNCTION; Schema: bund; Owner: -
155 --
156
157 CREATE FUNCTION is_status_unique() RETURNS trigger
158 LANGUAGE plpgsql
159 AS $$
160 BEGIN
161 -- Check that empname and salary are given
162 IF NEW.id IN (SELECT id from bund.status) THEN
163 RAISE EXCEPTION 'Key (id)=(%) already present in bund.status', NEW.id;
164 ELSE
165 RETURN NEW;
166 END IF;
167 END;
168 $$;
169
170
171 --
172 -- Name: is_zusatz_wert_unique(); Type: FUNCTION; Schema: bund; Owner: -
173 --
174
175 CREATE FUNCTION is_zusatz_wert_unique() RETURNS trigger
176 LANGUAGE plpgsql
177 AS $$
178 BEGIN
179 -- Check that empname and salary are given
180 IF NEW.id IN (SELECT id from bund.zusatz_wert) THEN
181 RAISE EXCEPTION 'Key (id)=(%) already present in bund.zusatz_wert', NEW.id;
182 ELSE
183 RETURN NEW;
184 END IF;
185 END;
186 $$;
187
188
189 SET search_path = land, pg_catalog;
190
191 --
192 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
193 --
194
195 CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
196 LANGUAGE plpgsql STABLE SECURITY DEFINER
197 AS $_$
198 DECLARE result BOOLEAN;
199 BEGIN
200 SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE;
201 RETURN result;
202 END;
203 $_$;
204
205
206 --
207 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
208 --
209
210 CREATE FUNCTION update_time_messung() RETURNS trigger
211 LANGUAGE plpgsql
212 AS $$
213 BEGIN
214 RAISE NOTICE 'messung is %',NEW.id;
215 NEW.tree_modified = now();
216 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
217 UPDATE land.status SET tree_modified = now() WHERE messungs_id = NEW.id;
218 RETURN NEW;
219 END;
220 $$;
221
222
223 --
224 -- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
225 --
226
227 CREATE FUNCTION update_time_messwert() RETURNS trigger
228 LANGUAGE plpgsql
229 AS $$
230 BEGIN
231 NEW.tree_modified = now();
232 RETURN NEW;
233 END;
234 $$;
235
236
237 --
238 -- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
239 --
240
241 CREATE FUNCTION update_time_ort() RETURNS trigger
242 LANGUAGE plpgsql
243 AS $$
244 BEGIN
245 NEW.tree_modified = now();
246 RETURN NEW;
247 END;
248 $$;
249
250
251 --
252 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
253 --
254
255 CREATE FUNCTION update_time_probe() RETURNS trigger
256 LANGUAGE plpgsql
257 AS $$
258 BEGIN
259 RAISE NOTICE 'probe is %',NEW.id;
260 NEW.tree_modified = now();
261 RAISE NOTICE 'updating other rows';
262 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
263 UPDATE land.ort SET tree_modified = now() WHERE probe_id = NEW.id;
264 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
265 RETURN NEW;
266 END;
267 $$;
268
269
270 --
271 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
272 --
273
274 CREATE FUNCTION update_time_status() RETURNS trigger
275 LANGUAGE plpgsql
276 AS $$
277 BEGIN
278 NEW.tree_modified = now();
279 RETURN NEW;
280 END;
281 $$;
282
283
284 --
285 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
286 --
287
288 CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
289 LANGUAGE plpgsql
290 AS $$
291 BEGIN
292 NEW.tree_modified = now();
293 RETURN NEW;
294 END;
295 $$;
296
297
298 SET search_path = stammdaten, pg_catalog;
299
300 --
301 -- Name: get_media_from_media_desk(character varying); Type: FUNCTION; Schema: stammdaten; Owner: -
302 --
303
304 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
305 LANGUAGE plpgsql
306 AS $$
307 declare
308 result character varying(100);
309 d00 smallint;
310 d01 smallint;
311 d02 smallint;
312 d03 smallint;
313 begin
314 if media_desk like 'D: %' then
315 d00 := substring(media_desk,4,2);
316 d01 := substring(media_desk,7,2);
317 d02 := substring(media_desk,10,2);
318 d03 := substring(media_desk,13,2);
319 if d00 = '00' then
320 result := null;
321 else
322 if d01 = '00' then
323 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
324 where s00.ebene = 0 and s00.sn = d00::smallint;
325 else
326 if d02 = '00' or d00 <> '01' then
327 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
328 where s01.ebene = 1 and s01.sn = d01::smallint
329 and s01.vorgaenger =
330 (select s00.id FROM stammdaten.deskriptoren s00
331 where s00.ebene = 0 and s00.sn = d00::smallint);
332 else
333 if d03 = '00' then
334 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
335 where s02.ebene = 2 and s02.sn = d02::smallint
336 and s02.vorgaenger =
337 (select s01.id FROM stammdaten.deskriptoren s01
338 where s01.ebene = 1 and s01.sn = d01::smallint
339 and s01.vorgaenger =
340 (select s00.id FROM stammdaten.deskriptoren s00
341 where s00.ebene = 0 and s00.sn = d00::smallint));
342 else
343 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
344 where s03.ebene = 3 and s03.sn = d03::smallint
345 and s03.vorgaenger =
346 (select s02.id FROM stammdaten.deskriptoren s02
347 where s02.ebene = 2 and s02.sn = d02::smallint
348 and s02.vorgaenger =
349 (select s01.id FROM stammdaten.deskriptoren s01
350 where s01.ebene = 1 and s01.sn = d01::smallint
351 and s01.vorgaenger =
352 (select s00.id FROM stammdaten.deskriptoren s00
353 where s00.ebene = 0 and s00.sn = d00::smallint)));
354 end if;
355 end if;
356 end if;
357 end if;
358 else
359 result := null;
360 end if;
361 return (result);
362 end;
363 $$;
364
365
366 SET search_path = bund, pg_catalog;
367
368 --
369 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
370 --
371
372 CREATE SEQUENCE kommentar_id_seq
373 START WITH 1
374 INCREMENT BY 1
375 NO MINVALUE
376 NO MAXVALUE
377 CACHE 1;
378
379
380 SET default_tablespace = '';
381
382 SET default_with_oids = false;
383
384 --
385 -- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace:
386 --
387
388 CREATE TABLE kommentar (
389 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL,
390 erzeuger character varying(5) NOT NULL,
391 datum timestamp without time zone DEFAULT now(),
392 text character varying(1024)
393 );
394
395
396 --
397 -- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace:
398 --
399
400 CREATE TABLE kommentar_m (
401 messungs_id integer NOT NULL
402 )
403 INHERITS (kommentar);
404
405
406 --
407 -- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace:
408 --
409
410 CREATE TABLE kommentar_p (
411 probe_id integer NOT NULL
412 )
413 INHERITS (kommentar);
414
415
416 --
417 -- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace:
418 --
419
420 CREATE TABLE list (
421 id integer NOT NULL,
422 typ character(1) NOT NULL,
423 bezeichnung character varying(20) NOT NULL,
424 beschreibuing character varying(512),
425 letzte_aenderung timestamp with time zone NOT NULL,
426 gueltig_bis timestamp with time zone
427 );
428
429
430 --
431 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
432 --
433
434 CREATE SEQUENCE list_id_seq
435 START WITH 1
436 INCREMENT BY 1
437 NO MINVALUE
438 NO MAXVALUE
439 CACHE 1;
440
441
442 --
443 -- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: -
444 --
445
446 ALTER SEQUENCE list_id_seq OWNED BY list.id;
447
448
449 --
450 -- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
451 --
452
453 CREATE TABLE list_zuordnung (
454 list_id integer NOT NULL,
455 probe_id integer
456 );
457
458
459 --
460 -- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
461 --
462
463 CREATE SEQUENCE messung_id_seq
464 START WITH 1
465 INCREMENT BY 1
466 NO MINVALUE
467 NO MAXVALUE
468 CACHE 1;
469
470
471 --
472 -- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
473 --
474
475 CREATE TABLE messung (
476 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL,
477 probe_id integer NOT NULL,
478 nebenproben_nr character varying(10),
479 mmt_id character varying(2) NOT NULL,
480 messdauer integer,
481 messzeitpunkt timestamp with time zone,
482 fertig boolean DEFAULT false NOT NULL,
483 letzte_aenderung timestamp without time zone DEFAULT now()
484 );
485
486
487 --
488 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: -
489 --
490
491 CREATE SEQUENCE messung_messung_id_alt_seq
492 START WITH 1
493 INCREMENT BY 1
494 NO MINVALUE
495 NO MAXVALUE
496 CACHE 1;
497
498
499 --
500 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
501 --
502
503 CREATE SEQUENCE messung_messungs_id_seq
504 START WITH 1
505 INCREMENT BY 1
506 NO MINVALUE
507 NO MAXVALUE
508 CACHE 1;
509
510
511 --
512 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
513 --
514
515 CREATE SEQUENCE messwert_id_seq
516 START WITH 1
517 INCREMENT BY 1
518 NO MINVALUE
519 NO MAXVALUE
520 CACHE 1;
521
522
523 --
524 -- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
525 --
526
527 CREATE TABLE messwert (
528 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL,
529 messungs_id integer NOT NULL,
530 messgroesse_id integer NOT NULL,
531 messwert_nwg character varying(1),
532 messwert real NOT NULL,
533 messfehler real,
534 nwg_zu_messwert real,
535 meh_id smallint NOT NULL,
536 grenzwertueberschreitung boolean DEFAULT false,
537 letzte_aenderung timestamp without time zone DEFAULT now()
538 );
539
540
541 --
542 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
543 --
544
545 CREATE SEQUENCE ort_id_seq
546 START WITH 1
547 INCREMENT BY 1
548 NO MINVALUE
549 NO MAXVALUE
550 CACHE 1;
551
552
553 --
554 -- Name: ort; Type: TABLE; Schema: bund; Owner: -; Tablespace:
555 --
556
557 CREATE TABLE ort (
558 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL,
559 probe_id integer NOT NULL,
560 ort_id bigint NOT NULL,
561 orts_typ character varying(1),
562 ortszusatztext character varying(100),
563 letzte_aenderung timestamp without time zone DEFAULT now()
564 );
565
566
567 --
568 -- Name: COLUMN ort.orts_typ; Type: COMMENT; Schema: bund; Owner: -
569 --
570
571 COMMENT ON COLUMN ort.orts_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
572
573
574 --
575 -- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
576 --
577
578 CREATE SEQUENCE probe_id_seq
579 START WITH 1
580 INCREMENT BY 1
581 NO MINVALUE
582 NO MAXVALUE
583 CACHE 1;
584
585
586 --
587 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace:
588 --
589
590 CREATE TABLE probe (
591 id integer DEFAULT nextval('probe_id_seq'::regclass) NOT NULL,
592 test boolean DEFAULT false NOT NULL,
593 netzbetreiber_id character varying(2),
594 mst_id character varying(5),
595 hauptproben_nr character varying(20),
596 datenbasis_id smallint,
597 ba_id character varying(1),
598 probenart_id smallint NOT NULL,
599 media_desk character varying(100),
600 media character varying(100),
601 umw_id character varying(3),
602 probeentnahme_beginn timestamp with time zone,
603 probeentnahme_ende timestamp with time zone,
604 mittelungsdauer bigint,
605 letzte_aenderung timestamp without time zone DEFAULT now()
606 );
607
608
609 --
610 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: -
611 --
612
613 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
614
615
616 --
617 -- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: -
618 --
619
620 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
621
622
623 --
624 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
625 --
626
627 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle/Messlabor';
628
629
630 --
631 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: -
632 --
633
634 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
635
636
637 --
638 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: -
639 --
640
641 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
642
643
644 --
645 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: -
646 --
647
648 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
649
650
651 --
652 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: -
653 --
654
655 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
656
657
658 --
659 -- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: -
660 --
661
662 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
663
664
665 --
666 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: -
667 --
668
669 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
670
671
672 --
673 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
674 --
675
676 CREATE SEQUENCE probe_probe_id_seq
677 START WITH 1
678 INCREMENT BY 1
679 NO MINVALUE
680 NO MAXVALUE
681 CACHE 1;
682
683
684 --
685 -- Name: status_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
686 --
687
688 CREATE SEQUENCE status_id_seq
689 START WITH 1
690 INCREMENT BY 1
691 NO MINVALUE
692 NO MAXVALUE
693 CACHE 1;
694
695
696 --
697 -- Name: status; Type: TABLE; Schema: bund; Owner: -; Tablespace:
698 --
699
700 CREATE TABLE status (
701 id integer DEFAULT nextval('status_id_seq'::regclass) NOT NULL,
702 messungs_id integer NOT NULL,
703 erzeuger character varying(5) NOT NULL,
704 status smallint,
705 s_datum timestamp with time zone NOT NULL,
706 s_kommentar character varying(1024)
707 );
708
709
710 --
711 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
712 --
713
714 CREATE SEQUENCE zusatz_wert_id_seq
715 START WITH 1
716 INCREMENT BY 1
717 NO MINVALUE
718 NO MAXVALUE
719 CACHE 1;
720
721
722 --
723 -- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
724 --
725
726 CREATE TABLE zusatz_wert (
727 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL,
728 probe_id integer NOT NULL,
729 pzs_id character varying(3) NOT NULL,
730 messwert_pzs real,
731 messfehler real,
732 letzte_aenderung timestamp without time zone DEFAULT now(),
733 nwg_zu_messwert real
734 );
735
736
737 SET search_path = land, pg_catalog;
738
739 --
740 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
741 --
742
743 CREATE TABLE kommentar_m (
744 )
745 INHERITS (bund.kommentar_m);
746
747
748 --
749 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
750 --
751
752 CREATE TABLE kommentar_p (
753 )
754 INHERITS (bund.kommentar_p);
755
756
757 --
758 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
759 --
760
761 CREATE TABLE messprogramm (
762 id integer NOT NULL,
763 name character varying(256),
764 test boolean DEFAULT false NOT NULL,
765 netzbetreiber_id character varying(2) NOT NULL,
766 mst_id character varying(5) NOT NULL,
767 datenbasis_id integer NOT NULL,
768 ba_id character varying(1),
769 gem_id character varying(8),
770 ort_id integer,
771 media_desk character varying(100),
772 umw_id character varying(3),
773 probenart_id integer NOT NULL,
774 probenintervall character varying(2),
775 teilintervall_von integer,
776 teilintervall_bis integer,
777 intervall_offset integer,
778 gueltig_von integer,
779 gueltig_bis integer,
780 probe_nehmer_id integer,
781 probe_kommentar character varying(80),
782 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
783 );
784
785
786 --
787 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
788 --
789
790 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
791
792
793 --
794 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: -
795 --
796
797 CREATE SEQUENCE messprogramm_id_seq
798 START WITH 1
799 INCREMENT BY 1
800 NO MINVALUE
801 NO MAXVALUE
802 CACHE 1;
803
804
805 --
806 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
807 --
808
809 ALTER SEQUENCE messprogramm_id_seq OWNED BY messprogramm.id;
810
811
812 --
813 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
814 --
815
816 CREATE TABLE messprogramm_mmt (
817 id integer NOT NULL,
818 messprogramm_id integer NOT NULL,
819 mmt_id character varying(2) NOT NULL,
820 messgroessen integer[],
821 letzte_aenderung timestamp without time zone DEFAULT now()
822 );
823
824
825 --
826 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: -
827 --
828
829 CREATE SEQUENCE messprogramm_mmt_id_seq
830 START WITH 1
831 INCREMENT BY 1
832 NO MINVALUE
833 NO MAXVALUE
834 CACHE 1;
835
836
837 --
838 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
839 --
840
841 ALTER SEQUENCE messprogramm_mmt_id_seq OWNED BY messprogramm_mmt.id;
842
843
844 --
845 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
846 --
847
848 CREATE TABLE messung (
849 geplant boolean DEFAULT false NOT NULL,
850 tree_modified timestamp without time zone DEFAULT now()
851 )
852 INHERITS (bund.messung);
853
854
855 --
856 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
857 --
858
859 CREATE TABLE messung_translation (
860 id integer NOT NULL,
861 messungs_id integer NOT NULL,
862 messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL
863 );
864
865
866 --
867 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
868 --
869
870 CREATE SEQUENCE messung_translation_id_seq
871 START WITH 1
872 INCREMENT BY 1
873 NO MINVALUE
874 NO MAXVALUE
875 CACHE 1;
876
877
878 --
879 -- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
880 --
881
882 ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id;
883
884
885 --
886 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
887 --
888
889 CREATE TABLE messwert (
890 tree_modified timestamp without time zone DEFAULT now()
891 )
892 INHERITS (bund.messwert);
893
894
895 --
896 -- Name: ort; Type: TABLE; Schema: land; Owner: -; Tablespace:
897 --
898
899 CREATE TABLE ort (
900 tree_modified timestamp without time zone DEFAULT now()
901 )
902 INHERITS (bund.ort);
903
904
905 --
906 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
907 --
908
909 CREATE TABLE probe (
910 erzeuger_id integer,
911 probe_nehmer_id integer,
912 mp_kat character(1),
913 mpl_id character varying(3),
914 mpr_id integer,
915 solldatum_beginn timestamp without time zone,
916 solldatum_ende timestamp without time zone,
917 tree_modified timestamp without time zone DEFAULT now()
918 )
919 INHERITS (bund.probe);
920
921
922 --
923 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
924 --
925
926 CREATE TABLE probe_translation (
927 id integer NOT NULL,
928 probe_id integer NOT NULL,
929 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
930 );
931
932
933 --
934 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
935 --
936
937 CREATE SEQUENCE probe_translation_id_seq
938 START WITH 1
939 INCREMENT BY 1
940 NO MINVALUE
941 NO MAXVALUE
942 CACHE 1;
943
944
945 --
946 -- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
947 --
948
949 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id;
950
951
952 --
953 -- Name: status; Type: TABLE; Schema: land; Owner: -; Tablespace:
954 --
955
956 CREATE TABLE status (
957 tree_modified timestamp without time zone DEFAULT now()
958 )
959 INHERITS (bund.status);
960
961
962 --
963 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
964 --
965
966 CREATE TABLE zusatz_wert (
967 tree_modified timestamp without time zone DEFAULT now()
968 )
969 INHERITS (bund.zusatz_wert);
970
971
972 SET search_path = stammdaten, pg_catalog;
973
974 --
975 -- Name: auth; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
976 --
977
978 CREATE TABLE auth (
979 id integer NOT NULL,
980 ldap_group character varying(40) NOT NULL,
981 netzbetreiber_id character varying(2),
982 mst_id character varying(5)
983 );
984
985
986 --
987 -- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
988 --
989
990 CREATE SEQUENCE auth_id_seq
991 START WITH 1
992 INCREMENT BY 1
993 NO MINVALUE
994 NO MAXVALUE
995 CACHE 1;
996
997
998 --
999 -- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1000 --
1001
1002 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
1003
1004
1005 --
1006 -- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1007 --
1008
1009 CREATE TABLE datenbasis (
1010 id integer NOT NULL,
1011 beschreibung character varying(30),
1012 datenbasis character varying(6)
1013 );
1014
1015
1016 --
1017 -- Name: datenbasis_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1018 --
1019
1020 CREATE SEQUENCE datenbasis_id_seq
1021 START WITH 1
1022 INCREMENT BY 1
1023 NO MINVALUE
1024 NO MAXVALUE
1025 CACHE 1;
1026
1027
1028 --
1029 -- Name: datenbasis_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1030 --
1031
1032 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
1033
1034
1035 --
1036 -- Name: de_vg; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1037 --
1038
1039 CREATE TABLE de_vg (
1040 id integer NOT NULL,
1041 use double precision,
1042 rs character varying(12),
1043 gf double precision,
1044 rau_rs character varying(12),
1045 gen character varying(50),
1046 des character varying(75),
1047 isn double precision,
1048 bemerk character varying(75),
1049 nambild character varying(16),
1050 ags character varying(12),
1051 rs_alt character varying(20),
1052 wirksamkei date,
1053 debkg_id character varying(16),
1054 length numeric,
1055 shape_area numeric,
1056 geom public.geometry(MultiPolygon,4326)
1057 );
1058
1059
1060 --
1061 -- Name: de_vg_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1062 --
1063
1064 CREATE SEQUENCE de_vg_id_seq
1065 START WITH 1
1066 INCREMENT BY 1
1067 NO MINVALUE
1068 NO MAXVALUE
1069 CACHE 1;
1070
1071
1072 --
1073 -- Name: de_vg_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1074 --
1075
1076 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
1077
1078
1079 --
1080 -- Name: deskriptor_umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1081 --
1082
1083 CREATE TABLE deskriptor_umwelt (
1084 id integer NOT NULL,
1085 s00 integer NOT NULL,
1086 s01 integer NOT NULL,
1087 s02 integer,
1088 s03 integer,
1089 s04 integer,
1090 s05 integer,
1091 s06 integer,
1092 s07 integer,
1093 s08 integer,
1094 s09 integer,
1095 s10 integer,
1096 s11 integer,
1097 s12 integer,
1098 umw_id character varying(3) NOT NULL
1099 );
1100
1101
1102 --
1103 -- Name: deskriptoren; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1104 --
1105
1106 CREATE TABLE deskriptoren (
1107 id integer NOT NULL,
1108 vorgaenger integer,
1109 ebene smallint,
1110 s_xx integer,
1111 sn smallint,
1112 beschreibung character varying(100),
1113 bedeutung character varying(300)
1114 );
1115
1116
1117 --
1118 -- Name: deskriptoren_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1119 --
1120
1121 CREATE SEQUENCE deskriptoren_id_seq
1122 START WITH 1
1123 INCREMENT BY 1
1124 NO MINVALUE
1125 NO MAXVALUE
1126 CACHE 1;
1127
1128
1129 --
1130 -- Name: deskriptoren_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1131 --
1132
1133 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
1134
1135
1136 --
1137 -- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1138 --
1139
1140 CREATE TABLE koordinaten_art (
1141 id integer NOT NULL,
1142 koordinatenart character varying(50),
1143 idf_geo_key character varying(1)
1144 );
1145
1146
1147 --
1148 -- Name: koordinaten_art_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1149 --
1150
1151 CREATE SEQUENCE koordinaten_art_id_seq
1152 START WITH 1
1153 INCREMENT BY 1
1154 NO MINVALUE
1155 NO MAXVALUE
1156 CACHE 1;
1157
1158
1159 --
1160 -- Name: koordinaten_art_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1161 --
1162
1163 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
1164
1165
1166 --
1167 -- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1168 --
1169
1170 CREATE TABLE mess_einheit (
1171 id integer NOT NULL,
1172 beschreibung character varying(50),
1173 einheit character varying(12),
1174 eudf_messeinheit_id character varying(8),
1175 umrechnungs_faktor_eudf bigint
1176 );
1177
1178
1179 --
1180 -- Name: mess_einheit_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1181 --
1182
1183 CREATE SEQUENCE mess_einheit_id_seq
1184 START WITH 1
1185 INCREMENT BY 1
1186 NO MINVALUE
1187 NO MAXVALUE
1188 CACHE 1;
1189
1190
1191 --
1192 -- Name: mess_einheit_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1193 --
1194
1195 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
1196
1197
1198 --
1199 -- Name: mess_methode; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1200 --
1201
1202 CREATE TABLE mess_methode (
1203 id character varying(2) NOT NULL,
1204 beschreibung character varying(300),
1205 messmethode character varying(50)
1206 );
1207
1208
1209 --
1210 -- Name: mess_stelle; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1211 --
1212
1213 CREATE TABLE mess_stelle (
1214 id character varying(5) NOT NULL,
1215 netzbetreiber_id character varying(2),
1216 beschreibung character varying(300),
1217 mess_stelle character varying(60),
1218 mst_typ character varying(1),
1219 amtskennung character varying(6)
1220 );
1221
1222
1223 --
1224 -- Name: messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1225 --
1226
1227 CREATE TABLE messgroesse (
1228 id integer NOT NULL,
1229 beschreibung character varying(300),
1230 messgroesse character varying(50) NOT NULL,
1231 default_farbe character varying(9),
1232 idf_nuklid_key character varying(6),
1233 ist_leitnuklid boolean DEFAULT false,
1234 eudf_nuklid_id bigint,
1235 kennung_bvl character varying(7)
1236 );
1237
1238
1239 --
1240 -- Name: messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1241 --
1242
1243 CREATE SEQUENCE messgroesse_id_seq
1244 START WITH 1
1245 INCREMENT BY 1
1246 NO MINVALUE
1247 NO MAXVALUE
1248 CACHE 1;
1249
1250
1251 --
1252 -- Name: messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1253 --
1254
1255 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
1256
1257
1258 --
1259 -- Name: messgroessen_gruppe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1260 --
1261
1262 CREATE TABLE messgroessen_gruppe (
1263 id integer NOT NULL,
1264 bezeichnung character varying(80),
1265 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
1266 );
1267
1268
1269 --
1270 -- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1271 --
1272
1273 CREATE SEQUENCE messgroessen_gruppe_id_seq
1274 START WITH 1
1275 INCREMENT BY 1
1276 NO MINVALUE
1277 NO MAXVALUE
1278 CACHE 1;
1279
1280
1281 --
1282 -- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1283 --
1284
1285 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
1286
1287
1288 --
1289 -- Name: mg_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1290 --
1291
1292 CREATE TABLE mg_grp (
1293 messgroessengruppe_id integer NOT NULL,
1294 messgroesse_id integer NOT NULL
1295 );
1296
1297
1298 --
1299 -- Name: mmt_messgroesse_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1300 --
1301
1302 CREATE TABLE mmt_messgroesse_grp (
1303 messgroessengruppe_id integer NOT NULL,
1304 mmt_id character varying(2) NOT NULL
1305 );
1306
1307
1308 --
1309 -- Name: mmt_messgroesse; Type: VIEW; Schema: stammdaten; Owner: -
1310 --
1311
1312 CREATE VIEW mmt_messgroesse AS
1313 SELECT mmt_messgroesse_grp.mmt_id,
1314 mg_grp.messgroesse_id
1315 FROM mmt_messgroesse_grp,
1316 mg_grp
1317 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
1318
1319
1320 --
1321 -- Name: netz_betreiber; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1322 --
1323
1324 CREATE TABLE netz_betreiber (
1325 id character varying(2) NOT NULL,
1326 netzbetreiber character varying(50),
1327 idf_netzbetreiber character varying(1),
1328 is_bmn boolean DEFAULT false,
1329 mailverteiler character varying(512),
1330 aktiv boolean DEFAULT false,
1331 zust_mst_id character varying(5)
1332 );
1333
1334
1335 --
1336 -- Name: ort; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1337 --
1338
1339 CREATE TABLE ort (
1340 id integer NOT NULL,
1341 o_typ character varying(1),
1342 netzbetreiber_id character varying(2),
1343 bezeichnung character varying(10),
1344 beschreibung character varying(100),
1345 staat_id smallint,
1346 gem_id character varying(8),
1347 unscharf character(1) DEFAULT NULL::bpchar,
1348 nuts_code character varying(10),
1349 kda_id integer,
1350 koord_x_extern character varying(22),
1351 koord_y_extern character varying(22),
1352 hoehe_land real,
1353 letzte_aenderung timestamp without time zone DEFAULT now(),
1354 latitude double precision,
1355 longitude double precision,
1356 geom public.geometry(Point,4326),
1357 shape public.geometry(MultiPolygon,4326)
1358 );
1359
1360
1361 --
1362 -- Name: COLUMN ort.o_typ; Type: COMMENT; Schema: stammdaten; Owner: -
1363 --
1364
1365 COMMENT ON COLUMN ort.o_typ IS 'D = dynamischer Messpunkt (nicht vordefiniert)
1366 V = vordefinierter Messpunkt
1367 R = REI-Messpunkt
1368 S = Station
1369 Z = Ortzszusatz';
1370
1371
1372 --
1373 -- Name: ort_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1374 --
1375
1376 CREATE SEQUENCE ort_id_seq
1377 START WITH 1
1378 INCREMENT BY 1
1379 NO MINVALUE
1380 NO MAXVALUE
1381 CACHE 1;
1382
1383
1384 --
1385 -- Name: ort_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1386 --
1387
1388 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
1389
1390
1391 --
1392 -- Name: pflicht_messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1393 --
1394
1395 CREATE TABLE pflicht_messgroesse (
1396 id integer NOT NULL,
1397 messgroesse_id integer,
1398 mmt_id character varying(2),
1399 umw_id character varying(3),
1400 datenbasis_id smallint NOT NULL
1401 );
1402
1403
1404 --
1405 -- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1406 --
1407
1408 CREATE SEQUENCE pflicht_messgroesse_id_seq
1409 START WITH 1
1410 INCREMENT BY 1
1411 NO MINVALUE
1412 NO MAXVALUE
1413 CACHE 1;
1414
1415
1416 --
1417 -- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1418 --
1419
1420 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
1421
1422
1423 --
1424 -- Name: proben_zusatz; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1425 --
1426
1427 CREATE TABLE proben_zusatz (
1428 id character varying(3) NOT NULL,
1429 meh_id integer,
1430 beschreibung character varying(50) NOT NULL,
1431 zusatzwert character varying(7) NOT NULL,
1432 eudf_keyword character varying(40)
1433 );
1434
1435
1436 --
1437 -- Name: probenart; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1438 --
1439
1440 CREATE TABLE probenart (
1441 id integer NOT NULL,
1442 beschreibung character varying(30),
1443 probenart character varying(5) NOT NULL,
1444 probenart_eudf_id character varying(1) NOT NULL
1445 );
1446
1447
1448 --
1449 -- Name: probenart_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1450 --
1451
1452 CREATE SEQUENCE probenart_id_seq
1453 START WITH 1
1454 INCREMENT BY 1
1455 NO MINVALUE
1456 NO MAXVALUE
1457 CACHE 1;
1458
1459
1460 --
1461 -- Name: probenart_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1462 --
1463
1464 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
1465
1466
1467 --
1468 -- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: -
1469 --
1470
1471 CREATE VIEW s_00_view AS
1472 SELECT deskriptoren.s_xx AS s00,
1473 deskriptoren.bedeutung,
1474 deskriptoren.beschreibung,
1475 deskriptoren.sn
1476 FROM deskriptoren
1477 WHERE (deskriptoren.ebene = 0);
1478
1479
1480 --
1481 -- Name: s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1482 --
1483
1484 CREATE VIEW s_01_view AS
1485 SELECT d1.s_xx AS s01,
1486 d2.s_xx AS s00,
1487 d1.bedeutung,
1488 d1.beschreibung,
1489 d1.sn
1490 FROM (deskriptoren d1
1491 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1492 WHERE (d1.ebene = 1);
1493
1494
1495 --
1496 -- Name: s_02_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1497 --
1498
1499 CREATE VIEW s_02_s_01_view AS
1500 SELECT d1.s_xx AS s01,
1501 d2.s_xx AS s02
1502 FROM (deskriptoren d1
1503 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1504 WHERE (d2.ebene = 2);
1505
1506
1507 --
1508 -- Name: s_02_view; Type: VIEW; Schema: stammdaten; Owner: -
1509 --
1510
1511 CREATE VIEW s_02_view AS
1512 SELECT DISTINCT deskriptoren.s_xx AS s00,
1513 deskriptoren.bedeutung,
1514 deskriptoren.beschreibung,
1515 deskriptoren.sn
1516 FROM deskriptoren
1517 WHERE (deskriptoren.ebene = 2);
1518
1519
1520 --
1521 -- Name: s_03_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1522 --
1523
1524 CREATE VIEW s_03_s_01_view AS
1525 SELECT d1.s_xx AS s01,
1526 d2.s_xx AS s03
1527 FROM (deskriptoren d1
1528 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1529 WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
1530
1531
1532 --
1533 -- Name: s_03_view; Type: VIEW; Schema: stammdaten; Owner: -
1534 --
1535
1536 CREATE VIEW s_03_view AS
1537 SELECT d1.s_xx AS s03,
1538 d2.s_xx AS s02,
1539 d1.bedeutung,
1540 d1.beschreibung,
1541 d1.sn
1542 FROM (deskriptoren d1
1543 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1544 WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
1545 UNION
1546 SELECT d1.s_xx AS s03,
1547 NULL::integer AS s02,
1548 d1.bedeutung,
1549 d1.beschreibung,
1550 d1.sn
1551 FROM (deskriptoren d1
1552 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1553 WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
1554
1555
1556 --
1557 -- Name: s_04_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1558 --
1559
1560 CREATE VIEW s_04_s_01_view AS
1561 SELECT DISTINCT d1.s_xx AS s01,
1562 d2.s_xx AS s04
1563 FROM (deskriptoren d1
1564 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1565 WHERE (d2.ebene = 4);
1566
1567
1568 --
1569 -- Name: s_04_view; Type: VIEW; Schema: stammdaten; Owner: -
1570 --
1571
1572 CREATE VIEW s_04_view AS
1573 SELECT DISTINCT deskriptoren.s_xx AS s04,
1574 deskriptoren.bedeutung,
1575 deskriptoren.beschreibung,
1576 deskriptoren.sn
1577 FROM deskriptoren
1578 WHERE (deskriptoren.ebene = 4);
1579
1580
1581 --
1582 -- Name: s_05_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1583 --
1584
1585 CREATE VIEW s_05_s_01_view AS
1586 SELECT d1.s_xx AS s01,
1587 d2.s_xx AS s05
1588 FROM (deskriptoren d1
1589 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1590 WHERE (d2.ebene = 5);
1591
1592
1593 --
1594 -- Name: s_05_view; Type: VIEW; Schema: stammdaten; Owner: -
1595 --
1596
1597 CREATE VIEW s_05_view AS
1598 SELECT DISTINCT deskriptoren.s_xx AS s05,
1599 deskriptoren.bedeutung,
1600 deskriptoren.beschreibung,
1601 deskriptoren.sn
1602 FROM deskriptoren
1603 WHERE (deskriptoren.ebene = 5);
1604
1605
1606 --
1607 -- Name: s_06_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1608 --
1609
1610 CREATE VIEW s_06_s_01_view AS
1611 SELECT d1.s_xx AS s01,
1612 d2.s_xx AS s06
1613 FROM (deskriptoren d1
1614 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1615 WHERE (d2.ebene = 6);
1616
1617
1618 --
1619 -- Name: s_06_view; Type: VIEW; Schema: stammdaten; Owner: -
1620 --
1621
1622 CREATE VIEW s_06_view AS
1623 SELECT DISTINCT deskriptoren.s_xx AS s06,
1624 deskriptoren.bedeutung,
1625 deskriptoren.beschreibung,
1626 deskriptoren.sn
1627 FROM deskriptoren
1628 WHERE (deskriptoren.ebene = 6);
1629
1630
1631 --
1632 -- Name: s_07_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1633 --
1634
1635 CREATE VIEW s_07_s_01_view AS
1636 SELECT d1.s_xx AS s01,
1637 d2.s_xx AS s07
1638 FROM (deskriptoren d1
1639 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1640 WHERE (d2.ebene = 7);
1641
1642
1643 --
1644 -- Name: s_07_view; Type: VIEW; Schema: stammdaten; Owner: -
1645 --
1646
1647 CREATE VIEW s_07_view AS
1648 SELECT DISTINCT deskriptoren.s_xx AS s07,
1649 deskriptoren.bedeutung,
1650 deskriptoren.beschreibung,
1651 deskriptoren.sn
1652 FROM deskriptoren
1653 WHERE (deskriptoren.ebene = 7);
1654
1655
1656 --
1657 -- Name: s_08_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1658 --
1659
1660 CREATE VIEW s_08_s_01_view AS
1661 SELECT d1.s_xx AS s01,
1662 d2.s_xx AS s08
1663 FROM (deskriptoren d1
1664 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1665 WHERE (d2.ebene = 8);
1666
1667
1668 --
1669 -- Name: s_08_view; Type: VIEW; Schema: stammdaten; Owner: -
1670 --
1671
1672 CREATE VIEW s_08_view AS
1673 SELECT DISTINCT deskriptoren.s_xx AS s08,
1674 deskriptoren.bedeutung,
1675 deskriptoren.beschreibung,
1676 deskriptoren.sn
1677 FROM deskriptoren
1678 WHERE (deskriptoren.ebene = 8);
1679
1680
1681 --
1682 -- Name: s_09_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1683 --
1684
1685 CREATE VIEW s_09_s_01_view AS
1686 SELECT d1.s_xx AS s01,
1687 d2.s_xx AS s09
1688 FROM (deskriptoren d1
1689 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1690 WHERE (d2.ebene = 9);
1691
1692
1693 --
1694 -- Name: s_09_view; Type: VIEW; Schema: stammdaten; Owner: -
1695 --
1696
1697 CREATE VIEW s_09_view AS
1698 SELECT DISTINCT deskriptoren.s_xx AS s09,
1699 deskriptoren.bedeutung,
1700 deskriptoren.beschreibung,
1701 deskriptoren.sn
1702 FROM deskriptoren
1703 WHERE (deskriptoren.ebene = 9);
1704
1705
1706 --
1707 -- Name: s_10_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1708 --
1709
1710 CREATE VIEW s_10_s_01_view AS
1711 SELECT d1.s_xx AS s01,
1712 d2.s_xx AS s10
1713 FROM (deskriptoren d1
1714 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1715 WHERE (d2.ebene = 10);
1716
1717
1718 --
1719 -- Name: s_10_view; Type: VIEW; Schema: stammdaten; Owner: -
1720 --
1721
1722 CREATE VIEW s_10_view AS
1723 SELECT DISTINCT deskriptoren.s_xx AS s10,
1724 deskriptoren.bedeutung,
1725 deskriptoren.beschreibung,
1726 deskriptoren.sn
1727 FROM deskriptoren
1728 WHERE (deskriptoren.ebene = 10);
1729
1730
1731 --
1732 -- Name: s_11_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1733 --
1734
1735 CREATE VIEW s_11_s_01_view AS
1736 SELECT d1.s_xx AS s01,
1737 d2.s_xx AS s11
1738 FROM (deskriptoren d1
1739 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1740 WHERE (d2.ebene = 11);
1741
1742
1743 --
1744 -- Name: s_11_view; Type: VIEW; Schema: stammdaten; Owner: -
1745 --
1746
1747 CREATE VIEW s_11_view AS
1748 SELECT DISTINCT deskriptoren.s_xx AS s11,
1749 deskriptoren.bedeutung,
1750 deskriptoren.beschreibung,
1751 deskriptoren.sn
1752 FROM deskriptoren
1753 WHERE (deskriptoren.ebene = 11);
1754
1755
1756 --
1757 -- Name: s_12_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1758 --
1759
1760 CREATE VIEW s_12_s_01_view AS
1761 SELECT d1.s_xx AS s01,
1762 d2.s_xx AS s12
1763 FROM (deskriptoren d1
1764 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1765 WHERE (d2.ebene = 12);
1766
1767
1768 --
1769 -- Name: s_12_view; Type: VIEW; Schema: stammdaten; Owner: -
1770 --
1771
1772 CREATE VIEW s_12_view AS
1773 SELECT DISTINCT deskriptoren.s_xx AS s12,
1774 deskriptoren.bedeutung,
1775 deskriptoren.beschreibung,
1776 deskriptoren.sn
1777 FROM deskriptoren
1778 WHERE (deskriptoren.ebene = 12);
1779
1780
1781 --
1782 -- Name: staat; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1783 --
1784
1785 CREATE TABLE staat (
1786 id integer NOT NULL,
1787 staat character varying(50) NOT NULL,
1788 hkl_id smallint NOT NULL,
1789 staat_iso character varying(2) NOT NULL,
1790 staat_kurz character varying(5),
1791 eu character(1) DEFAULT NULL::bpchar,
1792 koord_x_extern character varying(22),
1793 koord_y_extern character varying(22),
1794 kda_id integer
1795 );
1796
1797
1798 --
1799 -- Name: staat_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1800 --
1801
1802 CREATE SEQUENCE staat_id_seq
1803 START WITH 1
1804 INCREMENT BY 1
1805 NO MINVALUE
1806 NO MAXVALUE
1807 CACHE 1;
1808
1809
1810 --
1811 -- Name: staat_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1812 --
1813
1814 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
1815
1816
1817 --
1818 -- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1819 --
1820
1821 CREATE TABLE umwelt (
1822 id character varying(3) NOT NULL,
1823 beschreibung character varying(300),
1824 umwelt_bereich character varying(80) NOT NULL,
1825 meh_id integer
1826 );
1827
1828
1829 --
1830 -- Name: verwaltungseinheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1831 --
1832
1833 CREATE TABLE verwaltungseinheit (
1834 id character varying(8) NOT NULL,
1835 bundesland character varying(8) NOT NULL,
1836 kda_id integer,
1837 kreis character varying(8),
1838 nuts character varying(10),
1839 regbezirk character varying(8),
1840 bezeichnung character varying(80) NOT NULL,
1841 is_bundesland character(1) NOT NULL,
1842 is_gemeinde character(1) NOT NULL,
1843 is_landkreis character(1) NOT NULL,
1844 is_regbezirk character(1) NOT NULL,
1845 koord_x_extern character varying(22),
1846 koord_y_extern character varying(22),
1847 plz character varying(6),
1848 longitude double precision,
1849 latitude double precision
1850 );
1851
1852
1853 SET search_path = bund, pg_catalog;
1854
1855 --
1856 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
1857 --
1858
1859 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
1860
1861
1862 --
1863 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
1864 --
1865
1866 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
1867
1868
1869 --
1870 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
1871 --
1872
1873 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
1874
1875
1876 --
1877 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
1878 --
1879
1880 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
1881
1882
1883 --
1884 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
1885 --
1886
1887 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
1888
1889
1890 SET search_path = land, pg_catalog;
1891
1892 --
1893 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1894 --
1895
1896 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
1897
1898
1899 --
1900 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
1901 --
1902
1903 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
1904
1905
1906 --
1907 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1908 --
1909
1910 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
1911
1912
1913 --
1914 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
1915 --
1916
1917 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
1918
1919
1920 --
1921 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1922 --
1923
1924 ALTER TABLE ONLY messprogramm ALTER COLUMN id SET DEFAULT nextval('messprogramm_id_seq'::regclass);
1925
1926
1927 --
1928 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1929 --
1930
1931 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass);
1932
1933
1934 --
1935 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1936 --
1937
1938 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass);
1939
1940
1941 --
1942 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
1943 --
1944
1945 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
1946
1947
1948 --
1949 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
1950 --
1951
1952 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now();
1953
1954
1955 --
1956 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1957 --
1958
1959 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass);
1960
1961
1962 --
1963 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1964 --
1965
1966 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass);
1967
1968
1969 --
1970 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
1971 --
1972
1973 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
1974
1975
1976 --
1977 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
1978 --
1979
1980 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
1981
1982
1983 --
1984 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1985 --
1986
1987 ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass);
1988
1989
1990 --
1991 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
1992 --
1993
1994 ALTER TABLE ONLY ort ALTER COLUMN letzte_aenderung SET DEFAULT now();
1995
1996
1997 --
1998 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
1999 --
2000
2001 ALTER TABLE ONLY probe ALTER COLUMN id SET DEFAULT nextval('bund.probe_id_seq'::regclass);
2002
2003
2004 --
2005 -- Name: test; Type: DEFAULT; Schema: land; Owner: -
2006 --
2007
2008 ALTER TABLE ONLY probe ALTER COLUMN test SET DEFAULT false;
2009
2010
2011 --
2012 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
2013 --
2014
2015 ALTER TABLE ONLY probe ALTER COLUMN letzte_aenderung SET DEFAULT now();
2016
2017
2018 --
2019 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
2020 --
2021
2022 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
2023
2024
2025 --
2026 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
2027 --
2028
2029 ALTER TABLE ONLY status ALTER COLUMN id SET DEFAULT nextval('bund.status_id_seq'::regclass);
2030
2031
2032 --
2033 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
2034 --
2035
2036 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass);
2037
2038
2039 --
2040 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
2041 --
2042
2043 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now();
2044
2045
2046 SET search_path = stammdaten, pg_catalog;
2047
2048 --
2049 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2050 --
2051
2052 ALTER TABLE ONLY auth ALTER COLUMN id SET DEFAULT nextval('auth_id_seq'::regclass);
2053
2054
2055 --
2056 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2057 --
2058
2059 ALTER TABLE ONLY datenbasis ALTER COLUMN id SET DEFAULT nextval('datenbasis_id_seq'::regclass);
2060
2061
2062 --
2063 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2064 --
2065
2066 ALTER TABLE ONLY de_vg ALTER COLUMN id SET DEFAULT nextval('de_vg_id_seq'::regclass);
2067
2068
2069 --
2070 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2071 --
2072
2073 ALTER TABLE ONLY deskriptoren ALTER COLUMN id SET DEFAULT nextval('deskriptoren_id_seq'::regclass);
2074
2075
2076 --
2077 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2078 --
2079
2080 ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass);
2081
2082
2083 --
2084 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2085 --
2086
2087 ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass);
2088
2089
2090 --
2091 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2092 --
2093
2094 ALTER TABLE ONLY messgroesse ALTER COLUMN id SET DEFAULT nextval('messgroesse_id_seq'::regclass);
2095
2096
2097 --
2098 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2099 --
2100
2101 ALTER TABLE ONLY messgroessen_gruppe ALTER COLUMN id SET DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass);
2102
2103
2104 --
2105 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2106 --
2107
2108 ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('ort_id_seq'::regclass);
2109
2110
2111 --
2112 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2113 --
2114
2115 ALTER TABLE ONLY pflicht_messgroesse ALTER COLUMN id SET DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass);
2116
2117
2118 --
2119 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2120 --
2121
2122 ALTER TABLE ONLY probenart ALTER COLUMN id SET DEFAULT nextval('probenart_id_seq'::regclass);
2123
2124
2125 --
2126 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2127 --
2128
2129 ALTER TABLE ONLY staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass);
2130
2131
2132 SET search_path = bund, pg_catalog;
2133
2134 --
2135 -- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2136 --
2137
2138 ALTER TABLE ONLY kommentar
2139 ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id);
2140
2141
2142 --
2143 -- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2144 --
2145
2146 ALTER TABLE ONLY list
2147 ADD CONSTRAINT list_pkey PRIMARY KEY (id);
2148
2149
2150 --
2151 -- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2152 --
2153
2154 ALTER TABLE ONLY messung
2155 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
2156
2157
2158 --
2159 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2160 --
2161
2162 ALTER TABLE ONLY messwert
2163 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
2164
2165
2166 --
2167 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2168 --
2169
2170 ALTER TABLE ONLY messwert
2171 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
2172
2173
2174 --
2175 -- Name: ort_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2176 --
2177
2178 ALTER TABLE ONLY ort
2179 ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
2180
2181
2182 --
2183 -- Name: probe_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2184 --
2185
2186 ALTER TABLE ONLY probe
2187 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
2188
2189
2190 --
2191 -- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2192 --
2193
2194 ALTER TABLE ONLY status
2195 ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
2196
2197
2198 --
2199 -- Name: status_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2200 --
2201
2202 ALTER TABLE ONLY status
2203 ADD CONSTRAINT status_pkey PRIMARY KEY (id);
2204
2205
2206 --
2207 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2208 --
2209
2210 ALTER TABLE ONLY zusatz_wert
2211 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
2212
2213
2214 --
2215 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2216 --
2217
2218 ALTER TABLE ONLY zusatz_wert
2219 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
2220
2221
2222 SET search_path = land, pg_catalog;
2223
2224 --
2225 -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2226 --
2227
2228 ALTER TABLE ONLY kommentar_m
2229 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id);
2230
2231
2232 --
2233 -- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2234 --
2235
2236 ALTER TABLE ONLY kommentar_p
2237 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id);
2238
2239
2240 --
2241 -- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2242 --
2243
2244 ALTER TABLE ONLY messprogramm_mmt
2245 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id);
2246
2247
2248 --
2249 -- Name: messprogramm_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2250 --
2251
2252 ALTER TABLE ONLY messprogramm
2253 ADD CONSTRAINT messprogramm_pkey PRIMARY KEY (id);
2254
2255
2256 --
2257 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2258 --
2259
2260 ALTER TABLE ONLY messung
2261 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
2262
2263
2264 --
2265 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2266 --
2267
2268 ALTER TABLE ONLY messung_translation
2269 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
2270
2271
2272 --
2273 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2274 --
2275
2276 ALTER TABLE ONLY messung_translation
2277 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
2278
2279
2280 --
2281 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2282 --
2283
2284 ALTER TABLE ONLY messwert
2285 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
2286
2287
2288 --
2289 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2290 --
2291
2292 ALTER TABLE ONLY messwert
2293 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
2294
2295
2296 --
2297 -- Name: ort_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2298 --
2299
2300 ALTER TABLE ONLY ort
2301 ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
2302
2303
2304 --
2305 -- Name: probe_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2306 --
2307
2308 ALTER TABLE ONLY probe
2309 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
2310
2311
2312 --
2313 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2314 --
2315
2316 ALTER TABLE ONLY probe_translation
2317 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
2318
2319
2320 --
2321 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2322 --
2323
2324 ALTER TABLE ONLY probe_translation
2325 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
2326
2327
2328 --
2329 -- Name: status_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2330 --
2331
2332 ALTER TABLE ONLY status
2333 ADD CONSTRAINT status_messungs_id_key UNIQUE (messungs_id);
2334
2335
2336 --
2337 -- Name: status_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2338 --
2339
2340 ALTER TABLE ONLY status
2341 ADD CONSTRAINT status_pkey PRIMARY KEY (id);
2342
2343
2344 --
2345 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2346 --
2347
2348 ALTER TABLE ONLY zusatz_wert
2349 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
2350
2351
2352 --
2353 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2354 --
2355
2356 ALTER TABLE ONLY zusatz_wert
2357 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
2358
2359
2360 SET search_path = stammdaten, pg_catalog;
2361
2362 --
2363 -- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2364 --
2365
2366 ALTER TABLE ONLY auth
2367 ADD CONSTRAINT auth_pkey PRIMARY KEY (id);
2368
2369
2370 --
2371 -- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2372 --
2373
2374 ALTER TABLE ONLY datenbasis
2375 ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id);
2376
2377
2378 --
2379 -- Name: de_vg_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2380 --
2381
2382 ALTER TABLE ONLY de_vg
2383 ADD CONSTRAINT de_vg_pkey PRIMARY KEY (id);
2384
2385
2386 --
2387 -- Name: deskriptor_umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2388 --
2389
2390 ALTER TABLE ONLY deskriptor_umwelt
2391 ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id);
2392
2393
2394 --
2395 -- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2396 --
2397
2398 ALTER TABLE ONLY koordinaten_art
2399 ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id);
2400
2401
2402 --
2403 -- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2404 --
2405
2406 ALTER TABLE ONLY mess_einheit
2407 ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id);
2408
2409
2410 --
2411 -- Name: mess_methode_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2412 --
2413
2414 ALTER TABLE ONLY mess_methode
2415 ADD CONSTRAINT mess_methode_pkey PRIMARY KEY (id);
2416
2417
2418 --
2419 -- Name: mess_stelle_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2420 --
2421
2422 ALTER TABLE ONLY mess_stelle
2423 ADD CONSTRAINT mess_stelle_pkey PRIMARY KEY (id);
2424
2425
2426 --
2427 -- Name: messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2428 --
2429
2430 ALTER TABLE ONLY messgroesse
2431 ADD CONSTRAINT messgroesse_pkey PRIMARY KEY (id);
2432
2433
2434 --
2435 -- Name: messgroessen_gruppe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2436 --
2437
2438 ALTER TABLE ONLY messgroessen_gruppe
2439 ADD CONSTRAINT messgroessen_gruppe_pkey PRIMARY KEY (id);
2440
2441
2442 --
2443 -- Name: mg_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2444 --
2445
2446 ALTER TABLE ONLY mg_grp
2447 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
2448
2449
2450 --
2451 -- Name: mmt_messgroesse_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2452 --
2453
2454 ALTER TABLE ONLY mmt_messgroesse_grp
2455 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
2456
2457
2458 --
2459 -- Name: netz_betreiber_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2460 --
2461
2462 ALTER TABLE ONLY netz_betreiber
2463 ADD CONSTRAINT netz_betreiber_pkey PRIMARY KEY (id);
2464
2465
2466 --
2467 -- Name: ort_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2468 --
2469
2470 ALTER TABLE ONLY ort
2471 ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
2472
2473
2474 --
2475 -- Name: pflicht_messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2476 --
2477
2478 ALTER TABLE ONLY pflicht_messgroesse
2479 ADD CONSTRAINT pflicht_messgroesse_pkey PRIMARY KEY (id);
2480
2481
2482 --
2483 -- Name: pk_deskriptoren; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2484 --
2485
2486 ALTER TABLE ONLY deskriptoren
2487 ADD CONSTRAINT pk_deskriptoren PRIMARY KEY (id);
2488
2489
2490 --
2491 -- Name: proben_zusatz_eudf_keyword_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2492 --
2493
2494 ALTER TABLE ONLY proben_zusatz
2495 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
2496
2497
2498 --
2499 -- Name: proben_zusatz_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2500 --
2501
2502 ALTER TABLE ONLY proben_zusatz
2503 ADD CONSTRAINT proben_zusatz_pkey PRIMARY KEY (id);
2504
2505
2506 --
2507 -- Name: probenart_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2508 --
2509
2510 ALTER TABLE ONLY probenart
2511 ADD CONSTRAINT probenart_pkey PRIMARY KEY (id);
2512
2513
2514 --
2515 -- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2516 --
2517
2518 ALTER TABLE ONLY staat
2519 ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
2520
2521
2522 --
2523 -- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2524 --
2525
2526 ALTER TABLE ONLY umwelt
2527 ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id);
2528
2529
2530 --
2531 -- Name: umwelt_umwelt_bereich_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2532 --
2533
2534 ALTER TABLE ONLY umwelt
2535 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
2536
2537
2538 --
2539 -- Name: verwaltungseinheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2540 --
2541
2542 ALTER TABLE ONLY verwaltungseinheit
2543 ADD CONSTRAINT verwaltungseinheit_pkey PRIMARY KEY (id);
2544
2545
2546 SET search_path = bund, pg_catalog;
2547
2548 --
2549 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace:
2550 --
2551
2552 CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id);
2553
2554
2555 --
2556 -- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace:
2557 --
2558
2559 CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id);
2560
2561
2562 SET search_path = land, pg_catalog;
2563
2564 --
2565 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
2566 --
2567
2568 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
2569
2570
2571 --
2572 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
2573 --
2574
2575 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id);
2576
2577
2578 --
2579 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
2580 --
2581
2582 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
2583
2584
2585 --
2586 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
2587 --
2588
2589 CREATE INDEX probe_id_idx ON probe USING btree (id);
2590
2591
2592 --
2593 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
2594 --
2595
2596 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id);
2597
2598
2599 SET search_path = stammdaten, pg_catalog;
2600
2601 --
2602 -- Name: de_vg_geom_gist; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
2603 --
2604
2605 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom);
2606
2607
2608 --
2609 -- Name: fts_stauts_kooin10001; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
2610 --
2611
2612 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
2613
2614
2615 SET search_path = land, pg_catalog;
2616
2617 --
2618 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
2619 --
2620
2621 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung();
2622
2623
2624 --
2625 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
2626 --
2627
2628 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert();
2629
2630
2631 --
2632 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
2633 --
2634
2635 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_time_ort();
2636
2637
2638 --
2639 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
2640 --
2641
2642 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
2643
2644
2645 --
2646 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
2647 --
2648
2649 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status FOR EACH ROW EXECUTE PROCEDURE update_time_status();
2650
2651
2652 --
2653 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
2654 --
2655
2656 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
2657
2658
2659 --
2660 -- Name: verify_kommentar_m_id; Type: TRIGGER; Schema: land; Owner: -
2661 --
2662
2663 CREATE TRIGGER verify_kommentar_m_id BEFORE INSERT ON kommentar_m FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
2664
2665
2666 --
2667 -- Name: verify_kommentar_p_id; Type: TRIGGER; Schema: land; Owner: -
2668 --
2669
2670 CREATE TRIGGER verify_kommentar_p_id BEFORE INSERT ON kommentar_p FOR EACH ROW EXECUTE PROCEDURE bund.is_kommentar_unique();
2671
2672
2673 --
2674 -- Name: verify_messung_id; Type: TRIGGER; Schema: land; Owner: -
2675 --
2676
2677 CREATE TRIGGER verify_messung_id BEFORE INSERT ON messung FOR EACH ROW EXECUTE PROCEDURE bund.is_messung_unique();
2678
2679
2680 --
2681 -- Name: verify_messwert_id; Type: TRIGGER; Schema: land; Owner: -
2682 --
2683
2684 CREATE TRIGGER verify_messwert_id BEFORE INSERT ON messwert FOR EACH ROW EXECUTE PROCEDURE bund.is_messwert_unique();
2685
2686
2687 --
2688 -- Name: verify_ort_id; Type: TRIGGER; Schema: land; Owner: -
2689 --
2690
2691 CREATE TRIGGER verify_ort_id BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE bund.is_ort_unique();
2692
2693
2694 --
2695 -- Name: verify_probe_id; Type: TRIGGER; Schema: land; Owner: -
2696 --
2697
2698 CREATE TRIGGER verify_probe_id BEFORE INSERT ON probe FOR EACH ROW EXECUTE PROCEDURE bund.is_probe_unique();
2699
2700
2701 --
2702 -- Name: verify_status_id; Type: TRIGGER; Schema: land; Owner: -
2703 --
2704
2705 CREATE TRIGGER verify_status_id BEFORE INSERT ON status FOR EACH ROW EXECUTE PROCEDURE bund.is_status_unique();
2706
2707
2708 --
2709 -- Name: verify_zusatz_wert_id; Type: TRIGGER; Schema: land; Owner: -
2710 --
2711
2712 CREATE TRIGGER verify_zusatz_wert_id BEFORE INSERT ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE bund.is_zusatz_wert_unique();
2713
2714
2715 SET search_path = bund, pg_catalog;
2716
2717 --
2718 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2719 --
2720
2721 ALTER TABLE ONLY kommentar_m
2722 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
2723
2724
2725 --
2726 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2727 --
2728
2729 ALTER TABLE ONLY kommentar_p
2730 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
2731
2732
2733 --
2734 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2735 --
2736
2737 ALTER TABLE ONLY messung
2738 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
2739
2740
2741 --
2742 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2743 --
2744
2745 ALTER TABLE ONLY messung
2746 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
2747
2748
2749 --
2750 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2751 --
2752
2753 ALTER TABLE ONLY messwert
2754 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
2755
2756
2757 --
2758 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2759 --
2760
2761 ALTER TABLE ONLY messwert
2762 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
2763
2764
2765 --
2766 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2767 --
2768
2769 ALTER TABLE ONLY messwert
2770 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
2771
2772
2773 --
2774 -- Name: ort_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2775 --
2776
2777 ALTER TABLE ONLY ort
2778 ADD CONSTRAINT ort_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
2779
2780
2781 --
2782 -- Name: ort_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2783 --
2784
2785 ALTER TABLE ONLY ort
2786 ADD CONSTRAINT ort_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
2787
2788
2789 --
2790 -- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2791 --
2792
2793 ALTER TABLE ONLY probe
2794 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
2795
2796
2797 --
2798 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2799 --
2800
2801 ALTER TABLE ONLY probe
2802 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
2803
2804
2805 --
2806 -- Name: probe_mst_id_fkey1; Type: FK CONSTRAINT; Schema: bund; Owner: -
2807 --
2808
2809 ALTER TABLE ONLY probe
2810 ADD CONSTRAINT probe_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
2811
2812
2813 --
2814 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2815 --
2816
2817 ALTER TABLE ONLY probe
2818 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
2819
2820
2821 --
2822 -- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2823 --
2824
2825 ALTER TABLE ONLY probe
2826 ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
2827
2828
2829 --
2830 -- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2831 --
2832
2833 ALTER TABLE ONLY probe
2834 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
2835
2836
2837 --
2838 -- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2839 --
2840
2841 ALTER TABLE ONLY status
2842 ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
2843
2844
2845 --
2846 -- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2847 --
2848
2849 ALTER TABLE ONLY status
2850 ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
2851
2852
2853 --
2854 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2855 --
2856
2857 ALTER TABLE ONLY zusatz_wert
2858 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
2859
2860
2861 --
2862 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2863 --
2864
2865 ALTER TABLE ONLY zusatz_wert
2866 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
2867
2868
2869 SET search_path = land, pg_catalog;
2870
2871 --
2872 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2873 --
2874
2875 ALTER TABLE ONLY kommentar_m
2876 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
2877
2878
2879 --
2880 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2881 --
2882
2883 ALTER TABLE ONLY kommentar_p
2884 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
2885
2886
2887 --
2888 -- Name: messprogramm_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2889 --
2890
2891 ALTER TABLE ONLY messprogramm
2892 ADD CONSTRAINT messprogramm_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
2893
2894
2895 --
2896 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2897 --
2898
2899 ALTER TABLE ONLY messprogramm_mmt
2900 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE;
2901
2902
2903 --
2904 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2905 --
2906
2907 ALTER TABLE ONLY messprogramm_mmt
2908 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
2909
2910
2911 --
2912 -- Name: messprogramm_mst_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2913 --
2914
2915 ALTER TABLE ONLY messprogramm
2916 ADD CONSTRAINT messprogramm_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
2917
2918
2919 --
2920 -- Name: messprogramm_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2921 --
2922
2923 ALTER TABLE ONLY messprogramm
2924 ADD CONSTRAINT messprogramm_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
2925
2926
2927 --
2928 -- Name: messprogramm_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2929 --
2930
2931 ALTER TABLE ONLY messprogramm
2932 ADD CONSTRAINT messprogramm_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
2933
2934
2935 --
2936 -- Name: messprogramm_probenart_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2937 --
2938
2939 ALTER TABLE ONLY messprogramm
2940 ADD CONSTRAINT messprogramm_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
2941
2942
2943 --
2944 -- Name: messprogramm_umw_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2945 --
2946
2947 ALTER TABLE ONLY messprogramm
2948 ADD CONSTRAINT messprogramm_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
2949
2950
2951 --
2952 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2953 --
2954
2955 ALTER TABLE ONLY messung
2956 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE;
2957
2958
2959 --
2960 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2961 --
2962
2963 ALTER TABLE ONLY messung
2964 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
2965
2966
2967 --
2968 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2969 --
2970
2971 ALTER TABLE ONLY messung_translation
2972 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
2973
2974
2975 --
2976 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2977 --
2978
2979 ALTER TABLE ONLY messwert
2980 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
2981
2982
2983 --
2984 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2985 --
2986
2987 ALTER TABLE ONLY messwert
2988 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
2989
2990
2991 --
2992 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2993 --
2994
2995 ALTER TABLE ONLY messwert
2996 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
2997
2998
2999 --
3000 -- Name: ort_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3001 --
3002
3003 ALTER TABLE ONLY ort
3004 ADD CONSTRAINT ort_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
3005
3006
3007 --
3008 -- Name: ort_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3009 --
3010
3011 ALTER TABLE ONLY ort
3012 ADD CONSTRAINT ort_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
3013
3014
3015 --
3016 -- Name: probe_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3017 --
3018
3019 ALTER TABLE ONLY probe
3020 ADD CONSTRAINT probe_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
3021
3022
3023 --
3024 -- Name: probe_mst_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3025 --
3026
3027 ALTER TABLE ONLY probe
3028 ADD CONSTRAINT probe_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
3029
3030
3031 --
3032 -- Name: probe_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3033 --
3034
3035 ALTER TABLE ONLY probe
3036 ADD CONSTRAINT probe_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
3037
3038
3039 --
3040 -- Name: probe_probenart_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3041 --
3042
3043 ALTER TABLE ONLY probe
3044 ADD CONSTRAINT probe_probenart_id_fkey FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
3045
3046
3047 --
3048 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3049 --
3050
3051 ALTER TABLE ONLY probe_translation
3052 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
3053
3054
3055 --
3056 -- Name: probe_umw_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3057 --
3058
3059 ALTER TABLE ONLY probe
3060 ADD CONSTRAINT probe_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
3061
3062
3063 --
3064 -- Name: status_erzeuger_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3065 --
3066
3067 ALTER TABLE ONLY status
3068 ADD CONSTRAINT status_erzeuger_fkey FOREIGN KEY (erzeuger) REFERENCES stammdaten.mess_stelle(id);
3069
3070
3071 --
3072 -- Name: status_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3073 --
3074
3075 ALTER TABLE ONLY status
3076 ADD CONSTRAINT status_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
3077
3078
3079 --
3080 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3081 --
3082
3083 ALTER TABLE ONLY zusatz_wert
3084 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
3085
3086
3087 --
3088 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
3089 --
3090
3091 ALTER TABLE ONLY zusatz_wert
3092 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
3093
3094
3095 SET search_path = stammdaten, pg_catalog;
3096
3097 --
3098 -- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3099 --
3100
3101 ALTER TABLE ONLY auth
3102 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
3103
3104
3105 --
3106 -- Name: auth_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3107 --
3108
3109 ALTER TABLE ONLY auth
3110 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3111
3112
3113 --
3114 -- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3115 --
3116
3117 ALTER TABLE ONLY deskriptoren
3118 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
3119
3120
3121 --
3122 -- Name: ort_gem_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3123 --
3124
3125 ALTER TABLE ONLY ort
3126 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
3127
3128
3129 --
3130 -- Name: ort_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3131 --
3132
3133 ALTER TABLE ONLY ort
3134 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3135
3136
3137 --
3138 -- Name: ort_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3139 --
3140
3141 ALTER TABLE ONLY ort
3142 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3143
3144
3145 --
3146 -- Name: ort_staat_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3147 --
3148
3149 ALTER TABLE ONLY ort
3150 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
3151
3152
3153 --
3154 -- Name: pflicht_messgroesse_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3155 --
3156
3157 ALTER TABLE ONLY pflicht_messgroesse
3158 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
3159
3160
3161 --
3162 -- Name: pflicht_messgroesse_mmt_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3163 --
3164
3165 ALTER TABLE ONLY pflicht_messgroesse
3166 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
3167
3168
3169 --
3170 -- Name: pflicht_messgroesse_umw_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3171 --
3172
3173 ALTER TABLE ONLY pflicht_messgroesse
3174 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
3175
3176
3177 --
3178 -- Name: proben_zusatz_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3179 --
3180
3181 ALTER TABLE ONLY proben_zusatz
3182 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
3183
3184
3185 --
3186 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3187 --
3188
3189 ALTER TABLE ONLY staat
3190 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3191
3192
3193 --
3194 -- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3195 --
3196
3197 ALTER TABLE ONLY umwelt
3198 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
3199
3200
3201 --
3202 -- Name: verwaltungseinheit_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3203 --
3204
3205 ALTER TABLE ONLY verwaltungseinheit
3206 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3207
3208
3209 --
3210 -- Name: public; Type: ACL; Schema: -; Owner: -
3211 --
3212
3213 REVOKE ALL ON SCHEMA public FROM PUBLIC;
3214 REVOKE ALL ON SCHEMA public FROM postgres;
3215 GRANT ALL ON SCHEMA public TO postgres;
3216 GRANT ALL ON SCHEMA public TO PUBLIC;
3217
3218
3219 --
3220 -- PostgreSQL database dump complete
3221 --
3222
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)