--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

--

CREATE SCHEMA py_functions;

--

CREATE FUNCTION "_dateRangeEnd"() RETURNS trigger

    LANGUAGE plpgsql IMMUTABLE

    AS $$

BEGIN

    new.result := (SELECT (py_functions.parse_date_range(new.value))[2]);

    RETURN new;

END;

$$;

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

--

--

CREATE FUNCTION "_dateRangeStart"() RETURNS trigger

    LANGUAGE plpgsql IMMUTABLE

    AS $$

BEGIN

END;

$$;

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

--

--

CREATE FUNCTION "_namePart"() RETURNS trigger

    LANGUAGE plpythonu IMMUTABLE

    AS $$

new = TD['new']

_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 ' '.join(parts)

out_items = []

for part, value in new.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_])))

new['result'] = _name(out_items)

return 'MODIFY'

$$;

ALTER FUNCTION py_functions."_namePart"() OWNER TO bien;

--

--

CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]

    LANGUAGE plpythonu 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_, None)

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

SET default_tablespace = '';

SET default_with_oids = false;

--

--

CREATE TABLE "_dateRangeEnd" (

    result text,

    not_null_col boolean DEFAULT true NOT NULL,

    value text

);

ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien;

--

--

CREATE TABLE "_dateRangeStart" (

    result text,

    not_null_col boolean DEFAULT true NOT NULL,

    value text

);

ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien;

--

--

CREATE TABLE "_namePart" (

    result text,

    not_null_col boolean DEFAULT true NOT NULL,

    first text,

    middle text,

    last text

);

ALTER TABLE py_functions."_namePart" OWNER TO bien;

--

--

CREATE UNIQUE INDEX "_dateRangeEnd_unique" ON "_dateRangeEnd" USING btree (functions.ensure_not_null(value));

--

--

CREATE INDEX "_dateRangeEnd_value" ON "_dateRangeEnd" USING btree (value);

--

--

CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree (functions.ensure_not_null(value));

--

--

CREATE INDEX "_dateRangeStart_value" ON "_dateRangeStart" USING btree (value);

--

-- Name: _namePart_first; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:

--

CREATE INDEX "_namePart_first" ON "_namePart" USING btree (first);

--

-- Name: _namePart_last; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:

--

CREATE INDEX "_namePart_last" ON "_namePart" USING btree (last);

--

-- Name: _namePart_middle; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:

--

CREATE INDEX "_namePart_middle" ON "_namePart" USING btree (middle);

--

--

CREATE UNIQUE INDEX "_namePart_unique" ON "_namePart" USING btree (functions.ensure_not_null(first), functions.ensure_not_null(middle), functions.ensure_not_null(last));

--

--

CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeEnd"();

--

--

CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"();

--

--

CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "_namePart"();

--

--