Project

General

Profile

1 2094 aaronmk
--
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: functions; Type: SCHEMA; Schema: -; Owner: -
14
--
15
16
CREATE SCHEMA functions;
17
18
19 2107 aaronmk
SET search_path = functions, pg_catalog;
20
21 2094 aaronmk
--
22 2610 aaronmk
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
23
--
24
25
CREATE TYPE datatype AS ENUM (
26
    'str',
27
    'float'
28
);
29
30
31
--
32 2566 aaronmk
-- Name: _alt(); Type: FUNCTION; Schema: functions; Owner: -
33
--
34
35
CREATE FUNCTION _alt() RETURNS trigger
36
    LANGUAGE plpgsql IMMUTABLE
37
    AS $$
38
BEGIN
39
    new.result := coalesce(new."0", new."1", new."2", new."3", new."4", new."5",
40
        new."6", new."7", new."8", new."9");
41
    RETURN new;
42
END;
43
$$;
44
45
46
--
47 2606 aaronmk
-- Name: _label(); Type: FUNCTION; Schema: functions; Owner: -
48
--
49
50
CREATE FUNCTION _label() RETURNS trigger
51
    LANGUAGE plpgsql IMMUTABLE
52
    AS $$
53
BEGIN
54 2607 aaronmk
    new.result := (SELECT new.label||': '||new.value);
55 2606 aaronmk
    RETURN new;
56
END;
57
$$;
58
59
60
--
61 2596 aaronmk
-- Name: _merge(); Type: FUNCTION; Schema: functions; Owner: -
62
--
63
64
CREATE FUNCTION _merge() RETURNS trigger
65
    LANGUAGE plpgsql IMMUTABLE
66
    AS $$
67
BEGIN
68
    new.result := (
69 2603 aaronmk
        SELECT functions.join_strs('; ', value)
70 2596 aaronmk
        FROM
71 2603 aaronmk
        (
72
            SELECT *
73
            FROM
74
            (
75
                SELECT
76
                DISTINCT ON (value)
77
                *
78
                FROM
79
                (VALUES
80
                      (0, new."0")
81
                    , (1, new."1")
82
                    , (2, new."2")
83
                    , (3, new."3")
84
                    , (4, new."4")
85
                    , (5, new."5")
86
                    , (6, new."6")
87
                    , (7, new."7")
88
                    , (8, new."8")
89
                    , (9, new."9")
90
                )
91
                AS v (sort_order, value)
92
            )
93
            AS v
94
            ORDER BY sort_order
95
        )
96
        AS v
97 2596 aaronmk
    );
98
    RETURN new;
99
END;
100
$$;
101
102
103
--
104 2610 aaronmk
-- Name: _nullIf(); Type: FUNCTION; Schema: functions; Owner: -
105
--
106
107
CREATE FUNCTION "_nullIf"() RETURNS trigger
108
    LANGUAGE plpgsql IMMUTABLE
109
    AS $$
110
BEGIN
111 2662 aaronmk
    IF new.type = 'str' THEN -- no cast needed
112 2610 aaronmk
        new.result := (SELECT nullif(new.value, new."null"));
113 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
114 2662 aaronmk
    ELSIF new.type = 'float' THEN
115 2722 aaronmk
        DECLARE
116
            -- Outside the try block so that invalid null value generates error
117
            "null" double precision := new."null"::double precision;
118
        BEGIN
119
            new.result := (SELECT nullif(new.value::double precision, "null"));
120
        EXCEPTION
121
            WHEN data_exception THEN NULL; -- ignore invalid value
122
        END;
123 2610 aaronmk
    END IF;
124
125
    RETURN new;
126
END;
127
$$;
128
129
130
--
131 2566 aaronmk
-- Name: ensure_not_null(text); Type: FUNCTION; Schema: functions; Owner: -
132
--
133
134
CREATE FUNCTION ensure_not_null(value text) RETURNS text
135
    LANGUAGE sql IMMUTABLE
136
    AS $_$
