Mercurial > gemma

comparison schema/isrs.sql @ 2081:40711ca3aa19

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add function to get text representation of location code The text representation is appropriate for user visible output as well as for use in SQL expressions where the composite type is not supported.
author Tom Gottfried <tom@intevation.de>
date Thu, 31 Jan 2019 12:36:54 +0100
parents 2304778c4432
children 99274fed6f3d
comparison
equal deleted inserted replaced
2080:1dafdbfca100 2081:40711ca3aa19
28 -- could be validated against countries table. 28 -- could be validated against countries table.
29 locode char(3), -- without the country code: 29 locode char(3), -- without the country code:
30 -- http://www.unece.org/cefact/locode/welcome.html 30 -- http://www.unece.org/cefact/locode/welcome.html
31 fairway_section char(5), 31 fairway_section char(5),
32 orc char(5), -- Object Reference Code 32 orc char(5), -- Object Reference Code
33 hectometre int -- should be constrained to five digits 33 hectometre int -- TODO: should be constrained to five digits
34 ); 34 );
35 35
36 CREATE TYPE isrsrange AS RANGE ( 36 CREATE TYPE isrsrange AS RANGE (
37 subtype = isrs 37 subtype = isrs
38 ); 38 );
65 END IF; 65 END IF;
66 END; 66 END;
67 $$ 67 $$
68 LANGUAGE plpgsql; 68 LANGUAGE plpgsql;
69 69
70 CREATE OR REPLACE FUNCTION isrs_asText(
71 code isrs
72 )
73 RETURNS text
74 AS $$
75 SELECT code.country_code
76 || code.locode
77 || code.fairway_section
78 || code.orc
79 || lpad(CAST(code.hectometre AS text), 5, '0');
80 $$
81 LANGUAGE sql
82 IMMUTABLE PARALLEL SAFE;
83
70 COMMIT; 84 COMMIT;