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 |