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 = on;
8
SET check_function_bodies = false;
9
SET client_min_messages = warning;
10

    
11
--
12
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
13
--
14

    
15
CREATE SCHEMA util;
16

    
17

    
18
--
19
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
20
--
21

    
22
COMMENT ON SCHEMA util IS 'IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
23

    
24
NOTE: IMMUTABLE SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding.';
25

    
26

    
27
SET search_path = util, pg_catalog;
28

    
29
--
30
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
31
--
32

    
33
CREATE TYPE col_cast AS (
34
	col_name text,
35
	type regtype
36
);
37

    
38

    
39
--
40
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
41
--
42

    
43
CREATE TYPE col_ref AS (
44
	table_ regclass,
45
	name text
46
);
47

    
48

    
49
--
50
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
51
--
52

    
53
CREATE TYPE compass_dir AS ENUM (
54
    'N',
55
    'E',
56
    'S',
57
    'W'
58
);
59

    
60

    
61
--
62
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
63
--
64

    
65
CREATE TYPE datatype AS ENUM (
66
    'str',
67
    'float'
68
);
69

    
70

    
71
--
72
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
73
--
74

    
75
CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
76
    LANGUAGE sql IMMUTABLE
77
    AS $_$
78
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
79
$_$;
80

    
81

    
82
--
83
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
84
--
85

    
86
CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
87
    LANGUAGE sql IMMUTABLE
88
    AS $_$
89
SELECT bool_and(value)
90
FROM
91
(VALUES
92
      ($1)
93
    , ($2)
94
    , ($3)
95
    , ($4)
96
    , ($5)
97
)
98
AS v (value)
99
$_$;
100

    
101

    
102
--
103
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
104
--
105

    
106
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.';
107

    
108

    
109
--
110
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
111
--
112

    
113
CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
114
    LANGUAGE sql IMMUTABLE
115
    AS $_$
116
SELECT avg(value)
117
FROM
118
(VALUES
119
      ($1)
120
    , ($2)
121
    , ($3)
122
    , ($4)
123
    , ($5)
124
)
125
AS v (value)
126
$_$;
127

    
128

    
129
--
130
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
131
--
132

    
133
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
134
    LANGUAGE sql IMMUTABLE STRICT
135
    AS $_$
136
SELECT (g[1]||'1')::integer*util._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::util.compass_dir)
137
FROM 
138
(
139
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
140
    UNION ALL
141
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
142
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
143
)
144
matches (g)
145
$_$;
146

    
147

    
148
--
149
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
150
--
151

    
152
CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
153
    LANGUAGE sql IMMUTABLE
154
    AS $_$
155
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
156
FROM
157
(VALUES
158
      ($1)
159
    , ($2/60)
160
    , ($3/60/60)
161
)
162
AS v (value)
163
$_$;
164

    
165

    
166
--
167
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
168
--
169

    
170
CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
171
    LANGUAGE sql IMMUTABLE
172
    AS $_$
173
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
174
$_$;
175

    
176

    
177
--
178
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
179
--
180

    
181
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
182
    LANGUAGE sql IMMUTABLE
183
    AS $_$
184
SELECT $1 = $2
185
$_$;
186

    
187

    
188
--
189
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
190
--
191

    
192
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
193
    LANGUAGE sql IMMUTABLE
194
    AS $_$
195
-- Fix dates after threshold date
196
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
197
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
198
$_$;
199

    
200

    
201
--
202
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
203
--
204

    
205
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
206
    LANGUAGE sql IMMUTABLE
207
    AS $_$
208
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
209
$_$;
210

    
211

    
212
--
213
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
214
--
215

    
216
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
217
    LANGUAGE sql IMMUTABLE
218
    AS $_$
