Mercurial > lada > lada-server

comparison db_schema/lada_schema.sql @ 1002:8ec5ee1c2dc6 schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated trigger and functions.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 19:46:11 +0200
parents 1c4aa7ea63aa
children 552215760ba8
comparison
equal deleted inserted replaced
1001:1c4aa7ea63aa 1002:8ec5ee1c2dc6
16 16
17 CREATE SCHEMA land; 17 CREATE SCHEMA land;
18 18
19 SET search_path = land, pg_catalog; 19 SET search_path = land, pg_catalog;
20 20
21 --
22 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
23 --
24
25 CREATE FUNCTION update_time_status() RETURNS trigger
26 LANGUAGE plpgsql
27 AS $$
28 BEGIN
29 NEW.tree_modified = now();
30 RETURN NEW;
31 END;
32 $$;
33 21
34 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger 22 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
35 LANGUAGE plpgsql 23 LANGUAGE plpgsql
36 AS $$ 24 AS $$
37 BEGIN 25 BEGIN
40 END; 28 END;
41 $$; 29 $$;
42 30
43 31
44 -- 32 --
45 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - 33 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
46 -- 34 --
47 35
48 CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean 36 CREATE FUNCTION update_tree_modified() RETURNS trigger
49 LANGUAGE plpgsql STABLE SECURITY DEFINER 37 LANGUAGE plpgsql
50 AS $_$ 38 AS $$
51 DECLARE result BOOLEAN; 39 BEGIN
52 BEGIN 40 NEW.tree_modified = now();
53 SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE; 41 RETURN NEW;
54 RETURN result; 42 END;
55 END; 43 $$;
56 $_$;
57 44
58 45
59 -- 46 --
60 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: - 47 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
61 -- 48 --
62 49
63 CREATE FUNCTION update_time_messung() RETURNS trigger 50 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
64 LANGUAGE plpgsql 51 LANGUAGE plpgsql
65 AS $$ 52 AS $$
66 BEGIN 53 BEGIN
67 RAISE NOTICE 'messung is %',NEW.id; 54 RAISE NOTICE 'messung is %',NEW.id;
68 NEW.tree_modified = now(); 55 NEW.tree_modified = now();
72 END; 59 END;
73 $$; 60 $$;
74 61
75 62
76 -- 63 --
77 -- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
78 --
79
80 CREATE FUNCTION update_time_messwert() RETURNS trigger
81 LANGUAGE plpgsql
82 AS $$
83 BEGIN
84 NEW.tree_modified = now();
85 RETURN NEW;
86 END;
87 $$;
88
89
90 --
91 -- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
92 --
93
94 CREATE FUNCTION update_time_ort() RETURNS trigger
95 LANGUAGE plpgsql
96 AS $$
97 BEGIN
98 NEW.tree_modified = now();
99 RETURN NEW;
100 END;
101 $$;
102
103
104 --
105 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: - 64 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
106 -- 65 --
107 66
108 CREATE FUNCTION update_time_probe() RETURNS trigger 67 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
109 LANGUAGE plpgsql 68 LANGUAGE plpgsql
110 AS $$ 69 AS $$
111 BEGIN 70 BEGIN
112 RAISE NOTICE 'probe is %',NEW.id; 71 RAISE NOTICE 'probe is %',NEW.id;
113 NEW.tree_modified = now(); 72 NEW.tree_modified = now();
114 RAISE NOTICE 'updating other rows'; 73 RAISE NOTICE 'updating other rows';
115 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id; 74 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
116 UPDATE land.ortszuordnung SET tree_modified = now() WHERE probe_id = NEW.id; 75 UPDATE land.ortszuordnung SET tree_modified = now() WHERE probe_id = NEW.id;
117 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id; 76 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
118 RETURN NEW;
119 END;
120 $$;
121
122
123 --
124 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
125 --
126
127 CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
128 LANGUAGE plpgsql
129 AS $$
130 BEGIN
131 NEW.tree_modified = now();
132 RETURN NEW; 77 RETURN NEW;
133 END; 78 END;
134 $$; 79 $$;
135 80
136 81
250 solldatum_ende timestamp without time zone, 195 solldatum_ende timestamp without time zone,
251 tree_modified timestamp without time zone DEFAULT now(), 196 tree_modified timestamp without time zone DEFAULT now(),
252 UNIQUE (mst_id, hauptproben_nr) 197 UNIQUE (mst_id, hauptproben_nr)
253 ); 198 );
254 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 199 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
255 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe(); 200 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
256 201
257 202
258 -- 203 --
259 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: 204 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
260 -- 205 --
280 ortszusatztext character varying(100), 225 ortszusatztext character varying(100),
281 letzte_aenderung timestamp without time zone DEFAULT now(), 226 letzte_aenderung timestamp without time zone DEFAULT now(),
282 tree_modified timestamp without time zone DEFAULT now() 227 tree_modified timestamp without time zone DEFAULT now()
283 ); 228 );
284 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 229 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
285 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort(); 230 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
286 231
287 232
288 -- 233 --
289 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: 234 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
290 -- 235 --
299 nwg_zu_messwert double precision, 244 nwg_zu_messwert double precision,
300 tree_modified timestamp without time zone DEFAULT now(), 245 tree_modified timestamp without time zone DEFAULT now(),
301 UNIQUE (probe_id, pzs_id) 246 UNIQUE (probe_id, pzs_id)
302 ); 247 );
303 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 248 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
304 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); 249 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
305 250
306 251
307 -- 252 --
308 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: - 253 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
309 -- 254 --
397 status integer REFERENCES status_protokoll, 342 status integer REFERENCES status_protokoll,
398 letzte_aenderung timestamp without time zone DEFAULT now(), 343 letzte_aenderung timestamp without time zone DEFAULT now(),
399 geplant boolean DEFAULT false NOT NULL, 344 geplant boolean DEFAULT false NOT NULL,
400 tree_modified timestamp without time zone DEFAULT now() 345 tree_modified timestamp without time zone DEFAULT now()
401 ); 346 );
402 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung(); 347 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
348 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
403 349
404 350
405 -- 351 --
406 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: 352 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
407 -- 353 --
432 letzte_aenderung timestamp without time zone DEFAULT now(), 378 letzte_aenderung timestamp without time zone DEFAULT now(),
433 tree_modified timestamp without time zone DEFAULT now(), 379 tree_modified timestamp without time zone DEFAULT now(),
434 UNIQUE (messungs_id, messgroesse_id) 380 UNIQUE (messungs_id, messgroesse_id)
435 ); 381 );
436 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); 382 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
437 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert(); 383 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
438 384
439 385
440 -- 386 --
441 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: 387 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
442 -- 388 --
449 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE, 395 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
450 status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe, 396 status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe,
451 status_wert integer NOT NULL REFERENCES stammdaten.status_wert, 397 status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
452 tree_modified timestamp without time zone DEFAULT now() 398 tree_modified timestamp without time zone DEFAULT now()
453 ); 399 );
454 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status(); 400 CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
455 401
456 402
457 -- 403 --
458 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: 404 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
459 -- 405 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)