Mercurial > gemma

comparison schema/gemma.sql @ 4041:3fcb95a07948 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Started to remove references to gauge validity.
author Sascha Wilde <wilde@intevation.de>
date Tue, 23 Jul 2019 13:10:51 +0200
parents 9ab7e1056360
children fbd7c012f10c
comparison
equal deleted inserted replaced
4040:39441cdc5021 4041:3fcb95a07948
66 TABLE = TG_TABLE_NAME, 66 TABLE = TG_TABLE_NAME,
67 COLUMN = TG_ARGV[0], 67 COLUMN = TG_ARGV[0],
68 CONSTRAINT = TG_NAME; 68 CONSTRAINT = TG_NAME;
69 END IF; 69 END IF;
70 RETURN NEW; 70 RETURN NEW;
71 END;
72 $$
73 LANGUAGE plpgsql;
74
75 -- Trigger function to be used as a constraint trigger to enforce
76 -- existance of a referenced gauge with intersecting validity. The
77 -- columns with the referenced gauge isrs code an the validity are
78 -- given as arguments to the trigger function.
79 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
80 $$
81 DECLARE
82 -- FIXME: I'm using text for the isrs code and cast it on demand.
83 -- If someone is able to get it to work with isrs or isrs_base as
84 -- type, feel free to show me how its done... ;-) [sw]
85 referenced_gauge text;
86 new_validity tstzrange;
87 BEGIN
88 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
89 INTO referenced_gauge
90 USING NEW;
91 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
92 INTO new_validity
93 USING NEW;
94 IF EXISTS ( SELECT * FROM waterway.gauges
95 WHERE location = referenced_gauge::isrs
96 AND validity && new_validity )
97 THEN
98 RETURN NEW;
99 ELSE
100 RAISE EXCEPTION
101 'new row for relation "%" violates constraint trigger "%"',
102 TG_TABLE_NAME, TG_NAME
103 USING
104 DETAIL = format('No matching gauge %s found.',
105 isrs_AsText(referenced_gauge::isrs)),
106 ERRCODE = 23505,
107 SCHEMA = TG_TABLE_SCHEMA,
108 TABLE = TG_TABLE_NAME,
109 COLUMN = TG_ARGV[0],
110 CONSTRAINT = TG_NAME;
111 END IF;
71 END; 112 END;
72 $$ 113 $$
73 LANGUAGE plpgsql; 114 LANGUAGE plpgsql;
74 115
75 -- Trigger functions to be used as statement-level AFTER triggers, 116 -- Trigger functions to be used as statement-level AFTER triggers,
646 validity tstzrange NOT NULL CHECK (NOT isempty(validity)), 687 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
647 UNIQUE (bottleneck_id, validity), 688 UNIQUE (bottleneck_id, validity),
648 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) 689 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
649 DEFERRABLE INITIALLY DEFERRED, 690 DEFERRABLE INITIALLY DEFERRED,
650 gauge_location isrs NOT NULL, 691 gauge_location isrs NOT NULL,
651 gauge_validity tstzrange NOT NULL,
652 CHECK(validity <@ gauge_validity),
653 CONSTRAINT gauge_key
654 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
655 ON UPDATE CASCADE,
656 objnam varchar, 692 objnam varchar,
657 nobjnm varchar, 693 nobjnm varchar,
658 stretch isrsrange NOT NULL, 694 stretch isrsrange NOT NULL,
659 area geography(MULTIPOLYGON, 4326) NOT NULL 695 area geography(MULTIPOLYGON, 4326) NOT NULL
660 CHECK(ST_IsValid(CAST(area AS geometry))), 696 CHECK(ST_IsValid(CAST(area AS geometry))),
673 -- XXX: Also an attribut of sounding result? 709 -- XXX: Also an attribut of sounding result?
674 date_info timestamp with time zone NOT NULL, 710 date_info timestamp with time zone NOT NULL,
675 source_organization varchar NOT NULL, 711 source_organization varchar NOT NULL,
676 staging_done boolean NOT NULL DEFAULT false 712 staging_done boolean NOT NULL DEFAULT false
677 ) 713 )
714 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
715 AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
716 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
678 -- Associate referencing objects to matching bottleneck version 717 -- Associate referencing objects to matching bottleneck version
679 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity 718 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
680 ON bottlenecks FOR EACH ROW 719 ON bottlenecks FOR EACH ROW
681 EXECUTE FUNCTION move_bottleneck_referencing() 720 EXECUTE FUNCTION move_bottleneck_referencing()
682 721