219
SELECT util._if($1 != '', $2, $3)
220
$_$;
221

    
222

    
223
--
224
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
225
--
226

    
227
CREATE FUNCTION _join("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
228
    LANGUAGE sql IMMUTABLE
229
    AS $_$
230
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
231
$_$;
232

    
233

    
234
--
235
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
236
--
237

    
238
CREATE FUNCTION _join_words("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
239
    LANGUAGE sql IMMUTABLE
240
    AS $_$
241
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
242
$_$;
243

    
244

    
245
--
246
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
247
--
248

    
249
CREATE FUNCTION _label(label text, value text) RETURNS text
250
    LANGUAGE sql IMMUTABLE
251
    AS $_$
252
SELECT coalesce($1 || ': ', '') || $2
253
$_$;
254

    
255

    
256
--
257
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
258
--
259

    
260
CREATE FUNCTION _lowercase(value text) RETURNS text
261
    LANGUAGE sql IMMUTABLE
262
    AS $_$
263
SELECT lower($1)
264
$_$;
265

    
266

    
267
--
268
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
269
--
270

    
271
CREATE FUNCTION _map(map hstore, value text) RETURNS text
272
    LANGUAGE plpgsql IMMUTABLE STRICT
273
    AS $$
274
DECLARE
275
    match text := map -> value;
276
BEGIN
277
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
278
        match := map -> '*'; -- use default entry
279
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
280
        END IF;
281
    END IF;
282
    
283
    -- Interpret result
284
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
285
    ELSIF match = '*' THEN RETURN value;
286
    ELSE RETURN match;
287
    END IF;
288
END;
289
$$;
290

    
291

    
292
--
293
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
294
--
295

    
296
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
297
    LANGUAGE plpgsql IMMUTABLE STRICT
298
    AS $$
299
DECLARE
300
    result value%TYPE := util._map(map, value::text)::unknown;
301
BEGIN
302
    RETURN result;
303
END;
304
$$;
305

    
306

    
307
--
308
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
309
--
310

    
311
CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
312
    LANGUAGE sql IMMUTABLE
313
    AS $_$
314
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
315
$_$;
316

    
317

    
318
--
319
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
320
--
321

    
322
CREATE FUNCTION _merge("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
323
    LANGUAGE sql IMMUTABLE
324
    AS $_$
325
SELECT util.join_strs(value, '; ')
326
FROM
327
(
328
    SELECT *
329
    FROM
330
    (
331
        SELECT
332
        DISTINCT ON (value)
333
        *
334
        FROM
335
        (VALUES
336
              (1, $1)
337
            , (2, $2)
338
            , (3, $3)
339
            , (4, $4)
340
            , (5, $5)
341
            , (6, $6)
342
            , (7, $7)
343
            , (8, $8)
344
            , (9, $9)
345
            , (10, $10)
346
        )
347
        AS v (sort_order, value)
348
        WHERE value IS NOT NULL
349
    )
350
    AS v
351
    ORDER BY sort_order
352
)
353
AS v
354
$_$;
355

    
356

    
357
--
358
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
359
--
360

    
361
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
362
    LANGUAGE sql IMMUTABLE
363
    AS $_$
364
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
365
$_$;
366

    
367

    
368
--
369
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
370
--
371

    
372
CREATE FUNCTION _merge_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
373
    LANGUAGE sql IMMUTABLE
374
    AS $_$
375
SELECT util.join_strs(value, ' ')
376
FROM
377
(
378
    SELECT *
379
    FROM
380
    (
381
        SELECT
382
        DISTINCT ON (value)
383
        *
384
        FROM
385
        (VALUES
386
              (1, $1)
387
            , (2, $2)
388
            , (3, $3)
389
            , (4, $4)
390
            , (5, $5)
391
            , (6, $6)
392
            , (7, $7)
393
            , (8, $8)
394
            , (9, $9)
395
            , (10, $10)
396
        )
397
        AS v (sort_order, value)
398
        WHERE value IS NOT NULL
399
    )
400
    AS v
401
    ORDER BY sort_order
402
)
403
AS v
404
$_$;
405

    
406

    
407
--
408
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
409
--
410

    
411
CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
412
    LANGUAGE sql IMMUTABLE
413
    AS $_$
414
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
415
$_$;
416

    
417

    
418
--
419
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
420
--
421

    
422
CREATE FUNCTION _not(value boolean) RETURNS boolean
423
    LANGUAGE sql IMMUTABLE
424
    AS $_$
425
SELECT NOT $1
426
$_$;
427

    
428

    
429
--
430
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
431
--
432

    
433
CREATE FUNCTION _now() RETURNS timestamp with time zone
434
    LANGUAGE sql STABLE
435
    AS $$
436
SELECT now()
437
$$;
438

    
439

    
440
--
441
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
442
--
443

    
444
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
445
    LANGUAGE plpgsql IMMUTABLE
446
    AS $$
447
DECLARE
448
    type util.datatype NOT NULL := type; -- add NOT NULL
449
BEGIN
450
    IF type = 'str' THEN RETURN nullif(value::text, "null");
451
    -- Invalid value is ignored, but invalid null value generates error
452
    ELSIF type = 'float' THEN
453
        DECLARE
454
            -- Outside the try block so that invalid null value generates error
455
            "null" double precision := "null"::double precision;
456
        BEGIN
457
            RETURN nullif(value::double precision, "null");
458
        EXCEPTION
459
            WHEN data_exception THEN RETURN value; -- ignore invalid value
460
        END;
461
    END IF;
462
END;
463
$$;
464

    
465

    
466
--
467
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
468
--
469

    
470
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
471
    LANGUAGE sql IMMUTABLE
472
    AS $_$
473
SELECT util."_nullIf"($1, $2, $3::util.datatype)
474
$_$;
475

    
476

    
477
--
478
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
479
--
480

    
481
CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
482
    LANGUAGE sql IMMUTABLE
483
    AS $_$
484
SELECT bool_or(value)
485
FROM
486
(VALUES
487
      ($1)
488
    , ($2)
489
    , ($3)
490
    , ($4)
491
    , ($5)
492
)
493
AS v (value)
494
$_$;
495

    
496

    
497
--
498
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
499
--
500

    
501
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.';
502

    
503

    
504
--
505
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
506
--
507

    
508
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
509
    LANGUAGE sql IMMUTABLE
510
    AS $_$
511
SELECT $2 - $1
512
$_$;
513

    
514

    
515
--
516
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
517
--
518

    
519
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
520
    LANGUAGE sql IMMUTABLE
521
    AS $_$
522
SELECT regexp_split_to_table($1, $2)
523
$_$;
524

    
525

    
526
--
527
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
528
--
529

    
530
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
531
    LANGUAGE sql STABLE STRICT
532
    AS $_$
533
SELECT util.derived_cols($1, $2)
534
UNION
535
SELECT util.eval2set($$
536
SELECT col
537
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
538
JOIN $$||$2||$$ ON "to" = col
539
WHERE "from" LIKE ':%'
540
$$, NULL::text)
541
$_$;
542

    
543

    
544
--
545
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
546
--
547

    
548
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS 'gets table_''s added columns (all the columns not in the original data)';
549

    
550

    
551
--
552
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
553
--
554

    
555
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
556
    LANGUAGE sql IMMUTABLE
557
    AS $_$
558
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
559
$_$;
560

    
561

    
562
--
563
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
564
--
565

    
566
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
567
    LANGUAGE plpgsql IMMUTABLE
568
    AS $$
569
DECLARE
570
	value_cmp         state%TYPE = ARRAY[value];
571
	state             state%TYPE = COALESCE(state, value_cmp);
572
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
573
BEGIN
574
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
575
END;
576
$$;
577

    
578

    
579
--
580
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
581
--
582

    
583
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
584
    LANGUAGE sql IMMUTABLE
585
    AS $_$
586
SELECT pg_catalog.array_fill($1, ARRAY[$2])
587
$_$;
588

    
589

    
590
--
591
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
592
--
593

    
594
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
595
    LANGUAGE sql IMMUTABLE
596
    AS $_$
597
SELECT util.array_length($1, 1)
598
$_$;
599

    
600

    
601
--
602
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
603
--
604

    
605
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
606
    LANGUAGE sql IMMUTABLE
607
    AS $_$
608
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
609
$_$;
610

    
611

    
612
--
613
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
614
--
615

    
616
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS 'returns 0 instead of NULL for empty arrays';
617

    
618

    
619
--
620
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
621
--
622

    
623
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
624
    LANGUAGE sql STABLE STRICT
625
    AS $_$
626
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
627
$_$;
628

    
629

    
630
--
631
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
632
--
633

    
634
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
635
    LANGUAGE plpgsql STRICT
636
    AS $_$
637
BEGIN
638
    -- not yet clustered (ARRAY[] compares NULLs literally)
639
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
640
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
641
    END IF;
642
END;
643
$_$;
644

    
645

    
646
--
647
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
648
--
649

    
650
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
651

    
652

    
653
--
654
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
655
--
656

    
657
CREATE FUNCTION col_comment(col col_ref) RETURNS text
658
    LANGUAGE plpgsql STABLE STRICT
659
    AS $$
660
DECLARE
661
	comment text;
662
BEGIN
663
	SELECT description
664
	FROM pg_attribute
665
	LEFT JOIN pg_description ON objoid = attrelid
666
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
667
	WHERE attrelid = col.table_ AND attname = col.name
668
	INTO STRICT comment
669
	;
670
	RETURN comment;
671
EXCEPTION
672
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
673
END;
674
$$;
675

    
676

    
677
--
678
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
679
--
680

    
681
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
682
    LANGUAGE plpgsql STABLE STRICT
683
    AS $$
684
DECLARE
685
	default_sql text;
686
BEGIN
687
	SELECT adsrc
688
	FROM pg_attribute
689
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
690
	WHERE attrelid = col.table_ AND attname = col.name
691
	INTO STRICT default_sql
692
	;
693
	RETURN default_sql;
694
EXCEPTION
695
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
696
END;
697
$$;
698

    
699

    
700
--
701
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
702
--
703

    
704
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
705
    LANGUAGE sql STABLE
706
    AS $_$
707
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
708
$_$;
709

    
710

    
711
--
712
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
713
--
714

    
715
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
716

    
717

    
718
--
719
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
720
--
721

    
722
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
723
    LANGUAGE plpgsql STRICT
724
    AS $$
725
BEGIN
726
    PERFORM util.col_type(col);
727
    RETURN true;
728
EXCEPTION
729
    WHEN undefined_column THEN RETURN false;
730
END;
731
$$;
732

    
733

    
734
--
735
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
736
--
737

    
738
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
739
    LANGUAGE plpgsql STABLE STRICT
740
    AS $$
741
DECLARE
742
    prefix text := util.name(type)||'.';
743
BEGIN
744
    RETURN QUERY
745
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
746
        FROM util.col_names(type) f (name_);
747
END;
748
$$;
749

    
750

    
751
--
752
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
753
--
754

    
755
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
756
    LANGUAGE plpgsql STABLE STRICT
757
    AS $_$
758
BEGIN
759
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
760
END;
761
$_$;
762

    
763

    
764
--
765
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
766
--
767

    
768
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
769
    LANGUAGE sql STABLE STRICT
770
    AS $_$
771
SELECT attname::text
772
FROM pg_attribute
773
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
774
ORDER BY attnum
775
$_$;
776

    
777

    
778
--
779
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
780
--
781

    
782
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
783
    LANGUAGE plpgsql STABLE STRICT
784
    AS $$
785
DECLARE
786
    type regtype;
787
BEGIN
788
    SELECT atttypid FROM pg_attribute
789
    WHERE attrelid = col.table_ AND attname = col.name
790
    INTO STRICT type
791
    ;
792
    RETURN type;
793
EXCEPTION
794
    WHEN no_data_found THEN
795
        RAISE undefined_column USING MESSAGE =
796
            concat('undefined column: ', col.name);
797
END;
798
$$;
799

    
800

    
801
--
802
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
803
--
804

    
805
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
806
    LANGUAGE sql IMMUTABLE
807
    AS $_$
808
SELECT position($1 in $2) > 0 /*1-based offset*/
809
$_$;
810

    
811

    
812
--
813
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
814
--
815

    
816
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
817
    LANGUAGE plpgsql STRICT
818
    AS $$
819
BEGIN
820
    PERFORM util.eval(sql);
821
EXCEPTION
822
    WHEN duplicate_table  THEN NULL;
823
    WHEN duplicate_object THEN NULL; -- e.g. constraint
824
    WHEN duplicate_column THEN NULL;
825
    WHEN invalid_table_definition THEN
826
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
827
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
828
        END IF;
829
END;
830
$$;
831

    
832

    
833
--
834
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
835
--
836

    
837
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
838

    
839

    
840
--
841
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
842
--
843

    
844
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
845
    LANGUAGE sql STABLE STRICT
846
    AS $_$
847
SELECT util.eval2set($$
848
SELECT col
849
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
850
LEFT JOIN $$||$2||$$ ON "to" = col
851
WHERE "from" IS NULL
852
$$, NULL::text)
853
$_$;
854

    
855

    
856
--
857
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
858
--
859

    
860
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS 'gets table_''s derived columns (all the columns not in the names table)';
861

    
862

    
863
--
864
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
865
--
866

    
867
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
868
    LANGUAGE sql STRICT
869
    AS $_$
870
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
871
$_$;
872

    
873

    
874
--
875
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
876
--
877

    
878
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
879

    
880

    
881
--
882
-- Name: drop_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
883
--
884

    
885
CREATE FUNCTION drop_column(col col_ref) RETURNS void
886
    LANGUAGE sql STRICT
887
    AS $_$
888
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
889
quote_ident($1.name))
890
$_$;
891

    
892

    
893
--
894
-- Name: FUNCTION drop_column(col col_ref); Type: COMMENT; Schema: util; Owner: -
895
--
896

    
897
COMMENT ON FUNCTION drop_column(col col_ref) IS 'idempotent';
898

    
899

    
900
--
901
-- Name: drop_table(text); Type: FUNCTION; Schema: util; Owner: -
902
--
903

    
904
CREATE FUNCTION drop_table(table_ text) RETURNS void
905
    LANGUAGE sql STRICT
906
    AS $_$
907
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
908
$_$;
909

    
910

    
911
--
912
-- Name: FUNCTION drop_table(table_ text); Type: COMMENT; Schema: util; Owner: -
913
--
914

    
915
COMMENT ON FUNCTION drop_table(table_ text) IS 'idempotent';
916

    
917

    
918
--
919
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
920
--
921

    
922
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
923
    LANGUAGE sql IMMUTABLE
924
    AS $_$
925
SELECT util.array_fill($1, 0)
926
$_$;
927

    
928

    
929
--
930
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
931
--
932

    
933
COMMENT ON FUNCTION empty_array(elem_type_null anyelement) IS 'constructs proper empty 1-dimensional array whose dimensions are not NULL ( ''{}''::text[] does not do this)';
934

    
935

    
936
--
937
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
938
--
939

    
940
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
941
    LANGUAGE sql IMMUTABLE
942
    AS $_$
943
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
944
$_$;
945

    
946

    
947
--
948
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
949
--
950

    
951
CREATE FUNCTION eval(sql text) RETURNS void
952
    LANGUAGE plpgsql STRICT
953
    AS $$
954
BEGIN
955
    RAISE NOTICE '%', sql;
956
    EXECUTE sql;
957
END;
958
$$;
959

    
960

    
961
--
962
-- Name: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
963
--
964

    
965
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS SETOF anyelement
966
    LANGUAGE plpgsql
967
    AS $$
968
BEGIN
969
	RAISE NOTICE '%', sql;
970
	RETURN QUERY EXECUTE sql;
971
END;
972
$$;
973

    
974

    
975
--
976
-- Name: FUNCTION eval2set(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
977
--
978

    
979
COMMENT ON FUNCTION eval2set(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
980

    
981

    
982
--
983
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
984
--
985

    
986
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
987
    LANGUAGE plpgsql
988
    AS $$
989
DECLARE
990
	ret_val ret_type_null%TYPE;
991
BEGIN
992
	RAISE NOTICE '%', sql;
993
	EXECUTE sql INTO STRICT ret_val;
994
	RETURN ret_val;
995
END;
996
$$;
997

    
998

    
999
--
1000
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1001
--
1002

    
1003
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
1004

    
1005

    
1006
--
1007
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1008
--
1009

    
1010
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1011
    LANGUAGE sql
1012
    AS $_$
1013
SELECT util.eval2val($$SELECT $$||$1, $2)
1014
$_$;
1015

    
1016

    
1017
--
1018
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1019
--
1020

    
1021
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
1022

    
1023

    
1024
--
1025
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1026
--
1027

    
1028
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1029
    LANGUAGE sql
1030
    AS $_$
1031
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1032
$_$;
1033

    
1034

    
1035
--
1036
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1037
--
1038

    
1039
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS 'sql: can be NULL, which will be passed through
1040
ret_type_null: NULL::ret_type';
1041

    
1042

    
1043
--
1044
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1045
--
1046

    
1047
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1048
    LANGUAGE sql STABLE STRICT
1049
    AS $_$
1050
SELECT col_name
1051
FROM unnest($2) s (col_name)
1052
WHERE util.col_exists(($1, col_name))
1053
$_$;
1054

    
1055

    
1056
--
1057
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1058
--
1059

    
1060
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1061
    LANGUAGE sql IMMUTABLE
1062
    AS $_$
1063
-- STRICT handles NULLs, so that the array will always be a value
1064
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1065
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1066
) END
1067
$_$;
1068

    
1069

    
1070
--
1071
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1072
--
1073

    
1074
COMMENT ON FUNCTION fix_array("array" anyarray) IS 'ensures that an array will always have proper non-NULL dimensions';
1075

    
1076

    
1077
--
1078
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1079
--
1080

    
1081
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1082
    LANGUAGE plpgsql STRICT
1083
    AS $_$
1084
DECLARE
1085
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1086
$$||query;
1087
BEGIN
1088
	EXECUTE mk_view;
1089
EXCEPTION
1090
WHEN invalid_table_definition THEN
1091
	IF SQLERRM = 'cannot drop columns from view'
1092
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1093
	THEN
1094
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1095
		EXECUTE mk_view;
1096
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1097
	END IF;
1098
END;
1099
$_$;
1100

    
1101

    
1102
--
1103
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1104
--
1105

    
1106
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
1107

    
1108

    
1109
--
1110
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1111
--
1112

    
1113
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1114
    LANGUAGE sql IMMUTABLE
1115
    AS $_$
1116
SELECT substring($2 for length($1)) = $1
1117
$_$;
1118

    
1119

    
1120
--
1121
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1122
--
1123

    
1124
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1125
    LANGUAGE sql IMMUTABLE
1126
    AS $_$
1127
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1128
$_$;
1129

    
1130

    
1131
--
1132
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1133
--
1134

    
1135
COMMENT ON FUNCTION hstore(keys text[], value text) IS 'avoids repeating the same value for each key';
1136

    
1137

    
1138
--
1139
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1140
--
1141

    
1142
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1143
    LANGUAGE sql STABLE STRICT
1144
    AS $_$
1145
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1146
$_$;
1147

    
1148

    
1149
--
1150
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1151
--
1152

    
1153
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1154
    LANGUAGE sql IMMUTABLE
1155
    AS $_$
1156
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1157
$_$;
1158

    
1159

    
1160
--
1161
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1162
--
1163

    
1164
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1165
    LANGUAGE sql IMMUTABLE
1166
    AS $_$
1167
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1168
$_$;
1169

    
1170

    
1171
--
1172
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1173
--
1174

    
1175
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1176
    LANGUAGE sql IMMUTABLE STRICT
1177
    AS $_$
1178
SELECT $1 || $3 || $2
1179
$_$;
1180

    
1181

    
1182
--
1183
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1184
--
1185

    
1186
CREATE FUNCTION map_filter_insert() RETURNS trigger
1187
    LANGUAGE plpgsql
1188
    AS $$
1189
BEGIN
1190
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1191
	RETURN new;
1192
END;
1193
$$;
1194

    
1195

    
1196
--
1197
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1198
--
1199

    
1200
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1201
    LANGUAGE plpgsql STABLE STRICT
1202
    AS $_$
1203
DECLARE
1204
    value text;
1205
BEGIN
1206
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1207
        INTO value USING key;
1208
    RETURN value;
1209
END;
1210
$_$;
1211

    
1212

    
1213
--
1214
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1215
--
1216

    
1217
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1218
    LANGUAGE sql IMMUTABLE
1219
    AS $_$
1220
SELECT util._map(util.nulls_map($1), $2)
1221
$_$;
1222

    
1223

    
1224
--
1225
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1226
--
1227

    
1228
COMMENT ON FUNCTION map_nulls(nulls text[], value anyelement) IS 'due to dynamic inlining[1], this is just as fast as util._map() which it wraps[2].
1229

    
1230
[1] inlining of function calls, which is different from constant folding
1231
[2] _map()''s profiling query
1232
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1233
and map_nulls()''s profiling query
1234
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1235
both take ~920 ms.
1236
also, /inputs/REMIB/Specimen/postprocess.sql > country takes the same amount of time (56000 ms) to build with map_nulls() as with a literal hstore.';
1237

    
1238

    
1239
--
1240
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1241
--
1242

    
1243
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1244
    LANGUAGE plpgsql STABLE STRICT
1245
    AS $_$
1246
BEGIN
1247
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1248
END;
1249
$_$;
1250

    
1251

    
1252
--
1253
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1254
--
1255

    
1256
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1257
    LANGUAGE sql STRICT
1258
    AS $_$
1259
SELECT util.create_if_not_exists($$
1260
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1261
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1262
||quote_literal($2)||$$;
1263
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS 'constant';
1264
$$)
1265
$_$;
1266

    
1267

    
1268
--
1269
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1270
--
1271

    
1272
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
1273

    
1274

    
1275
--
1276
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1277
--
1278

    
1279
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1280
    LANGUAGE plpgsql STRICT
1281
    AS $_$
1282
DECLARE
1283
    type regtype = util.typeof(expr, col.table_::text::regtype);
1284
    col_name_sql text = quote_ident(col.name);
1285
BEGIN
1286
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1287
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1288
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1289
$$||expr||$$;
1290
$$);
1291
END;
1292
$_$;
1293

    
1294

    
1295
--
1296
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1297
--
1298

    
1299
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS 'idempotent';
1300

    
1301

    
1302
--
1303
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1304
--
1305

    
1306
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1307
    LANGUAGE sql STRICT
1308
    AS $_$
1309
SELECT util.create_if_not_exists($$
1310
CREATE TABLE $$||$1||$$
1311
(
1312
    LIKE util.map INCLUDING ALL
1313
);
1314

    
1315
CREATE TRIGGER map_filter_insert
1316
  BEFORE INSERT
1317
  ON $$||$1||$$
1318
  FOR EACH ROW
1319
  EXECUTE PROCEDURE util.map_filter_insert();
1320
$$)
1321
$_$;
1322

    
1323

    
1324
--
1325
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1326
--
1327

    
1328
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1329
    LANGUAGE sql STRICT
1330
    AS $_$
1331
SELECT util.mk_const_col(($1, 'source'), util.table_schema($1))
1332
$_$;
1333

    
1334

    
1335
--
1336
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1337
--
1338

    
1339
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS 'idempotent';
1340

    
1341

    
1342
--
1343
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1344
--
1345

    
1346
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1347
    LANGUAGE plpgsql STRICT
1348
    AS $_$
1349
BEGIN
1350
	EXECUTE $$
1351
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1352
  RETURNS SETOF $$||view_||$$ AS
1353
$BODY1$
1354
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
1355
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
1356
$BODY1$
1357
  LANGUAGE sql STABLE
1358
  COST 100
1359
  ROWS 1000
1360
$$;
1361
-- Also create subset function which turns off enable_sort
1362
	EXECUTE $$
1363
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1364
  RETURNS SETOF $$||view_||$$
1365
  SET enable_sort TO 'off'
1366
  AS
1367
$BODY1$
1368
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
1369
$BODY1$
1370
  LANGUAGE sql STABLE
1371
  COST 100
1372
  ROWS 1000
1373
;
1374
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1375
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1376
If you want to run EXPLAIN and get expanded output, use the regular subset
1377
function instead. (When a config param is set on a function, EXPLAIN produces
1378
just a function scan.)
1379
';
1380
$$;
1381
END;
1382
$_$;
1383

    
1384

    
1385
--
1386
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1387
--
1388

    
1389
CREATE FUNCTION name(type regtype) RETURNS text
1390
    LANGUAGE sql STABLE STRICT
1391
    AS $_$
1392
SELECT typname::text FROM pg_type WHERE oid = $1
1393
$_$;
1394

    
1395

    
1396
--
1397
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1398
--
1399

    
1400
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
1401
    LANGUAGE sql IMMUTABLE
1402
    AS $_$
1403
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
1404
$_$;
1405

    
1406

    
1407
--
1408
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
1409
--
1410

    
1411
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
1412
    LANGUAGE sql IMMUTABLE
1413
    AS $_$
1414
SELECT $1 IS NOT NULL
1415
$_$;
1416

    
1417

    
1418
--
1419
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
1420
--
1421

    
1422
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
1423
    LANGUAGE sql IMMUTABLE
1424
    AS $_$
1425
SELECT util.hstore($1, NULL) || '*=>*'
1426
$_$;
1427

    
1428

    
1429
--
1430
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
1431
--
1432

    
1433
COMMENT ON FUNCTION nulls_map(nulls text[]) IS 'for use with _map()';
1434

    
1435

    
1436
--
1437
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
1438
--
1439

    
1440
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
1441
    LANGUAGE plpgsql IMMUTABLE STRICT
1442
    AS $$
1443
BEGIN
1444
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
1445
END;
1446
$$;
1447

    
1448

    
1449
--
1450
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1451
--
1452

    
1453
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
1454
    LANGUAGE sql STRICT
1455
    AS $_$
1456
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1457
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
1458
FROM util.col_names($1::text::regtype) f (name);
1459
SELECT NULL::void; -- don't fold away functions called in previous query
1460
$_$;
1461

    
1462

    
1463
--
1464
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
1465
--
1466

    
1467
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
1468

    
1469

    
1470
--
1471
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1472
--
1473

    
1474
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
1475
    LANGUAGE sql STRICT
1476
    AS $_$
1477
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
1478
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
1479
SELECT util.set_col_names($1, $2);
1480
$_$;
1481

    
1482

    
1483
--
1484
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1485
--
1486

    
1487
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS 'idempotent.
1488
alters the names table, so it will need to be repopulated after running this function.';
1489

    
1490

    
1491
--
1492
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1493
--
1494

    
1495
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
1496
    LANGUAGE sql STRICT
1497
    AS $_$
1498
SELECT util.drop_table($1);
1499
SELECT util.mk_map_table($1);
1500
$_$;
1501

    
1502

    
1503
--
1504
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
1505
--
1506

    
1507
CREATE FUNCTION schema(type regtype) RETURNS text
1508
    LANGUAGE sql STABLE
1509
    AS $_$
1510
SELECT nspname::text
1511
FROM pg_type
1512
JOIN pg_namespace ON pg_namespace.oid = typnamespace
1513
WHERE pg_type.oid = $1
1514
$_$;
1515

    
1516

    
1517
--
1518
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
1519
--
1520

    
1521
CREATE FUNCTION schema(type_null anyelement) RETURNS text
1522
    LANGUAGE sql STABLE
1523
    AS $_$
1524
SELECT util.schema(pg_typeof($1))
1525
$_$;
1526

    
1527

    
1528
--
1529
-- Name: schema_ident(anyelement); Type: FUNCTION; Schema: util; Owner: -
1530
--
1531

    
1532
CREATE FUNCTION schema_ident(type_null anyelement) RETURNS text
1533
    LANGUAGE sql STABLE
1534
    AS $_$
1535
SELECT quote_ident(util.schema($1))
1536
$_$;
1537

    
1538

    
1539
--
1540
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
1541
--
1542

    
1543
CREATE FUNCTION search_path_append(schemas text) RETURNS void
1544
    LANGUAGE sql STRICT
1545
    AS $_$
1546
SELECT util.eval(
1547
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
1548
$_$;
1549

    
1550

    
1551
--
1552
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1553
--
1554

    
1555
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
1556
    LANGUAGE plpgsql STRICT
1557
    AS $_$
1558
DECLARE
1559
    old text[] = ARRAY(SELECT util.col_names(table_));
1560
    new text[] = ARRAY(SELECT util.map_values(names));
1561
BEGIN
1562
    old = old[1:array_length(new, 1)]; -- truncate to same length
1563
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
1564
||$$ TO $$||quote_ident(value))
1565
    FROM each(hstore(old, new))
1566
    WHERE value != key -- not same name
1567
    ;
1568
END;
1569
$_$;
1570

    
1571

    
1572
--
1573
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1574
--
1575

    
1576
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
1577

    
1578

    
1579
--
1580
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1581
--
1582

    
1583
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
1584
    LANGUAGE plpgsql STRICT
1585
    AS $_$
1586
DECLARE
1587
	row_ util.map;
1588
BEGIN
1589
	-- rename any metadata cols rather than re-adding them with new names
1590
	BEGIN
1591
		PERFORM util.set_col_names(table_, names);
1592
	EXCEPTION
1593
		WHEN array_subscript_error THEN -- selective suppress
1594
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
1595
				-- metadata cols not yet added
1596
			ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
1597
			END IF;
1598
	END;
1599
	
1600
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
1601
	LOOP
1602
		PERFORM util.mk_const_col((table_, row_."to"),
1603
			substring(row_."from" from 2));
1604
	END LOOP;
1605
	
1606
	PERFORM util.set_col_names(table_, names);
1607
END;
1608
$_$;
1609

    
1610

    
1611
--
1612
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1613
--
1614

    
1615
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS 'idempotent.
1616
the metadata mappings must be *last* in the names table.';
1617

    
1618

    
1619
--
1620
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
1621
--
1622

    
1623
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
1624
    LANGUAGE plpgsql STRICT
1625
    AS $_$
1626
DECLARE
1627
    sql text = $$ALTER TABLE $$||table_||$$
1628
$$||NULLIF(array_to_string(ARRAY(
1629
    SELECT
1630
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
1631
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1632
    FROM
1633
    (
1634
        SELECT
1635
          quote_ident(col_name) AS col_name_sql
1636
        , util.col_type((table_, col_name)) AS curr_type
1637
        , type AS target_type
1638
        FROM unnest(col_casts)
1639
    ) s
1640
    WHERE curr_type != target_type
1641
), '
1642
, '), '');
1643
BEGIN
1644
    RAISE NOTICE '%', sql;
1645
    EXECUTE COALESCE(sql, '');
1646
END;
1647
$_$;
1648

    
1649

    
1650
--
1651
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1652
--
1653

    
1654
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
1655

    
1656

    
1657
--
1658
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1659
--
1660

    
1661
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1662
    LANGUAGE plpgsql STABLE STRICT
1663
    AS $_$
1664
DECLARE
1665
    hstore hstore;
1666
BEGIN
1667
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1668
        table_||$$))$$ INTO STRICT hstore;
1669
    RETURN hstore;
1670
END;
1671
$_$;
1672

    
1673

    
1674
--
1675
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1676
--
1677

    
1678
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
1679
    LANGUAGE sql STABLE STRICT
1680
    AS $_$
1681
SELECT COUNT(*) > 0 FROM pg_constraint
1682
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
1683
$_$;
1684

    
1685

    
1686
--
1687
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1688
--
1689

    
1690
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS 'gets whether a status flag is set by the presence of a table constraint';
1691

    
1692

    
1693
--
1694
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1695
--
1696

    
1697
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
1698
    LANGUAGE sql STRICT
1699
    AS $_$
1700
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
1701
||quote_ident($2)||$$ CHECK (true)$$)
1702
$_$;
1703

    
1704

    
1705
--
1706
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1707
--
1708

    
1709
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS 'stores a status flag by the presence of a table constraint.
1710
idempotent.';
1711

    
1712

    
1713
--
1714
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
1715
--
1716

    
1717
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
1718
    LANGUAGE sql STABLE STRICT
1719
    AS $_$
1720
SELECT util.table_flag__get($1, 'nulls_mapped')
1721
$_$;
1722

    
1723

    
1724
--
1725
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1726
--
1727

    
1728
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS 'gets whether a table''s NULL-equivalent strings have been replaced with NULL';
1729

    
1730

    
1731
--
1732
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
1733
--
1734

    
1735
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
1736
    LANGUAGE sql STRICT
1737
    AS $_$
1738
SELECT util.table_flag__set($1, 'nulls_mapped')
1739
$_$;
1740

    
1741

    
1742
--
1743
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1744
--
1745

    
1746
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS 'sets that a table''s NULL-equivalent strings have been replaced with NULL.
1747
idempotent.';
1748

    
1749

    
1750
--
1751
-- Name: table_schema(regclass); Type: FUNCTION; Schema: util; Owner: -
1752
--
1753

    
1754
CREATE FUNCTION table_schema(table_ regclass) RETURNS text
1755
    LANGUAGE sql STABLE STRICT
1756
    AS $_$
1757
SELECT nspname::text FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = $1)
1758
$_$;
1759

    
1760

    
1761
--
1762
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1763
--
1764

    
1765
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1766
    LANGUAGE plpgsql STRICT
