1
|
--
|
2
|
-- PostgreSQL database dump
|
3
|
--
|
4
|
|
5
|
SET statement_timeout = 0;
|
6
|
SET client_encoding = 'UTF8';
|
7
|
SET standard_conforming_strings = off;
|
8
|
SET check_function_bodies = false;
|
9
|
SET client_min_messages = warning;
|
10
|
SET escape_string_warning = off;
|
11
|
|
12
|
--
|
13
|
-- Name: py_functions; Type: SCHEMA; Schema: -; Owner: bien
|
14
|
--
|
15
|
|
16
|
CREATE SCHEMA py_functions;
|
17
|
|
18
|
|
19
|
ALTER SCHEMA py_functions OWNER TO bien;
|
20
|
|
21
|
SET search_path = py_functions, pg_catalog;
|
22
|
|
23
|
--
|
24
|
-- Name: _dateRangeEnd(); Type: FUNCTION; Schema: py_functions; Owner: bien
|
25
|
--
|
26
|
|
27
|
CREATE FUNCTION "_dateRangeEnd"() RETURNS trigger
|
28
|
LANGUAGE plpgsql IMMUTABLE
|
29
|
AS $$
|
30
|
BEGIN
|
31
|
new.result := (SELECT (py_functions.parse_date_range(new.value))[2]);
|
32
|
RETURN new;
|
33
|
END;
|
34
|
$$;
|
35
|
|
36
|
|
37
|
ALTER FUNCTION py_functions."_dateRangeEnd"() OWNER TO bien;
|
38
|
|
39
|
--
|
40
|
-- Name: _dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien
|
41
|
--
|
42
|
|
43
|
CREATE FUNCTION "_dateRangeStart"() RETURNS trigger
|
44
|
LANGUAGE plpgsql IMMUTABLE
|
45
|
AS $$
|
46
|
BEGIN
|
47
|
new.result := (SELECT (py_functions.parse_date_range(new.value))[1]);
|
48
|
RETURN new;
|
49
|
END;
|
50
|
$$;
|
51
|
|
52
|
|
53
|
ALTER FUNCTION py_functions."_dateRangeStart"() OWNER TO bien;
|
54
|
|
55
|
--
|
56
|
-- Name: _namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien
|
57
|
--
|
58
|
|
59
|
CREATE FUNCTION "_namePart"() RETURNS trigger
|
60
|
LANGUAGE plpythonu IMMUTABLE
|
61
|
AS $$
|
62
|
new = TD['new']
|
63
|
|
64
|
_name_parts_slices_items = [
|
65
|
('first', slice(None, 1)),
|
66
|
('middle', slice(1, -1)),
|
67
|
('last', slice(-1, None)),
|
68
|
]
|
69
|
name_parts_slices = dict(_name_parts_slices_items)
|
70
|
name_parts = [name for name, slice_ in _name_parts_slices_items]
|
71
|
|
72
|
def _name(items):
|
73
|
items = dict(items)
|
74
|
parts = []
|
75
|
for part in name_parts:
|
76
|
if part in items: parts.append(items[part])
|
77
|
return ' '.join(parts)
|
78
|
|
79
|
out_items = []
|
80
|
for part, value in new.iteritems():
|
81
|
if value == None: continue
|
82
|
|
83
|
try: slice_ = name_parts_slices[part]
|
84
|
except KeyError: pass # a non-value column
|
85
|
else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
|
86
|
|
87
|
new['result'] = _name(out_items)
|
88
|
|
89
|
return 'MODIFY'
|
90
|
$$;
|
91
|
|
92
|
|
93
|
ALTER FUNCTION py_functions."_namePart"() OWNER TO bien;
|
94
|
|
95
|
--
|
96
|
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
|
97
|
--
|
98
|
|
99
|
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
|
100
|
LANGUAGE plpythonu IMMUTABLE STRICT
|
101
|
AS $$
|
102
|
import re
|
103
|
|
104
|
def single_space(str_): return re.sub(r' {2,}', r' ', str_.strip())
|
105
|
|
106
|
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
|
107
|
|
108
|
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
|
109
|
|
110
|
range_sep='-'
|
111
|
part_sep=' '
|
112
|
|
113
|
default = (str_, None)
|
114
|
# range_sep might be used as date part separator instead
|
115
|
if str_.find(part_sep) < 0: return default
|
116
|
|
117
|
start, sep, end = str_.partition(range_sep)
|
118
|
if sep == '': return default # not a range
|
119
|
start, end = (single_space(d).split(part_sep) for d in (start, end))
|
120
|
|
121
|
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
|
122
|
if len(start) == 2 and (len(end) == 1 or (
|
123
|
len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
|
124
|
and could_be_year(end[-1])
|
125
|
)):
|
126
|
end.insert(0, start[0]) # make end fully specified
|
127
|
ct_diff = len(end) - len(start)
|
128
|
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
|
129
|
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
|
130
|
# Other forms are invalid and will be left as-is
|
131
|
|
132
|
return [part_sep.join(d) for d in (start, end)]
|
133
|
$$;
|
134
|
|
135
|
|
136
|
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
|
137
|
|
138
|
SET default_tablespace = '';
|
139
|
|
140
|
SET default_with_oids = false;
|
141
|
|
142
|
--
|
143
|
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
|
144
|
--
|
145
|
|
146
|
CREATE TABLE "_dateRangeEnd" (
|
147
|
result text,
|
148
|
not_null_col boolean DEFAULT true NOT NULL,
|
149
|
value text
|
150
|
);
|
151
|
|
152
|
|
153
|
ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien;
|
154
|
|
155
|
--
|
156
|
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
|
157
|
--
|
158
|
|
159
|
CREATE TABLE "_dateRangeStart" (
|
160
|
result text,
|
161
|
not_null_col boolean DEFAULT true NOT NULL,
|
162
|
value text
|
163
|
);
|
164
|
|
165
|
|
166
|
ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien;
|
167
|
|
168
|
--
|
169
|
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
|
170
|
--
|
171
|
|
172
|
CREATE TABLE "_namePart" (
|
173
|
result text,
|
174
|
not_null_col boolean DEFAULT true NOT NULL,
|
175
|
first text,
|
176
|
middle text,
|
177
|
last text
|
178
|
);
|
179
|
|
180
|
|
181
|
ALTER TABLE py_functions."_namePart" OWNER TO bien;
|
182
|
|
183
|
--
|
184
|
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
185
|
--
|
186
|
|
187
|
CREATE UNIQUE INDEX "_dateRangeEnd_unique" ON "_dateRangeEnd" USING btree (functions.ensure_not_null(value));
|
188
|
|
189
|
|
190
|
--
|
191
|
-- Name: _dateRangeEnd_value; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
192
|
--
|
193
|
|
194
|
CREATE INDEX "_dateRangeEnd_value" ON "_dateRangeEnd" USING btree (value);
|
195
|
|
196
|
|
197
|
--
|
198
|
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
199
|
--
|
200
|
|
201
|
CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree (functions.ensure_not_null(value));
|
202
|
|
203
|
|
204
|
--
|
205
|
-- Name: _dateRangeStart_value; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
206
|
--
|
207
|
|
208
|
CREATE INDEX "_dateRangeStart_value" ON "_dateRangeStart" USING btree (value);
|
209
|
|
210
|
|
211
|
--
|
212
|
-- Name: _namePart_first; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
213
|
--
|
214
|
|
215
|
CREATE INDEX "_namePart_first" ON "_namePart" USING btree (first);
|
216
|
|
217
|
|
218
|
--
|
219
|
-- Name: _namePart_last; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
220
|
--
|
221
|
|
222
|
CREATE INDEX "_namePart_last" ON "_namePart" USING btree (last);
|
223
|
|
224
|
|
225
|
--
|
226
|
-- Name: _namePart_middle; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
227
|
--
|
228
|
|
229
|
CREATE INDEX "_namePart_middle" ON "_namePart" USING btree (middle);
|
230
|
|
231
|
|
232
|
--
|
233
|
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
|
234
|
--
|
235
|
|
236
|
CREATE UNIQUE INDEX "_namePart_unique" ON "_namePart" USING btree (functions.ensure_not_null(first), functions.ensure_not_null(middle), functions.ensure_not_null(last));
|
237
|
|
238
|
|
239
|
--
|
240
|
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien
|
241
|
--
|
242
|
|
243
|
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeEnd"();
|
244
|
|
245
|
|
246
|
--
|
247
|
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien
|
248
|
--
|
249
|
|
250
|
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"();
|
251
|
|
252
|
|
253
|
--
|
254
|
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien
|
255
|
--
|
256
|
|
257
|
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "_namePart"();
|
258
|
|
259
|
|
260
|
--
|
261
|
-- PostgreSQL database dump complete
|
262
|
--
|
263
|
|