# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1568820388 -7200
# Node ID 8fcabb6f971e4a74f555eb09c6b455ed76725b51
# Parent  86c89824aab481bf248e80a84678ce429233941c
Fix operator support functions

Operators used in an operator class should not be based on SQL functions,
which might be inlined, thus preventing index usage based on the
operator.

Schema qualify functions called inside functions to make the outer
function work independendly from search_path setting. This makes
it possible to use the operators using the 'OPERATOR(<schema>.<opname>)'
syntax. Most importantly, it fixes the usage of isrs_diff() during
autovacuum.

diff -r 86c89824aab4 -r 8fcabb6f971e schema/isrs.sql
--- a/schema/isrs.sql	Wed Sep 18 16:36:06 2019 +0200
+++ b/schema/isrs.sql	Wed Sep 18 17:26:28 2019 +0200
@@ -58,32 +58,42 @@
 
 CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
 AS $$
-    SELECT isrs_cmp(a, b) < 0
-$$ LANGUAGE sql
+BEGIN
+    RETURN public.isrs_cmp(a, b) < 0;
+END;
+$$ LANGUAGE plpgsql
     IMMUTABLE PARALLEL SAFE;
 
 CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
 AS $$
-    SELECT isrs_cmp(a, b) <= 0
-$$ LANGUAGE sql
+BEGIN
+    RETURN public.isrs_cmp(a, b) <= 0;
+END;
+$$ LANGUAGE plpgsql
     IMMUTABLE PARALLEL SAFE;
 
 CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
 AS $$
-    SELECT isrs_cmp(a, b) = 0
-$$ LANGUAGE sql
+BEGIN
+    RETURN public.isrs_cmp(a, b) = 0;
+END;
+$$ LANGUAGE plpgsql
     IMMUTABLE PARALLEL SAFE;
 
 CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
 AS $$
-    SELECT isrs_cmp(a, b) >= 0
-$$ LANGUAGE sql
+BEGIN
+    RETURN public.isrs_cmp(a, b) >= 0;
+END;
+$$ LANGUAGE plpgsql
     IMMUTABLE PARALLEL SAFE;
 
 CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
 AS $$
-    SELECT isrs_cmp(a, b) > 0
-$$ LANGUAGE sql
+BEGIN
+    RETURN public.isrs_cmp(a, b) > 0;
+END;
+$$ LANGUAGE plpgsql
     IMMUTABLE PARALLEL SAFE;
 
 CREATE OPERATOR <~ (
@@ -131,7 +141,7 @@
 
 CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
 AS $$
-    SELECT CAST(isrs_cmp(a, b) AS double precision)
+    SELECT CAST(public.isrs_cmp(a, b) AS double precision)
 $$ LANGUAGE sql
     IMMUTABLE PARALLEL SAFE;
 
diff -r 86c89824aab4 -r 8fcabb6f971e schema/isrs_tests.sql
--- a/schema/isrs_tests.sql	Wed Sep 18 16:36:06 2019 +0200
+++ b/schema/isrs_tests.sql	Wed Sep 18 17:26:28 2019 +0200
@@ -81,6 +81,15 @@
         isrs_fromText('ATXXX000000000006570')),
     'isrsrange: Overlap depends on hectometre');
 
+SELECT lives_ok($$
+    SET search_path TO '';
+    SELECT public.isrs_diff(
+        CAST('(AT,XXX,00000,00000,0)' as public.isrs),
+        CAST('(AT,XXX,00000,00000,1)' as public.isrs));
+    RESET search_path;
+    $$,
+    'Support function runs with empty search path (as during autovacuum)');
+
 --
 -- Geometry processing
 --
diff -r 86c89824aab4 -r 8fcabb6f971e schema/run_tests.sh
--- a/schema/run_tests.sh	Wed Sep 18 16:36:06 2019 +0200
+++ b/schema/run_tests.sh	Wed Sep 18 17:26:28 2019 +0200
@@ -80,7 +80,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f "$BASEDIR"/tap_tests_data.sql \
-    -c "SELECT plan(74 + (
+    -c "SELECT plan(75 + (
             SELECT count(*)::int
                 FROM information_schema.tables
                 WHERE table_schema = 'waterway'))" \
diff -r 86c89824aab4 -r 8fcabb6f971e schema/updates/1201/01.fix_support_functions.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1201/01.fix_support_functions.sql	Wed Sep 18 17:26:28 2019 +0200
@@ -0,0 +1,45 @@
+CREATE OR REPLACE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
+AS $$
+BEGIN
+    RETURN public.isrs_cmp(a, b) < 0;
+END;
+$$ LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OR REPLACE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
+AS $$
+BEGIN
+    RETURN public.isrs_cmp(a, b) <= 0;
+END;
+$$ LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OR REPLACE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
+AS $$
+BEGIN
+    RETURN public.isrs_cmp(a, b) = 0;
+END;
+$$ LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OR REPLACE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
+AS $$
+BEGIN
+    RETURN public.isrs_cmp(a, b) >= 0;
+END;
+$$ LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OR REPLACE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
+AS $$
+BEGIN
+    RETURN public.isrs_cmp(a, b) > 0;
+END;
+$$ LANGUAGE plpgsql
+    IMMUTABLE PARALLEL SAFE;
+
+CREATE OR REPLACE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
+AS $$
+    SELECT CAST(public.isrs_cmp(a, b) AS double precision)
+$$ LANGUAGE sql
+    IMMUTABLE PARALLEL SAFE;
diff -r 86c89824aab4 -r 8fcabb6f971e schema/version.sql
--- a/schema/version.sql	Wed Sep 18 16:36:06 2019 +0200
+++ b/schema/version.sql	Wed Sep 18 17:26:28 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1200);
+INSERT INTO gemma_schema_version(version) VALUES (1201);