1767
    AS $_$
1768
DECLARE
1769
    row record;
1770
BEGIN
1771
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1772
    LOOP
1773
        IF row.global_name != row.name THEN
1774
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1775
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1776
        END IF;
1777
    END LOOP;
1778
END;
1779
$_$;
1780

    
1781

    
1782
--
1783
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1784
--
1785

    
1786
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1787

    
1788

    
1789
--
1790
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1791
--
1792

    
1793
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
1794
    LANGUAGE sql STRICT
1795
    AS $_$
1796
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
1797
SELECT NULL::void; -- don't fold away functions called in previous query
1798
$_$;
1799

    
1800

    
1801
--
1802
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1803
--
1804

    
1805
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS 'trims table_ to include only columns in the original data.
1806
idempotent.';
1807

    
1808

    
1809
--
1810
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
1811
--
1812

    
1813
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1814
    LANGUAGE plpgsql STRICT
1815
    AS $_$
1816
BEGIN
1817
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1818
END;
1819
$_$;
1820

    
1821

    
1822
--
1823
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1824
--
1825

    
1826
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1827

    
1828

    
1829
--
1830
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1831
--
1832

    
1833
CREATE FUNCTION try_create(sql text) RETURNS void
1834
    LANGUAGE plpgsql STRICT
1835
    AS $$
