gemma: schema/isrs.sql annotate

annotate schema/isrs.sql @ 3678:8f58851927c0

client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.

author	Markus Kottlaender <markus@intevation.de>
date	Mon, 17 Jun 2019 17:31:35 +0200
parents	4c585b5d4fe8
children	d9ac1c56bfe0

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
2556 5b3d778db00c schema: extend copyright year to 2019 Bernhard Reiter <bernhard@intevation.de> parents: 2381 diff changeset	7 -- Copyright (C) 2018, 2019 by via donau
1284 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
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	50 CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	51 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	52 /* TODO: handle non-matching combinations of country_codes and
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	53 fairway_sections. Otherwise, this will give unexpected results if
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	54 both hectometre values do not refer to the same river. */
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	55 SELECT (a).hectometre - (b).hectometre
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	56 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	57 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	58
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	59 CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	60 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	61 SELECT isrs_cmp(a, b) < 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	62 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	63 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	64
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	65 CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	66 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	67 SELECT isrs_cmp(a, b) <= 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	68 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	69 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	70
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	71 CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	72 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	73 SELECT isrs_cmp(a, b) = 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	74 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	75 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	76
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	77 CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	78 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	79 SELECT isrs_cmp(a, b) >= 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	80 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	81 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	82
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	83 CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	84 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	85 SELECT isrs_cmp(a, b) > 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	86 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	87 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	88
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	89 CREATE OPERATOR <~ (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	90 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	91 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	92 function = isrslt
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	93 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	94
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	95 CREATE OPERATOR <~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	96 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	97 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	98 function = isrsle
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	99 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	100
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	101 CREATE OPERATOR ~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	102 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	103 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	104 function = isrseq,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	105 commutator = ~=
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	106 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	107
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	108 CREATE OPERATOR >~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	109 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	110 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	111 function = isrsge,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	112 commutator = <~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	113 negator = <~
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	114 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	115
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	116 CREATE OPERATOR >~ (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	117 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	118 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	119 function = isrsgt,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	120 commutator = <~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	121 negator = <~=
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	122 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	123
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	124 CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	125 OPERATOR 1 <~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	126 OPERATOR 2 <~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	127 OPERATOR 3 ~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	128 OPERATOR 4 >~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	129 OPERATOR 5 >~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	130 FUNCTION 1 isrs_cmp;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	131
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	132 CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	133 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	134 SELECT CAST(isrs_cmp(a, b) AS double precision)
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	135 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	136 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	137
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	138 CREATE TYPE isrsrange AS RANGE (
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	139 subtype = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	140 subtype_opclass = isrs_ops,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	141 subtype_diff = isrs_diff
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	142 );
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	143
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 2556 diff changeset	144
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	145 --
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	146 -- Functions
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	147 --
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	148
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	149 CREATE OR REPLACE FUNCTION isrs_fromText(
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	150 isrs_text varchar
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	151 )
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	152 RETURNS isrs
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	153 AS $$
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	154 DECLARE
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	155 isrs_len CONSTANT int = 20;
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	156 BEGIN
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	157 IF char_length(isrs_text) <> isrs_len
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	158 THEN
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	159 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	160 USING ERRCODE = 'invalid_parameter_value',
72430f050d90 Improve error message for isrs_fromText Tom Gottfried <tom@intevation.de> parents: 579 diff changeset	161 DETAIL = 'Failing value: ' \|\| isrs_text;
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	162 ELSE
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	163 RETURN CAST((
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	164 substring(isrs_text from 1 for 2),
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	165 substring(isrs_text from 3 for 3),
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	166 substring(isrs_text from 6 for 5),
578 61af85a432bf Fix extraction of object reference code Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	167 substring(isrs_text from 11 for 5),
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	168 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	169 AS isrs);
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	170 END IF;
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	171 END;
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	172 $$
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	173 LANGUAGE plpgsql;
ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	174
2081 40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	175 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	176 code isrs
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	177 )
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	178 RETURNS text
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	179 AS $$
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	180 SELECT code.country_code
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	181 \|\| code.locode
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	182 \|\| code.fairway_section
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	183 \|\| code.orc
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	184 \|\| 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	185 $$
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	186 LANGUAGE sql
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	187 IMMUTABLE PARALLEL SAFE;
40711ca3aa19 Add function to get text representation of location code Tom Gottfried <tom@intevation.de> parents: 1301 diff changeset	188
569 ad07846b09d1 Add function to construct isrs from text Tom Gottfried <tom@intevation.de> parents: diff changeset	189 COMMIT;

Mercurial > gemma

annotate schema/isrs.sql @ 3678:8f58851927c0