Project

General

Profile

1
--
2
-- PostgreSQL database dump
3
--
4

    
5
SET statement_timeout = 0;
6
SET lock_timeout = 0;
7
SET client_encoding = 'UTF8';
8
SET standard_conforming_strings = on;
9
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11

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

    
16
CREATE SCHEMA util;
17

    
18

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

    
23
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.
24

    
25
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.';
26

    
27

    
28
SET search_path = util, pg_catalog;
29

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

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

    
39

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

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

    
49

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

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

    
61

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

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

    
71

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

    
76
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
77
    LANGUAGE sql IMMUTABLE
78
    AS $_$
79
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
80
$_$;
81

    
82

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

    
87
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
88
    LANGUAGE sql IMMUTABLE
89
    AS $_$
90
SELECT bool_and(value)
91
FROM
92
(VALUES
93
      ($1)
94
    , ($2)
95
    , ($3)
96
    , ($4)
97
    , ($5)
98
)
99
AS v (value)
100
$_$;
101

    
102

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

    
107
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.';
108

    
109

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

    
114
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
115
    LANGUAGE sql IMMUTABLE
116
    AS $_$
117
SELECT avg(value)
118
FROM
119
(VALUES
120
      ($1)
121
    , ($2)
122
    , ($3)
123
    , ($4)
124
    , ($5)
125
)
126
AS v (value)
127
$_$;
128

    
129

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

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

    
148

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

    
153
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
154
    LANGUAGE sql IMMUTABLE
155
    AS $_$
156
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
157
FROM
158
(VALUES
159
      ($1)
160
    , ($2/60)
161
    , ($3/60/60)
162
)
163
AS v (value)
164
$_$;
165

    
166

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

    
171
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
172
    LANGUAGE sql IMMUTABLE
173
    AS $_$
174
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
175
$_$;
176

    
177

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

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

    
188

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

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

    
201

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

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

    
212

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

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

    
223

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

    
228
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
229
    LANGUAGE sql IMMUTABLE
230
    AS $_$
231
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
232
$_$;
233

    
234

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

    
239
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
240
    LANGUAGE sql IMMUTABLE
241
    AS $_$
242
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
243
$_$;
244

    
245

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

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

    
256

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

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

    
267

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

    
272
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
273
    LANGUAGE plpgsql IMMUTABLE STRICT
274
    AS $$
275
DECLARE
276
    result value%TYPE := util._map(map, value::text)::unknown;
277
BEGIN
278
    RETURN result;
279
END;
280
$$;
281

    
282

    
283
--
284
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
285
--
286

    
287
CREATE FUNCTION _map(map hstore, value text) RETURNS text
288
    LANGUAGE plpgsql IMMUTABLE STRICT
289
    AS $$
290
DECLARE
291
    match text := map -> value;
292
BEGIN
293
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
294
        match := map -> '*'; -- use default entry
295
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
296
        END IF;
297
    END IF;
298
    
299
    -- Interpret result
300
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
301
    ELSIF match = '*' THEN RETURN value;
302
    ELSE RETURN match;
303
    END IF;
304
END;
305
$$;
306

    
307

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

    
312
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
313
    LANGUAGE sql IMMUTABLE
314
    AS $_$
315
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
316
$_$;
317

    
318

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

    
323
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
324
    LANGUAGE sql IMMUTABLE
325
    AS $_$
326
SELECT util.join_strs(value, '; ')
327
FROM
328
(
329
    SELECT *
330
    FROM
331
    (
332
        SELECT
333
        DISTINCT ON (value)
334
        *
335
        FROM
336
        (VALUES
337
              (1, $1)
338
            , (2, $2)
339
            , (3, $3)
340
            , (4, $4)
341
            , (5, $5)
342
            , (6, $6)
343
            , (7, $7)
344
            , (8, $8)
345
            , (9, $9)
346
            , (10, $10)
347
        )
348
        AS v (sort_order, value)
349
        WHERE value IS NOT NULL
350
    )
351
    AS v
352
    ORDER BY sort_order
353
)
354
AS v
355
$_$;
356

    
357

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

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

    
368

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

    
373
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
374
    LANGUAGE sql IMMUTABLE
375
    AS $_$
376
SELECT util.join_strs(value, ' ')
377
FROM
378
(
379
    SELECT *
380
    FROM
381
    (
382
        SELECT
383
        DISTINCT ON (value)
384
        *
385
        FROM
386
        (VALUES
387
              (1, $1)
388
            , (2, $2)
389
            , (3, $3)
390
            , (4, $4)
391
            , (5, $5)
392
            , (6, $6)
393
            , (7, $7)
394
            , (8, $8)
395
            , (9, $9)
396
            , (10, $10)
397
        )
398
        AS v (sort_order, value)
399
        WHERE value IS NOT NULL
400
    )
401
    AS v
402
    ORDER BY sort_order
403
)
404
AS v
405
$_$;
406

    
407

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

    
412
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
413
    LANGUAGE sql IMMUTABLE
414
    AS $_$
415
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
416
$_$;
417

    
418

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

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

    
429

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

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

    
440

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

    
445
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
446
    LANGUAGE sql IMMUTABLE
447
    AS $_$
