Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 1175:60b91dbb98cc
Added trigger to fill stammdaten.ort attributes.
author | Raimund Renkert <raimund.renkert@intevation.de> |
---|---|
date | Wed, 16 Nov 2016 15:56:43 +0100 |
parents | b49c1fb84040 |
children | 33dc7dc6b7bc |
comparison
equal
deleted
inserted
replaced
1172:7c510615a4e8 | 1175:60b91dbb98cc |
---|---|
11 | 11 |
12 | 12 |
13 CREATE SCHEMA stammdaten; | 13 CREATE SCHEMA stammdaten; |
14 | 14 |
15 SET search_path = stammdaten, pg_catalog; | 15 SET search_path = stammdaten, pg_catalog; |
16 | |
17 CREATE FUNCTION set_ort_id() RETURNS trigger | |
18 LANGUAGE plpgsql | |
19 AS $$ | |
20 BEGIN | |
21 value = '#'::text || lpad((NEW.id::character varying)::text, 9, '0'::text); | |
22 IF NEW.ort_id IS NULL THEN | |
23 NEW.ort_id = value; | |
24 END IF; | |
25 IF NEW.langtext IS NULL THEN | |
26 NEW.langtext = value; | |
27 END IF; | |
28 IF NEW.kurztext IS NULL THEN | |
29 NEW.kurztext = value; | |
30 END IF; | |
31 RETURN NEW; | |
32 END; | |
33 $$; | |
16 | 34 |
17 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | 35 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger |
18 LANGUAGE plpgsql | 36 LANGUAGE plpgsql |
19 AS $$ | 37 AS $$ |
20 BEGIN | 38 BEGIN |
448 hoehe_ueber_nn real, | 466 hoehe_ueber_nn real, |
449 UNIQUE(ort_id, netzbetreiber_id) | 467 UNIQUE(ort_id, netzbetreiber_id) |
450 ); | 468 ); |
451 | 469 |
452 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | 470 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
471 CREATE TRIGGER set_ort_id_ort BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE set_ort_id(); | |
453 | 472 |
454 ALTER TABLE ONLY ort | 473 ALTER TABLE ONLY ort |
455 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id); | 474 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id); |
456 | 475 |
457 | 476 |