--

-- 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: py_functions; Type: SCHEMA; Schema: -; Owner: bien

--

CREATE SCHEMA py_functions;

ALTER SCHEMA py_functions OWNER TO bien;

--

-- Name: SCHEMA py_functions; Type: COMMENT; Schema: -; Owner: bien

--

COMMENT ON SCHEMA py_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 = py_functions, pg_catalog;

--

-- Name: _date(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION _date(date timestamp with time zone) RETURNS timestamp with time zone

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1

$_$;

ALTER FUNCTION py_functions._date(date timestamp with time zone) OWNER TO bien;

--

-- Name: _date(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION _date(year text DEFAULT NULL::text, month text DEFAULT NULL::text, day text DEFAULT NULL::text) RETURNS text

    LANGUAGE plpython3u IMMUTABLE

    AS $$

global date, year, month, day

import datetime

def e_msg(e): return e.args[0].rstrip()

# Year is required

if year == None:

    if month == None and day == None: return None # entire date is empty

    else: raise AssertionError(

        'null value in column "year" violates not-null constraint')

# Convert month name to number

if month != None and not month.isdigit(): # month is name

    month = str(datetime.datetime.strptime(month, '%b').month)

if month == None: month = 1

if day == None: day = 1

year, month, day = map(int, (year, month, day))

for try_num in range(2):

    try:

        date = datetime.date(year, month, day)

        break

    except ValueError as e:

        if try_num > 0: raise # exception still raised after retry

        msg = e_msg(e)

        if msg == 'month must be in 1..12': # try swapping month and day

            month, day = day, month

        else: raise

return str(date)

$$;

ALTER FUNCTION py_functions._date(year text, month text, day text) OWNER TO bien;

--

-- Name: _date(integer, integer, integer); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION _date(year integer DEFAULT NULL::integer, month integer DEFAULT NULL::integer, day integer DEFAULT NULL::integer) RETURNS text

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT py_functions._date($1::text, $2::text, $3::text)

$_$;

ALTER FUNCTION py_functions._date(year integer, month integer, day integer) OWNER TO bien;

--

-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT (py_functions.parse_date_range($1))[2]

$_$;

ALTER FUNCTION py_functions."_dateRangeEnd"(value text) OWNER TO bien;

--

-- Name: _dateRangeEnd(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION "_dateRangeEnd"(value timestamp with time zone) RETURNS timestamp with time zone

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1

$_$;

ALTER FUNCTION py_functions."_dateRangeEnd"(value timestamp with time zone) OWNER TO bien;

--

-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT (py_functions.parse_date_range($1))[1]

$_$;

ALTER FUNCTION py_functions."_dateRangeStart"(value text) OWNER TO bien;

--

-- Name: _dateRangeStart(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION "_dateRangeStart"(value timestamp with time zone) RETURNS timestamp with time zone

    LANGUAGE sql IMMUTABLE STRICT

    AS $_$

SELECT $1

$_$;

ALTER FUNCTION py_functions."_dateRangeStart"(value timestamp with time zone) OWNER TO bien;

--

-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text

    LANGUAGE plpython3u IMMUTABLE

    AS $$

params = dict(first=first, middle=middle, last=last)

def none_if(val, *none_vals):

    for none_val in none_vals:

        if val == none_val: return None

    return val

_name_parts_slices_items = [

    ('first', slice(None, -1)),

    ('middle', slice(1, -1)),

    ('last', slice(-1, None)),

]

name_parts_slices = dict(_name_parts_slices_items)

name_parts = [name for name, slice_ in _name_parts_slices_items]

def _name(items):

    items = dict(items)

    parts = []

    for part in name_parts:

        if part in items: parts.append(items[part])

    return none_if(' '.join(parts), '')

out_items = []

for part, value in params.iteritems():

    if value == None: continue

    try: slice_ = name_parts_slices[part]

    except KeyError: pass # a non-value column

    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))

return _name(out_items)

$$;

ALTER FUNCTION py_functions."_namePart"(first text, middle text, last text) OWNER TO bien;

--

-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien

--

CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]

    LANGUAGE plpython3u IMMUTABLE STRICT

    AS $$

import re

def single_space(str_): return re.sub(r' {2,}', r' ', str_.strip())

def could_be_year(str_): return str_.isdigit() and len(str_) == 4

def could_be_day(str_): return str_.isdigit() and len(str_) <= 2

range_sep='-'

part_sep=' '

default = (str_, str_)

if str_.find(':') >= 0: return default

# range_sep might be used as date part separator instead

if str_.find(part_sep) < 0: return default

start, sep, end = str_.partition(range_sep)

if sep == '': return default # not a range

start, end = (single_space(d).split(part_sep) for d in (start, end))

# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)

if len(start) == 2 and (len(end) == 1 or (

        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])

        and could_be_year(end[-1])

    )):

    end.insert(0, start[0]) # make end fully specified

