Mercurial > gemma

changeset 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 1dafdbfca100
children b2451f00f57d
files schema/isrs.sql schema/isrs_tests.sql
diffstat 2 files changed, 20 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/schema/isrs.sql	Thu Jan 31 11:29:43 2019 +0100
+++ b/schema/isrs.sql	Thu Jan 31 12:36:54 2019 +0100
@@ -30,7 +30,7 @@
     -- http://www.unece.org/cefact/locode/welcome.html
     fairway_section char(5),
     orc char(5), -- Object Reference Code
-    hectometre int -- should be constrained to five digits
+    hectometre int -- TODO: should be constrained to five digits
 );
 
 CREATE TYPE isrsrange AS RANGE (
@@ -67,4 +67,18 @@
 $$
     LANGUAGE plpgsql;
 
+CREATE OR REPLACE FUNCTION isrs_asText(
+    code isrs
+    )
+    RETURNS text
+AS $$
+    SELECT code.country_code
+        || code.locode
+        || code.fairway_section
+        || code.orc
+        || lpad(CAST(code.hectometre AS text), 5, '0');
+$$
+    LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
+
 COMMIT;
--- a/schema/isrs_tests.sql	Thu Jan 31 11:29:43 2019 +0100
+++ b/schema/isrs_tests.sql	Thu Jan 31 12:36:54 2019 +0100
@@ -30,6 +30,11 @@
     'ISRS text input needs to have correct length');
 
 SELECT ok(
+    'DEBON03901G007906548' = isrs_asText(isrs_fromText('DEBON03901G007906548'))
+    ,
+    'isrs_asText() is the inverse of isrs_fromText()');
+
+SELECT ok(
     ISRSrange_area(isrsrange(
             ('AT', 'XXX', '00001', '00000', 0)::isrs,
             ('AT', 'XXX', '00001', '00000', 1)::isrs),