Mercurial > gemma

view schema/updates/1201/01.fix_support_functions.sql @ 4431:8fcabb6f971e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Sep 2019 17:26:28 +0200
parents
children
line wrap: on
line source

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;