448
SELECT util."_nullIf"($1, $2, $3::util.datatype)
449
$_$;
450

    
451

    
452
--
453
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
454
--
455

    
456
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
457
    LANGUAGE plpgsql IMMUTABLE
458
    AS $$
459
DECLARE
460
    type util.datatype NOT NULL := type; -- add NOT NULL
461
BEGIN
462
    IF type = 'str' THEN RETURN nullif(value::text, "null");
463
    -- Invalid value is ignored, but invalid null value generates error
464
    ELSIF type = 'float' THEN
465
        DECLARE
466
            -- Outside the try block so that invalid null value generates error
467
            "null" double precision := "null"::double precision;
468
        BEGIN
469
            RETURN nullif(value::double precision, "null");
470
        EXCEPTION
471
            WHEN data_exception THEN RETURN value; -- ignore invalid value
472
        END;
473
    END IF;
474
END;
475
$$;
476

    
477

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

    
482
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
483
    LANGUAGE sql IMMUTABLE
484
    AS $_$
485
SELECT bool_or(value)
486
FROM
487
(VALUES
488
      ($1)
489
    , ($2)
490
    , ($3)
491
    , ($4)
492
    , ($5)
493
)
494
AS v (value)
495
$_$;
496

    
497

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

    
502
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.';
503

    
504

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

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

    
515

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

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

    
526

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

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

    
544

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

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

    
551

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

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

    
562

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

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

    
579

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

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

    
590

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

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

    
601

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

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

    
612

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

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

    
619

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

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

    
630

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

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

    
646

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

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

    
653

    
654
--
655
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
656
--
657

    
658
CREATE FUNCTION col__min(col col_ref) RETURNS integer
659
    LANGUAGE sql STABLE
660
    AS $_$
661
SELECT util.eval2val($$
662
SELECT $$||quote_ident($1.name)||$$
663
FROM $$||$1.table_||$$
664
ORDER BY $$||quote_ident($1.name)||$$ ASC
665
LIMIT 1
666
$$, NULL::integer)
667
$_$;
668

    
669

    
670
--
671
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
672
--
673

    
674
CREATE FUNCTION col_comment(col col_ref) RETURNS text
675
    LANGUAGE plpgsql STABLE STRICT
676
    AS $$
677
DECLARE
678
	comment text;
679
BEGIN
680
	SELECT description
681
	FROM pg_attribute
682
	LEFT JOIN pg_description ON objoid = attrelid
683
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
684
	WHERE attrelid = col.table_ AND attname = col.name
685
	INTO STRICT comment
686
	;
687
	RETURN comment;
688
EXCEPTION
689
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
690
END;
691
$$;
692

    
693

    
694
--
695
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
696
--
697

    
698
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
699
    LANGUAGE plpgsql STABLE STRICT
700
    AS $$
701
DECLARE
702
	default_sql text;
703
BEGIN
704
	SELECT adsrc
705
	FROM pg_attribute
706
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
707
	WHERE attrelid = col.table_ AND attname = col.name
708
	INTO STRICT default_sql
709
	;
710
	RETURN default_sql;
711
EXCEPTION
712
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
713
END;
714
$$;
715

    
716

    
717
--
718
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
719
--
720

    
721
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
722
    LANGUAGE sql STABLE
723
    AS $_$
724
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
725
$_$;
726

    
727

    
728
--
729
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
730
--
731

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

    
734

    
735
--
736
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
737
--
738

    
739
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
740
    LANGUAGE plpgsql STRICT
741
    AS $$
742
BEGIN
743
    PERFORM util.col_type(col);
744
    RETURN true;
745
EXCEPTION
746
    WHEN undefined_column THEN RETURN false;
747
END;
748
$$;
749

    
750

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

    
755
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
756
    LANGUAGE plpgsql STABLE STRICT
757
    AS $$
758
DECLARE
759
    prefix text := util.name(type)||'.';
760
BEGIN
761
    RETURN QUERY
762
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
763
        FROM util.col_names(type) f (name_);
764
END;
765
$$;
766

    
767

    
768
--
769
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
770
--
771

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

    
781

    
782
--
783
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
784
--
785

    
786
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
787
    LANGUAGE plpgsql STABLE STRICT
788
    AS $_$
789
BEGIN
790
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
791
END;
792
$_$;
793

    
794

    
795
--
796
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
797
--
798

    
799
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
800
    LANGUAGE plpgsql STABLE STRICT
801
    AS $$
802
DECLARE
803
    type regtype;
804
BEGIN
805
    SELECT atttypid FROM pg_attribute
806
    WHERE attrelid = col.table_ AND attname = col.name
807
    INTO STRICT type
808
    ;
809
    RETURN type;
810
EXCEPTION
811
    WHEN no_data_found THEN
812
        RAISE undefined_column USING MESSAGE =
813
            concat('undefined column: ', col.name);
814
END;
815
$$;
816

    
817

    
818
--
819
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
820
--
821

    
822
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
823
    LANGUAGE sql IMMUTABLE
824
    AS $_$
825
SELECT util.esc_name__append($2, $1)
826
$_$;
827

    
828

    
829
--
830
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
831
--
832

    
833
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
834
    LANGUAGE sql IMMUTABLE
835
    AS $_$
