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
    -- Invalid value -> warning, but invalid null value -> error
114 2662 aaronmk
    ELSIF new.type = 'float' THEN
115 2610 aaronmk
        new.result := (SELECT nullif(functions."double precision"(new.value),
116
            new."null"::double precision));
117
    END IF;
118
119
    RETURN new;
120
END;
121
$$;
122
123
124
--
125 2528 aaronmk
-- Name: boolean(text); Type: FUNCTION; Schema: functions; Owner: -
126
--
127
128
CREATE FUNCTION "boolean"(value text) RETURNS boolean
129 2554 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
130 2528 aaronmk
    AS $$
131
BEGIN
132
    BEGIN
133 2543 aaronmk
        RETURN value::boolean;
134 2528 aaronmk
    EXCEPTION
135
        WHEN data_exception THEN
136
            RAISE WARNING '%', SQLERRM;
137
            RETURN NULL;
138
    END;
139
    RETURN new;
140
END;
141
$$;
142
143
144
--
145
-- Name: double precision(text); Type: FUNCTION; Schema: functions; Owner: -
146
--
147
148
CREATE FUNCTION "double precision"(value text) RETURNS double precision
149 2554 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
150 2528 aaronmk
    AS $$
151
BEGIN
152
    BEGIN
153 2543 aaronmk
        RETURN value::double precision;
154 2528 aaronmk
    EXCEPTION
155
        WHEN data_exception THEN
156
            RAISE WARNING '%', SQLERRM;
157
            RETURN NULL;
158
    END;
159
    RETURN new;
160
END;
161
$$;
162
163
164
--
165 2566 aaronmk
-- Name: ensure_not_null(text); Type: FUNCTION; Schema: functions; Owner: -
166
--
167
168
CREATE FUNCTION ensure_not_null(value text) RETURNS text
169
    LANGUAGE sql IMMUTABLE
170
    AS $_$
