--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET client_min_messages = warning;

--

-- Name: functions; Type: SCHEMA; Schema: -; Owner: -

--

CREATE SCHEMA functions;

-- Name: SCHEMA functions; Type: COMMENT; Schema: -; Owner: -

--

COMMENT ON SCHEMA functions IS 'IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.';

--

CREATE TYPE col_cast AS (

	col_name text,

	type regtype

);

--

--

CREATE TYPE col_ref AS (

	table_ regclass,

	name text

);

--

--

CREATE TYPE compass_dir AS ENUM (

    'N',

    'E',

    'S',

    'W'

);

--

--

CREATE TYPE datatype AS ENUM (

    'str',

    'float'

);

--

    AS $_$

--

    AS $_$

SELECT bool_and(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($4)

    , ($5)

AS v (value)

$_$;

--

--

--

CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT avg(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($3)

    , ($4)

    , ($5)

)

AS v (value)

$_$;

--

--

CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT (g[1]||'1')::integer*functions._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::functions.compass_dir)

(

    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')

    UNION ALL

    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm

matches (g)

--

    AS $_$

(VALUES

    , ($3/60/60)

)

AS v (value)

$_$;

--

--

CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT functions._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::functions.compass_dir)

$_$;

--

--

CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT $1 = $2

$_$;

--

--

CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date

    LANGUAGE sql IMMUTABLE

    AS $_$

-- Fix dates after threshold date

-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx

SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)

$_$;

--

--

CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)

$_$;

--

-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT functions._if($1 != '', $2, $3)

$_$;

--

--

CREATE FUNCTION _join("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

--

--

CREATE FUNCTION _join_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

--

    AS $_$

SELECT coalesce($1 || ': ', '') || $2

$_$;

--

--

CREATE FUNCTION _map(map hstore, value text) RETURNS text

DECLARE

        match := map -> '*'; -- use default entry

        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default

        END IF;

    END IF;

    -- Interpret result

    ELSIF match = '*' THEN RETURN value;

    ELSE RETURN match;

END;

$$;

--

--

CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

--

    AS $_$

(

    SELECT *

    FROM

    (

        SELECT

        DISTINCT ON (value)

        *

        FROM

        (VALUES

            , (2, $2)

            , (3, $3)

            , (4, $4)

            , (5, $5)

            , (6, $6)

            , (7, $7)

            , (8, $8)

            , (9, $9)

            , (10, $10)

        AS v (sort_order, value)

    AS v

    ORDER BY sort_order

)

AS v

$_$;

--

--

CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)

$_$;

--

--

CREATE FUNCTION _merge_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT functions.join_strs(value, ' ')

FROM

(

    SELECT *

    FROM

    (

        SELECT

        DISTINCT ON (value)

        *

        FROM

        (VALUES

              (1, $1)

            , (2, $2)

            , (3, $3)

            , (4, $4)

            , (5, $5)

            , (6, $6)

            , (7, $7)

            , (8, $8)

            , (9, $9)

            , (10, $10)

        )

        AS v (sort_order, value)

        WHERE value IS NOT NULL

    )

    AS v

    ORDER BY sort_order

)

AS v

$_$;

--

--

CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

--

--

CREATE FUNCTION _not(value boolean) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT NOT $1

$_$;

--

--

CREATE FUNCTION _now() RETURNS timestamp with time zone

    LANGUAGE sql STABLE

    AS $$

SELECT now()

$$;

--

    AS $$

DECLARE

    type functions.datatype NOT NULL := type; -- add NOT NULL

BEGIN

            -- Outside the try block so that invalid null value generates error

END;

$$;

--

--

CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT functions."_nullIf"($1, $2, $3::functions.datatype)

$_$;

--

--

CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT bool_or(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($3)

    , ($4)

    , ($5)

)

AS v (value)

$_$;

--

--

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.';

--

--

CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT $2 - $1

$_$;

--

--

CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT regexp_split_to_table($1, $2)

$_$;

--

--

CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered

$_$;

--

--

CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $_$

BEGIN

    -- not yet clustered (ARRAY[] compares NULLs literally)

    IF ARRAY[functions.cluster_index(table_)] != ARRAY[index] THEN

        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;

    END IF;

END;

$_$;

--

-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: functions; Owner: -

--

COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';

--

--

CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record

DECLARE

    prefix text := functions.name(type)||'.';

BEGIN

    RETURN QUERY

$$;

--

--

CREATE FUNCTION col_names(type regtype) RETURNS SETOF text

BEGIN

    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;

END;

$_$;

--

--

CREATE FUNCTION col_type(col col_ref) RETURNS regtype

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT atttypid FROM pg_attribute

WHERE attrelid = $1.table_ AND attname = $1.name

$_$;

--

--

CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT position($1 in $2) > 0 /*1-based offset*/

$_$;

--

--

CREATE FUNCTION create_if_not_exists(sql text) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $$

BEGIN

    EXECUTE sql;

EXCEPTION

    WHEN duplicate_column THEN NULL;

$$;

--

-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: functions; Owner: -

--

COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';

--

--

CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text

SELECT (CASE WHEN functions.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)

$_$;

--

--

CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT substring($2 for length($1)) = $1

$_$;

--

--

--

CREATE FUNCTION name(type regtype) RETURNS text

SELECT typname::text FROM pg_type WHERE oid = $1

$_$;

--

--

CREATE FUNCTION rename_if_exists(sql text) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $$

BEGIN

    EXECUTE sql;