836
SELECT position($1 in $2) > 0 /*1-based offset*/
837
$_$;
838

    
839

    
840
--
841
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
842
--
843

    
844
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
845
    LANGUAGE plpgsql STRICT
846
    AS $$
847
BEGIN
848
    PERFORM util.eval(sql);
849
EXCEPTION
850
    WHEN duplicate_table  THEN NULL;
851
    WHEN duplicate_object THEN NULL; -- e.g. constraint
852
    WHEN duplicate_column THEN NULL;
853
    WHEN invalid_table_definition THEN
854
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
855
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
856
        END IF;
857
END;
858
$$;
859

    
860

    
861
--
862
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
863
--
864

    
865
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
866

    
867

    
868
--
869
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
870
--
871

    
872
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
873
    LANGUAGE sql STABLE STRICT
874
    AS $_$
875
SELECT util.eval2set($$
876
SELECT col
877
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
878
LEFT JOIN $$||$2||$$ ON "to" = col
879
WHERE "from" IS NULL
880
$$, NULL::text)
881
$_$;
882

    
883

    
884
--
885
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
886
--
887

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

    
890

    
891
--
892
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
893
--
894

    
895
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
896
    LANGUAGE sql STRICT
897
    AS $_$
898
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
899
$_$;
900

    
901

    
902
--
903
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
904
--
905

    
906
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
907

    
908

    
909
--
910
-- Name: drop_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
911
--
912

    
913
CREATE FUNCTION drop_column(col col_ref) RETURNS void
914
    LANGUAGE sql STRICT
915
    AS $_$
916
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
917
quote_ident($1.name))
918
$_$;
919

    
920

    
921
--
922
-- Name: FUNCTION drop_column(col col_ref); Type: COMMENT; Schema: util; Owner: -
923
--
924

    
925
COMMENT ON FUNCTION drop_column(col col_ref) IS 'idempotent';
926

    
927

    
928
--
929
-- Name: drop_table(text); Type: FUNCTION; Schema: util; Owner: -
930
--
931

    
932
CREATE FUNCTION drop_table(table_ text) RETURNS void
933
    LANGUAGE sql STRICT
934
    AS $_$
935
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
936
$_$;
937

    
938

    
939
--
940
-- Name: FUNCTION drop_table(table_ text); Type: COMMENT; Schema: util; Owner: -
941
--
942

    
943
COMMENT ON FUNCTION drop_table(table_ text) IS 'idempotent';
944

    
945

    
946
--
947
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
948
--
949

    
950
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
951
    LANGUAGE sql IMMUTABLE
952
    AS $_$
953
SELECT util.array_fill($1, 0)
954
$_$;
955

    
956

    
957
--
958
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
959
--
960

    
961
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)';
962

    
963

    
964
--
965
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
966
--
967

    
968
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
969
    LANGUAGE sql IMMUTABLE
970
    AS $_$
971
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
972
$_$;
973

    
974

    
975
--
976
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
977
--
978

    
979
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
980
    LANGUAGE sql IMMUTABLE
981
    AS $_$
982
SELECT regexp_replace($2, '("?)$', $1||'\1')
983
$_$;
984

    
985

    
986
--
987
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
988
--
989

    
990
CREATE FUNCTION eval(sql text) RETURNS void
991
    LANGUAGE plpgsql STRICT
992
    AS $$
993
BEGIN
994
    RAISE NOTICE '%', sql;
995
    EXECUTE sql;
996
END;
997
$$;
998

    
999

    
1000
--
1001
-- Name: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1002
--
1003

    
1004
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS SETOF anyelement
1005
    LANGUAGE plpgsql
1006
    AS $$
1007
BEGIN
1008
	RAISE NOTICE '%', sql;
1009
	RETURN QUERY EXECUTE sql;
1010
END;
1011
$$;
1012

    
1013

    
1014
--
1015
-- Name: FUNCTION eval2set(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1016
--
1017

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

    
1020

    
1021
--
1022
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1023
--
1024

    
1025
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1026
    LANGUAGE plpgsql
1027
    AS $$
1028
DECLARE
1029
	ret_val ret_type_null%TYPE;
1030
BEGIN
1031
	RAISE NOTICE '%', sql;
1032
	EXECUTE sql INTO STRICT ret_val;
1033
	RETURN ret_val;
1034
END;
1035
$$;
1036

    
1037

    
1038
--
1039
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1040
--
1041

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

    
1044

    
1045
--
1046
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1047
--
1048

    
1049
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1050
    LANGUAGE sql
1051
    AS $_$
1052
SELECT util.eval2val($$SELECT $$||$1, $2)
1053
$_$;
1054

    
1055

    
1056
--
1057
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1058
--
1059

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

    
1062

    
1063
--
1064
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1065
--
1066

    
1067
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1068
    LANGUAGE sql
1069
    AS $_$
1070
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1071
$_$;
1072

    
1073

    
1074
--
1075
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1076
--
1077

    
1078
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS 'sql: can be NULL, which will be passed through
1079
ret_type_null: NULL::ret_type';
1080

    
1081

    
1082
--
1083
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1084
--
1085

    
1086
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1087
    LANGUAGE sql STABLE STRICT
1088
    AS $_$
1089
SELECT col_name
1090
FROM unnest($2) s (col_name)
1091
WHERE util.col_exists(($1, col_name))
1092
$_$;
1093

    
1094

    
1095
--
1096
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1097
--
1098

    
1099
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1100
    LANGUAGE sql IMMUTABLE
1101
    AS $_$
1102
-- STRICT handles NULLs, so that the array will always be a value
1103
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1104
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1105
) END
1106
$_$;
1107

    
1108

    
1109
--
1110
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1111
--
1112

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

    
1115

    
1116
--
1117
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1118
--
1119

    
1120
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1121
    LANGUAGE plpgsql STRICT