ct_diff = len(end) - len(start)

# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.

if ct_diff > 0: start += end[-ct_diff:] # make start fully specified

# Other forms are invalid and will be left as-is

return [part_sep.join(d) for d in (start, end)]

$$;

ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;

--

-- PostgreSQL database dump complete

--

--

-- 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: col_cast; Type: TYPE; Schema: functions; Owner: -

--

CREATE TYPE col_cast AS (

	col_name text,

	type regtype

);

--

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

--

CREATE TYPE col_ref AS (

	table_ regclass,

	name text

);

--

-- 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: cluster_index(regclass); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered

$_$;

--

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

--

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

--

-- Name: col_exists(col_ref); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION col_exists(col col_ref) RETURNS boolean

    LANGUAGE plpgsql STRICT

    AS $$

BEGIN

    PERFORM functions.col_type(col);

    RETURN true;

EXCEPTION

    WHEN undefined_column THEN RETURN false;

END;

$$;

--

-- 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: col_names(regclass); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT attname::text

FROM pg_attribute

WHERE attrelid = $1 AND attnum >= 1

ORDER BY attnum

$_$;

--

-- Name: col_type(col_ref); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION col_type(col col_ref) RETURNS regtype

    LANGUAGE plpgsql STABLE STRICT

    AS $$

DECLARE

    type regtype;

BEGIN

    SELECT atttypid FROM pg_attribute

    WHERE attrelid = col.table_ AND attname = col.name

    INTO STRICT type

    ;

    RETURN type;

EXCEPTION

    WHEN no_data_found THEN

        RAISE undefined_column USING MESSAGE =

            concat('undefined column: ', col.name);

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;

    WHEN duplicate_column 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 IMMUTABLE STRICT

    AS $_$

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

$_$;

--

-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text

    LANGUAGE sql STABLE STRICT

    AS $_$

SELECT col_name

FROM unnest($2) s (col_name)

WHERE functions.col_exists(($1, col_name))

$_$;

--

-- 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: map_get(regclass, text); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION map_get(map regclass, key text) RETURNS text

    LANGUAGE plpgsql STABLE STRICT

    AS $_$

DECLARE

    value text;

BEGIN

    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$

        INTO value USING key;

    RETURN value;

END;

$_$;

--

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

--

CREATE FUNCTION map_values(map regclass) RETURNS SETOF text

    LANGUAGE plpgsql STABLE STRICT

    AS $_$

BEGIN

    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;

END;

$_$;

--

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

--

CREATE FUNCTION mk_map_table(table_ text) RETURNS void

    LANGUAGE sql STRICT

    AS $_$

SELECT functions.create_if_not_exists($$

CREATE TABLE $$||$1||$$

(

    LIKE functions.map INCLUDING ALL

)

$$)

$_$;

--

-- 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_cols(regclass, anyelement); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void

    LANGUAGE sql STRICT

    AS $_$

SELECT functions.rename_if_exists($$ALTER TABLE $$||$1||$$ RENAME $$

||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))

FROM functions.col_names($1::text::regtype) f (name)

$_$;

--

-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: functions; Owner: -

--

COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';

--

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

    WHEN duplicate_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: reset_map_table(text); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION reset_map_table(table_ text) RETURNS void

    LANGUAGE sql STRICT

    AS $_$

SELECT functions.mk_map_table($1);

SELECT functions.truncate($1);

$_$;

--

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

--

CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $_$

DECLARE

    old text[] = ARRAY(SELECT functions.col_names(table_));

    new text[] = ARRAY(SELECT functions.map_values(names));

BEGIN

    old = old[1:array_length(new, 1)]; -- truncate to same length

    PERFORM functions.rename_if_exists($$ALTER TABLE $$||$1||$$ RENAME $$

        ||quote_ident(key)||$$ TO $$||quote_ident(value))

    FROM each(hstore(old, new));

END;

