Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 866:d47e6b8f3897

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 47dc3c4e42dd
children 9293d37c65f7
comparison
equal deleted inserted replaced
865:186d30e5b44a 866:d47e6b8f3897
1 \set ON_ERROR_STOP on 1 \set ON_ERROR_STOP on
2 2
3 BEGIN; 3 BEGIN;
4 4
5 --
6 -- PostgreSQL database dump
7 --
8 5
9 SET statement_timeout = 0; 6 SET statement_timeout = 0;
10 SET lock_timeout = 0; 7 SET lock_timeout = 0;
11 SET client_encoding = 'UTF8'; 8 SET client_encoding = 'UTF8';
12 SET standard_conforming_strings = on; 9 SET standard_conforming_strings = on;
23 -- 20 --
24 -- Name: land; Type: SCHEMA; Schema: -; Owner: - 21 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
25 -- 22 --
26 23
27 CREATE SCHEMA land; 24 CREATE SCHEMA land;
28
29
30 --
31 -- Name: stammdaten; Type: SCHEMA; Schema: -; Owner: -
32 --
33
34 CREATE SCHEMA stammdaten;
35
36
37 --
38 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
39 --
40
41 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
42
43
44 --
45 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
46 --
47
48 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
49
50
51 --
52 -- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
53 --
54
55 CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
56
57
58 --
59 -- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
60 --
61
62 COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';
63 25
64 26
65 SET search_path = bund, pg_catalog; 27 SET search_path = bund, pg_catalog;
66 28
67 -- 29 --
168 AS $$ 130 AS $$
169 BEGIN 131 BEGIN
170 NEW.tree_modified = now(); 132 NEW.tree_modified = now();
171 RETURN NEW; 133 RETURN NEW;
172 END; 134 END;
173 $$;
174
175
176 SET search_path = stammdaten, pg_catalog;
177
178 --
179 -- Name: get_media_from_media_desk(character varying); Type: FUNCTION; Schema: stammdaten; Owner: -
180 --
181
182 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
183 LANGUAGE plpgsql
184 AS $$
185 declare
186 result character varying(100);
187 d00 smallint;
188 d01 smallint;
189 d02 smallint;
190 d03 smallint;
191 begin
192 if media_desk like 'D: %' then
193 d00 := substring(media_desk,4,2);
194 d01 := substring(media_desk,7,2);
195 d02 := substring(media_desk,10,2);
196 d03 := substring(media_desk,13,2);
197 if d00 = '00' then
198 result := null;
199 else
200 if d01 = '00' then
201 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
202 where s00.ebene = 0 and s00.sn = d00::smallint;
203 else
204 if d02 = '00' or d00 <> '01' then
205 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
206 where s01.ebene = 1 and s01.sn = d01::smallint
207 and s01.vorgaenger =
208 (select s00.id FROM stammdaten.deskriptoren s00
209 where s00.ebene = 0 and s00.sn = d00::smallint);
210 else
211 if d03 = '00' then
212 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
213 where s02.ebene = 2 and s02.sn = d02::smallint
214 and s02.vorgaenger =
215 (select s01.id FROM stammdaten.deskriptoren s01
216 where s01.ebene = 1 and s01.sn = d01::smallint
217 and s01.vorgaenger =
218 (select s00.id FROM stammdaten.deskriptoren s00
219 where s00.ebene = 0 and s00.sn = d00::smallint));
220 else
221 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
222 where s03.ebene = 3 and s03.sn = d03::smallint
223 and s03.vorgaenger =
224 (select s02.id FROM stammdaten.deskriptoren s02
225 where s02.ebene = 2 and s02.sn = d02::smallint
226 and s02.vorgaenger =
227 (select s01.id FROM stammdaten.deskriptoren s01
228 where s01.ebene = 1 and s01.sn = d01::smallint
229 and s01.vorgaenger =
230 (select s00.id FROM stammdaten.deskriptoren s00
231 where s00.ebene = 0 and s00.sn = d00::smallint)));
232 end if;
233 end if;
234 end if;
235 end if;
236 else
237 result := null;
238 end if;
239 return (result);
240 end;
241 $$; 135 $$;
242 136
243 137
244 SET search_path = bund, pg_catalog; 138 SET search_path = bund, pg_catalog;
245 139
839 tree_modified timestamp without time zone DEFAULT now() 733 tree_modified timestamp without time zone DEFAULT now()
840 ) 734 )
841 INHERITS (bund.zusatz_wert); 735 INHERITS (bund.zusatz_wert);
842 736
843 737
844 SET search_path = stammdaten, pg_catalog;
845
846 --
847 -- Name: auth; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
848 --
849
850 CREATE TABLE auth (
851 id integer NOT NULL,
852 ldap_group character varying(40) NOT NULL,
853 netzbetreiber_id character varying(2),
854 mst_id character varying(5),
855 labor_mst_id character varying(5),
856 funktion_id smallint
857 );
858
859
860 --
861 -- Name: auth_funktion; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
862 --
863
864 CREATE TABLE auth_funktion (
865 id smallint NOT NULL,
866 funktion character varying(40)
867 );
868
869
870 --
871 -- Name: auth_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
872 --
873
874 CREATE SEQUENCE auth_id_seq
875 START WITH 1
876 INCREMENT BY 1
877 NO MINVALUE
878 NO MAXVALUE
879 CACHE 1;
880
881
882 --
883 -- Name: auth_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
884 --
885
886 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
887
888
889 --
890 -- Name: auth_id_seq1; Type: SEQUENCE; Schema: stammdaten; Owner: -
891 --
892
893 CREATE SEQUENCE auth_id_seq1
894 START WITH 1
895 INCREMENT BY 1
896 NO MINVALUE
897 NO MAXVALUE
898 CACHE 1;
899
900
901 --
902 -- Name: auth_id_seq1; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
903 --
904
905 ALTER SEQUENCE auth_id_seq1 OWNED BY auth.id;
906
907
908 --
909 -- Name: auth_lst_umw; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
910 --
911
912 CREATE TABLE auth_lst_umw (
913 id integer NOT NULL,
914 lst_id character varying(5),
915 umw_id character varying(3)
916 );
917
918
919 --
920 -- Name: auth_lst_umw_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
921 --
922
923 CREATE SEQUENCE auth_lst_umw_id_seq
924 START WITH 1
925 INCREMENT BY 1
926 NO MINVALUE
927 NO MAXVALUE
928 CACHE 1;
929
930
931 --
932 -- Name: auth_lst_umw_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
933 --
934
935 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id;
936
937
938 --
939 -- Name: datenbasis; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
940 --
941
942 CREATE TABLE datenbasis (
943 id integer NOT NULL,
944 beschreibung character varying(30),
945 datenbasis character varying(6)
946 );
947
948
949 --
950 -- Name: datenbasis_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
951 --
952
953 CREATE SEQUENCE datenbasis_id_seq
954 START WITH 1
955 INCREMENT BY 1
956 NO MINVALUE
957 NO MAXVALUE
958 CACHE 1;
959
960
961 --
962 -- Name: datenbasis_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
963 --
964
965 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
966
967
968 --
969 -- Name: datensatz_erzeuger; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
970 --
971
972 CREATE TABLE datensatz_erzeuger (
973 id integer NOT NULL,
974 netzbetreiber_id character varying(2),
975 da_erzeuger_id character varying(2),
976 mst_id character varying(5),
977 bezeichnung character varying(120),
978 letzte_aenderung timestamp without time zone
979 );
980
981
982 --
983 -- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
984 --
985
986 CREATE SEQUENCE datensatz_erzeuger_id_seq
987 START WITH 1
988 INCREMENT BY 1
989 NO MINVALUE
990 NO MAXVALUE
991 CACHE 1;
992
993
994 --
995 -- Name: datensatz_erzeuger_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
996 --
997
998 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
999
1000
1001 --
1002 -- Name: de_vg; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1003 --
1004
1005 CREATE TABLE de_vg (
1006 id integer NOT NULL,
1007 use double precision,
1008 rs character varying(12),
1009 gf double precision,
1010 rau_rs character varying(12),
1011 gen character varying(50),
1012 des character varying(75),
1013 isn double precision,
1014 bemerk character varying(75),
1015 nambild character varying(16),
1016 ags character varying(12),
1017 rs_alt character varying(20),
1018 wirksamkei date,
1019 debkg_id character varying(16),
1020 length numeric,
1021 shape_area numeric,
1022 geom public.geometry(MultiPolygon,4326)
1023 );
1024
1025
1026 --
1027 -- Name: de_vg_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1028 --
1029
1030 CREATE SEQUENCE de_vg_id_seq
1031 START WITH 1
1032 INCREMENT BY 1
1033 NO MINVALUE
1034 NO MAXVALUE
1035 CACHE 1;
1036
1037
1038 --
1039 -- Name: de_vg_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1040 --
1041
1042 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
1043
1044
1045 --
1046 -- Name: deskriptor_umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1047 --
1048
1049 CREATE TABLE deskriptor_umwelt (
1050 id integer NOT NULL,
1051 s00 integer NOT NULL,
1052 s01 integer NOT NULL,
1053 s02 integer,
1054 s03 integer,
1055 s04 integer,
1056 s05 integer,
1057 s06 integer,
1058 s07 integer,
1059 s08 integer,
1060 s09 integer,
1061 s10 integer,
1062 s11 integer,
1063 s12 integer,
1064 umw_id character varying(3) NOT NULL
1065 );
1066
1067
1068 --
1069 -- Name: deskriptoren; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1070 --
1071
1072 CREATE TABLE deskriptoren (
1073 id integer NOT NULL,
1074 vorgaenger integer,
1075 ebene smallint,
1076 s_xx integer,
1077 sn smallint,
1078 beschreibung character varying(100),
1079 bedeutung character varying(300)
1080 );
1081
1082
1083 --
1084 -- Name: deskriptoren_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1085 --
1086
1087 CREATE SEQUENCE deskriptoren_id_seq
1088 START WITH 1
1089 INCREMENT BY 1
1090 NO MINVALUE
1091 NO MAXVALUE
1092 CACHE 1;
1093
1094
1095 --
1096 -- Name: deskriptoren_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1097 --
1098
1099 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
1100
1101
1102 --
1103 -- Name: favorite; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1104 --
1105
1106 CREATE TABLE favorite (
1107 id integer NOT NULL,
1108 user_id integer NOT NULL,
1109 query_id integer NOT NULL
1110 );
1111
1112
1113 --
1114 -- Name: favorite_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1115 --
1116
1117 CREATE SEQUENCE favorite_id_seq
1118 START WITH 1
1119 INCREMENT BY 1
1120 NO MINVALUE
1121 NO MAXVALUE
1122 CACHE 1;
1123
1124
1125 --
1126 -- Name: favorite_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1127 --
1128
1129 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
1130
1131
1132 --
1133 -- Name: filter; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1134 --
1135
1136 CREATE TABLE filter (
1137 id integer NOT NULL,
1138 query_id integer NOT NULL,
1139 data_index character varying(50) NOT NULL,
1140 type character varying(10) NOT NULL,
1141 label character varying(50) NOT NULL,
1142 multiselect boolean
1143 );
1144
1145
1146 --
1147 -- Name: filter_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1148 --
1149
1150 CREATE SEQUENCE filter_id_seq
1151 START WITH 1
1152 INCREMENT BY 1
1153 NO MINVALUE
1154 NO MAXVALUE
1155 CACHE 1;
1156
1157
1158 --
1159 -- Name: filter_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1160 --
1161
1162 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
1163
1164
1165 --
1166 -- Name: filter_value; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1167 --
1168
1169 CREATE TABLE filter_value (
1170 id integer NOT NULL,
1171 query_id integer NOT NULL,
1172 user_id integer NOT NULL,
1173 filter_id integer NOT NULL,
1174 value text
1175 );
1176
1177
1178 --
1179 -- Name: filter_value_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1180 --
1181
1182 CREATE SEQUENCE filter_value_id_seq
1183 START WITH 1
1184 INCREMENT BY 1
1185 NO MINVALUE
1186 NO MAXVALUE
1187 CACHE 1;
1188
1189
1190 --
1191 -- Name: filter_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1192 --
1193
1194 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
1195
1196
1197 --
1198 -- Name: koordinaten_art; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1199 --
1200
1201 CREATE TABLE koordinaten_art (
1202 id integer NOT NULL,
1203 koordinatenart character varying(50),
1204 idf_geo_key character varying(1)
1205 );
1206
1207
1208 --
1209 -- Name: koordinaten_art_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1210 --
1211
1212 CREATE SEQUENCE koordinaten_art_id_seq
1213 START WITH 1
1214 INCREMENT BY 1
1215 NO MINVALUE
1216 NO MAXVALUE
1217 CACHE 1;
1218
1219
1220 --
1221 -- Name: koordinaten_art_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1222 --
1223
1224 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
1225
1226
1227 --
1228 -- Name: lada_user; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1229 --
1230
1231 CREATE TABLE lada_user (
1232 id integer NOT NULL,
1233 name character varying(80) NOT NULL
1234 );
1235
1236
1237 --
1238 -- Name: lada_user_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1239 --
1240
1241 CREATE SEQUENCE lada_user_id_seq
1242 START WITH 1
1243 INCREMENT BY 1
1244 NO MINVALUE
1245 NO MAXVALUE
1246 CACHE 1;
1247
1248
1249 --
1250 -- Name: lada_user_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1251 --
1252
1253 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
1254
1255
1256 --
1257 -- Name: mess_einheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1258 --
1259
1260 CREATE TABLE mess_einheit (
1261 id integer NOT NULL,
1262 beschreibung character varying(50),
1263 einheit character varying(12),
1264 eudf_messeinheit_id character varying(8),
1265 umrechnungs_faktor_eudf bigint
1266 );
1267
1268
1269 --
1270 -- Name: mess_einheit_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1271 --
1272
1273 CREATE SEQUENCE mess_einheit_id_seq
1274 START WITH 1
1275 INCREMENT BY 1
1276 NO MINVALUE
1277 NO MAXVALUE
1278 CACHE 1;
1279
1280
1281 --
1282 -- Name: mess_einheit_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1283 --
1284
1285 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
1286
1287
1288 --
1289 -- Name: mess_methode; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1290 --
1291
1292 CREATE TABLE mess_methode (
1293 id character varying(2) NOT NULL,
1294 beschreibung character varying(300),
1295 messmethode character varying(50)
1296 );
1297
1298
1299 --
1300 -- Name: mess_stelle; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1301 --
1302
1303 CREATE TABLE mess_stelle (
1304 id character varying(5) NOT NULL,
1305 netzbetreiber_id character varying(2),
1306 beschreibung character varying(300),
1307 mess_stelle character varying(60),
1308 mst_typ character varying(1),
1309 amtskennung character varying(6)
1310 );
1311
1312
1313 --
1314 -- Name: messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1315 --
1316
1317 CREATE TABLE messgroesse (
1318 id integer NOT NULL,
1319 beschreibung character varying(300),
1320 messgroesse character varying(50) NOT NULL,
1321 default_farbe character varying(9),
1322 idf_nuklid_key character varying(6),
1323 ist_leitnuklid boolean DEFAULT false,
1324 eudf_nuklid_id bigint,
1325 kennung_bvl character varying(7)
1326 );
1327
1328
1329 --
1330 -- Name: messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1331 --
1332
1333 CREATE SEQUENCE messgroesse_id_seq
1334 START WITH 1
1335 INCREMENT BY 1
1336 NO MINVALUE
1337 NO MAXVALUE
1338 CACHE 1;
1339
1340
1341 --
1342 -- Name: messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1343 --
1344
1345 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
1346
1347
1348 --
1349 -- Name: messgroessen_gruppe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1350 --
1351
1352 CREATE TABLE messgroessen_gruppe (
1353 id integer NOT NULL,
1354 bezeichnung character varying(80),
1355 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
1356 );
1357
1358
1359 --
1360 -- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1361 --
1362
1363 CREATE SEQUENCE messgroessen_gruppe_id_seq
1364 START WITH 1
1365 INCREMENT BY 1
1366 NO MINVALUE
1367 NO MAXVALUE
1368 CACHE 1;
1369
1370
1371 --
1372 -- Name: messgroessen_gruppe_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1373 --
1374
1375 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
1376
1377
1378 --
1379 -- Name: messprogramm_kategorie; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1380 --
1381
1382 CREATE TABLE messprogramm_kategorie (
1383 id integer NOT NULL,
1384 netzbetreiber_id character varying(2),
1385 mpl_id character varying(3),
1386 bezeichnung character varying(120),
1387 letzte_aenderung timestamp without time zone
1388 );
1389
1390
1391 --
1392 -- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1393 --
1394
1395 CREATE SEQUENCE messprogramm_kategorie_id_seq
1396 START WITH 1
1397 INCREMENT BY 1
1398 NO MINVALUE
1399 NO MAXVALUE
1400 CACHE 1;
1401
1402
1403 --
1404 -- Name: messprogramm_kategorie_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1405 --
1406
1407 ALTER SEQUENCE messprogramm_kategorie_id_seq OWNED BY messprogramm_kategorie.id;
1408
1409
1410 --
1411 -- Name: mg_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1412 --
1413
1414 CREATE TABLE mg_grp (
1415 messgroessengruppe_id integer NOT NULL,
1416 messgroesse_id integer NOT NULL
1417 );
1418
1419
1420 --
1421 -- Name: mmt_messgroesse_grp; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1422 --
1423
1424 CREATE TABLE mmt_messgroesse_grp (
1425 messgroessengruppe_id integer NOT NULL,
1426 mmt_id character varying(2) NOT NULL
1427 );
1428
1429
1430 --
1431 -- Name: mmt_messgroesse; Type: VIEW; Schema: stammdaten; Owner: -
1432 --
1433
1434 CREATE VIEW mmt_messgroesse AS
1435 SELECT mmt_messgroesse_grp.mmt_id,
1436 mg_grp.messgroesse_id
1437 FROM mmt_messgroesse_grp,
1438 mg_grp
1439 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
1440
1441
1442 --
1443 -- Name: netz_betreiber; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1444 --
1445
1446 CREATE TABLE netz_betreiber (
1447 id character varying(2) NOT NULL,
1448 netzbetreiber character varying(50),
1449 idf_netzbetreiber character varying(1),
1450 is_bmn boolean DEFAULT false,
1451 mailverteiler character varying(512),
1452 aktiv boolean DEFAULT false,
1453 zust_mst_id character varying(5)
1454 );
1455
1456
1457 --
1458 -- Name: ort; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1459 --
1460
1461 CREATE TABLE ort (
1462 id integer NOT NULL,
1463 netzbetreiber_id character varying(2),
1464 ort_id character varying(10),
1465 langtext character varying(100),
1466 staat_id smallint,
1467 gem_id character varying(8),
1468 unscharf character(1) DEFAULT NULL::bpchar,
1469 nuts_code character varying(10),
1470 kda_id integer,
1471 koord_x_extern character varying(22),
1472 koord_y_extern character varying(22),
1473 hoehe_land real,
1474 letzte_aenderung timestamp without time zone DEFAULT now(),
1475 latitude double precision,
1476 longitude double precision,
1477 geom public.geometry(Point,4326),
1478 shape public.geometry(MultiPolygon,4326),
1479 ort_typ smallint,
1480 kurztext character varying(15),
1481 berichtstext character varying(70),
1482 zone character varying(1),
1483 sektor character varying(2),
1484 zustaendigkeit character varying(10),
1485 mp_art character varying(10),
1486 aktiv character(1),
1487 anlage_id integer,
1488 oz_id integer
1489 );
1490
1491
1492 --
1493 -- Name: ort_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1494 --
1495
1496 CREATE SEQUENCE ort_id_seq
1497 START WITH 1
1498 INCREMENT BY 1
1499 NO MINVALUE
1500 NO MAXVALUE
1501 CACHE 1;
1502
1503
1504 --
1505 -- Name: ort_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1506 --
1507
1508 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
1509
1510
1511 --
1512 -- Name: ort_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1513 --
1514
1515 CREATE TABLE ort_typ (
1516 id smallint NOT NULL,
1517 ort_typ character varying(60)
1518 );
1519
1520
1521 --
1522 -- Name: ortszuordnung_typ; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1523 --
1524
1525 CREATE TABLE ortszuordnung_typ (
1526 id character(1) NOT NULL,
1527 ortstyp character varying(60)
1528 );
1529
1530
1531 --
1532 -- Name: pflicht_messgroesse; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1533 --
1534
1535 CREATE TABLE pflicht_messgroesse (
1536 id integer NOT NULL,
1537 messgroesse_id integer,
1538 mmt_id character varying(2),
1539 umw_id character varying(3),
1540 datenbasis_id smallint NOT NULL
1541 );
1542
1543
1544 --
1545 -- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1546 --
1547
1548 CREATE SEQUENCE pflicht_messgroesse_id_seq
1549 START WITH 1
1550 INCREMENT BY 1
1551 NO MINVALUE
1552 NO MAXVALUE
1553 CACHE 1;
1554
1555
1556 --
1557 -- Name: pflicht_messgroesse_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1558 --
1559
1560 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
1561
1562
1563 --
1564 -- Name: proben_zusatz; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1565 --
1566
1567 CREATE TABLE proben_zusatz (
1568 id character varying(3) NOT NULL,
1569 meh_id integer,
1570 beschreibung character varying(50) NOT NULL,
1571 zusatzwert character varying(7) NOT NULL,
1572 eudf_keyword character varying(40)
1573 );
1574
1575
1576 --
1577 -- Name: probenart; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1578 --
1579
1580 CREATE TABLE probenart (
1581 id integer NOT NULL,
1582 beschreibung character varying(30),
1583 probenart character varying(5) NOT NULL,
1584 probenart_eudf_id character varying(1) NOT NULL
1585 );
1586
1587
1588 --
1589 -- Name: probenart_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1590 --
1591
1592 CREATE SEQUENCE probenart_id_seq
1593 START WITH 1
1594 INCREMENT BY 1
1595 NO MINVALUE
1596 NO MAXVALUE
1597 CACHE 1;
1598
1599
1600 --
1601 -- Name: probenart_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1602 --
1603
1604 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
1605
1606
1607 --
1608 -- Name: probenehmer; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1609 --
1610
1611 CREATE TABLE probenehmer (
1612 id integer NOT NULL,
1613 netzbetreiber_id character varying(2),
1614 prn_id character varying(9),
1615 bearbeiter character varying(25),
1616 bemerkung character varying(60),
1617 betrieb character varying(80),
1618 bezeichnung character varying(80),
1619 kurz_bezeichnung character varying(10),
1620 ort character varying(20),
1621 plz character varying(5),
1622 strasse character varying(30),
1623 telefon character varying(20),
1624 tp character varying(3),
1625 typ character(1),
1626 letzte_aenderung timestamp without time zone
1627 );
1628
1629
1630 --
1631 -- Name: probenehmer_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1632 --
1633
1634 CREATE SEQUENCE probenehmer_id_seq
1635 START WITH 1
1636 INCREMENT BY 1
1637 NO MINVALUE
1638 NO MAXVALUE
1639 CACHE 1;
1640
1641
1642 --
1643 -- Name: probenehmer_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1644 --
1645
1646 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
1647
1648
1649 --
1650 -- Name: query; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1651 --
1652
1653 CREATE TABLE query (
1654 id integer NOT NULL,
1655 name character varying(80) NOT NULL,
1656 type character varying(30) NOT NULL,
1657 sql character varying(1500) NOT NULL,
1658 description character varying(100)
1659 );
1660
1661
1662 --
1663 -- Name: query_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1664 --
1665
1666 CREATE SEQUENCE query_id_seq
1667 START WITH 1
1668 INCREMENT BY 1
1669 NO MINVALUE
1670 NO MAXVALUE
1671 CACHE 1;
1672
1673
1674 --
1675 -- Name: query_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1676 --
1677
1678 ALTER SEQUENCE query_id_seq OWNED BY query.id;
1679
1680
1681 --
1682 -- Name: result; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
1683 --
1684
1685 CREATE TABLE result (
1686 id integer NOT NULL,
1687 query_id integer NOT NULL,
1688 data_index character varying(50) NOT NULL,
1689 header character varying(50) NOT NULL,
1690 width integer,
1691 flex boolean,
1692 index integer
1693 );
1694
1695
1696 --
1697 -- Name: result_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
1698 --
1699
1700 CREATE SEQUENCE result_id_seq
1701 START WITH 1
1702 INCREMENT BY 1
1703 NO MINVALUE
1704 NO MAXVALUE
1705 CACHE 1;
1706
1707
1708 --
1709 -- Name: result_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
1710 --
1711
1712 ALTER SEQUENCE result_id_seq OWNED BY result.id;
1713
1714
1715 --
1716 -- Name: s_00_view; Type: VIEW; Schema: stammdaten; Owner: -
1717 --
1718
1719 CREATE VIEW s_00_view AS
1720 SELECT deskriptoren.s_xx AS s00,
1721 deskriptoren.bedeutung,
1722 deskriptoren.beschreibung,
1723 deskriptoren.sn
1724 FROM deskriptoren
1725 WHERE (deskriptoren.ebene = 0);
1726
1727
1728 --
1729 -- Name: s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1730 --
1731
1732 CREATE VIEW s_01_view AS
1733 SELECT d1.s_xx AS s01,
1734 d2.s_xx AS s00,
1735 d1.bedeutung,
1736 d1.beschreibung,
1737 d1.sn
1738 FROM (deskriptoren d1
1739 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1740 WHERE (d1.ebene = 1);
1741
1742
1743 --
1744 -- Name: s_02_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1745 --
1746
1747 CREATE VIEW s_02_s_01_view AS
1748 SELECT d1.s_xx AS s01,
1749 d2.s_xx AS s02
1750 FROM (deskriptoren d1
1751 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1752 WHERE (d2.ebene = 2);
1753
1754
1755 --
1756 -- Name: s_02_view; Type: VIEW; Schema: stammdaten; Owner: -
1757 --
1758
1759 CREATE VIEW s_02_view AS
1760 SELECT DISTINCT deskriptoren.s_xx AS s00,
1761 deskriptoren.bedeutung,
1762 deskriptoren.beschreibung,
1763 deskriptoren.sn
1764 FROM deskriptoren
1765 WHERE (deskriptoren.ebene = 2);
1766
1767
1768 --
1769 -- Name: s_03_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1770 --
1771
1772 CREATE VIEW s_03_s_01_view AS
1773 SELECT d1.s_xx AS s01,
1774 d2.s_xx AS s03
1775 FROM (deskriptoren d1
1776 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1777 WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
1778
1779
1780 --
1781 -- Name: s_03_view; Type: VIEW; Schema: stammdaten; Owner: -
1782 --
1783
1784 CREATE VIEW s_03_view AS
1785 SELECT d1.s_xx AS s03,
1786 d2.s_xx AS s02,
1787 d1.bedeutung,
1788 d1.beschreibung,
1789 d1.sn
1790 FROM (deskriptoren d1
1791 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1792 WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
1793 UNION
1794 SELECT d1.s_xx AS s03,
1795 NULL::integer AS s02,
1796 d1.bedeutung,
1797 d1.beschreibung,
1798 d1.sn
1799 FROM (deskriptoren d1
1800 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
1801 WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
1802
1803
1804 --
1805 -- Name: s_04_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1806 --
1807
1808 CREATE VIEW s_04_s_01_view AS
1809 SELECT DISTINCT d1.s_xx AS s01,
1810 d2.s_xx AS s04
1811 FROM (deskriptoren d1
1812 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1813 WHERE (d2.ebene = 4);
1814
1815
1816 --
1817 -- Name: s_04_view; Type: VIEW; Schema: stammdaten; Owner: -
1818 --
1819
1820 CREATE VIEW s_04_view AS
1821 SELECT DISTINCT deskriptoren.s_xx AS s04,
1822 deskriptoren.bedeutung,
1823 deskriptoren.beschreibung,
1824 deskriptoren.sn
1825 FROM deskriptoren
1826 WHERE (deskriptoren.ebene = 4);
1827
1828
1829 --
1830 -- Name: s_05_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1831 --
1832
1833 CREATE VIEW s_05_s_01_view AS
1834 SELECT d1.s_xx AS s01,
1835 d2.s_xx AS s05
1836 FROM (deskriptoren d1
1837 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1838 WHERE (d2.ebene = 5);
1839
1840
1841 --
1842 -- Name: s_05_view; Type: VIEW; Schema: stammdaten; Owner: -
1843 --
1844
1845 CREATE VIEW s_05_view AS
1846 SELECT DISTINCT deskriptoren.s_xx AS s05,
1847 deskriptoren.bedeutung,
1848 deskriptoren.beschreibung,
1849 deskriptoren.sn
1850 FROM deskriptoren
1851 WHERE (deskriptoren.ebene = 5);
1852
1853
1854 --
1855 -- Name: s_06_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1856 --
1857
1858 CREATE VIEW s_06_s_01_view AS
1859 SELECT d1.s_xx AS s01,
1860 d2.s_xx AS s06
1861 FROM (deskriptoren d1
1862 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1863 WHERE (d2.ebene = 6);
1864
1865
1866 --
1867 -- Name: s_06_view; Type: VIEW; Schema: stammdaten; Owner: -
1868 --
1869
1870 CREATE VIEW s_06_view AS
1871 SELECT DISTINCT deskriptoren.s_xx AS s06,
1872 deskriptoren.bedeutung,
1873 deskriptoren.beschreibung,
1874 deskriptoren.sn
1875 FROM deskriptoren
1876 WHERE (deskriptoren.ebene = 6);
1877
1878
1879 --
1880 -- Name: s_07_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1881 --
1882
1883 CREATE VIEW s_07_s_01_view AS
1884 SELECT d1.s_xx AS s01,
1885 d2.s_xx AS s07
1886 FROM (deskriptoren d1
1887 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1888 WHERE (d2.ebene = 7);
1889
1890
1891 --
1892 -- Name: s_07_view; Type: VIEW; Schema: stammdaten; Owner: -
1893 --
1894
1895 CREATE VIEW s_07_view AS
1896 SELECT DISTINCT deskriptoren.s_xx AS s07,
1897 deskriptoren.bedeutung,
1898 deskriptoren.beschreibung,
1899 deskriptoren.sn
1900 FROM deskriptoren
1901 WHERE (deskriptoren.ebene = 7);
1902
1903
1904 --
1905 -- Name: s_08_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1906 --
1907
1908 CREATE VIEW s_08_s_01_view AS
1909 SELECT d1.s_xx AS s01,
1910 d2.s_xx AS s08
1911 FROM (deskriptoren d1
1912 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1913 WHERE (d2.ebene = 8);
1914
1915
1916 --
1917 -- Name: s_08_view; Type: VIEW; Schema: stammdaten; Owner: -
1918 --
1919
1920 CREATE VIEW s_08_view AS
1921 SELECT DISTINCT deskriptoren.s_xx AS s08,
1922 deskriptoren.bedeutung,
1923 deskriptoren.beschreibung,
1924 deskriptoren.sn
1925 FROM deskriptoren
1926 WHERE (deskriptoren.ebene = 8);
1927
1928
1929 --
1930 -- Name: s_09_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1931 --
1932
1933 CREATE VIEW s_09_s_01_view AS
1934 SELECT d1.s_xx AS s01,
1935 d2.s_xx AS s09
1936 FROM (deskriptoren d1
1937 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1938 WHERE (d2.ebene = 9);
1939
1940
1941 --
1942 -- Name: s_09_view; Type: VIEW; Schema: stammdaten; Owner: -
1943 --
1944
1945 CREATE VIEW s_09_view AS
1946 SELECT DISTINCT deskriptoren.s_xx AS s09,
1947 deskriptoren.bedeutung,
1948 deskriptoren.beschreibung,
1949 deskriptoren.sn
1950 FROM deskriptoren
1951 WHERE (deskriptoren.ebene = 9);
1952
1953
1954 --
1955 -- Name: s_10_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1956 --
1957
1958 CREATE VIEW s_10_s_01_view AS
1959 SELECT d1.s_xx AS s01,
1960 d2.s_xx AS s10
1961 FROM (deskriptoren d1
1962 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1963 WHERE (d2.ebene = 10);
1964
1965
1966 --
1967 -- Name: s_10_view; Type: VIEW; Schema: stammdaten; Owner: -
1968 --
1969
1970 CREATE VIEW s_10_view AS
1971 SELECT DISTINCT deskriptoren.s_xx AS s10,
1972 deskriptoren.bedeutung,
1973 deskriptoren.beschreibung,
1974 deskriptoren.sn
1975 FROM deskriptoren
1976 WHERE (deskriptoren.ebene = 10);
1977
1978
1979 --
1980 -- Name: s_11_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
1981 --
1982
1983 CREATE VIEW s_11_s_01_view AS
1984 SELECT d1.s_xx AS s01,
1985 d2.s_xx AS s11
1986 FROM (deskriptoren d1
1987 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
1988 WHERE (d2.ebene = 11);
1989
1990
1991 --
1992 -- Name: s_11_view; Type: VIEW; Schema: stammdaten; Owner: -
1993 --
1994
1995 CREATE VIEW s_11_view AS
1996 SELECT DISTINCT deskriptoren.s_xx AS s11,
1997 deskriptoren.bedeutung,
1998 deskriptoren.beschreibung,
1999 deskriptoren.sn
2000 FROM deskriptoren
2001 WHERE (deskriptoren.ebene = 11);
2002
2003
2004 --
2005 -- Name: s_12_s_01_view; Type: VIEW; Schema: stammdaten; Owner: -
2006 --
2007
2008 CREATE VIEW s_12_s_01_view AS
2009 SELECT d1.s_xx AS s01,
2010 d2.s_xx AS s12
2011 FROM (deskriptoren d1
2012 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
2013 WHERE (d2.ebene = 12);
2014
2015
2016 --
2017 -- Name: s_12_view; Type: VIEW; Schema: stammdaten; Owner: -
2018 --
2019
2020 CREATE VIEW s_12_view AS
2021 SELECT DISTINCT deskriptoren.s_xx AS s12,
2022 deskriptoren.bedeutung,
2023 deskriptoren.beschreibung,
2024 deskriptoren.sn
2025 FROM deskriptoren
2026 WHERE (deskriptoren.ebene = 12);
2027
2028
2029 --
2030 -- Name: staat; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2031 --
2032
2033 CREATE TABLE staat (
2034 id integer NOT NULL,
2035 staat character varying(50) NOT NULL,
2036 hkl_id smallint NOT NULL,
2037 staat_iso character varying(2) NOT NULL,
2038 staat_kurz character varying(5),
2039 eu character(1) DEFAULT NULL::bpchar,
2040 koord_x_extern character varying(22),
2041 koord_y_extern character varying(22),
2042 kda_id integer
2043 );
2044
2045
2046 --
2047 -- Name: staat_id_seq; Type: SEQUENCE; Schema: stammdaten; Owner: -
2048 --
2049
2050 CREATE SEQUENCE staat_id_seq
2051 START WITH 1
2052 INCREMENT BY 1
2053 NO MINVALUE
2054 NO MAXVALUE
2055 CACHE 1;
2056
2057
2058 --
2059 -- Name: staat_id_seq; Type: SEQUENCE OWNED BY; Schema: stammdaten; Owner: -
2060 --
2061
2062 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
2063
2064
2065 --
2066 -- Name: status_kombi; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2067 --
2068
2069 CREATE TABLE status_kombi (
2070 id integer NOT NULL,
2071 stufe_id integer,
2072 wert_id integer
2073 );
2074
2075
2076 --
2077 -- Name: status_reihenfolge; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2078 --
2079
2080 CREATE TABLE status_reihenfolge (
2081 id integer NOT NULL,
2082 von_id integer,
2083 zu_id integer
2084 );
2085
2086
2087 --
2088 -- Name: status_stufe; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2089 --
2090
2091 CREATE TABLE status_stufe (
2092 id integer NOT NULL,
2093 stufe character varying(50)
2094 );
2095
2096
2097 --
2098 -- Name: status_wert; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2099 --
2100
2101 CREATE TABLE status_wert (
2102 id integer NOT NULL,
2103 wert character varying(50)
2104 );
2105
2106
2107 --
2108 -- Name: umwelt; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2109 --
2110
2111 CREATE TABLE umwelt (
2112 id character varying(3) NOT NULL,
2113 beschreibung character varying(300),
2114 umwelt_bereich character varying(80) NOT NULL,
2115 meh_id integer
2116 );
2117
2118
2119 --
2120 -- Name: verwaltungseinheit; Type: TABLE; Schema: stammdaten; Owner: -; Tablespace:
2121 --
2122
2123 CREATE TABLE verwaltungseinheit (
2124 id character varying(8) NOT NULL,
2125 bundesland character varying(8) NOT NULL,
2126 kda_id integer,
2127 kreis character varying(8),
2128 nuts character varying(10),
2129 regbezirk character varying(8),
2130 bezeichnung character varying(80) NOT NULL,
2131 is_bundesland character(1) NOT NULL,
2132 is_gemeinde character(1) NOT NULL,
2133 is_landkreis character(1) NOT NULL,
2134 is_regbezirk character(1) NOT NULL,
2135 koord_x_extern character varying(22),
2136 koord_y_extern character varying(22),
2137 plz character varying(6),
2138 longitude double precision,
2139 latitude double precision
2140 );
2141
2142 --
2143 -- Name status_erreichbar; Type: VIEW; Schema: stammdaten; Owner: -;
2144 --
2145 CREATE VIEW status_erreichbar AS (
2146 SELECT DISTINCT k.wert_id,
2147 j.wert_id AS cur_wert,
2148 j.stufe_id AS cur_stufe
2149 FROM stammdaten.status_kombi k
2150 JOIN (SELECT r.zu_id,
2151 kom.wert_id,
2152 kom.stufe_id
2153 FROM stammdaten.status_reihenfolge r
2154 JOIN stammdaten.status_kombi kom
2155 ON kom.id = r.von_id) j
2156 ON j.zu_id = k.id
2157 );
2158
2159
2160
2161 SET search_path = bund, pg_catalog; 738 SET search_path = bund, pg_catalog;
2162 739
2163 -- 740 --
2164 -- Name: id; Type: DEFAULT; Schema: bund; Owner: - 741 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
2165 -- 742 --
2375 -- 952 --
2376 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - 953 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
2377 -- 954 --
2378 955
2379 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now(); 956 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now();
2380
2381
2382 SET search_path = stammdaten, pg_catalog;
2383
2384 --
2385 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2386 --
2387
2388 ALTER TABLE ONLY auth ALTER COLUMN id SET DEFAULT nextval('auth_id_seq'::regclass);
2389
2390
2391 --
2392 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2393 --
2394
2395 ALTER TABLE ONLY datenbasis ALTER COLUMN id SET DEFAULT nextval('datenbasis_id_seq'::regclass);
2396
2397
2398 --
2399 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2400 --
2401
2402 ALTER TABLE ONLY datensatz_erzeuger ALTER COLUMN id SET DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass);
2403
2404
2405 --
2406 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2407 --
2408
2409 ALTER TABLE ONLY de_vg ALTER COLUMN id SET DEFAULT nextval('de_vg_id_seq'::regclass);
2410
2411
2412 --
2413 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2414 --
2415
2416 ALTER TABLE ONLY deskriptoren ALTER COLUMN id SET DEFAULT nextval('deskriptoren_id_seq'::regclass);
2417
2418
2419 --
2420 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2421 --
2422
2423 ALTER TABLE ONLY favorite ALTER COLUMN id SET DEFAULT nextval('favorite_id_seq'::regclass);
2424
2425
2426 --
2427 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2428 --
2429
2430 ALTER TABLE ONLY filter ALTER COLUMN id SET DEFAULT nextval('filter_id_seq'::regclass);
2431
2432
2433 --
2434 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2435 --
2436
2437 ALTER TABLE ONLY filter_value ALTER COLUMN id SET DEFAULT nextval('filter_value_id_seq'::regclass);
2438
2439
2440 --
2441 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2442 --
2443
2444 ALTER TABLE ONLY koordinaten_art ALTER COLUMN id SET DEFAULT nextval('koordinaten_art_id_seq'::regclass);
2445
2446
2447 --
2448 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2449 --
2450
2451 ALTER TABLE ONLY lada_user ALTER COLUMN id SET DEFAULT nextval('lada_user_id_seq'::regclass);
2452
2453
2454 --
2455 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2456 --
2457
2458 ALTER TABLE ONLY mess_einheit ALTER COLUMN id SET DEFAULT nextval('mess_einheit_id_seq'::regclass);
2459
2460
2461 --
2462 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2463 --
2464
2465 ALTER TABLE ONLY messgroesse ALTER COLUMN id SET DEFAULT nextval('messgroesse_id_seq'::regclass);
2466
2467
2468 --
2469 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2470 --
2471
2472 ALTER TABLE ONLY messgroessen_gruppe ALTER COLUMN id SET DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass);
2473
2474
2475 --
2476 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2477 --
2478
2479 ALTER TABLE ONLY messprogramm_kategorie ALTER COLUMN id SET DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass);
2480
2481
2482 --
2483 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2484 --
2485
2486 ALTER TABLE ONLY ort ALTER COLUMN id SET DEFAULT nextval('ort_id_seq'::regclass);
2487
2488
2489 --
2490 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2491 --
2492
2493 ALTER TABLE ONLY pflicht_messgroesse ALTER COLUMN id SET DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass);
2494
2495
2496 --
2497 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2498 --
2499
2500 ALTER TABLE ONLY probenart ALTER COLUMN id SET DEFAULT nextval('probenart_id_seq'::regclass);
2501
2502
2503 --
2504 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2505 --
2506
2507 ALTER TABLE ONLY probenehmer ALTER COLUMN id SET DEFAULT nextval('probenehmer_id_seq'::regclass);
2508
2509
2510 --
2511 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2512 --
2513
2514 ALTER TABLE ONLY query ALTER COLUMN id SET DEFAULT nextval('query_id_seq'::regclass);
2515
2516
2517 --
2518 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2519 --
2520
2521 ALTER TABLE ONLY result ALTER COLUMN id SET DEFAULT nextval('result_id_seq'::regclass);
2522
2523
2524 --
2525 -- Name: id; Type: DEFAULT; Schema: stammdaten; Owner: -
2526 --
2527
2528 ALTER TABLE ONLY staat ALTER COLUMN id SET DEFAULT nextval('staat_id_seq'::regclass);
2529 957
2530 958
2531 SET search_path = bund, pg_catalog; 959 SET search_path = bund, pg_catalog;
2532 960
2533 -- 961 --
2738 1166
2739 ALTER TABLE ONLY zusatz_wert 1167 ALTER TABLE ONLY zusatz_wert
2740 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); 1168 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
2741 1169
2742 1170
2743 SET search_path = stammdaten, pg_catalog;
2744
2745 --
2746 -- Name: auth_lst_umw_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2747 --
2748
2749 ALTER TABLE ONLY auth_lst_umw
2750 ADD CONSTRAINT auth_lst_umw_pkey PRIMARY KEY (id);
2751
2752
2753 --
2754 -- Name: auth_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2755 --
2756
2757 ALTER TABLE ONLY auth
2758 ADD CONSTRAINT auth_pkey PRIMARY KEY (id);
2759
2760
2761 --
2762 -- Name: auth_role_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2763 --
2764
2765 ALTER TABLE ONLY auth_funktion
2766 ADD CONSTRAINT auth_role_pkey PRIMARY KEY (id);
2767
2768
2769 --
2770 -- Name: datenbasis_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2771 --
2772
2773 ALTER TABLE ONLY datenbasis
2774 ADD CONSTRAINT datenbasis_pkey PRIMARY KEY (id);
2775
2776
2777 --
2778 -- Name: datensatz_erzeuger_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2779 --
2780
2781 ALTER TABLE ONLY datensatz_erzeuger
2782 ADD CONSTRAINT datensatz_erzeuger_pkey PRIMARY KEY (id);
2783
2784
2785 --
2786 -- Name: de_vg_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2787 --
2788
2789 ALTER TABLE ONLY de_vg
2790 ADD CONSTRAINT de_vg_pkey PRIMARY KEY (id);
2791
2792
2793 --
2794 -- Name: deskriptor_umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2795 --
2796
2797 ALTER TABLE ONLY deskriptor_umwelt
2798 ADD CONSTRAINT deskriptor_umwelt_pkey PRIMARY KEY (id);
2799
2800
2801 --
2802 -- Name: favorite_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2803 --
2804
2805 ALTER TABLE ONLY favorite
2806 ADD CONSTRAINT favorite_pkey PRIMARY KEY (id);
2807
2808
2809 --
2810 -- Name: filter_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2811 --
2812
2813 ALTER TABLE ONLY filter
2814 ADD CONSTRAINT filter_pkey PRIMARY KEY (id);
2815
2816
2817 --
2818 -- Name: filter_value_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2819 --
2820
2821 ALTER TABLE ONLY filter_value
2822 ADD CONSTRAINT filter_value_pkey PRIMARY KEY (id);
2823
2824
2825 --
2826 -- Name: koordinaten_art_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2827 --
2828
2829 ALTER TABLE ONLY koordinaten_art
2830 ADD CONSTRAINT koordinaten_art_pkey PRIMARY KEY (id);
2831
2832
2833 --
2834 -- Name: lada_user_name_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2835 --
2836
2837 ALTER TABLE ONLY lada_user
2838 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
2839
2840
2841 --
2842 -- Name: lada_user_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2843 --
2844
2845 ALTER TABLE ONLY lada_user
2846 ADD CONSTRAINT lada_user_pkey PRIMARY KEY (id);
2847
2848
2849 --
2850 -- Name: mess_einheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2851 --
2852
2853 ALTER TABLE ONLY mess_einheit
2854 ADD CONSTRAINT mess_einheit_pkey PRIMARY KEY (id);
2855
2856
2857 --
2858 -- Name: mess_methode_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2859 --
2860
2861 ALTER TABLE ONLY mess_methode
2862 ADD CONSTRAINT mess_methode_pkey PRIMARY KEY (id);
2863
2864
2865 --
2866 -- Name: mess_stelle_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2867 --
2868
2869 ALTER TABLE ONLY mess_stelle
2870 ADD CONSTRAINT mess_stelle_pkey PRIMARY KEY (id);
2871
2872
2873 --
2874 -- Name: messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2875 --
2876
2877 ALTER TABLE ONLY messgroesse
2878 ADD CONSTRAINT messgroesse_pkey PRIMARY KEY (id);
2879
2880
2881 --
2882 -- Name: messgroessen_gruppe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2883 --
2884
2885 ALTER TABLE ONLY messgroessen_gruppe
2886 ADD CONSTRAINT messgroessen_gruppe_pkey PRIMARY KEY (id);
2887
2888
2889 --
2890 -- Name: messprogramm_kategorie_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2891 --
2892
2893 ALTER TABLE ONLY messprogramm_kategorie
2894 ADD CONSTRAINT messprogramm_kategorie_pkey PRIMARY KEY (id);
2895
2896
2897 --
2898 -- Name: mg_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2899 --
2900
2901 ALTER TABLE ONLY mg_grp
2902 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
2903
2904
2905 --
2906 -- Name: mmt_messgroesse_grp_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2907 --
2908
2909 ALTER TABLE ONLY mmt_messgroesse_grp
2910 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
2911
2912
2913 --
2914 -- Name: netz_betreiber_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2915 --
2916
2917 ALTER TABLE ONLY netz_betreiber
2918 ADD CONSTRAINT netz_betreiber_pkey PRIMARY KEY (id);
2919
2920
2921 --
2922 -- Name: ort_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2923 --
2924
2925 ALTER TABLE ONLY ort
2926 ADD CONSTRAINT ort_pkey PRIMARY KEY (id);
2927
2928
2929 --
2930 -- Name: ort_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2931 --
2932
2933 ALTER TABLE ONLY ort_typ
2934 ADD CONSTRAINT ort_typ_pkey PRIMARY KEY (id);
2935
2936
2937 --
2938 -- Name: ortszuordnung_typ_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2939 --
2940
2941 ALTER TABLE ONLY ortszuordnung_typ
2942 ADD CONSTRAINT ortszuordnung_typ_pkey PRIMARY KEY (id);
2943
2944
2945 --
2946 -- Name: pflicht_messgroesse_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2947 --
2948
2949 ALTER TABLE ONLY pflicht_messgroesse
2950 ADD CONSTRAINT pflicht_messgroesse_pkey PRIMARY KEY (id);
2951
2952
2953 --
2954 -- Name: pk_deskriptoren; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2955 --
2956
2957 ALTER TABLE ONLY deskriptoren
2958 ADD CONSTRAINT pk_deskriptoren PRIMARY KEY (id);
2959
2960
2961 --
2962 -- Name: proben_zusatz_eudf_keyword_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2963 --
2964
2965 ALTER TABLE ONLY proben_zusatz
2966 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
2967
2968
2969 --
2970 -- Name: proben_zusatz_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2971 --
2972
2973 ALTER TABLE ONLY proben_zusatz
2974 ADD CONSTRAINT proben_zusatz_pkey PRIMARY KEY (id);
2975
2976
2977 --
2978 -- Name: probenart_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2979 --
2980
2981 ALTER TABLE ONLY probenart
2982 ADD CONSTRAINT probenart_pkey PRIMARY KEY (id);
2983
2984
2985 --
2986 -- Name: probenehmer_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2987 --
2988
2989 ALTER TABLE ONLY probenehmer
2990 ADD CONSTRAINT probenehmer_pkey PRIMARY KEY (id);
2991
2992
2993 --
2994 -- Name: query_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
2995 --
2996
2997 ALTER TABLE ONLY query
2998 ADD CONSTRAINT query_pkey PRIMARY KEY (id);
2999
3000
3001 --
3002 -- Name: result_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3003 --
3004
3005 ALTER TABLE ONLY result
3006 ADD CONSTRAINT result_pkey PRIMARY KEY (id);
3007
3008
3009 --
3010 -- Name: staat_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3011 --
3012
3013 ALTER TABLE ONLY staat
3014 ADD CONSTRAINT staat_pkey PRIMARY KEY (id);
3015
3016
3017 --
3018 -- Name: status_kombi_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3019 --
3020
3021 ALTER TABLE ONLY status_kombi
3022 ADD CONSTRAINT status_kombi_pkey PRIMARY KEY (id);
3023
3024
3025 --
3026 -- Name: status_reihenfolge_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3027 --
3028
3029 ALTER TABLE ONLY status_reihenfolge
3030 ADD CONSTRAINT status_reihenfolge_pkey PRIMARY KEY (id);
3031
3032
3033 --
3034 -- Name: status_stufe_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3035 --
3036
3037 ALTER TABLE ONLY status_stufe
3038 ADD CONSTRAINT status_stufe_pkey PRIMARY KEY (id);
3039
3040
3041 --
3042 -- Name: status_wert_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3043 --
3044
3045 ALTER TABLE ONLY status_wert
3046 ADD CONSTRAINT status_wert_pkey PRIMARY KEY (id);
3047
3048
3049 --
3050 -- Name: umwelt_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3051 --
3052
3053 ALTER TABLE ONLY umwelt
3054 ADD CONSTRAINT umwelt_pkey PRIMARY KEY (id);
3055
3056
3057 --
3058 -- Name: umwelt_umwelt_bereich_key; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3059 --
3060
3061 ALTER TABLE ONLY umwelt
3062 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
3063
3064
3065 --
3066 -- Name: verwaltungseinheit_pkey; Type: CONSTRAINT; Schema: stammdaten; Owner: -; Tablespace:
3067 --
3068
3069 ALTER TABLE ONLY verwaltungseinheit
3070 ADD CONSTRAINT verwaltungseinheit_pkey PRIMARY KEY (id);
3071
3072
3073 SET search_path = bund, pg_catalog; 1171 SET search_path = bund, pg_catalog;
3074 1172
3075 -- 1173 --
3076 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace: 1174 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace:
3077 -- 1175 --
3119 -- 1217 --
3120 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 1218 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
3121 -- 1219 --
3122 1220
3123 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); 1221 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id);
3124
3125
3126 SET search_path = stammdaten, pg_catalog;
3127
3128 --
3129 -- Name: de_vg_geom_gist; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
3130 --
3131
3132 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom);
3133
3134
3135 --
3136 -- Name: fts_stauts_kooin10001; Type: INDEX; Schema: stammdaten; Owner: -; Tablespace:
3137 --
3138
3139 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
3140 1222
3141 1223
3142 SET search_path = bund, pg_catalog; 1224 SET search_path = bund, pg_catalog;
3143 1225
3144 -- 1226 --
3587 1669
3588 ALTER TABLE ONLY zusatz_wert 1670 ALTER TABLE ONLY zusatz_wert
3589 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); 1671 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
3590 1672
3591 1673
3592 SET search_path = stammdaten, pg_catalog;
3593
3594 --
3595 -- Name: auth_funktion_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3596 --
3597
3598 ALTER TABLE ONLY auth
3599 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id);
3600
3601
3602 --
3603 -- Name: auth_labor_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3604 --
3605
3606 ALTER TABLE ONLY auth
3607 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id);
3608
3609
3610 --
3611 -- Name: auth_lst_umw_lst_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3612 --
3613
3614 ALTER TABLE ONLY auth_lst_umw
3615 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id);
3616
3617
3618 --
3619 -- Name: auth_lst_umw_umw_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3620 --
3621
3622 ALTER TABLE ONLY auth_lst_umw
3623 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
3624
3625
3626 --
3627 -- Name: auth_mst_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3628 --
3629
3630 ALTER TABLE ONLY auth
3631 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
3632
3633
3634 --
3635 -- Name: auth_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3636 --
3637
3638 ALTER TABLE ONLY auth
3639 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3640
3641
3642 --
3643 -- Name: datensatz_erzeuger_mst_id_fkey1; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3644 --
3645
3646 ALTER TABLE ONLY datensatz_erzeuger
3647 ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
3648
3649
3650 --
3651 -- Name: datensatz_erzeuger_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3652 --
3653
3654 ALTER TABLE ONLY datensatz_erzeuger
3655 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3656
3657
3658 --
3659 -- Name: favorite_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3660 --
3661
3662 ALTER TABLE ONLY favorite
3663 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3664
3665
3666 --
3667 -- Name: favorite_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3668 --
3669
3670 ALTER TABLE ONLY favorite
3671 ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
3672
3673
3674 --
3675 -- Name: filter_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3676 --
3677
3678 ALTER TABLE ONLY filter
3679 ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3680
3681
3682 --
3683 -- Name: filter_value_filter_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3684 --
3685
3686 ALTER TABLE ONLY filter_value
3687 ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
3688
3689
3690 --
3691 -- Name: filter_value_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3692 --
3693
3694 ALTER TABLE ONLY filter_value
3695 ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3696
3697
3698 --
3699 -- Name: filter_value_user_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3700 --
3701
3702 ALTER TABLE ONLY filter_value
3703 ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
3704
3705
3706 --
3707 -- Name: fk_deskriptoren_vorgaenger; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3708 --
3709
3710 ALTER TABLE ONLY deskriptoren
3711 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
3712
3713
3714 --
3715 -- Name: messprogramm_kategorie_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3716 --
3717
3718 ALTER TABLE ONLY messprogramm_kategorie
3719 ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3720
3721
3722 --
3723 -- Name: ort_anlage_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3724 --
3725
3726 ALTER TABLE ONLY ort
3727 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
3728
3729
3730 --
3731 -- Name: ort_gem_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3732 --
3733
3734 ALTER TABLE ONLY ort
3735 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
3736
3737
3738 --
3739 -- Name: ort_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3740 --
3741
3742 ALTER TABLE ONLY ort
3743 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3744
3745
3746 --
3747 -- Name: ort_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3748 --
3749
3750 ALTER TABLE ONLY ort
3751 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3752
3753
3754 --
3755 -- Name: ort_ort_typ_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3756 --
3757
3758 ALTER TABLE ONLY ort
3759 ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
3760
3761
3762 --
3763 -- Name: ort_oz_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3764 --
3765
3766 ALTER TABLE ONLY ort
3767 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
3768
3769
3770 --
3771 -- Name: ort_staat_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3772 --
3773
3774 ALTER TABLE ONLY ort
3775 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
3776
3777
3778 --
3779 -- Name: pflicht_messgroesse_datenbasis_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3780 --
3781
3782 ALTER TABLE ONLY pflicht_messgroesse
3783 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id);
3784
3785
3786 --
3787 -- Name: pflicht_messgroesse_mmt_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3788 --
3789
3790 ALTER TABLE ONLY pflicht_messgroesse
3791 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id);
3792
3793
3794 --
3795 -- Name: pflicht_messgroesse_umw_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3796 --
3797
3798 ALTER TABLE ONLY pflicht_messgroesse
3799 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id);
3800
3801
3802 --
3803 -- Name: proben_zusatz_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3804 --
3805
3806 ALTER TABLE ONLY proben_zusatz
3807 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
3808
3809
3810 --
3811 -- Name: probenehmer_netzbetreiber_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3812 --
3813
3814 ALTER TABLE ONLY probenehmer
3815 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
3816
3817
3818 --
3819 -- Name: result_query_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3820 --
3821
3822 ALTER TABLE ONLY result
3823 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
3824
3825
3826 --
3827 -- Name: staat_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3828 --
3829
3830 ALTER TABLE ONLY staat
3831 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3832
3833
3834 --
3835 -- Name: status_kombi_stufe_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3836 --
3837
3838 ALTER TABLE ONLY status_kombi
3839 ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id);
3840
3841
3842 --
3843 -- Name: status_kombi_wert_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3844 --
3845
3846 ALTER TABLE ONLY status_kombi
3847 ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(id);
3848
3849
3850 --
3851 -- Name: status_reihenfolge_von_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3852 --
3853
3854 ALTER TABLE ONLY status_reihenfolge
3855 ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(id);
3856
3857
3858 --
3859 -- Name: status_reihenfolge_zu_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3860 --
3861
3862 ALTER TABLE ONLY status_reihenfolge
3863 ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id);
3864
3865
3866 --
3867 -- Name: umwelt_meh_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3868 --
3869
3870 ALTER TABLE ONLY umwelt
3871 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id);
3872
3873
3874 --
3875 -- Name: verwaltungseinheit_kda_id_fkey; Type: FK CONSTRAINT; Schema: stammdaten; Owner: -
3876 --
3877
3878 ALTER TABLE ONLY verwaltungseinheit
3879 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
3880
3881
3882 --
3883 -- Name: public; Type: ACL; Schema: -; Owner: -
3884 --
3885
3886 REVOKE ALL ON SCHEMA public FROM PUBLIC;
3887 REVOKE ALL ON SCHEMA public FROM postgres;
3888 GRANT ALL ON SCHEMA public TO postgres;
3889 GRANT ALL ON SCHEMA public TO PUBLIC;
3890
3891
3892 --
3893 -- PostgreSQL database dump complete
3894 --
3895
3896 COMMIT; 1674 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)