1122
    AS $_$
1123
DECLARE
1124
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1125
$$||query;
1126
BEGIN
1127
	EXECUTE mk_view;
1128
EXCEPTION
1129
WHEN invalid_table_definition THEN
1130
	IF SQLERRM = 'cannot drop columns from view'
1131
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1132
	THEN
1133
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1134
		EXECUTE mk_view;
1135
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1136
	END IF;
1137
END;
1138
$_$;
1139

    
1140

    
1141
--
1142
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1143
--
1144

    
1145
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
1146

    
1147

    
1148
--
1149
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1150
--
1151

    
1152
CREATE FUNCTION grants_users() RETURNS SETOF text
1153
    LANGUAGE sql IMMUTABLE
1154
    AS $$
1155
VALUES ('bien_read'), ('public_')
1156
$$;
1157

    
1158

    
1159
--
1160
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1161
--
1162

    
1163
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1164
    LANGUAGE sql IMMUTABLE
1165
    AS $_$
1166
SELECT substring($2 for length($1)) = $1
1167
$_$;
1168

    
1169

    
1170
--
1171
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1172
--
1173

    
1174
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1175
    LANGUAGE sql IMMUTABLE
1176
    AS $_$
1177
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1178
$_$;
1179

    
1180

    
1181
--
1182
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1183
--
1184

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

    
1187

    
1188
--
1189
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1190
--
1191

    
1192
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1193
    LANGUAGE sql STABLE STRICT
1194
    AS $_$
1195
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1196
$_$;
1197

    
1198

    
1199
--
1200
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1201
--
1202

    
1203
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
1204
    LANGUAGE sql IMMUTABLE
1205
    AS $_$
1206
SELECT util.array_length($1) = 0
1207
$_$;
1208

    
1209

    
1210
--
1211
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1212
--
1213

    
1214
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1215
    LANGUAGE sql IMMUTABLE
1216
    AS $_$
1217
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1218
$_$;
1219

    
1220

    
1221
--
1222
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1223
--
1224

    
1225
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1226
    LANGUAGE sql IMMUTABLE
1227
    AS $_$
1228
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1229
$_$;
1230

    
1231

    
1232
--
1233
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1234
--
1235

    
1236
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1237
    LANGUAGE sql IMMUTABLE STRICT
1238
    AS $_$
1239
SELECT $1 || $3 || $2
1240
$_$;
1241

    
1242

    
1243
--
1244
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
1245
--
1246

    
1247
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
1248
    LANGUAGE sql IMMUTABLE
1249
    AS $_$
1250
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
1251
$_$;
1252

    
1253

    
1254
--
1255
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1256
--
1257

    
1258
CREATE FUNCTION map_filter_insert() RETURNS trigger
1259
    LANGUAGE plpgsql
1260
    AS $$
1261
BEGIN
1262
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1263
	RETURN new;
1264
END;
1265
$$;
1266

    
1267

    
1268
--
1269
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1270
--
1271

    
1272
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1273
    LANGUAGE plpgsql STABLE STRICT
1274
    AS $_$
1275
DECLARE
1276
    value text;
1277
BEGIN
1278
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1279
        INTO value USING key;
1280
    RETURN value;
1281
END;
1282
$_$;
1283

    
1284

    
1285
--
1286
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1287
--
1288

    
1289
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1290
    LANGUAGE sql IMMUTABLE
1291
    AS $_$
1292
SELECT util._map(util.nulls_map($1), $2)
1293
$_$;
1294

    
1295

    
1296
--
1297
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1298
--
1299

    
1300
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].
1301

    
1302
[1] inlining of function calls, which is different from constant folding
1303
[2] _map()''s profiling query
1304
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1305
and map_nulls()''s profiling query
1306
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1307
both take ~920 ms.
1308
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.';
1309

    
1310

    
1311
--
1312
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1313
--
1314

    
1315
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1316
    LANGUAGE plpgsql STABLE STRICT
1317
    AS $_$
1318
BEGIN
1319
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1320
END;
1321
$_$;
1322

    
1323

    
1324
--
1325
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1326
--
1327

    
1328
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1329
    LANGUAGE sql STRICT
1330
    AS $_$
1331
SELECT util.create_if_not_exists($$
1332
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1333
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1334
||quote_literal($2)||$$;
1335
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS 'constant';
1336
$$)
1337
$_$;
1338

    
1339

    
1340
--
1341
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1342
--
1343

    
1344
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
1345

    
1346

    
1347
--
1348
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1349
--
1350

    
1351
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1352
    LANGUAGE plpgsql STRICT
1353
    AS $_$
1354
DECLARE
1355
    type regtype = util.typeof(expr, col.table_::text::regtype);
1356
    col_name_sql text = quote_ident(col.name);
