--

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

--