-- -- 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; -- -- Name: py_functions; Type: SCHEMA; Schema: -; Owner: bien -- CREATE SCHEMA py_functions; ALTER SCHEMA py_functions OWNER TO bien; SET search_path = py_functions, pg_catalog; -- -- Name: _dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien -- CREATE FUNCTION "_dateRangeStart"() RETURNS trigger LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN new.result := (SELECT (py_functions.parse_date_range(new.value))[1]); RETURN new; END; $$; ALTER FUNCTION py_functions."_dateRangeStart"() 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 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; -- -- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: -- CREATE TABLE "_dateRangeStart" ( result text, not_null_col boolean DEFAULT true NOT NULL, value text ); ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien; -- -- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: -- CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree (functions.ensure_not_null(value)); -- -- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien -- CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"(); -- -- PostgreSQL database dump complete --