Mercurial > lada > lada-server

comparison db_schema/stammdaten_data_query.sql @ 1319:512616ea3228 release-2.5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix somme test data
author Michael Stanko <mstanko@bfs.de>
date Thu, 23 Mar 2017 09:02:26 +0100
parents 3ac30d816c9a
children bfbbd390c9a8
comparison
equal deleted inserted replaced
1317:3ac30d816c9a 1319:512616ea3228
1 \set ON_ERROR_STOP on 1 \set ON_ERROR_STOP on--
2
3 --
4 -- PostgreSQL database dump 2 -- PostgreSQL database dump
5 -- 3 --
6 4
7 -- Dumped from database version 9.5.1 5 -- Dumped from database version 9.6.2
8 -- Dumped by pg_dump version 9.5.0 6 -- Dumped by pg_dump version 9.6.2
9
10 -- Started on 2016-03-31 11:38:13
11 7
12 SET statement_timeout = 0; 8 SET statement_timeout = 0;
13 SET lock_timeout = 0; 9 SET lock_timeout = 0;
10 SET idle_in_transaction_session_timeout = 0;
14 SET client_encoding = 'UTF8'; 11 SET client_encoding = 'UTF8';
15 SET standard_conforming_strings = on; 12 SET standard_conforming_strings = on;
16 SET check_function_bodies = false; 13 SET check_function_bodies = false;
17 SET client_min_messages = warning; 14 SET client_min_messages = warning;
15 SET row_security = off;
18 16
19 SET search_path = stammdaten, pg_catalog; 17 SET search_path = stammdaten, pg_catalog;
20 18
21 19 --
22 -- 20 -- Data for Name: query; Type: TABLE DATA; Schema: stammdaten; Owner: postgres
23 -- TOC entry 4715 (class 0 OID 535823)
24 -- Dependencies: 281
25 -- Data for Name: query; Type: TABLE DATA; Schema: stammdaten; Owner: lada
26 -- 21 --
27 22
28 COPY query (id, name, type, sql, description) FROM stdin; 23 COPY query (id, name, type, sql, description) FROM stdin;
29 10 Orte 3 Abfrage der Orte 24 10 Orte 3 Abfrage der Orte
30 11 Probenehmer 4 Abfrage der Probenehmer 25 11 Probenehmer 4 Abfrage der Probenehmer
41 13 nach Status 1 SELECT messung.id, probe.id AS probeId, probe.hauptproben_nr AS hpNr, messung.nebenproben_nr AS npNr, to_char(status_protokoll.datum, 'dd.mm.YYYY hh24:MI') AS statusD, stammdaten.status_stufe.stufe AS statusSt, stammdaten.status_wert.wert AS statusW, datenbasis.datenbasis AS dBasis, stammdaten.mess_stelle.netzbetreiber_id AS netzId, probe.mst_id AS mstId, probe.umw_id AS umwId, probenart.probenart AS pArt, to_char(probe.probeentnahme_beginn, 'dd.mm.YYYY hh24:MI') AS peBegin, to_char(probe.probeentnahme_ende, 'dd.mm.YYYY hh24:MI') AS peEnd, ort.ort_id AS ortId, ort.gem_id AS eGemId, verwaltungseinheit.bezeichnung AS eGem FROM land.probe LEFT JOIN stammdaten.mess_stelle ON (probe.mst_id = stammdaten.mess_stelle.id) INNER JOIN land.messung ON probe.id = messung.probe_id INNER JOIN land.status_protokoll ON messung.STATUS = status_protokoll.id LEFT JOIN stammdaten.status_kombi ON status_protokoll.status_kombi = stammdaten.status_kombi.id LEFT JOIN stammdaten.status_wert ON stammdaten.status_wert.id = stammdaten.status_kombi.wert_id LEFT JOIN stammdaten.status_stufe ON stammdaten.status_stufe.id = stammdaten.status_kombi.stufe_id LEFT JOIN stammdaten.datenbasis ON (probe.datenbasis_id = datenbasis.id) LEFT JOIN stammdaten.probenart ON (probe.probenart_id = probenart.id) LEFT JOIN land.ortszuordnung ON ( probe.id = ortszuordnung.probe_id AND ortszuordnung.ortszuordnung_typ = 'E' ) LEFT JOIN stammdaten.ort ON (ortszuordnung.ort_id = ort.id) LEFT JOIN stammdaten.verwaltungseinheit ON (ort.gem_id = verwaltungseinheit.id) WHERE CAST(stammdaten.status_wert.id AS text) SIMILAR TO CASE WHEN :statusFilter = '' THEN '%' ELSE :statusFilter END Messungen nach Status 36 13 nach Status 1 SELECT messung.id, probe.id AS probeId, probe.hauptproben_nr AS hpNr, messung.nebenproben_nr AS npNr, to_char(status_protokoll.datum, 'dd.mm.YYYY hh24:MI') AS statusD, stammdaten.status_stufe.stufe AS statusSt, stammdaten.status_wert.wert AS statusW, datenbasis.datenbasis AS dBasis, stammdaten.mess_stelle.netzbetreiber_id AS netzId, probe.mst_id AS mstId, probe.umw_id AS umwId, probenart.probenart AS pArt, to_char(probe.probeentnahme_beginn, 'dd.mm.YYYY hh24:MI') AS peBegin, to_char(probe.probeentnahme_ende, 'dd.mm.YYYY hh24:MI') AS peEnd, ort.ort_id AS ortId, ort.gem_id AS eGemId, verwaltungseinheit.bezeichnung AS eGem FROM land.probe LEFT JOIN stammdaten.mess_stelle ON (probe.mst_id = stammdaten.mess_stelle.id) INNER JOIN land.messung ON probe.id = messung.probe_id INNER JOIN land.status_protokoll ON messung.STATUS = status_protokoll.id LEFT JOIN stammdaten.status_kombi ON status_protokoll.status_kombi = stammdaten.status_kombi.id LEFT JOIN stammdaten.status_wert ON stammdaten.status_wert.id = stammdaten.status_kombi.wert_id LEFT JOIN stammdaten.status_stufe ON stammdaten.status_stufe.id = stammdaten.status_kombi.stufe_id LEFT JOIN stammdaten.datenbasis ON (probe.datenbasis_id = datenbasis.id) LEFT JOIN stammdaten.probenart ON (probe.probenart_id = probenart.id) LEFT JOIN land.ortszuordnung ON ( probe.id = ortszuordnung.probe_id AND ortszuordnung.ortszuordnung_typ = 'E' ) LEFT JOIN stammdaten.ort ON (ortszuordnung.ort_id = ort.id) LEFT JOIN stammdaten.verwaltungseinheit ON (ort.gem_id = verwaltungseinheit.id) WHERE CAST(stammdaten.status_wert.id AS text) SIMILAR TO CASE WHEN :statusFilter = '' THEN '%' ELSE :statusFilter END Messungen nach Status
42 \. 37 \.
43 38
44 39
45 -- 40 --
46 -- TOC entry 4683 (class 0 OID 535720) 41 -- Data for Name: filter; Type: TABLE DATA; Schema: stammdaten; Owner: postgres
47 -- Dependencies: 248
48 -- Data for Name: filter; Type: TABLE DATA; Schema: stammdaten; Owner: lada
49 -- 42 --
50 43
51 COPY filter (id, query_id, data_index, type, label, multiselect) FROM stdin; 44 COPY filter (id, query_id, data_index, type, label, multiselect) FROM stdin;
52 2 2 hpNrFilter 0 HP-Nr-Filter f 45 2 2 hpNrFilter 0 HP-Nr-Filter f
53 3 3 probeIdFilter 0 Probe_id-Filter f 46 3 3 probeIdFilter 0 Probe_id-Filter f
64 18 12 netzbetreiberId 2 Land f 57 18 12 netzbetreiberId 2 Land f
65 \. 58 \.
66 59
67 60
68 -- 61 --
69 -- TOC entry 4717 (class 0 OID 535831) 62 -- Name: filter_id_seq; Type: SEQUENCE SET; Schema: stammdaten; Owner: postgres
70 -- Dependencies: 283 63 --
71 -- Data for Name: result; Type: TABLE DATA; Schema: stammdaten; Owner: lada 64
65 SELECT pg_catalog.setval('filter_id_seq', 18, true);
66
67
68 --
69 -- Name: query_id_seq; Type: SEQUENCE SET; Schema: stammdaten; Owner: postgres
70 --
71
72 SELECT pg_catalog.setval('query_id_seq', 15, true);
73
74
75 --
76 -- Data for Name: result; Type: TABLE DATA; Schema: stammdaten; Owner: postgres
72 -- 77 --
73 78
74 COPY result (id, query_id, data_index, header, width, flex, index) FROM stdin; 79 COPY result (id, query_id, data_index, header, width, flex, index) FROM stdin;
75 1 1 hpNr Proben-Nr 100 f 0 80 1 1 hpNr Proben-Nr 100 f 0
76 2 1 dBasis DB 50 f 1 81 2 1 dBasis DB 50 f 1
187 124 15 eGemId E-Gem Id 80 f 14 192 124 15 eGemId E-Gem Id 80 f 14
188 125 15 eGem E-Gemeinde 200 f 15 193 125 15 eGem E-Gemeinde 200 f 15
189 126 15 probeId Probe_Id 100 f 0 194 126 15 probeId Probe_Id 100 f 0
190 \. 195 \.
191 196
197
198 --
199 -- Name: result_id_seq; Type: SEQUENCE SET; Schema: stammdaten; Owner: postgres
200 --
201
202 SELECT pg_catalog.setval('result_id_seq', 126, true);
203
204
205 --
206 -- PostgreSQL database dump complete
207 --
208
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)