[121] | 1 | -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
---|
| 2 | -- |
---|
| 3 | -- unique_id - Eindeutigkeit von "gml_id", "beginnt" und "endet" |
---|
[122] | 4 | -- sicherstellen und Historie auf Basis der "gml_id" pflegen |
---|
| 5 | -- - allgemeiner Trigger fÃŒr alle Tabellen auÃer "alkis_beziehungen" |
---|
[121] | 6 | -- |
---|
| 7 | -- Fr. Nov 4 2011 ralf dot suhr at itc-halle dot de |
---|
| 8 | -- |
---|
| 9 | -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - |
---|
| 10 | |
---|
| 11 | |
---|
| 12 | SET client_encoding = 'UTF-8'; |
---|
| 13 | |
---|
| 14 | |
---|
| 15 | CREATE OR REPLACE FUNCTION public.unique_id() |
---|
| 16 | RETURNS TRIGGER AS |
---|
| 17 | $$ |
---|
| 18 | DECLARE |
---|
| 19 | _test boolean; |
---|
| 20 | BEGIN |
---|
| 21 | |
---|
| 22 | IF NEW.gml_id IS NULL THEN |
---|
| 23 | RAISE NOTICE 'Leere gml_id in % abgelehnt', TG_RELNAME; |
---|
| 24 | RETURN NULL; |
---|
| 25 | END IF; |
---|
| 26 | |
---|
| 27 | -- doppelte DatensÀtze vermeiden (auch NBA) |
---|
| 28 | FOR _test IN EXECUTE ' |
---|
| 29 | SELECT TRUE |
---|
| 30 | FROM public.' || TG_RELNAME || ' |
---|
| 31 | WHERE gml_id = ''' || substr(NEW.gml_id, 1 , 16) || ''' AND |
---|
| 32 | beginnt = ''' || NEW.beginnt || ''' AND |
---|
| 33 | endet IS NULL |
---|
| 34 | LIMIT 1' LOOP |
---|
| 35 | -- Abarbeitung beenden |
---|
| 36 | RETURN NULL; |
---|
| 37 | END LOOP; |
---|
| 38 | |
---|
| 39 | -- auf NBA Update testen |
---|
| 40 | IF char_length(NEW.gml_id) > 18 THEN |
---|
| 41 | NEW.gml_id := substr(NEW.gml_id, 1 , 16); |
---|
| 42 | -- abgelaufenen Datensatz markieren (Enddatum setzen) |
---|
| 43 | EXECUTE 'UPDATE public.' || TG_RELNAME || ' SET endet = ''' || NEW.beginnt || ''' |
---|
| 44 | WHERE gml_id = ''' || NEW.gml_id || ''' AND endet IS NULL'; |
---|
| 45 | END IF; |
---|
| 46 | |
---|
| 47 | -- Lebenszeitintervall nachtragen (ATKIS) |
---|
| 48 | IF NEW.beginnt IS NULL THEN |
---|
| 49 | NEW.beginnt := NOW()::text; |
---|
| 50 | END IF; |
---|
| 51 | |
---|
| 52 | -- alles zurÃŒckgeben |
---|
| 53 | RETURN NEW; |
---|
| 54 | END; |
---|
| 55 | $$ |
---|
| 56 | LANGUAGE 'plpgsql'; |
---|