1836
BEGIN
1837
    PERFORM util.eval(sql);
1838
EXCEPTION
1839
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
1840
    WHEN undefined_column THEN NULL;
1841
    WHEN duplicate_column THEN NULL;
1842
END;
1843
$$;
1844

    
1845

    
1846
--
1847
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1848
--
1849

    
1850
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1851

    
1852

    
1853
--
1854
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1855
--
1856

    
1857
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1858
    LANGUAGE sql STRICT
1859
    AS $_$
1860
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1861
$_$;
1862

    
1863

    
1864
--
1865
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1866
--
1867

    
1868
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1869

    
1870

    
1871
--
1872
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1873
--
1874

    
1875
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1876
    LANGUAGE sql IMMUTABLE
1877
    AS $_$
1878
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1879
$_$;
1880

    
1881

    
1882
--
1883
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
1884
--
1885

    
1886
COMMENT ON FUNCTION type_qual(value anyelement) IS 'a type''s NOT NULL qualifier';
1887

    
1888

    
1889
--
1890
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1891
--
1892

    
1893
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1894
    LANGUAGE sql STABLE STRICT
1895
    SET search_path TO pg_temp
1896
    AS $_$
1897
SELECT $1::text
1898
$_$;
1899

    
1900

    
1901
--
1902
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
1903
--
1904

    
1905
COMMENT ON FUNCTION type_qual_name(type regtype) IS 'a type''s schema-qualified name';
1906

    
1907

    
1908
--
1909
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1910
--
1911

    
1912
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
1913
    LANGUAGE plpgsql STABLE