1357
BEGIN
1358
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1359
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1360
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1361
$$||expr||$$;
1362
$$);
1363
END;
1364
$_$;
1365

    
1366

    
1367
--
1368
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1369
--
1370

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

    
1373

    
1374
--
1375
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1376
--
1377

    
1378
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1379
    LANGUAGE sql STRICT
1380
    AS $_$
1381
SELECT util.create_if_not_exists($$
1382
CREATE TABLE $$||$1||$$
1383
(
1384
    LIKE util.map INCLUDING ALL
1385
);
1386

    
1387
CREATE TRIGGER map_filter_insert
1388
  BEFORE INSERT
1389
  ON $$||$1||$$
1390
  FOR EACH ROW
1391
  EXECUTE PROCEDURE util.map_filter_insert();
1392
$$)
1393
$_$;
1394

    
1395

    
1396
--
1397
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1398
--
1399

    
1400
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1401
    LANGUAGE sql STRICT
1402
    AS $_$
1403
SELECT util.mk_const_col(($1, 'source'), util.table_schema($1))
1404
$_$;
1405

    
1406

    
1407
--
1408
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1409
--
1410

    
1411
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS 'idempotent';
1412

    
1413

    
1414
--
1415
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
1416
--
1417

    
1418
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
1419
    LANGUAGE plpgsql STRICT
1420
    AS $_$
1421
DECLARE
1422
	view_qual_name text = util.qual_name(view_);
1423
BEGIN
1424
	EXECUTE $$
1425
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1426
  RETURNS SETOF $$||view_||$$ AS
1427
$BODY1$
1428
SELECT * FROM $$||view_qual_name||$$
1429
ORDER BY sort_col
1430
LIMIT $1 OFFSET $2
1431
$BODY1$
1432
  LANGUAGE sql STABLE
1433
  COST 100
1434
  ROWS 1000
1435
$$;
1436
	
1437
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
1438
END;
1439
$_$;
1440

    
1441

    
1442
--
1443
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1444
--
1445

    
1446
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1447
    LANGUAGE plpgsql STRICT
1448
    AS $_$
1449
DECLARE
1450
	view_qual_name text = util.qual_name(view_);
1451
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
1452
BEGIN
1453
	EXECUTE $$
1454
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
1455
  RETURNS integer AS
1456
$BODY1$
1457
SELECT $$||quote_ident(row_num_col)||$$
1458
FROM $$||view_qual_name||$$
1459
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
1460
LIMIT 1
1461
$BODY1$
1462
  LANGUAGE sql STABLE
1463
  COST 100;
1464
$$;
1465
	
1466
	EXECUTE $$
1467
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1468
  RETURNS SETOF $$||view_||$$ AS
1469
$BODY1$
1470
SELECT * FROM $$||view_qual_name||$$
1471
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
1472
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
1473
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
1474
ORDER BY $$||quote_ident(row_num_col)||$$
1475
$BODY1$
1476
  LANGUAGE sql STABLE
1477
  COST 100
1478
  ROWS 1000
1479
$$;
1480
	
1481
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
1482
END;
1483
$_$;
1484

    
1485

    
1486
--
1487
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
1488
--
1489

    
1490
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
1491
    LANGUAGE plpgsql STRICT
1492
    AS $_$
1493
DECLARE
1494
	view_qual_name text = util.qual_name(view_);
1495
BEGIN
1496
	EXECUTE $$
1497
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1498
  RETURNS SETOF $$||view_||$$
1499
  SET enable_sort TO 'off'
1500
  AS
1501
$BODY1$
1502
SELECT * FROM $$||view_qual_name||$$($2, $3)
1503
$BODY1$
1504
  LANGUAGE sql STABLE
1505
  COST 100
1506
  ROWS 1000
1507
;
1508
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1509
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1510
If you want to run EXPLAIN and get expanded output, use the regular subset
1511
function instead. (When a config param is set on a function, EXPLAIN produces
1512
just a function scan.)
1513
';
1514
$$;
1515
END;
1516
$_$;
1517

    
1518

    
1519
--
1520
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
1521
--
1522

    
1523
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS 'creates subset function which turns off enable_sort';
1524

    
1525

    
1526
--
1527
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1528
--
1529

    
1530
CREATE FUNCTION name(type regtype) RETURNS text
1531
    LANGUAGE sql STABLE STRICT
1532
    AS $_$
1533
SELECT typname::text FROM pg_type WHERE oid = $1
1534
$_$;
1535

    
1536

    
1537
--
1538
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1539
--
1540

    
1541
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
1542
    LANGUAGE sql IMMUTABLE
1543
    AS $_$
1544
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
1545
$_$;
1546

    
1547

    
1548
--
1549
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
1550
--
1551

    
1552
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
1553
    LANGUAGE sql IMMUTABLE
1554
    AS $_$
1555
SELECT $1 IS NOT NULL
1556
$_$;
1557

    
1558

    
1559
--
1560
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
1561
--
1562

    
1563
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
1564
    LANGUAGE sql IMMUTABLE
1565
    AS $_$
1566
SELECT util.hstore($1, NULL) || '*=>*'
1567
$_$;
1568

    
1569

    
1570
--
1571
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
1572
--
1573

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

    
1576

    
1577
--
1578
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
1579
--
1580

    
1581
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
1582
    LANGUAGE sql IMMUTABLE