171
SELECT COALESCE($1, E'\\N');
172
$_$;
173
174
175
--
176 2595 aaronmk
-- Name: join_strs_(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
177
--
178
179
CREATE FUNCTION join_strs_(state text, delim text, value text) RETURNS text
180
    LANGUAGE sql IMMUTABLE
181
    AS $_$
182
SELECT $1 || (CASE
183
WHEN $1 = '' OR $3 IS NULL OR $3 = ''
184
THEN ''
185
ELSE $2
186
END) || coalesce($3, '');
187
$_$;
188
189
190
--
191 2528 aaronmk
-- Name: timestamp with time zone(text); Type: FUNCTION; Schema: functions; Owner: -
192
--
193
194
CREATE FUNCTION "timestamp with time zone"(value text) RETURNS timestamp with time zone
195 2554 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
196 2528 aaronmk
    AS $$
197
BEGIN
198
    BEGIN
199 2543 aaronmk
        RETURN value::timestamp with time zone;
200 2528 aaronmk
    EXCEPTION
201
        WHEN data_exception THEN
202
            RAISE WARNING '%', SQLERRM;
203
            RETURN NULL;
204
    END;
205
    RETURN new;
206
END;
207
$$;
208
209
210 2595 aaronmk
--
211
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
212
--
213
214
CREATE AGGREGATE join_strs(text, text) (
215
    SFUNC = join_strs_,
216
    STYPE = text,
217
    INITCOND = ''
218
);
219
220
221 2566 aaronmk
SET default_tablespace = '';
222
223
SET default_with_oids = false;
224
225 2107 aaronmk
--
226 2566 aaronmk
-- Name: _alt; Type: TABLE; Schema: functions; Owner: -; Tablespace:
227
--
228
229
CREATE TABLE _alt (
230
    result text,
231
    not_null_col boolean DEFAULT true NOT NULL,
232
    "0" text,
233
    "1" text,
234
    "2" text,
235
    "3" text,
236
    "4" text,
237
    "5" text,
238
    "6" text,
239
    "7" text,
240
    "8" text,
241
    "9" text
242
);
243
244
245
--
246 2606 aaronmk
-- Name: _label; Type: TABLE; Schema: functions; Owner: -; Tablespace:
247
--
248
249
CREATE TABLE _label (
250
    result text,
251
    not_null_col boolean DEFAULT true NOT NULL,
252
    label text,
253
    value text
254
);
255
256
257
--
258 2596 aaronmk
-- Name: _merge; Type: TABLE; Schema: functions; Owner: -; Tablespace:
259
--
260
261
CREATE TABLE _merge (
262
    result text,
263
    not_null_col boolean DEFAULT true NOT NULL,
264
    "0" text,
265
    "1" text,
266
    "2" text,
267
    "3" text,
268
    "4" text,
269
    "5" text,
270
    "6" text,
271
    "7" text,
272
    "8" text,
273
    "9" text
274
);
275
276
277
--
278 2662 aaronmk
-- Name: _nullIf; Type: TABLE; Schema: functions; Owner: -; Tablespace:
279
--
280
281
CREATE TABLE "_nullIf" (
282
    result text,
283
    not_null_col boolean DEFAULT true NOT NULL,
284
    "null" text,
285
    type datatype DEFAULT 'str'::datatype NOT NULL,
286
    value text
287
);
288
289
290
--
291 2636 aaronmk
-- Name: _alt_0; Type: INDEX; Schema: functions; Owner: -; Tablespace:
292
--
293
294
CREATE INDEX _alt_0 ON _alt USING btree ("0");
295
296
297
--
298
-- Name: _alt_1; Type: INDEX; Schema: functions; Owner: -; Tablespace:
299
--
300
301
CREATE INDEX _alt_1 ON _alt USING btree ("1");
302
303
304
--
305
-- Name: _alt_2; Type: INDEX; Schema: functions; Owner: -; Tablespace:
306
--
307
308
CREATE INDEX _alt_2 ON _alt USING btree ("2");
309
310
311
--
312
-- Name: _alt_3; Type: INDEX; Schema: functions; Owner: -; Tablespace:
313
--
314
315
CREATE INDEX _alt_3 ON _alt USING btree ("3");
316
317
318
--
319
-- Name: _alt_4; Type: INDEX; Schema: functions; Owner: -; Tablespace:
320
--
321
322
CREATE INDEX _alt_4 ON _alt USING btree ("4");
323
324
325
--
326
-- Name: _alt_5; Type: INDEX; Schema: functions; Owner: -; Tablespace:
327
--
328
329
CREATE INDEX _alt_5 ON _alt USING btree ("5");
330
331
332
--
333
-- Name: _alt_6; Type: INDEX; Schema: functions; Owner: -; Tablespace:
334
--
335
336
CREATE INDEX _alt_6 ON _alt USING btree ("6");
337
338
339
--
340
-- Name: _alt_7; Type: INDEX; Schema: functions; Owner: -; Tablespace:
341
--
342
343
CREATE INDEX _alt_7 ON _alt USING btree ("7");
344
345
346
--
347
-- Name: _alt_8; Type: INDEX; Schema: functions; Owner: -; Tablespace:
348
--
349
350
CREATE INDEX _alt_8 ON _alt USING btree ("8");
351
352
353
--
354
-- Name: _alt_9; Type: INDEX; Schema: functions; Owner: -; Tablespace:
355
--
356
357
CREATE INDEX _alt_9 ON _alt USING btree ("9");
358
359
360
--
361 2566 aaronmk
-- Name: _alt_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
362
--
363
364
CREATE UNIQUE INDEX _alt_unique ON _alt USING btree (ensure_not_null("0"), ensure_not_null("1"), ensure_not_null("2"), ensure_not_null("3"), ensure_not_null("4"), ensure_not_null("5"), ensure_not_null("6"), ensure_not_null("7"), ensure_not_null("8"), ensure_not_null("9"));
365
366
367
--
368 2636 aaronmk
-- Name: _label_label; Type: INDEX; Schema: functions; Owner: -; Tablespace:
369
--
370
371
CREATE INDEX _label_label ON _label USING btree (label);
372
373
374
--
375 2606 aaronmk
-- Name: _label_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
376
--
377
378
CREATE UNIQUE INDEX _label_unique ON _label USING btree (ensure_not_null(label), ensure_not_null(value));
379
380
381
--
382 2636 aaronmk
-- Name: _label_value; Type: INDEX; Schema: functions; Owner: -; Tablespace:
383
--
384
385
CREATE INDEX _label_value ON _label USING btree (value);
386
387
388
--
389
-- Name: _merge_0; Type: INDEX; Schema: functions; Owner: -; Tablespace:
390
--
391
392
CREATE INDEX _merge_0 ON _merge USING btree ("0");
393
394
395
--
396
-- Name: _merge_1; Type: INDEX; Schema: functions; Owner: -; Tablespace:
397
--
398
399
CREATE INDEX _merge_1 ON _merge USING btree ("1");
400
401
402
--
403
-- Name: _merge_2; Type: INDEX; Schema: functions; Owner: -; Tablespace:
404
--
405
406
CREATE INDEX _merge_2 ON _merge USING btree ("2");
407
408
409
--
410
-- Name: _merge_3; Type: INDEX; Schema: functions; Owner: -; Tablespace:
411
--
412
413
CREATE INDEX _merge_3 ON _merge USING btree ("3");
414
415
416
--
417
-- Name: _merge_4; Type: INDEX; Schema: functions; Owner: -; Tablespace:
418
--
419
420
CREATE INDEX _merge_4 ON _merge USING btree ("4");
421
422
423
--
424
-- Name: _merge_5; Type: INDEX; Schema: functions; Owner: -; Tablespace:
425
--
426
427
CREATE INDEX _merge_5 ON _merge USING btree ("5");
428
429
430
--
431
-- Name: _merge_6; Type: INDEX; Schema: functions; Owner: -; Tablespace:
432
--
433
434
CREATE INDEX _merge_6 ON _merge USING btree ("6");
435
436
437
--
438
-- Name: _merge_7; Type: INDEX; Schema: functions; Owner: -; Tablespace:
439
--
440
441
CREATE INDEX _merge_7 ON _merge USING btree ("7");
442
443
444
--
445
-- Name: _merge_8; Type: INDEX; Schema: functions; Owner: -; Tablespace:
446
--
447
448
CREATE INDEX _merge_8 ON _merge USING btree ("8");
449
450
451
--
452
-- Name: _merge_9; Type: INDEX; Schema: functions; Owner: -; Tablespace:
453
--
454
455
CREATE INDEX _merge_9 ON _merge USING btree ("9");
456
457
458
--
459 2596 aaronmk
-- Name: _merge_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
460
--
461
462
CREATE UNIQUE INDEX _merge_unique ON _merge USING btree (ensure_not_null("0"), ensure_not_null("1"), ensure_not_null("2"), ensure_not_null("3"), ensure_not_null("4"), ensure_not_null("5"), ensure_not_null("6"), ensure_not_null("7"), ensure_not_null("8"), ensure_not_null("9"));
463
464
465
--
466 2636 aaronmk
-- Name: _nullIf_null; Type: INDEX; Schema: functions; Owner: -; Tablespace:
467
--
468
469 2662 aaronmk
CREATE INDEX "_nullIf_null" ON "_nullIf" USING btree ("null");
470 2636 aaronmk
471
472
--
473
-- Name: _nullIf_type; Type: INDEX; Schema: functions; Owner: -; Tablespace:
474
--
475
476 2662 aaronmk
CREATE INDEX "_nullIf_type" ON "_nullIf" USING btree (type);
477 2636 aaronmk
478
479
--
480 2610 aaronmk
-- Name: _nullIf_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace:
481
--
482
483 2662 aaronmk
CREATE UNIQUE INDEX "_nullIf_unique" ON "_nullIf" USING btree (ensure_not_null("null"), type, ensure_not_null(value));
484 2610 aaronmk
485
486
--
487 2636 aaronmk
-- Name: _nullIf_value; Type: INDEX; Schema: functions; Owner: -; Tablespace:
488
--
489
490 2662 aaronmk
CREATE INDEX "_nullIf_value" ON "_nullIf" USING btree (value);
491 2636 aaronmk
492
493
--
494 2566 aaronmk
-- Name: _alt; Type: TRIGGER; Schema: functions; Owner: -
495
--
496
497
CREATE TRIGGER _alt BEFORE INSERT OR UPDATE ON _alt FOR EACH ROW EXECUTE PROCEDURE _alt();
498
499
500
--
501 2606 aaronmk
-- Name: _label; Type: TRIGGER; Schema: functions; Owner: -
502
--
503
504
CREATE TRIGGER _label BEFORE INSERT OR UPDATE ON _label FOR EACH ROW EXECUTE PROCEDURE _label();
505
506
507
--
508 2596 aaronmk
-- Name: _merge; Type: TRIGGER; Schema: functions; Owner: -
509
--
510
511
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE _merge();
512
513
514
--
515 2610 aaronmk
-- Name: _nullIf; Type: TRIGGER; Schema: functions; Owner: -
516
--
517
518 2662 aaronmk
CREATE TRIGGER "_nullIf" BEFORE INSERT OR UPDATE ON "_nullIf" FOR EACH ROW EXECUTE PROCEDURE "_nullIf"();
519 2610 aaronmk
520
521
--
522 2136 aaronmk
-- PostgreSQL database dump complete
523
--