1914
    AS $_$
1915
DECLARE
1916
    type regtype;
1917
BEGIN
1918
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
1919
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
1920
    RETURN type;
1921
END;
1922
$_$;
1923

    
1924

    
1925
--
1926
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
1927
--
1928

    
1929
CREATE AGGREGATE all_same(anyelement) (
1930
    SFUNC = all_same_transform,
1931
    STYPE = anyarray,
1932
    FINALFUNC = all_same_final
1933
);
1934

    
1935

    
1936
--
1937
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
1938
--
1939

    
1940
COMMENT ON AGGREGATE all_same(anyelement) IS 'includes NULLs in comparison';
1941

    
1942

    
1943
--
1944
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1945
--
1946

    
1947
CREATE AGGREGATE join_strs(text, text) (
1948
    SFUNC = join_strs_transform,
1949
    STYPE = text
1950
);
1951

    
1952

    
1953
--
1954
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1955
--
1956

    
1957
CREATE OPERATOR -> (
1958
    PROCEDURE = map_get,
1959
    LEFTARG = regclass,
1960
    RIGHTARG = text
1961
);
1962

    
1963

    
1964
--
1965
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
1966
--
1967

    
1968
CREATE OPERATOR => (
1969
    PROCEDURE = hstore,
1970
    LEFTARG = text[],
1971
    RIGHTARG = text
1972
);
1973

    
1974

    
1975
--
1976
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
1977
--
1978

    
1979
COMMENT ON OPERATOR => (text[], text) IS 'usage: array[''key1'', ...]::text[] => ''value''';
1980

    
1981

    
1982
--
1983
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
1984
--
1985

    
1986
CREATE OPERATOR ?*>= (
1987
    PROCEDURE = is_populated_more_often_than,
1988
    LEFTARG = anyelement,
1989
    RIGHTARG = anyelement
1990
);
1991

    
1992

    
1993
--
1994
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
1995
--
1996

    
1997
CREATE OPERATOR ?>= (
1998
    PROCEDURE = is_more_complete_than,
1999
    LEFTARG = anyelement,
2000
    RIGHTARG = anyelement
2001
);
2002

    
2003

    
2004
SET default_tablespace = '';
2005

    
2006
SET default_with_oids = false;
2007

    
2008
--
2009
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
2010
--
2011

    
2012
CREATE TABLE map (
2013
    "from" text NOT NULL,
2014
    "to" text,
2015
    filter text,
2016
    notes text
2017
);
2018

    
2019

    
2020
--
2021
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
2022
--
2023

    
2024

    
2025

    
2026
--
2027
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
2028
--
2029

    
2030
ALTER TABLE ONLY map
2031
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
2032

    
2033

    
2034
--
2035
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
2036
--
2037

    
2038
ALTER TABLE ONLY map
2039
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
2040

    
2041

    
2042
--
2043
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
2044
--
2045

    
2046
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
2047

    
2048

    
2049
--
2050
-- PostgreSQL database dump complete
2051
--
2052

    
(17-17/27)