Mercurial > gemma

comparison schema/isrs.sql @ 2381:99274fed6f3d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Validate country and hectometre in ISRS code
author Tom Gottfried <tom@intevation.de>
date Fri, 22 Feb 2019 18:02:23 +0100
parents 40711ca3aa19
children 5b3d778db00c
comparison
equal deleted inserted replaced
2380:5f300bd7316f 2381:99274fed6f3d
15 -- Types and functions for ISRS location codes 15 -- Types and functions for ISRS location codes
16 -- 16 --
17 17
18 BEGIN; 18 BEGIN;
19 19
20 CREATE TABLE countries (
21 country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code
22 );
23
24 CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean
25 AS $$
26 SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code)
27 $$
28 LANGUAGE sql
29 STABLE PARALLEL SAFE;
30
20 -- 31 --
21 -- Types 32 -- Types
22 -- 33 --
23 34
24 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. 35 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
25 -- See RIS-Index Encoding Guide 36 -- See RIS-Index Encoding Guide
26 CREATE TYPE isrs AS ( 37 CREATE TYPE isrs_base AS (
27 country_code char(2), -- ISO 3166 country code 38 country_code char(2), -- ISO 3166 country code
28 -- could be validated against countries table.
29 locode char(3), -- without the country code: 39 locode char(3), -- without the country code:
30 -- http://www.unece.org/cefact/locode/welcome.html 40 -- http://www.unece.org/cefact/locode/welcome.html
31 fairway_section char(5), 41 fairway_section char(5),
32 orc char(5), -- Object Reference Code 42 orc char(5), -- Object Reference Code
33 hectometre int -- TODO: should be constrained to five digits 43 hectometre int
34 ); 44 );
45
46 CREATE DOMAIN isrs AS isrs_base
47 CHECK (is_country((VALUE).country_code))
48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
35 49
36 CREATE TYPE isrsrange AS RANGE ( 50 CREATE TYPE isrsrange AS RANGE (
37 subtype = isrs 51 subtype = isrs
38 ); 52 );
39 53