Mercurial > gemma
comparison schema/isrs.sql @ 569:ad07846b09d1
Add function to construct isrs from text
In passing moved all related functionality to its own set of files.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 05 Sep 2018 11:05:58 +0200 |
| parents | |
| children | 61af85a432bf |
comparison
equal
deleted
inserted
replaced
| 568:99968a7394f7 | 569:ad07846b09d1 |
|---|---|
| 1 -- | |
| 2 -- Types and functions for ISRS location codes | |
| 3 -- | |
| 4 | |
| 5 BEGIN; | |
| 6 | |
| 7 -- | |
| 8 -- Types | |
| 9 -- | |
| 10 | |
| 11 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. | |
| 12 -- See RIS-Index Encoding Guide | |
| 13 CREATE TYPE isrs AS ( | |
| 14 country_code char(2), -- ISO 3166 country code | |
| 15 -- could be validated against countries table. | |
| 16 locode char(3), -- without the country code: | |
| 17 -- http://www.unece.org/cefact/locode/welcome.html | |
| 18 fairway_section char(5), | |
| 19 object_reference char(5), | |
| 20 hectometre int -- should be constrained to five digits | |
| 21 ); | |
| 22 | |
| 23 CREATE TYPE isrsrange AS RANGE ( | |
| 24 subtype = isrs | |
| 25 ); | |
| 26 | |
| 27 -- | |
| 28 -- Functions | |
| 29 -- | |
| 30 | |
| 31 CREATE OR REPLACE FUNCTION isrs_fromText( | |
| 32 isrs_text varchar | |
| 33 ) | |
| 34 RETURNS isrs | |
| 35 AS $$ | |
| 36 DECLARE | |
| 37 isrs_len CONSTANT int = 20; | |
| 38 BEGIN | |
| 39 IF char_length(isrs_text) <> isrs_len | |
| 40 THEN | |
| 41 RAISE 'ISRS location code must be % characters long', isrs_len | |
| 42 USING ERRCODE = 'invalid_parameter_value'; | |
| 43 ELSE | |
| 44 RETURN CAST(( | |
| 45 substring(isrs_text from 1 for 2), | |
| 46 substring(isrs_text from 3 for 3), | |
| 47 substring(isrs_text from 6 for 5), | |
| 48 substring(isrs_text from 10 for 5), | |
| 49 CAST(substring(isrs_text from 16 for 5) AS int)) | |
| 50 AS isrs); | |
| 51 END IF; | |
| 52 END; | |
| 53 $$ | |
| 54 LANGUAGE plpgsql; | |
| 55 | |
| 56 COMMIT; |