Mercurial > gemma

annotate schema/isrs.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 99274fed6f3d
children 5b3d778db00c
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1284
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
2 -- without warranty, see README.md and license for details.
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
3
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
6
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
7 -- Copyright (C) 2018 by via donau
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
9 -- Software engineering by Intevation GmbH
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
10
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1284
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1284
d0a5bb018fc9 add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents: 937
diff changeset
13
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 -- Types and functions for ISRS location codes
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 BEGIN;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
20 CREATE TABLE countries (
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
21 country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
22 );
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
23
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
24 CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
25 AS $$
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
26 SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code)
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
27 $$
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
28 LANGUAGE sql
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
29 STABLE PARALLEL SAFE;
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
30
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 -- Types
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 -- See RIS-Index Encoding Guide
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
37 CREATE TYPE isrs_base AS (
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 country_code char(2), -- ISO 3166 country code
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 locode char(3), -- without the country code:
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 -- http://www.unece.org/cefact/locode/welcome.html
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 fairway_section char(5),
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 578
diff changeset
42 orc char(5), -- Object Reference Code
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
43 hectometre int
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 );
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
46 CREATE DOMAIN isrs AS isrs_base
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
47 CHECK (is_country((VALUE).country_code))
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
49
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 CREATE TYPE isrsrange AS RANGE (
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 subtype = isrs
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 );
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 -- Functions
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 CREATE OR REPLACE FUNCTION isrs_fromText(
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 isrs_text varchar
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 )
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 RETURNS isrs
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 AS $$
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 DECLARE
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 isrs_len CONSTANT int = 20;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 BEGIN
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 IF char_length(isrs_text) <> isrs_len
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 THEN
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 RAISE 'ISRS location code must be % characters long', isrs_len
937
72430f050d90 Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents: 579
diff changeset
69 USING ERRCODE = 'invalid_parameter_value',
72430f050d90 Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents: 579
diff changeset
70 DETAIL = 'Failing value: ' || isrs_text;
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 ELSE
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 RETURN CAST((
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 substring(isrs_text from 1 for 2),
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 substring(isrs_text from 3 for 3),
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 substring(isrs_text from 6 for 5),
578
61af85a432bf Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
76 substring(isrs_text from 11 for 5),
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 CAST(substring(isrs_text from 16 for 5) AS int))
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 AS isrs);
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 END IF;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 END;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 $$
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 LANGUAGE plpgsql;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83
2081
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
84 CREATE OR REPLACE FUNCTION isrs_asText(
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
85 code isrs
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
86 )
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
87 RETURNS text
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
88 AS $$
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
89 SELECT code.country_code
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
90 || code.locode
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
91 || code.fairway_section
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
92 || code.orc
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
93 || lpad(CAST(code.hectometre AS text), 5, '0');
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
94 $$
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
95 LANGUAGE sql
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
96 IMMUTABLE PARALLEL SAFE;
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
97
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 COMMIT;