Mercurial > gemma

comparison schema/isrs.sql @ 3566:4c585b5d4fe8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce hectometer based order for ranges of ISRS location codes
author Tom Gottfried <tom@intevation.de>
date Fri, 31 May 2019 20:06:27 +0200
parents 5b3d778db00c
children d9ac1c56bfe0
comparison
equal deleted inserted replaced
3565:f6d67697f158 3566:4c585b5d4fe8
45 45
46 CREATE DOMAIN isrs AS isrs_base 46 CREATE DOMAIN isrs AS isrs_base
47 CHECK (is_country((VALUE).country_code)) 47 CHECK (is_country((VALUE).country_code))
48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999); 48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
49 49
50 CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int
51 AS $$
52 /* TODO: handle non-matching combinations of country_codes and
53 fairway_sections. Otherwise, this will give unexpected results if
54 both hectometre values do not refer to the same river. */
55 SELECT (a).hectometre - (b).hectometre
56 $$ LANGUAGE sql
57 IMMUTABLE PARALLEL SAFE;
58
59 CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
60 AS $$
61 SELECT isrs_cmp(a, b) < 0
62 $$ LANGUAGE sql
63 IMMUTABLE PARALLEL SAFE;
64
65 CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
66 AS $$
67 SELECT isrs_cmp(a, b) <= 0
68 $$ LANGUAGE sql
69 IMMUTABLE PARALLEL SAFE;
70
71 CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
72 AS $$
73 SELECT isrs_cmp(a, b) = 0
74 $$ LANGUAGE sql
75 IMMUTABLE PARALLEL SAFE;
76
77 CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
78 AS $$
79 SELECT isrs_cmp(a, b) >= 0
80 $$ LANGUAGE sql
81 IMMUTABLE PARALLEL SAFE;
82
83 CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
84 AS $$
85 SELECT isrs_cmp(a, b) > 0
86 $$ LANGUAGE sql
87 IMMUTABLE PARALLEL SAFE;
88
89 CREATE OPERATOR <~ (
90 leftarg = isrs,
91 rightarg = isrs,
92 function = isrslt
93 );
94
95 CREATE OPERATOR <~= (
96 leftarg = isrs,
97 rightarg = isrs,
98 function = isrsle
99 );
100
101 CREATE OPERATOR ~= (
102 leftarg = isrs,
103 rightarg = isrs,
104 function = isrseq,
105 commutator = ~=
106 );
107
108 CREATE OPERATOR >~= (
109 leftarg = isrs,
110 rightarg = isrs,
111 function = isrsge,
112 commutator = <~=,
113 negator = <~
114 );
115
116 CREATE OPERATOR >~ (
117 leftarg = isrs,
118 rightarg = isrs,
119 function = isrsgt,
120 commutator = <~,
121 negator = <~=
122 );
123
124 CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS
125 OPERATOR 1 <~,
126 OPERATOR 2 <~=,
127 OPERATOR 3 ~=,
128 OPERATOR 4 >~=,
129 OPERATOR 5 >~,
130 FUNCTION 1 isrs_cmp;
131
132 CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
133 AS $$
134 SELECT CAST(isrs_cmp(a, b) AS double precision)
135 $$ LANGUAGE sql
136 IMMUTABLE PARALLEL SAFE;
137
50 CREATE TYPE isrsrange AS RANGE ( 138 CREATE TYPE isrsrange AS RANGE (
51 subtype = isrs 139 subtype = isrs,
140 subtype_opclass = isrs_ops,
141 subtype_diff = isrs_diff
52 ); 142 );
143
53 144
54 -- 145 --
55 -- Functions 146 -- Functions
56 -- 147 --
57 148