1583
    AS $_$
1584
SELECT $2 + COALESCE($1, 0)
1585
$_$;
1586

    
1587

    
1588
--
1589
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
1590
--
1591

    
1592
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
1593
    LANGUAGE sql STABLE
1594
    AS $_$
1595
SELECT util.type_qual_name($1::text::regtype)
1596
$_$;
1597

    
1598

    
1599
--
1600
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
1601
--
1602

    
1603
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
1604
    LANGUAGE plpgsql IMMUTABLE STRICT
1605
    AS $$
1606
BEGIN
1607
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
1608
END;
1609
$$;
1610

    
1611

    
1612
--
1613
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
1614
--
1615

    
1616
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
1617
    LANGUAGE sql IMMUTABLE
1618
    AS $_$
1619
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
1620
$_$;
1621

    
1622

    
1623
--
1624
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1625
--
1626

    
1627
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
1628
    LANGUAGE sql STRICT
1629
    AS $_$
1630
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1631
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
1632
FROM util.col_names($1::text::regtype) f (name);
1633
SELECT NULL::void; -- don't fold away functions called in previous query
1634
$_$;
1635

    
1636

    
1637
--
1638
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
1639
--
1640

    
1641
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
1642

    
1643

    
1644
--
1645
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1646
--
1647

    
1648
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
1649
    LANGUAGE sql STRICT
1650
    AS $_$
1651
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
1652
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
1653
SELECT util.set_col_names($1, $2);
1654
$_$;
1655

    
1656

    
1657
--
1658
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1659
--
1660

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

    
1664

    
1665
--
1666
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1667
--
1668

    
1669
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
1670
    LANGUAGE sql STRICT
1671
    AS $_$
1672
SELECT util.drop_table($1);
1673
SELECT util.mk_map_table($1);
1674
$_$;
1675

    
1676

    
1677
--
1678
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
1679
--
1680

    
1681
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
1682
    LANGUAGE plpgsql STRICT
1683
    AS $_$
1684
DECLARE
1685
	result text = NULL;
1686
BEGIN
1687
	BEGIN
1688
		result = util.show_create_view(view_);
1689
		PERFORM util.eval($$DROP VIEW $$||view_);
1690
	EXCEPTION
1691
		WHEN undefined_table THEN NULL;
1692
	END;
1693
	RETURN result;
1694
END;
1695
$_$;
1696

    
1697

    
1698
--
1699
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
1700
--
1701

    
1702
CREATE FUNCTION save_drop_views(views text[]) RETURNS text
1703
    LANGUAGE sql
1704
    AS $_$
1705
SELECT concat(util.save_drop_view(unnest)) FROM unnest($1)
1706
$_$;
1707

    
1708

    
1709
--
1710
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
1711
--
1712

    
1713
CREATE FUNCTION schema(type regtype) RETURNS text
1714
    LANGUAGE sql STABLE
1715
    AS $_$
1716
SELECT nspname::text
1717
FROM pg_type
1718
JOIN pg_namespace ON pg_namespace.oid = typnamespace
1719
WHERE pg_type.oid = $1
1720
$_$;
1721

    
1722

    
1723
--
1724
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
1725
--
1726

    
1727
CREATE FUNCTION schema(type_null anyelement) RETURNS text
1728
    LANGUAGE sql STABLE
1729
    AS $_$
1730
SELECT util.schema(pg_typeof($1))
1731
$_$;
1732

    
1733

    
1734
--
1735
-- Name: schema_ident(anyelement); Type: FUNCTION; Schema: util; Owner: -
1736
--
1737

    
1738
CREATE FUNCTION schema_ident(type_null anyelement) RETURNS text
1739
    LANGUAGE sql STABLE
1740
    AS $_$
1741
SELECT quote_ident(util.schema($1))
1742
$_$;
1743

    
1744

    
1745
--
1746
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
1747
--
1748

    
1749
CREATE FUNCTION search_path_append(schemas text) RETURNS void
1750
    LANGUAGE sql STRICT
1751
    AS $_$
1752
SELECT util.eval(
1753
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
1754
$_$;
1755

    
1756

    
1757
--
1758
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1759
--
1760

    
1761
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
1762
    LANGUAGE plpgsql STRICT
1763
    AS $_$
1764
DECLARE
1765
    old text[] = ARRAY(SELECT util.col_names(table_));
1766
    new text[] = ARRAY(SELECT util.map_values(names));
1767
BEGIN
1768
    old = old[1:array_length(new, 1)]; -- truncate to same length
1769
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
1770
||$$ TO $$||quote_ident(value))
1771
    FROM each(hstore(old, new))
1772
    WHERE value != key -- not same name
1773
    ;
1774
END;
1775
$_$;
1776

    
1777

    
1778
--
1779
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1780
--
1781

    
1782
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
1783

    
1784

    
1785
--
1786
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1787
--
1788

    
1789
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
1790
    LANGUAGE plpgsql STRICT
1791
    AS $_$
1792
DECLARE
1793
	row_ util.map;
1794
BEGIN
1795
	-- rename any metadata cols rather than re-adding them with new names
1796
	BEGIN
