Project

General

Profile

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: functions; Type: SCHEMA; Schema: -; Owner: -
14
--
15

    
16
CREATE SCHEMA functions;
17

    
18

    
19
SET search_path = functions, pg_catalog;
20

    
21
--
22
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
23
--
24

    
25
CREATE TYPE datatype AS ENUM (
26
    'str',
27
    'float'
28
);
29

    
30

    
31
--
32
-- 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
-- Name: _label(); Type: FUNCTION; Schema: functions; Owner: -
48
--
49

    
50
CREATE FUNCTION _label() RETURNS trigger
51
    LANGUAGE plpgsql IMMUTABLE
52
    AS $$
53
BEGIN
54
    new.result := (SELECT new.label||': '||new.value);
55
    RETURN new;
56
END;
57
$$;
58

    
59

    
60
--
61
-- 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
        SELECT functions.join_strs('; ', value)
70
        FROM
71
        (
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
    );
98
    RETURN new;
99
END;
100
$$;
101

    
102

    
103
--
104
-- Name: _nullIf(); Type: FUNCTION; Schema: functions; Owner: -
105
--
106

    
107
CREATE FUNCTION "_nullIf"() RETURNS trigger
108
    LANGUAGE plpgsql IMMUTABLE
109
    AS $$
110
BEGIN
111
    IF new.type = 'str' THEN -- no cast needed
112
        new.result := (SELECT nullif(new.value, new."null"));
113
    -- Invalid value is ignored, but invalid null value generates error
114
    ELSIF new.type = 'float' THEN
115
        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
    END IF;
124
    
125
    RETURN new;
126
END;
127
$$;
128

    
129

    
130
--
131
-- 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
-- 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
SET default_tablespace = '';
168

    
169
SET default_with_oids = false;
170

    
171
--
172
-- 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
-- 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
    label text NOT NULL,
199
    value text
200
);
201

    
202

    
203
--
204
-- 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
-- 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
    "null" text NOT NULL,
231
    type datatype DEFAULT 'str'::datatype NOT NULL,
232
    value text
