--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
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.';


SET search_path = functions, pg_catalog;

--
-- Name: compass_dir; Type: TYPE; Schema: functions; Owner: -
--

CREATE TYPE compass_dir AS ENUM (
    'N',
    'E',
    'S',
    'W'
);


--
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
--

CREATE TYPE datatype AS ENUM (
    'str',
    'float'
);


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

CREATE FUNCTION _alt("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, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
$_$;


--
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION _and("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_and(value)
FROM
(VALUES
      ($1)
    , ($2)
    , ($3)
    , ($4)
    , ($5)
)
AS v (value)
$_$;


--
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
--

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


--
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
--

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)
$_$;


--
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: functions; Owner: -
--

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)
FROM 
(
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
    UNION ALL
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
)
matches (g)
$_$;


--
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT sum(value)*COALESCE(functions._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
FROM
(VALUES
      ($1)
    , ($2/60)
    , ($3/60/60)
)
AS v (value)
$_$;


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

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)
$_$;


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

CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 = $2
$_$;


--
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: functions; Owner: -
--

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)
$_$;


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

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)
$_$;


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

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 $_$
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
$_$;


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

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 $_$
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
$_$;


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

CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT coalesce($1 || ': ', '') || $2
$_$;


--
-- Name: _map(hstore, text); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION _map(map hstore, value text) RETURNS text
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
DECLARE
    match text := map -> value;
BEGIN
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
        match := map -> '*'; -- use default entry
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
        END IF;
    END IF;
    
    -- Interpret result
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
    ELSIF match = '*' THEN RETURN value;
    ELSE RETURN match;
    END IF;
END;
$$;


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

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 $_$
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
$_$;


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

CREATE FUNCTION _merge("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
$_$;


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

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)
$_$;


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

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
$_$;


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

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 $_$
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
$_$;


--
-- Name: _not(boolean); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION _not(value boolean) RETURNS boolean
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
SELECT NOT $1
$_$;


--
-- Name: _now(); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION _now() RETURNS timestamp with time zone
    LANGUAGE sql STABLE
    AS $$
SELECT now()
$$;


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

CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
    type functions.datatype NOT NULL := type; -- add NOT NULL
BEGIN
    IF type = 'str' THEN RETURN nullif(value::text, "null");
    -- Invalid value is ignored, but invalid null value generates error
    ELSIF type = 'float' THEN
        DECLARE
            -- Outside the try block so that invalid null value generates error
            "null" double precision := "null"::double precision;
        BEGIN
            RETURN nullif(value::double precision, "null");
        EXCEPTION
            WHEN data_exception THEN RETURN value; -- ignore invalid value
        END;
    END IF;
END;
$$;


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

CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
$_$;


--
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
--

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)
$_$;


--
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
--

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.';


--
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
--

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
$_$;


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

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)
$_$;


--
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
    prefix text := functions.name(type)||'.';
BEGIN
    RETURN QUERY
        SELECT name_, (CASE WHEN functions.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
        FROM functions.col_names(type) f (name_);
END;
$$;


--
-- Name: col_names(regtype); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
    LANGUAGE plpgsql STABLE STRICT
    AS $_$
BEGIN
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
END;
$_$;


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

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*/
$_$;


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

CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
    EXECUTE sql;
EXCEPTION
    WHEN duplicate_table THEN NULL;
END;
$$;


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

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


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

CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
SELECT (CASE WHEN functions.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
$_$;


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

CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
SELECT substring($2 for length($1)) = $1
$_$;


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

CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
SELECT $1 || $3 || $2
$_$;


--
-- Name: name(regtype); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION name(type regtype) RETURNS text
    LANGUAGE sql STABLE STRICT
    AS $_$
SELECT typname::text FROM pg_type WHERE oid = $1
$_$;


--
-- Name: rename_if_exists(text); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION rename_if_exists(sql text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
    EXECUTE sql;
EXCEPTION
    WHEN undefined_column THEN NULL;
END;
$$;


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

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


--
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: functions; Owner: -
--

CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $_$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT * FROM functions.col_global_names(table_::text::regtype)
    LOOP
        IF row.global_name != row.name THEN
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
        END IF;
    END LOOP;
END;
$_$;


--
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: functions; Owner: -
--

COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';


--
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
--

CREATE AGGREGATE join_strs(text, text) (
    SFUNC = join_strs_transform,
    STYPE = text
);


--
-- PostgreSQL database dump complete
--