137
SELECT COALESCE($1, E'\\N');
138
$_$;
139
140
141
--
142 2595 aaronmk
-- Name: join_strs_(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
143
--
144
145
CREATE FUNCTION join_strs_(state text, delim text, value text) RETURNS text
146
    LANGUAGE sql IMMUTABLE
147
    AS $_$
148
SELECT $1 || (CASE
149
WHEN $1 = '' OR $3 IS NULL OR $3 = ''
150
THEN ''
151
ELSE $2
152
END) || coalesce($3, '');
153
$_$;
154
155
156
--
157
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
158
--
159
160
CREATE AGGREGATE join_strs(text, text) (
161
    SFUNC = join_strs_,
162
    STYPE = text,
163
    INITCOND = ''
164
);
165
166
167 2566 aaronmk
SET default_tablespace = '';
168
169
SET default_with_oids = false;
170
171 2107 aaronmk
--
172 2566 aaronmk
-- Name: _alt; Type: TABLE; Schema: functions; Owner: -; Tablespace:
173
--
174
175
CREATE TABLE _alt (
176
    result text,
177
    not_null_col boolean DEFAULT true NOT NULL,
178
    "0" text,
179
    "1" text,
180
    "2" text,
181
    "3" text,
182
    "4" text,
183
    "5" text,
184
    "6" text,
185
    "7" text,
186
    "8" text,
187
    "9" text
188
);
189
190
191
--
192 2606 aaronmk
-- Name: _label; Type: TABLE; Schema: functions; Owner: -; Tablespace:
193
--
194
195
CREATE TABLE _label (
196
    result text,
197
    not_null_col boolean DEFAULT true NOT NULL,
198 2862 aaronmk
    label text NOT NULL,
199 2606 aaronmk
    value text
200
);
201
202
203
--
204 2596 aaronmk
-- Name: _merge; Type: TABLE; Schema: functions; Owner: -; Tablespace:
205
--
206
207
CREATE TABLE _merge (
208
    result text,
209
    not_null_col boolean DEFAULT true NOT NULL,
210
    "0" text,
211
    "1" text,
212
    "2" text,
213
    "3" text,
214
    "4" text,
215
    "5" text,
216
    "6" text,
217
    "7" text,
218
    "8" text,
219
    "9" text
220
);
221
222
223
--
224 2662 aaronmk
-- Name: _nullIf; Type: TABLE; Schema: functions; Owner: -; Tablespace:
225
--
226
227
CREATE TABLE "_nullIf" (
228
    result text,
229
    not_null_col boolean DEFAULT true NOT NULL,
230 2864 aaronmk
    "null" text NOT NULL,
231 2662 aaronmk
    type datatype DEFAULT 'str'::datatype NOT NULL,
232
    value text
233
);
234
235
236
--
237 2636 aaronmk
-- Name: _alt_0; Type: INDEX; Schema: functions; Owner: -; Tablespace:
238
--
239
240 2838 aaronmk
CREATE INDEX _alt_0 ON _alt USING btree ((COALESCE("0", '\\N'::text)));
241 2636 aaronmk
242
243
--
244
-- Name: _alt_1; Type: INDEX; Schema: functions; Owner: -; Tablespace:
245
--
246
247 2838 aaronmk
CREATE INDEX _alt_1 ON _alt USING btree ((COALESCE("1", '\\N'::text)));
248 2636 aaronmk
249
250
--
251
-- Name: _alt_2; Type: INDEX; Schema: functions; Owner: -; Tablespace:
252
--
253
254 2838 aaronmk
CREATE INDEX _alt_2 ON _alt USING btree ((COALESCE("2", '\\N'::text)));
255 2636 aaronmk
256
257
--
258
-- Name: _alt_3; Type: INDEX; Schema: functions; Owner: -; Tablespace:
259
--
260
261 2838 aaronmk
CREATE INDEX _alt_3 ON _alt USING btree ((COALESCE("3", '\\N'::text)));
262 2636 aaronmk
263
264
--
265
-- Name: _alt_4; Type: INDEX; Schema: functions; Owner: -; Tablespace:
266
--
267
268 2838 aaronmk
CREATE INDEX _alt_4 ON _alt USING btree ((COALESCE("4", '\\N'::text)));
269 2636 aaronmk
270
271
--
272
-- Name: _alt_5; Type: INDEX; Schema: functions; Owner: -; Tablespace:
273
--
274
275 2838 aaronmk
CREATE INDEX _alt_5 ON _alt USING btree ((COALESCE("5", '\\N'::text)));
276 2636 aaronmk
277
278
--
279
-- Name: _alt_6; Type: INDEX; Schema: functions; Owner: -; Tablespace:
280
--
281
282 2838 aaronmk
CREATE INDEX _alt_6 ON _alt USING btree ((COALESCE("6", '\\N'::text)));
283 2636 aaronmk
284
285
--
286
-- Name: _alt_7; Type: INDEX; Schema: functions; Owner: -; Tablespace:
287
--
288
289 2838 aaronmk
CREATE INDEX _alt_7 ON _alt USING btree ((COALESCE("7", '\\N'::text)));
290 2636 aaronmk
291
292
--
293
-- Name: _alt_8; Type: INDEX; Schema: functions; Owner: -; Tablespace:
294
--
295
296 2838 aaronmk
CREATE INDEX _alt_8 ON _alt USING btree ((COALESCE("8", '\\N'::text)));
297 2636 aaronmk
298
299
--
300
-- Name: _alt_9; Type: INDEX; Schema: functions; Owner: -; Tablespace:
301
--
302
303 2838 aaronmk
CREATE INDEX _alt_9 ON _alt USING btree ((COALESCE("9", '\\N'::text)));
304 2636 aaronmk
305
306
--
307 2566 aaronmk
-- Name: _alt_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
308
--
309
310 2838 aaronmk
CREATE UNIQUE INDEX _alt_unique ON _alt USING btree ((COALESCE("0", '\\N'::text)), (COALESCE("1", '\\N'::text)), (COALESCE("2", '\\N'::text)), (COALESCE("3", '\\N'::text)), (COALESCE("4", '\\N'::text)), (COALESCE("5", '\\N'::text)), (COALESCE("6", '\\N'::text)), (COALESCE("7", '\\N'::text)), (COALESCE("8", '\\N'::text)), (COALESCE("9", '\\N'::text)));
311 2566 aaronmk
312
313
--
314 2636 aaronmk
-- Name: _label_label; Type: INDEX; Schema: functions; Owner: -; Tablespace:
315
--
316
317 2863 aaronmk
CREATE INDEX _label_label ON _label USING btree ((COALESCE(label, '\\N'::text)));
318 2636 aaronmk
319
320
--
321 2606 aaronmk
-- Name: _label_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
322
--
323
324 2863 aaronmk
CREATE UNIQUE INDEX _label_unique ON _label USING btree ((COALESCE(label, '\\N'::text)), (COALESCE(value, '\\N'::text)));
325 2606 aaronmk
326
327
--
328 2636 aaronmk
-- Name: _label_value; Type: INDEX; Schema: functions; Owner: -; Tablespace:
329
--
330
331
CREATE INDEX _label_value ON _label USING btree (value);
332
333
334
--
335
-- Name: _merge_0; Type: INDEX; Schema: functions; Owner: -; Tablespace:
336
--
337
338 2839 aaronmk
CREATE INDEX _merge_0 ON _merge USING btree ((COALESCE("0", '\\N'::text)));
339 2636 aaronmk
340
341
--
342
-- Name: _merge_1; Type: INDEX; Schema: functions; Owner: -; Tablespace:
343
--
344
345 2839 aaronmk
CREATE INDEX _merge_1 ON _merge USING btree ((COALESCE("1", '\\N'::text)));
346 2636 aaronmk
347
348
--
349
-- Name: _merge_2; Type: INDEX; Schema: functions; Owner: -; Tablespace:
350
--
351
352 2839 aaronmk
CREATE INDEX _merge_2 ON _merge USING btree ((COALESCE("2", '\\N'::text)));
353 2636 aaronmk
354
355
--
356
-- Name: _merge_3; Type: INDEX; Schema: functions; Owner: -; Tablespace:
357
--
358
359 2839 aaronmk
CREATE INDEX _merge_3 ON _merge USING btree ((COALESCE("3", '\\N'::text)));
360 2636 aaronmk
361
362
--
363
-- Name: _merge_4; Type: INDEX; Schema: functions; Owner: -; Tablespace:
364
--
365
366 2839 aaronmk
CREATE INDEX _merge_4 ON _merge USING btree ((COALESCE("4", '\\N'::text)));
367 2636 aaronmk
368
369
--
370
-- Name: _merge_5; Type: INDEX; Schema: functions; Owner: -; Tablespace:
371
--
372
373 2839 aaronmk
CREATE INDEX _merge_5 ON _merge USING btree ((COALESCE("5", '\\N'::text)));
374 2636 aaronmk
375
376
--
377
-- Name: _merge_6; Type: INDEX; Schema: functions; Owner: -; Tablespace:
378
--
379
380 2839 aaronmk
CREATE INDEX _merge_6 ON _merge USING btree ((COALESCE("6", '\\N'::text)));
381 2636 aaronmk
382
383
--
384
-- Name: _merge_7; Type: INDEX; Schema: functions; Owner: -; Tablespace:
385
--
386
387 2839 aaronmk
CREATE INDEX _merge_7 ON _merge USING btree ((COALESCE("7", '\\N'::text)));
388 2636 aaronmk
389
390
--
391
-- Name: _merge_8; Type: INDEX; Schema: functions; Owner: -; Tablespace:
392
--
393
394 2839 aaronmk
CREATE INDEX _merge_8 ON _merge USING btree ((COALESCE("8", '\\N'::text)));
395 2636 aaronmk
396
397
--
398
-- Name: _merge_9; Type: INDEX; Schema: functions; Owner: -; Tablespace:
399
--
400
401 2839 aaronmk
CREATE INDEX _merge_9 ON _merge USING btree ((COALESCE("9", '\\N'::text)));
402 2636 aaronmk
403
404
--
405 2596 aaronmk
-- Name: _merge_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
406
--
407
408 2839 aaronmk
CREATE UNIQUE INDEX _merge_unique ON _merge USING btree ((COALESCE("0", '\\N'::text)), (COALESCE("1", '\\N'::text)), (COALESCE("2", '\\N'::text)), (COALESCE("3", '\\N'::text)), (COALESCE("4", '\\N'::text)), (COALESCE("5", '\\N'::text)), (COALESCE("6", '\\N'::text)), (COALESCE("7", '\\N'::text)), (COALESCE("8", '\\N'::text)), (COALESCE("9", '\\N'::text)));
409 2596 aaronmk
410
411
--
412 2636 aaronmk
-- Name: _nullIf_null; Type: INDEX; Schema: functions; Owner: -; Tablespace:
413
--
414
415 2662 aaronmk
CREATE INDEX "_nullIf_null" ON "_nullIf" USING btree ("null");
416 2636 aaronmk
417
418
--
419
-- Name: _nullIf_type; Type: INDEX; Schema: functions; Owner: -; Tablespace:
420
--
421
422 2662 aaronmk
CREATE INDEX "_nullIf_type" ON "_nullIf" USING btree (type);
423 2636 aaronmk
424
425
--
426 2610 aaronmk
-- Name: _nullIf_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
427
--
428
429 2865 aaronmk
CREATE UNIQUE INDEX "_nullIf_unique" ON "_nullIf" USING btree ("null", type, (COALESCE(value, '\\N'::text)));
430 2610 aaronmk
431
432
--
433 2636 aaronmk
-- Name: _nullIf_value; Type: INDEX; Schema: functions; Owner: -; Tablespace:
434
--
435
436 2662 aaronmk
CREATE INDEX "_nullIf_value" ON "_nullIf" USING btree (value);
437 2636 aaronmk
438
439
--
440 2566 aaronmk
-- Name: _alt; Type: TRIGGER; Schema: functions; Owner: -
441
--
442
443
CREATE TRIGGER _alt BEFORE INSERT OR UPDATE ON _alt FOR EACH ROW EXECUTE PROCEDURE _alt();
444
445
446
--
447 2606 aaronmk
-- Name: _label; Type: TRIGGER; Schema: functions; Owner: -
448
--
449
450
CREATE TRIGGER _label BEFORE INSERT OR UPDATE ON _label FOR EACH ROW EXECUTE PROCEDURE _label();
451
452
453
--
454 2596 aaronmk
-- Name: _merge; Type: TRIGGER; Schema: functions; Owner: -
455
--
456
457
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE _merge();
458
459
460
--
461 2610 aaronmk
-- Name: _nullIf; Type: TRIGGER; Schema: functions; Owner: -
462
--
463
464 2662 aaronmk
CREATE TRIGGER "_nullIf" BEFORE INSERT OR UPDATE ON "_nullIf" FOR EACH ROW EXECUTE PROCEDURE "_nullIf"();
465 2610 aaronmk
466
467
--
468 2136 aaronmk
-- PostgreSQL database dump complete
469
--