1797
		PERFORM util.set_col_names(table_, names);
1798
	EXCEPTION
1799
		WHEN array_subscript_error THEN -- selective suppress
1800
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
1801
				-- metadata cols not yet added
1802
			ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
1803
			END IF;
1804
	END;
1805
	
1806
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
1807
	LOOP
1808
		PERFORM util.mk_const_col((table_, row_."to"),
1809
			substring(row_."from" from 2));
1810
	END LOOP;
1811
	
1812
	PERFORM util.set_col_names(table_, names);
1813
END;
1814
$_$;
1815

    
1816

    
1817
--
1818
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1819
--
1820

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

    
1824

    
1825
--
1826
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
1827
--
1828

    
1829
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
1830
    LANGUAGE plpgsql STRICT
1831
    AS $_$
1832
DECLARE
1833
    sql text = $$ALTER TABLE $$||table_||$$
1834
$$||NULLIF(array_to_string(ARRAY(
1835
    SELECT
1836
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
1837
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1838
    FROM
1839
    (
1840
        SELECT
1841
          quote_ident(col_name) AS col_name_sql
1842
        , util.col_type((table_, col_name)) AS curr_type
1843
        , type AS target_type
1844
        FROM unnest(col_casts)
1845
    ) s
1846
    WHERE curr_type != target_type
1847
), '
1848
, '), '');
1849
BEGIN
1850
    RAISE NOTICE '%', sql;
1851
    EXECUTE COALESCE(sql, '');
1852
END;
1853
$_$;
1854

    
1855

    
1856
--
1857
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1858
--
1859

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

    
1862

    
1863
--
1864
-- Name: show_create_view(regclass); Type: FUNCTION; Schema: util; Owner: -
1865
--
1866

    
1867
CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
1868
    LANGUAGE sql STABLE
1869
    AS $_$
1870
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
1871
$$||util.show_grants_for($1)
1872
$_$;
1873

    
1874

    
1875
--
1876
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
1877
--
1878

    
1879
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
1880
    LANGUAGE sql STABLE
1881
    AS $_$
1882
SELECT concat(cmd)
1883
FROM
1884
(
1885
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
1886
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
1887
$$ ELSE '' END) AS cmd
1888
	FROM util.grants_users() f (user_)
1889
) s
1890
$_$;
1891

    
1892

    
1893
--
1894
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1895
--
1896

    
1897
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1898
    LANGUAGE plpgsql STABLE STRICT
1899
    AS $_$
1900
DECLARE
1901
    hstore hstore;
1902
BEGIN
1903
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1904
        table_||$$))$$ INTO STRICT hstore;
1905
    RETURN hstore;
1906
END;
1907
$_$;
1908

    
1909

    
1910
--
1911
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1912
--
1913

    
1914
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
1915
    LANGUAGE sql STABLE STRICT
1916
    AS $_$
1917
SELECT COUNT(*) > 0 FROM pg_constraint
1918
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
1919
$_$;
1920

    
1921

    
1922
--
1923
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1924
--
1925

    
1926
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';
1927

    
1928

    
1929
--
1930
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1931
--
1932

    
1933
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
1934
    LANGUAGE sql STRICT
1935
    AS $_$
1936
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
1937
||quote_ident($2)||$$ CHECK (true)$$)
1938
$_$;
1939

    
1940

    
1941
--
1942
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1943
--
1944

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

    
1948

    
1949
--
1950
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
1951
--
1952

    
1953
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
1954
    LANGUAGE sql STABLE STRICT
1955
    AS $_$
1956
SELECT util.table_flag__get($1, 'nulls_mapped')
1957
$_$;
1958

    
1959

    
1960
--
1961
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1962
--
1963

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

    
1966

    
1967
--
1968
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
1969
--
1970

    
1971
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
1972
    LANGUAGE sql STRICT
1973
    AS $_$
1974
SELECT util.table_flag__set($1, 'nulls_mapped')
1975
$_$;
1976

    
1977

    
1978
--
1979
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1980
--
1981

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

    
1985

    
1986
--
1987
-- Name: table_schema(regclass); Type: FUNCTION; Schema: util; Owner: -
1988
--
1989

    
1990
CREATE FUNCTION table_schema(table_ regclass) RETURNS text
1991
    LANGUAGE sql STABLE STRICT
1992
    AS $_$
1993
SELECT nspname::text FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = $1)
1994
$_$;
1995

    
1996

    
1997
--
1998
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1999
--
2000

    
2001
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
2002
    LANGUAGE plpgsql STRICT
2003
    AS $_$
2004
DECLARE
2005
    row record;
2006
BEGIN
2007
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
2008
    LOOP
2009
        IF row.global_name != row.name THEN
2010
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
2011
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
2012
        END IF;
2013
    END LOOP;
2014
END;
2015
$_$;
2016

    
2017

    
2018
--
2019
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2020
--
2021

    
2022
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
2023

    
2024

    
2025
--
2026
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2027
--
2028

    
2029
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
2030
    LANGUAGE sql STRICT
2031
    AS $_$
2032
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
2033
SELECT NULL::void; -- don't fold away functions called in previous query
2034
$_$;
2035

    
2036

    
2037
--
2038
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2039
--
2040

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

    
2044

    
2045
--
2046
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
2047
--
2048

    
2049
CREATE FUNCTION truncate(table_ regclass) RETURNS void
2050
    LANGUAGE plpgsql STRICT
