Mercurial > lada > lada-server
comparison db_schema/lada_schema.sql @ 1164:84ff77bba9b0
Let the database generate id_alt and initial status for new messung objects.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Wed, 09 Nov 2016 12:39:08 +0100 |
| parents | 48c0132dbc85 |
| children | cd9bf2430eef |
comparison
equal
deleted
inserted
replaced
| 1163:315df94c679b | 1164:84ff77bba9b0 |
|---|---|
| 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 CREATE FUNCTION set_messung_id_alt() RETURNS trigger | |
| 22 LANGUAGE plpgsql | |
| 23 AS $$ | |
| 24 BEGIN | |
| 25 IF NEW.id_alt IS NULL THEN | |
| 26 NEW.id_alt = ( | |
| 27 SELECT count(*) | |
| 28 FROM land.messung | |
| 29 WHERE probe_id = NEW.probe_id) + 1; | |
| 30 END IF; | |
| 31 RETURN NEW; | |
| 32 END; | |
| 33 $$; | |
| 34 | |
| 35 CREATE FUNCTION set_messung_status() RETURNS trigger | |
| 36 LANGUAGE plpgsql | |
| 37 AS $$ | |
| 38 DECLARE status_id integer; | |
| 39 BEGIN | |
| 40 INSERT INTO land.status_protokoll | |
| 41 (mst_id, datum, text, messungs_id, status_kombi) | |
| 42 VALUES ((SELECT mst_id | |
| 43 FROM land.probe | |
| 44 WHERE id = NEW.probe_id), | |
| 45 now(), '', NEW.id, 1) | |
| 46 RETURNING id into status_id; | |
| 47 UPDATE land.messung SET status = status_id where id = NEW.id; | |
| 48 RETURN NEW; | |
| 49 END; | |
| 50 $$; | |
| 21 | 51 |
| 22 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | 52 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger |
| 23 LANGUAGE plpgsql | 53 LANGUAGE plpgsql |
| 24 AS $$ | 54 AS $$ |
| 25 BEGIN | 55 BEGIN |
| 277 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: | 307 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: |
| 278 -- | 308 -- |
| 279 | 309 |
| 280 CREATE TABLE messung ( | 310 CREATE TABLE messung ( |
| 281 id serial PRIMARY KEY, | 311 id serial PRIMARY KEY, |
| 282 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL, | 312 id_alt integer NOT NULL, |
| 283 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE, | 313 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE, |
| 284 nebenproben_nr character varying(10), | 314 nebenproben_nr character varying(10), |
| 285 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE, | 315 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE, |
| 286 messdauer integer, | 316 messdauer integer, |
| 287 messzeitpunkt timestamp with time zone, | 317 messzeitpunkt timestamp with time zone, |
| 291 geplant boolean DEFAULT false NOT NULL, | 321 geplant boolean DEFAULT false NOT NULL, |
| 292 tree_modified timestamp without time zone DEFAULT now() | 322 tree_modified timestamp without time zone DEFAULT now() |
| 293 ); | 323 ); |
| 294 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | 324 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
| 295 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung(); | 325 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung(); |
| 296 | 326 CREATE TRIGGER id_alt_messung BEFORE INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_id_alt(); |
| 327 CREATE TRIGGER status_messung AFTER INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_status(); | |
| 297 | 328 |
| 298 -- | 329 -- |
| 299 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: | 330 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: |
| 300 -- | 331 -- |
| 301 | 332 |