--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/isrs.sql	Wed Sep 05 11:05:58 2018 +0200
@@ -0,0 +1,56 @@
+--
+-- Types and functions for ISRS location codes
+--
+
+BEGIN;
+
+--
+-- Types
+--
+
+-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
+-- See RIS-Index Encoding Guide
+CREATE TYPE isrs AS (
+    country_code char(2), -- ISO 3166 country code
+    -- could be validated against countries table.
+    locode char(3), -- without the country code:
+    -- http://www.unece.org/cefact/locode/welcome.html
+    fairway_section char(5),
+    object_reference char(5),
+    hectometre int -- should be constrained to five digits
+);
+
+CREATE TYPE isrsrange AS RANGE (
+    subtype = isrs
+);
+
+--
+-- Functions
+--
+
+CREATE OR REPLACE FUNCTION isrs_fromText(
+    isrs_text varchar
+    )
+    RETURNS isrs
+AS $$
+DECLARE
+    isrs_len CONSTANT int = 20;
+BEGIN
+    IF char_length(isrs_text) <> isrs_len
+    THEN
+        RAISE 'ISRS location code must be % characters long', isrs_len
+            USING ERRCODE = 'invalid_parameter_value';
+    ELSE
+        RETURN CAST((
+            substring(isrs_text from 1 for 2),
+            substring(isrs_text from 3 for 3),
+            substring(isrs_text from 6 for 5),
+            substring(isrs_text from 10 for 5),
+            CAST(substring(isrs_text from 16 for 5) AS int))
+            AS isrs);
+    END IF;
+END;
+$$
+    LANGUAGE plpgsql;
+
+COMMIT;