Mercurial > gemma

diff schema/isrs_functions.sql @ 3731:0d45a3c34900

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
schema: Added function to detect string representation of ISRS codes.
author Sascha Wilde <wilde@intevation.de>
date Mon, 24 Jun 2019 11:21:41 +0200
parents 453f15ba8030
children e8b2dc771f9e
line wrap: on
line diff
--- a/schema/isrs_functions.sql	Mon Jun 24 11:20:34 2019 +0200
+++ b/schema/isrs_functions.sql	Mon Jun 24 11:21:41 2019 +0200
@@ -210,3 +210,16 @@
     $$
     LANGUAGE plpgsql
     STABLE PARALLEL SAFE;
+
+
+-- Check if a given string looks like an ISRS code
+CREATE OR REPLACE FUNCTION is_ISRSstring(str text) RETURNS boolean
+AS $$
+BEGIN
+  str = upper(str);
+  RETURN (SELECT str SIMILAR TO '[A-Z]{2}[A-Z0-9]{13}[0-9]{5}')
+         AND is_country(substring(str from 1 for 2));
+END;
+    $$
+    LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;