233
);
234

    
235

    
236
--
237
-- Name: _alt_0; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
238
--
239

    
240
CREATE INDEX _alt_0 ON _alt USING btree ((COALESCE("0", '\\N'::text)));
241

    
242

    
243
--
244
-- Name: _alt_1; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
245
--
246

    
247
CREATE INDEX _alt_1 ON _alt USING btree ((COALESCE("1", '\\N'::text)));
248

    
249

    
250
--
251
-- Name: _alt_2; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
252
--
253

    
254
CREATE INDEX _alt_2 ON _alt USING btree ((COALESCE("2", '\\N'::text)));
255

    
256

    
257
--
258
-- Name: _alt_3; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
259
--
260

    
261
CREATE INDEX _alt_3 ON _alt USING btree ((COALESCE("3", '\\N'::text)));
262

    
263

    
264
--
265
-- Name: _alt_4; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
266
--
267

    
268
CREATE INDEX _alt_4 ON _alt USING btree ((COALESCE("4", '\\N'::text)));
269

    
270

    
271
--
272
-- Name: _alt_5; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
273
--
274

    
275
CREATE INDEX _alt_5 ON _alt USING btree ((COALESCE("5", '\\N'::text)));
276

    
277

    
278
--
279
-- Name: _alt_6; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
280
--
281

    
282
CREATE INDEX _alt_6 ON _alt USING btree ((COALESCE("6", '\\N'::text)));
283

    
284

    
285
--
286
-- Name: _alt_7; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
287
--
288

    
289
CREATE INDEX _alt_7 ON _alt USING btree ((COALESCE("7", '\\N'::text)));
290

    
291

    
292
--
293
-- Name: _alt_8; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
294
--
295

    
296
CREATE INDEX _alt_8 ON _alt USING btree ((COALESCE("8", '\\N'::text)));
297

    
298

    
299
--
300
-- Name: _alt_9; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
301
--
302

    
303
CREATE INDEX _alt_9 ON _alt USING btree ((COALESCE("9", '\\N'::text)));
304

    
305

    
306
--
307
-- Name: _alt_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
308
--
309

    
310
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

    
312

    
313
--
314
-- Name: _label_label; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
315
--
316

    
317
CREATE INDEX _label_label ON _label USING btree ((COALESCE(label, '\\N'::text)));
318

    
319

    
320
--
321
-- Name: _label_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
322
--
323

    
324
CREATE UNIQUE INDEX _label_unique ON _label USING btree ((COALESCE(label, '\\N'::text)), (COALESCE(value, '\\N'::text)));
325

    
326

    
327
--
328
-- 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
CREATE INDEX _merge_0 ON _merge USING btree ((COALESCE("0", '\\N'::text)));
339

    
340

    
341
--
342
-- Name: _merge_1; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
343
--
344

    
345
CREATE INDEX _merge_1 ON _merge USING btree ((COALESCE("1", '\\N'::text)));
346

    
347

    
348
--
349
-- Name: _merge_2; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
350
--
351

    
352
CREATE INDEX _merge_2 ON _merge USING btree ((COALESCE("2", '\\N'::text)));
353

    
354

    
355
--
356
-- Name: _merge_3; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
357
--
358

    
359
CREATE INDEX _merge_3 ON _merge USING btree ((COALESCE("3", '\\N'::text)));
360

    
361

    
362
--
363
-- Name: _merge_4; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
364
--
365

    
366
CREATE INDEX _merge_4 ON _merge USING btree ((COALESCE("4", '\\N'::text)));
367

    
368

    
369
--
370
-- Name: _merge_5; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
371
--
372

    
373
CREATE INDEX _merge_5 ON _merge USING btree ((COALESCE("5", '\\N'::text)));
374

    
375

    
376
--
377
-- Name: _merge_6; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
378
--
379

    
380
CREATE INDEX _merge_6 ON _merge USING btree ((COALESCE("6", '\\N'::text)));
381

    
382

    
383
--
384
-- Name: _merge_7; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
385
--
386

    
387
CREATE INDEX _merge_7 ON _merge USING btree ((COALESCE("7", '\\N'::text)));
388

    
389

    
390
--
391
-- Name: _merge_8; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
392
--
393

    
394
CREATE INDEX _merge_8 ON _merge USING btree ((COALESCE("8", '\\N'::text)));
395

    
396

    
397
--
398
-- Name: _merge_9; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
399
--
400

    
401
CREATE INDEX _merge_9 ON _merge USING btree ((COALESCE("9", '\\N'::text)));
402

    
403

    
404
--
405
-- Name: _merge_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
406
--
407

    
408
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

    
410

    
411
--
412
-- Name: _nullIf_null; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
413
--
414

    
415
CREATE INDEX "_nullIf_null" ON "_nullIf" USING btree ("null");
416

    
417

    
418
--
419
-- Name: _nullIf_type; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
420
--
421

    
422
CREATE INDEX "_nullIf_type" ON "_nullIf" USING btree (type);
423

    
424

    
425
--
426
-- Name: _nullIf_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
427
--
428

    
429
CREATE UNIQUE INDEX "_nullIf_unique" ON "_nullIf" USING btree ("null", type, (COALESCE(value, '\\N'::text)));
430

    
431

    
432
--
433
-- Name: _nullIf_value; Type: INDEX; Schema: functions; Owner: -; Tablespace: 
434
--
435

    
436
CREATE INDEX "_nullIf_value" ON "_nullIf" USING btree (value);
437

    
438

    
439
--
440
-- 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
-- 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
-- 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
-- Name: _nullIf; Type: TRIGGER; Schema: functions; Owner: -
462
--
463

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

    
466

    
467
--
468
-- PostgreSQL database dump complete
469
--
470

    
(4-4/22)