$_$;

--

-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: functions; Owner: -

--

COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';

--

-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $_$

DECLARE

    sql text = $$ALTER TABLE $$||table_||$$

$$||NULLIF(array_to_string(ARRAY(

    SELECT

    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type

    ||$$ USING $$||col_name_sql||$$::$$||target_type

    FROM

    (

        SELECT

          quote_ident(col_name) AS col_name_sql

        , functions.col_type((table_, col_name)) AS curr_type

        , type AS target_type

        FROM unnest(col_casts)

    ) s

    WHERE curr_type != target_type

), '

, '), '');

BEGIN

    RAISE NOTICE '%', sql;

    EXECUTE COALESCE(sql, '');

END;

$_$;

--

-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: functions; Owner: -

--

COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';

--

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

--

CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore

    LANGUAGE plpgsql STABLE STRICT

    AS $_$

DECLARE

    hstore hstore;

BEGIN

    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||

        table_||$$))$$ INTO STRICT hstore;

    RETURN hstore;

END;

$_$;

--

-- 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: truncate(regclass); Type: FUNCTION; Schema: functions; Owner: -

--

CREATE FUNCTION truncate(table_ regclass) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $_$

BEGIN

    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;

END;

$_$;

--

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

--

COMMENT ON FUNCTION truncate(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

);

--

-- Name: ->; Type: OPERATOR; Schema: functions; Owner: -

--

CREATE OPERATOR -> (

    PROCEDURE = map_get,

    LEFTARG = regclass,

    RIGHTARG = text

);

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: map; Type: TABLE; Schema: functions; Owner: -; Tablespace: 

--

CREATE TABLE map (

    "from" text NOT NULL,

    "to" text,

    filter text,

    notes text

);

--

-- Data for Name: map; Type: TABLE DATA; Schema: functions; Owner: -

--

--

-- Name: map_pkey; Type: CONSTRAINT; Schema: functions; Owner: -; Tablespace: 

--

ALTER TABLE ONLY map

    ADD CONSTRAINT map_pkey PRIMARY KEY ("from");

--

-- PostgreSQL database dump complete

--

SELECT functions.set_col_types('"PLOT"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID"     UNIQUE ("locationID")$$);

SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR")$$);

SELECT functions.cluster_once('"PLOT"', '"PLOT.unique"');

SELECT functions.set_col_types('"COUNTY"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.ID"     UNIQUE ("COUNTY

SELECT functions.create_if_not_exists($$ALTER TABLE "COUNTY" ADD CONSTRAINT "COUNTY.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD")$$);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" ADD CONSTRAINT "REF_PLANT_DICTIONARY.unique" UNIQUE ("PLANT_SYMBOL_TYPE", "PLANT_SYMBOL")$$);

SELECT functions.set_col_types('"REF_UNIT"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_UNIT" ADD CONSTRAINT "REF_UNIT.unique" UNIQUE ("STATECD", "UNITCD")$$);

SELECT functions.set_col_types('"COND"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$

SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID"     UNIQUE ("COND

SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode")$$);

SELECT functions.cluster_once('"COND"', '"COND.unique"');

SELECT functions.set_col_types('"REF_SPECIES"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD COLUMN "PLANT_SYMBOL_TYPE" text NOT NULL DEFAULT 'Species'$$);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_SPECIES" ADD CONSTRAINT "REF_SPECIES.unique" UNIQUE ("SPCD")$$);

SELECT functions.set_col_types('"TREE"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("individualObservationID")$$);

SELECT functions.cluster_once('"TREE"', '"TREE.ID"');

SELECT functions.create_if_not_exists($$CREATE INDEX "TREE.parent" ON "TREE" ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "authorEventCode", "subplot")$$);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_HABTYP_DESCRIPTION" ADD CONSTRAINT "REF_HABTYP_DESCRIPTION.unique" UNIQUE ("HABTYPCD", "HABTYP_PUB_CD")$$);

SELECT functions.set_col_types('"SUBPLOT"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("subplotID")$$);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE ("STATECD", "UNITCD", "COUNTYCD", "locationName", "INVYR", "subplot")$$);

SELECT functions.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');

SELECT functions.set_col_types('"REF_RESEARCH_STATION"', ARRAY[

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "REF_RESEARCH_STATION" ADD CONSTRAINT "REF_RESEARCH_STATION.unique" UNIQUE ("STATECD")$$);