2051
    AS $_$
2052
BEGIN
2053
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
2054
END;
2055
$_$;
2056

    
2057

    
2058
--
2059
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2060
--
2061

    
2062
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
2063

    
2064

    
2065
--
2066
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
2067
--
2068

    
2069
CREATE FUNCTION try_create(sql text) RETURNS void
2070
    LANGUAGE plpgsql STRICT
2071
    AS $$
2072
BEGIN
2073
    PERFORM util.eval(sql);
2074
EXCEPTION
2075
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
2076
    WHEN undefined_column THEN NULL;
2077
    WHEN duplicate_column THEN NULL;
2078
END;
2079
$$;
2080

    
2081

    
2082
--
2083
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
2084
--
2085

    
2086
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
2087

    
2088

    
2089
--
2090
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
2091
--
2092

    
2093
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
2094
    LANGUAGE sql STRICT
2095
    AS $_$
2096
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
2097
$_$;
2098

    
2099

    
2100
--
2101
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
2102
--
2103

    
2104
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
2105

    
2106

    
2107
--
2108
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
2109
--
2110

    
2111
CREATE FUNCTION type_qual(value anyelement) RETURNS text
2112
    LANGUAGE sql IMMUTABLE
2113
    AS $_$
2114
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
2115
$_$;
2116

    
2117

    
2118
--
2119
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
2120
--
2121

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

    
2124

    
2125
--
2126
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2127
--
2128

    
2129
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
2130
    LANGUAGE sql STABLE STRICT
2131
    SET search_path TO pg_temp
2132
    AS $_$
2133
SELECT $1::text
2134
$_$;
2135

    
2136

    
2137
--
2138
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2139
--
2140

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

    
2143

    
2144
--
2145
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
2146
--
2147

    
2148
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
2149
    LANGUAGE plpgsql STABLE
2150
    AS $_$
2151
DECLARE
2152
    type regtype;
2153
BEGIN
2154
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
2155
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
2156
    RETURN type;
2157
END;
2158
$_$;
2159

    
2160

    
2161
--
2162
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
2163
--
2164

    
2165
CREATE AGGREGATE all_same(anyelement) (
2166
    SFUNC = all_same_transform,
2167
    STYPE = anyarray,
2168
    FINALFUNC = all_same_final
2169
);
2170

    
2171

    
2172
--
2173
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
2174
--
2175

    
2176
COMMENT ON AGGREGATE all_same(anyelement) IS 'includes NULLs in comparison';
2177

    
2178

    
2179
--
2180
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
2181
--
2182

    
2183
CREATE AGGREGATE join_strs(text, text) (
2184
    SFUNC = join_strs_transform,
2185
    STYPE = text
2186
);
2187

    
2188

    
2189
--
2190
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
2191
--
2192

    
2193
CREATE OPERATOR -> (
2194
    PROCEDURE = map_get,
2195
    LEFTARG = regclass,
2196
    RIGHTARG = text
2197
);
2198

    
2199

    
2200
--
2201
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
2202
--
2203

    
2204
CREATE OPERATOR => (
2205
    PROCEDURE = hstore,
2206
    LEFTARG = text[],
2207
    RIGHTARG = text
2208
);
2209

    
2210

    
2211
--
2212
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
2213
--
2214

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

    
2217

    
2218
--
2219
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
2220
--
2221

    
2222
CREATE OPERATOR ?*>= (
2223
    PROCEDURE = is_populated_more_often_than,
2224
    LEFTARG = anyelement,
2225
    RIGHTARG = anyelement
2226
);
2227

    
2228

    
2229
--
2230
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
2231
--
2232

    
2233
CREATE OPERATOR ?>= (
2234
    PROCEDURE = is_more_complete_than,
2235
    LEFTARG = anyelement,
2236
    RIGHTARG = anyelement
2237
);
2238

    
2239

    
2240
--
2241
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
2242
--
2243

    
2244
CREATE OPERATOR ||% (
2245
    PROCEDURE = concat_esc,
2246
    LEFTARG = text,
2247
    RIGHTARG = text
2248
);
2249

    
2250

    
2251
--
2252
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
2253
--
2254

    
2255
COMMENT ON OPERATOR ||% (text, text) IS '% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers';
2256

    
2257

    
2258
SET default_tablespace = '';
2259

    
2260
SET default_with_oids = false;
2261

    
2262
--
2263
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
2264
--
2265

    
2266
CREATE TABLE map (
2267
    "from" text NOT NULL,
2268
    "to" text,
2269
    filter text,
2270
    notes text
2271
);
2272

    
2273

    
2274
--
2275
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
2276
--
2277

    
2278

    
2279

    
2280
--
2281
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
2282
--
2283

    
2284
ALTER TABLE ONLY map
2285
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
2286

    
2287

    
2288
--
2289
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
2290
--
2291

    
2292
ALTER TABLE ONLY map
2293
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
2294

    
2295

    
2296
--
2297
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
2298
--
2299

    
2300
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
2301

    
2302

    
2303
--
2304
-- PostgreSQL database dump complete
2305
--
2306

    
(19-19/29)