diff 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
line wrap: on
line diff
--- a/db_schema/stammdaten_schema.sql	Thu Nov 10 13:49:31 2016 +0100
+++ b/db_schema/stammdaten_schema.sql	Wed Nov 16 15:56:43 2016 +0100
@@ -14,6 +14,24 @@
 
 SET search_path = stammdaten, pg_catalog;
 
+CREATE FUNCTION set_ort_id() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        value = '#'::text || lpad((NEW.id::character varying)::text, 9, '0'::text);
+        IF NEW.ort_id IS NULL THEN
+            NEW.ort_id = value;
+        END IF;
+        IF NEW.langtext IS NULL THEN
+            NEW.langtext = value;
+        END IF;
+        IF NEW.kurztext IS NULL THEN
+            NEW.kurztext = value;
+        END IF;
+        RETURN NEW;
+    END;
+$$;
+
 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
@@ -450,6 +468,7 @@
 );
 
 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
+CREATE TRIGGER set_ort_id_ort BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE set_ort_id();
 
 ALTER TABLE ONLY ort
     ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id);
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)