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 '
24
IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
25

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

    
29

    
30
SET search_path = util, pg_catalog;
31

    
32
--
33
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
34
--
35

    
36
CREATE TYPE col_cast AS (
37
	col_name text,
38
	type regtype
39
);
40

    
41

    
42
--
43
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
44
--
45

    
46
CREATE TYPE col_ref AS (
47
	table_ regclass,
48
	name text
49
);
50

    
51

    
52
--
53
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
54
--
55

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

    
63

    
64
--
65
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
66
--
67

    
68
CREATE TYPE datatype AS ENUM (
69
    'str',
70
    'float'
71
);
72

    
73

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

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

    
84

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

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

    
104

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

    
109
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
110
_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.
111
';
112

    
113

    
114
--
115
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
116
--
117

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

    
133

    
134
--
135
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
136
--
137

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

    
152

    
153
--
154
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
155
--
156

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

    
170

    
171
--
172
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
173
--
174

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

    
181

    
182
--
183
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
184
--
185

    
186
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
187
    LANGUAGE sql IMMUTABLE
188
    AS $_$
189
SELECT $1 = $2
190
$_$;
191

    
192

    
193
--
194
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
195
--
196

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

    
205

    
206
--
207
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
208
--
209

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

    
216

    
217
--
218
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
219
--
220

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

    
227

    
228
--
229
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
230
--
231

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

    
238

    
239
--
240
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
241
--
242

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

    
249

    
250
--
251
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
252
--
253

    
254
CREATE FUNCTION _label(label text, value text) RETURNS text
255
    LANGUAGE sql IMMUTABLE
256
    AS $_$
257
SELECT coalesce($1 || ': ', '') || $2
258
$_$;
259

    
260

    
261
--
262
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
263
--
264

    
265
CREATE FUNCTION _lowercase(value text) RETURNS text
266
    LANGUAGE sql IMMUTABLE
267
    AS $_$
268
SELECT lower($1)
269
$_$;
270

    
271

    
272
--
273
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
274
--
275

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

    
286

    
287
--
288
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
289
--
290

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

    
311

    
312
--
313
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
314
--
315

    
316
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
317
    LANGUAGE sql IMMUTABLE
318
    AS $_$
319
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
320
$_$;
321

    
322

    
323
--
324
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
325
--
326

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

    
361

    
362
--
363
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
364
--
365

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

    
372

    
373
--
374
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
375
--
376

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

    
411

    
412
--
413
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
414
--
415

    
416
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
417
    LANGUAGE sql IMMUTABLE
418
    AS $_$
419
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
420
$_$;
421

    
422

    
423
--
424
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
425
--
426

    
427
CREATE FUNCTION _not(value boolean) RETURNS boolean
428
    LANGUAGE sql IMMUTABLE
429
    AS $_$
430
SELECT NOT $1
431
$_$;
432

    
433

    
434
--
435
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
436
--
437

    
438
CREATE FUNCTION _now() RETURNS timestamp with time zone
439
    LANGUAGE sql STABLE
440
    AS $$
441
SELECT now()
442
$$;
443

    
444

    
445
--
446
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
447
--
448

    
449
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
450
    LANGUAGE sql IMMUTABLE
451
    AS $_$
452
SELECT util."_nullIf"($1, $2, $3::util.datatype)
453
$_$;
454

    
455

    
456
--
457
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
458
--
459

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

    
481

    
482
--
483
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
484
--
485

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

    
501

    
502
--
503
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
504
--
505

    
506
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
507
_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.
508
';
509

    
510

    
511
--
512
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
513
--
514

    
515
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
516
    LANGUAGE sql IMMUTABLE
517
    AS $_$
518
SELECT $2 - $1
519
$_$;
520

    
521

    
522
--
523
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
524
--
525

    
526
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
527
    LANGUAGE sql IMMUTABLE
528
    AS $_$
529
SELECT regexp_split_to_table($1, $2)
530
$_$;
531

    
532

    
533
--
534
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
535
--
536

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

    
550

    
551
--
552
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
553
--
554

    
555
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
556
gets table_''s added columns (all the columns not in the original data)
557
';
558

    
559

    
560
--
561
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
562
--
563

    
564
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
565
    LANGUAGE sql IMMUTABLE
566
    AS $_$
567
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
568
$_$;
569

    
570

    
571
--
572
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
573
--
574

    
575
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
576
    LANGUAGE plpgsql IMMUTABLE
577
    AS $$
578
DECLARE
579
	value_cmp         state%TYPE = ARRAY[value];
580
	state             state%TYPE = COALESCE(state, value_cmp);
581
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
582
BEGIN
583
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
584
END;
585
$$;
586

    
587

    
588
--
589
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
590
--
591

    
592
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
593
    LANGUAGE sql IMMUTABLE
594
    AS $_$
595
SELECT pg_catalog.array_fill($1, ARRAY[$2])
596
$_$;
597

    
598

    
599
--
600
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
601
--
602

    
603
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
604
    LANGUAGE sql IMMUTABLE
605
    AS $_$
606
SELECT util.array_length($1, 1)
607
$_$;
608

    
609

    
610
--
611
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
612
--
613

    
614
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
615
    LANGUAGE sql IMMUTABLE
616
    AS $_$
617
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
618
$_$;
619

    
620

    
621
--
622
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
623
--
624

    
625
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
626
returns 0 instead of NULL for empty arrays
627
';
628

    
629

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

    
634
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
635
    LANGUAGE sql STABLE STRICT
636
    AS $_$
637
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
638
$_$;
639

    
640

    
641
--
642
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
643
--
644

    
645
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
646
    LANGUAGE plpgsql STRICT
647
    AS $_$
648
BEGIN
649
    -- not yet clustered (ARRAY[] compares NULLs literally)
650
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
651
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
652
    END IF;
653
END;
654
$_$;
655

    
656

    
657
--
658
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
659
--
660

    
661
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
662
idempotent
663
';
664

    
665

    
666
--
667
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
668
--
669

    
670
CREATE FUNCTION col__min(col col_ref) RETURNS integer
671
    LANGUAGE sql STABLE
672
    AS $_$
673
SELECT util.eval2val($$
674
SELECT $$||quote_ident($1.name)||$$
675
FROM $$||$1.table_||$$
676
ORDER BY $$||quote_ident($1.name)||$$ ASC
677
LIMIT 1
678
$$, NULL::integer)
679
$_$;
680

    
681

    
682
--
683
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
684
--
685

    
686
CREATE FUNCTION col_comment(col col_ref) RETURNS text
687
    LANGUAGE plpgsql STABLE STRICT
688
    AS $$
689
DECLARE
690
	comment text;
691
BEGIN
692
	SELECT description
693
	FROM pg_attribute
694
	LEFT JOIN pg_description ON objoid = attrelid
695
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
696
	WHERE attrelid = col.table_ AND attname = col.name
697
	INTO STRICT comment
698
	;
699
	RETURN comment;
700
EXCEPTION
701
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
702
END;
703
$$;
704

    
705

    
706
--
707
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
708
--
709

    
710
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
711
    LANGUAGE plpgsql STABLE STRICT
712
    AS $$
713
DECLARE
714
	default_sql text;
715
BEGIN
716
	SELECT adsrc
717
	FROM pg_attribute
718
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
719
	WHERE attrelid = col.table_ AND attname = col.name
720
	INTO STRICT default_sql
721
	;
722
	RETURN default_sql;
723
EXCEPTION
724
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
725
END;
726
$$;
727

    
728

    
729
--
730
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
731
--
732

    
733
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
734
    LANGUAGE sql STABLE
735
    AS $_$
736
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
737
$_$;
738

    
739

    
740
--
741
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
742
--
743

    
744
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
745
ret_type_null: NULL::ret_type
746
';
747

    
748

    
749
--
750
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
751
--
752

    
753
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
754
    LANGUAGE plpgsql STRICT
755
    AS $$
756
BEGIN
757
    PERFORM util.col_type(col);
758
    RETURN true;
759
EXCEPTION
760
    WHEN undefined_column THEN RETURN false;
761
END;
762
$$;
763

    
764

    
765
--
766
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
767
--
768

    
769
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
770
    LANGUAGE plpgsql STABLE STRICT
771
    AS $$
772
DECLARE
773
    prefix text := util.name(type)||'.';
774
BEGIN
775
    RETURN QUERY
776
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
777
        FROM util.col_names(type) f (name_);
778
END;
779
$$;
780

    
781

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

    
786
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
787
    LANGUAGE sql STABLE STRICT
788
    AS $_$
789
SELECT attname::text
790
FROM pg_attribute
791
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
792
ORDER BY attnum
793
$_$;
794

    
795

    
796
--
797
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
798
--
799

    
800
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
801
    LANGUAGE plpgsql STABLE STRICT
802
    AS $_$
803
BEGIN
804
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
805
END;
806
$_$;
807

    
808

    
809
--
810
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
811
--
812

    
813
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
814
    LANGUAGE plpgsql STABLE STRICT
815
    AS $$
816
DECLARE
817
    type regtype;
818
BEGIN
819
    SELECT atttypid FROM pg_attribute
820
    WHERE attrelid = col.table_ AND attname = col.name
821
    INTO STRICT type
822
    ;
823
    RETURN type;
824
EXCEPTION
825
    WHEN no_data_found THEN
826
        RAISE undefined_column USING MESSAGE =
827
            concat('undefined column: ', col.name);
828
END;
829
$$;
830

    
831

    
832
--
833
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
834
--
835

    
836
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
837
    LANGUAGE sql IMMUTABLE
838
    AS $_$
839
SELECT util.esc_name__append($2, $1)
840
$_$;
841

    
842

    
843
--
844
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
845
--
846

    
847
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
848
    LANGUAGE sql IMMUTABLE
849
    AS $_$
850
SELECT position($1 in $2) > 0 /*1-based offset*/
851
$_$;
852

    
853

    
854
--
855
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
856
--
857

    
858
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
859
    LANGUAGE sql
860
    AS $_$
861
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
862
$_$;
863

    
864

    
865
--
866
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
867
--
868

    
869
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
870
    LANGUAGE plpgsql STRICT
871
    AS $$
872
BEGIN
873
    PERFORM util.eval(sql);
874
EXCEPTION
875
    WHEN duplicate_table  THEN NULL;
876
    WHEN duplicate_object THEN NULL; -- e.g. constraint
877
    WHEN duplicate_column THEN NULL;
878
    WHEN invalid_table_definition THEN
879
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
880
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
881
        END IF;
882
END;
883
$$;
884

    
885

    
886
--
887
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
888
--
889

    
890
COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
891
idempotent
892
';
893

    
894

    
895
--
896
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
897
--
898

    
899
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
900
    LANGUAGE sql IMMUTABLE
901
    AS $_$
902
/* newline before so the query starts at the beginning of the line.
903
newline after to visually separate queries from one another. */
904
SELECT util.raise_notice($$
905
$$||$1||$$
906
$$)
907
$_$;
908

    
909

    
910
--
911
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
912
--
913

    
914
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
915
    LANGUAGE sql STABLE STRICT
916
    AS $_$
917
SELECT util.eval2set($$
918
SELECT col
919
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
920
LEFT JOIN $$||$2||$$ ON "to" = col
921
WHERE "from" IS NULL
922
$$, NULL::text)
923
$_$;
924

    
925

    
926
--
927
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
928
--
929

    
930
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
931
gets table_''s derived columns (all the columns not in the names table)
932
';
933

    
934

    
935
--
936
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
937
--
938

    
939
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
940
    LANGUAGE sql STABLE
941
    AS $_$
942
SELECT * FROM util.diff($1::text, $2::text, $3,
943
	util.has_single_row($1) AND util.has_single_row($2))
944
$_$;
945

    
946

    
947
--
948
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
949
--
950

    
951
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
952
col_type_null (*required*): NULL::shared_base_type
953
usage:
954
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
955
';
956

    
957

    
958
--
959
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
960
--
961

    
962
CREATE FUNCTION diff(left_ text, right_ text, col_type_null anyelement, single_row boolean DEFAULT false, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
963
    LANGUAGE sql STABLE
964
    AS $_$
965
SELECT * FROM
966
util.eval2col_pair($$
967
/* need to explicitly cast each side to the return type because this does not
968
happen automatically even when an implicit cast is available */
969
SELECT left_::$$||pg_typeof($3)||$$, right_::$$||pg_typeof($3)||$$
970
FROM $$||$1||$$ left_
971
$$||util._if($4, 'CROSS'::text, 'FULL')||$$ JOIN $$||$2||$$ right_
972
$$||util._if($4, ''::text, $$ON left_ = right_
973
$$)||
974
$$WHERE left_ IS DISTINCT FROM right_
975
ORDER BY left_, right_
976
$$, $3)
977
$_$;
978

    
979

    
980
--
981
-- Name: FUNCTION diff(left_ text, right_ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
982
--
983

    
984
COMMENT ON FUNCTION diff(left_ text, right_ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
985
col_type_null (*required*): NULL::col_type
986
single_row: whether the tables consist of a single row, which should be
987
	displayed side-by-side
988

    
989
to run EXPLAIN on the FULL JOIN query:
990
# run this function
991
# look for a NOTICE containing the expanded query that it ran
992
# run EXPLAIN on this expanded query
993
';
994

    
995

    
996
--
997
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
998
--
999

    
1000
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1001
    LANGUAGE sql STRICT
1002
    AS $_$
1003
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1004
$_$;
1005

    
1006

    
1007
--
1008
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1009
--
1010

    
1011
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1012
idempotent
1013
';
1014

    
1015

    
1016
--
1017
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1018
--
1019

    
1020
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1021
    LANGUAGE sql STRICT
1022
    AS $_$
1023
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1024
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1025
$_$;
1026

    
1027

    
1028
--
1029
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1030
--
1031

    
1032
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1033
idempotent
1034
';
1035

    
1036

    
1037
--
1038
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1039
--
1040

    
1041
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1042
    LANGUAGE sql STRICT
1043
    AS $_$
1044
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1
1045
||util._if($2, $$ CASCADE$$, ''::text))
1046
$_$;
1047

    
1048

    
1049
--
1050
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1051
--
1052

    
1053
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1054
idempotent
1055
';
1056

    
1057

    
1058
--
1059
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1060
--
1061

    
1062
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1063
    LANGUAGE sql STRICT
1064
    AS $_$
1065
SELECT util.eval($$DROP VIEW IF EXISTS $$||$1
1066
||util._if($2, $$ CASCADE$$, ''::text))
1067
$_$;
1068

    
1069

    
1070
--
1071
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1072
--
1073

    
1074
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1075
idempotent
1076
';
1077

    
1078

    
1079
--
1080
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1081
--
1082

    
1083
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1084
    LANGUAGE sql IMMUTABLE
1085
    AS $_$
1086
SELECT util.array_fill($1, 0)
1087
$_$;
1088

    
1089

    
1090
--
1091
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1092
--
1093

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

    
1098

    
1099
--
1100
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1101
--
1102

    
1103
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1104
    LANGUAGE sql IMMUTABLE
1105
    AS $_$
1106
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1107
$_$;
1108

    
1109

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

    
1114
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1115
    LANGUAGE sql IMMUTABLE
1116
    AS $_$
1117
SELECT regexp_replace($2, '("?)$', $1||'\1')
1118
$_$;
1119

    
1120

    
1121
--
1122
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
1123
--
1124

    
1125
CREATE FUNCTION eval(sql text) RETURNS void
1126
    LANGUAGE plpgsql STRICT
1127
    AS $$
1128
BEGIN
1129
	PERFORM util.debug_print_sql(sql);
1130
	EXECUTE sql;
1131
END;
1132
$$;
1133

    
1134

    
1135
--
1136
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1137
--
1138

    
1139
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1140
    LANGUAGE plpgsql
1141
    AS $$
1142
BEGIN
1143
	PERFORM util.debug_print_sql(sql);
1144
	RETURN QUERY EXECUTE sql;
1145
END;
1146
$$;
1147

    
1148

    
1149
--
1150
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1151
--
1152

    
1153
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1154
col_type_null (*required*): NULL::col_type
1155
';
1156

    
1157

    
1158
--
1159
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1160
--
1161

    
1162
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1163
    LANGUAGE plpgsql
1164
    AS $$
1165
BEGIN
1166
	PERFORM util.debug_print_sql(sql);
1167
	RETURN QUERY EXECUTE sql;
1168
END;
1169
$$;
1170

    
1171

    
1172
--
1173
-- Name: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1174
--
1175

    
1176
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS SETOF anyelement
1177
    LANGUAGE plpgsql
1178
    AS $$
1179
BEGIN
1180
	PERFORM util.debug_print_sql(sql);
1181
	RETURN QUERY EXECUTE sql;
1182
END;
1183
$$;
1184

    
1185

    
1186
--
1187
-- Name: FUNCTION eval2set(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1188
--
1189

    
1190
COMMENT ON FUNCTION eval2set(sql text, ret_type_null anyelement) IS '
1191
ret_type_null: NULL::ret_type
1192
';
1193

    
1194

    
1195
--
1196
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1197
--
1198

    
1199
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1200
    LANGUAGE plpgsql
1201
    AS $$
1202
DECLARE
1203
	ret_val ret_type_null%TYPE;
1204
BEGIN
1205
	PERFORM util.debug_print_sql(sql);
1206
	EXECUTE sql INTO STRICT ret_val;
1207
	RETURN ret_val;
1208
END;
1209
$$;
1210

    
1211

    
1212
--
1213
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1214
--
1215

    
1216
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1217
ret_type_null: NULL::ret_type
1218
';
1219

    
1220

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

    
1225
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1226
    LANGUAGE sql
1227
    AS $_$
1228
SELECT util.eval2val($$SELECT $$||$1, $2)
1229
$_$;
1230

    
1231

    
1232
--
1233
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1234
--
1235

    
1236
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1237
ret_type_null: NULL::ret_type
1238
';
1239

    
1240

    
1241
--
1242
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1243
--
1244

    
1245
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1246
    LANGUAGE sql
1247
    AS $_$
1248
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1249
$_$;
1250

    
1251

    
1252
--
1253
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1254
--
1255

    
1256
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1257
sql: can be NULL, which will be passed through
1258
ret_type_null: NULL::ret_type
1259
';
1260

    
1261

    
1262
--
1263
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1264
--
1265

    
1266
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1267
    LANGUAGE sql STABLE STRICT
1268
    AS $_$
1269
SELECT col_name
1270
FROM unnest($2) s (col_name)
1271
WHERE util.col_exists(($1, col_name))
1272
$_$;
1273

    
1274

    
1275
--
1276
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1277
--
1278

    
1279
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1280
    LANGUAGE sql
1281
    AS $_$
1282
SELECT util.eval2set($$EXPLAIN $$||$1)
1283
$_$;
1284

    
1285

    
1286
--
1287
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1288
--
1289

    
1290
CREATE FUNCTION explain2notice(sql text) RETURNS void
1291
    LANGUAGE plpgsql
1292
    AS $_$
1293
BEGIN
1294
	RAISE NOTICE '%', $$EXPLAIN:
1295
$$||util.explain2str(sql);
1296
END;
1297
$_$;
1298

    
1299

    
1300
--
1301
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1302
--
1303

    
1304
CREATE FUNCTION explain2str(sql text) RETURNS text
1305
    LANGUAGE sql
1306
    AS $_$
1307
SELECT util.join_strs(explain, $$
1308
$$) FROM util.explain($1)
1309
$_$;
1310

    
1311

    
1312
SET default_tablespace = '';
1313

    
1314
SET default_with_oids = false;
1315

    
1316
--
1317
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1318
--
1319

    
1320
CREATE TABLE explain (
1321
    line text NOT NULL
1322
);
1323

    
1324

    
1325
--
1326
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1327
--
1328

    
1329
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1330
    LANGUAGE sql
1331
    AS $_$
1332
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1333
$$||quote_nullable($1)||$$
1334
)$$)
1335
$_$;
1336

    
1337

    
1338
--
1339
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1340
--
1341

    
1342
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1343
usage:
1344
PERFORM util.explain2table($$
1345
query
1346
$$);
1347
';
1348

    
1349

    
1350
--
1351
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1352
--
1353

    
1354
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1355
    LANGUAGE sql IMMUTABLE
1356
    AS $_$
1357
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1358
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1359
) END
1360
$_$;
1361

    
1362

    
1363
--
1364
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1365
--
1366

    
1367
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1368
ensures that an array will always have proper non-NULL dimensions
1369
';
1370

    
1371

    
1372
--
1373
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
1374
--
1375

    
1376
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1377
    LANGUAGE plpgsql
1378
    AS $_$
1379
DECLARE
1380
	PG_EXCEPTION_DETAIL text;
1381
	recreate_users_cmd text = util.save_drop_views(users);
1382
BEGIN
1383
	PERFORM util.eval(cmd);
1384
	PERFORM util.eval(recreate_users_cmd);
1385
EXCEPTION
1386
WHEN dependent_objects_still_exist THEN
1387
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1388
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1389
	users = array(SELECT * FROM util.regexp_matches_group(
1390
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on table .*$'));
1391
	IF util.is_empty(users) THEN RAISE; END IF;
1392
	PERFORM util.force_recreate(cmd, users);
1393
END;
1394
$_$;
1395

    
1396

    
1397
--
1398
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1399
--
1400

    
1401
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1402
idempotent
1403

    
1404
users: not necessary to provide this because it will be autopopulated
1405
';
1406

    
1407

    
1408
--
1409
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1410
--
1411

    
1412
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1413
    LANGUAGE plpgsql STRICT
1414
    AS $_$
1415
DECLARE
1416
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1417
$$||query;
1418
BEGIN
1419
	EXECUTE mk_view;
1420
EXCEPTION
1421
WHEN invalid_table_definition THEN
1422
	IF SQLERRM = 'cannot drop columns from view'
1423
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1424
	THEN
1425
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1426
		EXECUTE mk_view;
1427
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1428
	END IF;
1429
END;
1430
$_$;
1431

    
1432

    
1433
--
1434
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1435
--
1436

    
1437
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1438
idempotent
1439
';
1440

    
1441

    
1442
--
1443
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1444
--
1445

    
1446
CREATE FUNCTION grants_users() RETURNS SETOF text
1447
    LANGUAGE sql IMMUTABLE
1448
    AS $$
1449
VALUES ('bien_read'), ('public_')
1450
$$;
1451

    
1452

    
1453
--
1454
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1455
--
1456

    
1457
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1458
    LANGUAGE sql IMMUTABLE
1459
    AS $_$
1460
SELECT substring($2 for length($1)) = $1
1461
$_$;
1462

    
1463

    
1464
--
1465
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1466
--
1467

    
1468
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1469
    LANGUAGE sql STABLE
1470
    AS $_$
1471
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1472
$_$;
1473

    
1474

    
1475
--
1476
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1477
--
1478

    
1479
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1480
    LANGUAGE sql IMMUTABLE
1481
    AS $_$
1482
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1483
$_$;
1484

    
1485

    
1486
--
1487
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1488
--
1489

    
1490
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1491
avoids repeating the same value for each key
1492
';
1493

    
1494

    
1495
--
1496
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1497
--
1498

    
1499
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1500
    LANGUAGE sql IMMUTABLE
1501
    AS $_$
1502
SELECT COALESCE($1, $2)
1503
$_$;
1504

    
1505

    
1506
--
1507
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
1508
--
1509

    
1510
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
1511
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
1512
';
1513

    
1514

    
1515
--
1516
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1517
--
1518

    
1519
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
1520
    LANGUAGE sql
1521
    AS $_$
1522
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
1523
$_$;
1524

    
1525

    
1526
--
1527
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1528
--
1529

    
1530
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1531
    LANGUAGE sql STABLE STRICT
1532
    AS $_$
1533
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1534
$_$;
1535

    
1536

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

    
1541
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
1542
    LANGUAGE sql IMMUTABLE
1543
    AS $_$
1544
SELECT util.array_length($1) = 0
1545
$_$;
1546

    
1547

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

    
1552
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1553
    LANGUAGE sql IMMUTABLE
1554
    AS $_$
1555
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1556
$_$;
1557

    
1558

    
1559
--
1560
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1561
--
1562

    
1563
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1564
    LANGUAGE sql IMMUTABLE
1565
    AS $_$
1566
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1567
$_$;
1568

    
1569

    
1570
--
1571
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1572
--
1573

    
1574
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1575
    LANGUAGE sql IMMUTABLE STRICT
1576
    AS $_$
1577
SELECT $1 || $3 || $2
1578
$_$;
1579

    
1580

    
1581
--
1582
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
1583
--
1584

    
1585
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
1586
    LANGUAGE sql IMMUTABLE
1587
    AS $_$
1588
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
1589
$_$;
1590

    
1591

    
1592
--
1593
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
1594
--
1595

    
1596
CREATE FUNCTION ltrim_nl(str text) RETURNS text
1597
    LANGUAGE sql IMMUTABLE
1598
    AS $_$
1599
SELECT ltrim($1, $$
1600
$$)
1601
$_$;
1602

    
1603

    
1604
--
1605
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1606
--
1607

    
1608
CREATE FUNCTION map_filter_insert() RETURNS trigger
1609
    LANGUAGE plpgsql
1610
    AS $$
1611
BEGIN
1612
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1613
	RETURN new;
1614
END;
1615
$$;
1616

    
1617

    
1618
--
1619
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1620
--
1621

    
1622
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1623
    LANGUAGE plpgsql STABLE STRICT
1624
    AS $_$
1625
DECLARE
1626
    value text;
1627
BEGIN
1628
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1629
        INTO value USING key;
1630
    RETURN value;
1631
END;
1632
$_$;
1633

    
1634

    
1635
--
1636
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1637
--
1638

    
1639
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1640
    LANGUAGE sql IMMUTABLE
1641
    AS $_$
1642
SELECT util._map(util.nulls_map($1), $2)
1643
$_$;
1644

    
1645

    
1646
--
1647
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1648
--
1649

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

    
1653
[1] inlining of function calls, which is different from constant folding
1654
[2] _map()''s profiling query
1655
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1656
and map_nulls()''s profiling query
1657
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1658
both take ~920 ms.
1659
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.
1660
';
1661

    
1662

    
1663
--
1664
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1665
--
1666

    
1667
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1668
    LANGUAGE plpgsql STABLE STRICT
1669
    AS $_$
1670
BEGIN
1671
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1672
END;
1673
$_$;
1674

    
1675

    
1676
--
1677
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
1678
--
1679

    
1680
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
1681
    LANGUAGE sql
1682
    AS $_$
1683
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
1684
$$||util.ltrim_nl($2))
1685
$_$;
1686

    
1687

    
1688
--
1689
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
1690
--
1691

    
1692
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
1693
idempotent
1694
';
1695

    
1696

    
1697
--
1698
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1699
--
1700

    
1701
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
1702
    LANGUAGE sql
1703
    AS $_$
1704
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
1705
$_$;
1706

    
1707

    
1708
--
1709
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
1710
--
1711

    
1712
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
1713
idempotent
1714
';
1715

    
1716

    
1717
--
1718
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1719
--
1720

    
1721
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1722
    LANGUAGE sql STRICT
1723
    AS $_$
1724
SELECT util.create_if_not_exists($$
1725
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1726
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1727
||quote_literal($2)||$$;
1728
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
1729
constant
1730
';
1731
$$)
1732
$_$;
1733

    
1734

    
1735
--
1736
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1737
--
1738

    
1739
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
1740
idempotent
1741
';
1742

    
1743

    
1744
--
1745
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1746
--
1747

    
1748
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1749
    LANGUAGE plpgsql STRICT
1750
    AS $_$
1751
DECLARE
1752
    type regtype = util.typeof(expr, col.table_::text::regtype);
1753
    col_name_sql text = quote_ident(col.name);
1754
BEGIN
1755
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1756
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1757
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1758
$$||expr||$$;
1759
$$);
1760
END;
1761
$_$;
1762

    
1763

    
1764
--
1765
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1766
--
1767

    
1768
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
1769
idempotent
1770
';
1771

    
1772

    
1773
--
1774
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1775
--
1776

    
1777
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1778
    LANGUAGE sql STRICT
1779
    AS $_$
1780
SELECT util.create_if_not_exists($$
1781
CREATE TABLE $$||$1||$$
1782
(
1783
    LIKE util.map INCLUDING ALL
1784
);
1785

    
1786
CREATE TRIGGER map_filter_insert
1787
  BEFORE INSERT
1788
  ON $$||$1||$$
1789
  FOR EACH ROW
1790
  EXECUTE PROCEDURE util.map_filter_insert();
1791
$$)
1792
$_$;
1793

    
1794

    
1795
--
1796
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
1797
--
1798

    
1799
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
1800
    LANGUAGE sql IMMUTABLE
1801
    AS $_$
1802
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1)
1803
$_$;
1804

    
1805

    
1806
--
1807
-- Name: mk_set_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
1808
--
1809

    
1810
CREATE FUNCTION mk_set_search_path(VARIADIC schemas text[]) RETURNS text
1811
    LANGUAGE sql IMMUTABLE
1812
    AS $_$
1813
SELECT util.mk_set_search_path(util.mk_search_path(VARIADIC $1))
1814
$_$;
1815

    
1816

    
1817
--
1818
-- Name: FUNCTION mk_set_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
1819
--
1820

    
1821
COMMENT ON FUNCTION mk_set_search_path(VARIADIC schemas text[]) IS '
1822
usage:
1823
for *1* schema arg:
1824
SELECT util.mk_set_search_path(''schema'', NULL) AS search_path;
1825
	-- 2 params are needed to use the correct variant of mk_set_search_path()
1826
';
1827

    
1828

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

    
1833
CREATE FUNCTION mk_set_search_path(search_path text) RETURNS text
1834
    LANGUAGE sql IMMUTABLE
1835
    AS $_$
1836
SELECT $$SET LOCAL search_path TO $$||$1
1837
$_$;
1838

    
1839

    
1840
--
1841
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1842
--
1843

    
1844
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1845
    LANGUAGE sql STRICT
1846
    AS $_$
1847
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
1848
$_$;
1849

    
1850

    
1851
--
1852
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1853
--
1854

    
1855
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
1856
idempotent
1857
';
1858

    
1859

    
1860
--
1861
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
1862
--
1863

    
1864
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
1865
    LANGUAGE plpgsql STRICT
1866
    AS $_$
1867
DECLARE
1868
	view_qual_name text = util.qual_name(view_);
1869
BEGIN
1870
	EXECUTE $$
1871
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1872
  RETURNS SETOF $$||view_||$$ AS
1873
$BODY1$
1874
SELECT * FROM $$||view_qual_name||$$
1875
ORDER BY sort_col
1876
LIMIT $1 OFFSET $2
1877
$BODY1$
1878
  LANGUAGE sql STABLE
1879
  COST 100
1880
  ROWS 1000
1881
$$;
1882
	
1883
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
1884
END;
1885
$_$;
1886

    
1887

    
1888
--
1889
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1890
--
1891

    
1892
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1893
    LANGUAGE plpgsql STRICT
1894
    AS $_$
1895
DECLARE
1896
	view_qual_name text = util.qual_name(view_);
1897
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
1898
BEGIN
1899
	EXECUTE $$
1900
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
1901
  RETURNS integer AS
1902
$BODY1$
1903
SELECT $$||quote_ident(row_num_col)||$$
1904
FROM $$||view_qual_name||$$
1905
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
1906
LIMIT 1
1907
$BODY1$
1908
  LANGUAGE sql STABLE
1909
  COST 100;
1910
$$;
1911
	
1912
	EXECUTE $$
1913
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1914
  RETURNS SETOF $$||view_||$$ AS
1915
$BODY1$
1916
SELECT * FROM $$||view_qual_name||$$
1917
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
1918
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
1919
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
1920
ORDER BY $$||quote_ident(row_num_col)||$$
1921
$BODY1$
1922
  LANGUAGE sql STABLE
1923
  COST 100
1924
  ROWS 1000
1925
$$;
1926
	
1927
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
1928
END;
1929
$_$;
1930

    
1931

    
1932
--
1933
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
1934
--
1935

    
1936
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
1937
    LANGUAGE plpgsql STRICT
1938
    AS $_$
1939
DECLARE
1940
	view_qual_name text = util.qual_name(view_);
1941
BEGIN
1942
	EXECUTE $$
1943
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1944
  RETURNS SETOF $$||view_||$$
1945
  SET enable_sort TO 'off'
1946
  AS
1947
$BODY1$
1948
SELECT * FROM $$||view_qual_name||$$($2, $3)
1949
$BODY1$
1950
  LANGUAGE sql STABLE
1951
  COST 100
1952
  ROWS 1000
1953
;
1954
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1955
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1956
If you want to run EXPLAIN and get expanded output, use the regular subset
1957
function instead. (When a config param is set on a function, EXPLAIN produces
1958
just a function scan.)
1959
';
1960
$$;
1961
END;
1962
$_$;
1963

    
1964

    
1965
--
1966
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
1967
--
1968

    
1969
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
1970
creates subset function which turns off enable_sort
1971
';
1972

    
1973

    
1974
--
1975
-- Name: mk_use_own_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
1976
--
1977

    
1978
CREATE FUNCTION mk_use_own_schema(schema_anchor anyelement) RETURNS text
1979
    LANGUAGE sql IMMUTABLE
1980
    AS $_$
1981
SELECT util.mk_set_search_path(util.schema_esc($1))
1982
$_$;
1983

    
1984

    
1985
--
1986
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
1987
--
1988

    
1989
CREATE FUNCTION name(table_ regclass) RETURNS text
1990
    LANGUAGE sql STABLE
1991
    AS $_$
1992
SELECT relname::text FROM pg_class WHERE oid = $1
1993
$_$;
1994

    
1995

    
1996
--
1997
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1998
--
1999

    
2000
CREATE FUNCTION name(type regtype) RETURNS text
2001
    LANGUAGE sql STABLE STRICT
2002
    AS $_$
2003
SELECT typname::text FROM pg_type WHERE oid = $1
2004
$_$;
2005

    
2006

    
2007
--
2008
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2009
--
2010

    
2011
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2012
    LANGUAGE sql IMMUTABLE
2013
    AS $_$
2014
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2015
$_$;
2016

    
2017

    
2018
--
2019
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2020
--
2021

    
2022
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2023
    LANGUAGE sql IMMUTABLE
2024
    AS $_$
2025
SELECT $1 IS NOT NULL
2026
$_$;
2027

    
2028

    
2029
--
2030
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2031
--
2032

    
2033
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2034
    LANGUAGE sql IMMUTABLE
2035
    AS $_$
2036
SELECT util.hstore($1, NULL) || '*=>*'
2037
$_$;
2038

    
2039

    
2040
--
2041
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2042
--
2043

    
2044
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2045
for use with _map()
2046
';
2047

    
2048

    
2049
--
2050
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2051
--
2052

    
2053
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2054
    LANGUAGE sql IMMUTABLE
2055
    AS $_$
2056
SELECT $2 + COALESCE($1, 0)
2057
$_$;
2058

    
2059

    
2060
--
2061
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2062
--
2063

    
2064
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2065
    LANGUAGE sql IMMUTABLE
2066
    AS $_$
2067
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2068
$_$;
2069

    
2070

    
2071
--
2072
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2073
--
2074

    
2075
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2076
    LANGUAGE sql STABLE STRICT
2077
    SET search_path TO pg_temp
2078
    AS $_$
2079
SELECT $1::text
2080
$_$;
2081

    
2082

    
2083
--
2084
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2085
--
2086

    
2087
CREATE FUNCTION qual_name(type regtype) RETURNS text
2088
    LANGUAGE sql STABLE STRICT
2089
    SET search_path TO pg_temp
2090
    AS $_$
2091
SELECT $1::text
2092
$_$;
2093

    
2094

    
2095
--
2096
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2097
--
2098

    
2099
COMMENT ON FUNCTION qual_name(type regtype) IS '
2100
a type''s schema-qualified name
2101
';
2102

    
2103

    
2104
--
2105
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2106
--
2107

    
2108
CREATE FUNCTION qual_name(type unknown) RETURNS text
2109
    LANGUAGE sql STABLE STRICT
2110
    AS $_$
2111
SELECT util.qual_name($1::text::regtype)
2112
$_$;
2113

    
2114

    
2115
--
2116
-- Name: raise_error_notice(text); Type: FUNCTION; Schema: util; Owner: -
2117
--
2118

    
2119
CREATE FUNCTION raise_error_notice(msg text) RETURNS void
2120
    LANGUAGE sql IMMUTABLE STRICT
2121
    AS $_$
2122
SELECT util.raise_notice('ERROR:  '||$1)
2123
$_$;
2124

    
2125

    
2126
--
2127
-- Name: raise_notice(text); Type: FUNCTION; Schema: util; Owner: -
2128
--
2129

    
2130
CREATE FUNCTION raise_notice(msg text) RETURNS void
2131
    LANGUAGE plpgsql IMMUTABLE STRICT
2132
    AS $$
2133
BEGIN
2134
	RAISE NOTICE '%', msg;
2135
END;
2136
$$;
2137

    
2138

    
2139
--
2140
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
2141
--
2142

    
2143
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
2144
    LANGUAGE plpgsql IMMUTABLE STRICT
2145
    AS $$
2146
BEGIN
2147
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
2148
END;
2149
$$;
2150

    
2151

    
2152
--
2153
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
2154
--
2155

    
2156
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
2157
    LANGUAGE sql IMMUTABLE
2158
    AS $_$
2159
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
2160
$_$;
2161

    
2162

    
2163
--
2164
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
2165
--
2166

    
2167
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
2168
    LANGUAGE sql
2169
    AS $_$
2170
/* can't have in_table/out_table inherit from *each other*, because inheritance
2171
also causes the rows of the parent table to be included in the child table.
2172
instead, they need to inherit from a common, empty table. */
2173
SELECT util.drop_table($4, force := true);
2174
SELECT util.copy_struct($2, $4);
2175
SELECT util.inherit($2, $4);
2176
SELECT util.inherit($3, $4);
2177

    
2178
SELECT util.rematerialize_query($1, $$
2179
SELECT * FROM util.diff(
2180
  $$||quote_nullable($2)||$$::regclass
2181
, $$||quote_nullable($3)||$$::regclass
2182
, NULL::$$||$4||$$)
2183
$$);
2184

    
2185
/* the table unfortunately cannot be *materialized* in human-readable form,
2186
because this would create column name collisions between the two sides */
2187
SELECT util.set_comment($1, '
2188
to view this table in human-readable form (with each side''s tuple column
2189
expanded to its component fields):
2190
SELECT (left_).*, (right_).* FROM '||$1||';
2191
');
2192
$_$;
2193

    
2194

    
2195
--
2196
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
2197
--
2198

    
2199
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
2200
type_table (*required*): table to create as the shared base type
2201
';
2202

    
2203

    
2204
--
2205
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2206
--
2207

    
2208
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
2209
    LANGUAGE sql
2210
    AS $_$
2211
SELECT util.drop_table($1);
2212
SELECT util.materialize_query($1, $2);
2213
$_$;
2214

    
2215

    
2216
--
2217
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2218
--
2219

    
2220
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
2221
idempotent, but repeats action each time
2222
';
2223

    
2224

    
2225
--
2226
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2227
--
2228

    
2229
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
2230
    LANGUAGE sql
2231
    AS $_$
2232
SELECT util.drop_table($1);
2233
SELECT util.materialize_view($1, $2);
2234
$_$;
2235

    
2236

    
2237
--
2238
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2239
--
2240

    
2241
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
2242
idempotent, but repeats action each time
2243
';
2244

    
2245

    
2246
--
2247
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
2248
--
2249

    
2250
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
2251
    LANGUAGE sql STRICT
2252
    AS $_$
2253
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
2254
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
2255
FROM util.col_names($1::text::regtype) f (name);
2256
SELECT NULL::void; -- don't fold away functions called in previous query
2257
$_$;
2258

    
2259

    
2260
--
2261
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
2262
--
2263

    
2264
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
2265
idempotent
2266
';
2267

    
2268

    
2269
--
2270
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2271
--
2272

    
2273
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
2274
    LANGUAGE sql STRICT
2275
    AS $_$
2276
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
2277
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
2278
SELECT util.set_col_names($1, $2);
2279
$_$;
2280

    
2281

    
2282
--
2283
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2284
--
2285

    
2286
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
2287
idempotent.
2288
alters the names table, so it will need to be repopulated after running this function.
2289
';
2290

    
2291

    
2292
--
2293
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2294
--
2295

    
2296
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
2297
    LANGUAGE sql STRICT
2298
    AS $_$
2299
SELECT util.drop_table($1);
2300
SELECT util.mk_map_table($1);
2301
$_$;
2302

    
2303

    
2304
--
2305
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
2306
--
2307

    
2308
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
2309
    LANGUAGE plpgsql STRICT
2310
    AS $_$
2311
DECLARE
2312
	result text = NULL;
2313
BEGIN
2314
	BEGIN
2315
		result = util.show_create_view(view_);
2316
		PERFORM util.eval($$DROP VIEW $$||view_);
2317
	EXCEPTION
2318
		WHEN undefined_table THEN NULL;
2319
	END;
2320
	RETURN result;
2321
END;
2322
$_$;
2323

    
2324

    
2325
--
2326
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
2327
--
2328

    
2329
CREATE FUNCTION save_drop_views(views text[]) RETURNS text
2330
    LANGUAGE sql
2331
    AS $_$
2332
SELECT string_agg(util.save_drop_view(unnest), '') FROM unnest($1)
2333
$_$;
2334

    
2335

    
2336
--
2337
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
2338
--
2339

    
2340
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
2341
    LANGUAGE sql STABLE
2342
    AS $_$
2343
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
2344
$_$;
2345

    
2346

    
2347
--
2348
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
2349
--
2350

    
2351
CREATE FUNCTION schema(table_ regclass) RETURNS text
2352
    LANGUAGE sql STABLE
2353
    AS $_$
2354
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
2355
$_$;
2356

    
2357

    
2358
--
2359
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
2360
--
2361

    
2362
CREATE FUNCTION schema(type regtype) RETURNS text
2363
    LANGUAGE sql STABLE
2364
    AS $_$
2365
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
2366
$_$;
2367

    
2368

    
2369
--
2370
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
2371
--
2372

    
2373
CREATE FUNCTION schema(type_null anyelement) RETURNS text
2374
    LANGUAGE sql STABLE
2375
    AS $_$
2376
SELECT util.schema(pg_typeof($1))
2377
$_$;
2378

    
2379

    
2380
--
2381
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
2382
--
2383

    
2384
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
2385
    LANGUAGE sql STABLE
2386
    AS $_$
2387
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
2388
$_$;
2389

    
2390

    
2391
--
2392
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
2393
--
2394

    
2395
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
2396
a schema bundle is a group of schemas with a common prefix
2397
';
2398

    
2399

    
2400
--
2401
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
2402
--
2403

    
2404
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
2405
    LANGUAGE sql
2406
    AS $_$
2407
SELECT util.schema_rename(old_schema,
2408
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
2409
FROM util.schema_bundle_get_schemas($1) f (old_schema);
2410
SELECT NULL::void; -- don't fold away functions called in previous query
2411
$_$;
2412

    
2413

    
2414
--
2415
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
2416
--
2417

    
2418
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
2419
    LANGUAGE plpgsql
2420
    AS $$
2421
BEGIN
2422
	-- don't schema_bundle_rm() the schema_bundle to keep!
2423
	IF replace = with_ THEN RETURN; END IF;
2424
	
2425
	PERFORM util.schema_bundle_rm(replace);
2426
	PERFORM util.schema_bundle_rename(with_, replace);
2427
END;
2428
$$;
2429

    
2430

    
2431
--
2432
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
2433
--
2434

    
2435
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
2436
    LANGUAGE sql
2437
    AS $_$
2438
SELECT util.schema_rm(schema)
2439
FROM util.schema_bundle_get_schemas($1) f (schema);
2440
SELECT NULL::void; -- don't fold away functions called in previous query
2441
$_$;
2442

    
2443

    
2444
--
2445
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
2446
--
2447

    
2448
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
2449
    LANGUAGE sql STABLE
2450
    AS $_$
2451
SELECT quote_ident(util.schema($1))
2452
$_$;
2453

    
2454

    
2455
--
2456
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
2457
--
2458

    
2459
CREATE FUNCTION schema_oid(schema text) RETURNS oid
2460
    LANGUAGE sql STABLE
2461
    AS $_$
2462
SELECT oid FROM pg_namespace WHERE nspname = $1
2463
$_$;
2464

    
2465

    
2466
--
2467
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
2468
--
2469

    
2470
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
2471
    LANGUAGE sql
2472
    AS $_$
2473
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
2474
$_$;
2475

    
2476

    
2477
--
2478
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
2479
--
2480

    
2481
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
2482
    LANGUAGE plpgsql
2483
    AS $$
2484
BEGIN
2485
	-- don't schema_rm() the schema to keep!
2486
	IF replace = with_ THEN RETURN; END IF;
2487
	
2488
	PERFORM util.schema_rm(replace);
2489
	PERFORM util.schema_rename(with_, replace);
2490
END;
2491
$$;
2492

    
2493

    
2494
--
2495
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
2496
--
2497

    
2498
CREATE FUNCTION schema_rm(schema text) RETURNS void
2499
    LANGUAGE sql
2500
    AS $_$
2501
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
2502
$_$;
2503

    
2504

    
2505
--
2506
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
2507
--
2508

    
2509
CREATE FUNCTION search_path_append(schemas text) RETURNS void
2510
    LANGUAGE sql STRICT
2511
    AS $_$
2512
SELECT util.eval(
2513
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
2514
$_$;
2515

    
2516

    
2517
--
2518
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2519
--
2520

    
2521
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
2522
    LANGUAGE plpgsql STRICT
2523
    AS $_$
2524
DECLARE
2525
    old text[] = ARRAY(SELECT util.col_names(table_));
2526
    new text[] = ARRAY(SELECT util.map_values(names));
2527
BEGIN
2528
    old = old[1:array_length(new, 1)]; -- truncate to same length
2529
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
2530
||$$ TO $$||quote_ident(value))
2531
    FROM each(hstore(old, new))
2532
    WHERE value != key -- not same name
2533
    ;
2534
END;
2535
$_$;
2536

    
2537

    
2538
--
2539
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2540
--
2541

    
2542
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
2543
idempotent
2544
';
2545

    
2546

    
2547
--
2548
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2549
--
2550

    
2551
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
2552
    LANGUAGE plpgsql STRICT
2553
    AS $_$
2554
DECLARE
2555
	row_ util.map;
2556
BEGIN
2557
	-- rename any metadata cols rather than re-adding them with new names
2558
	BEGIN
2559
		PERFORM util.set_col_names(table_, names);
2560
	EXCEPTION
2561
		WHEN array_subscript_error THEN -- selective suppress
2562
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
2563
				-- metadata cols not yet added
2564
			ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
2565
			END IF;
2566
	END;
2567
	
2568
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
2569
	LOOP
2570
		PERFORM util.mk_const_col((table_, row_."to"),
2571
			substring(row_."from" from 2));
2572
	END LOOP;
2573
	
2574
	PERFORM util.set_col_names(table_, names);
2575
END;
2576
$_$;
2577

    
2578

    
2579
--
2580
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2581
--
2582

    
2583
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
2584
idempotent.
2585
the metadata mappings must be *last* in the names table.
2586
';
2587

    
2588

    
2589
--
2590
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2591
--
2592

    
2593
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
2594
    LANGUAGE plpgsql STRICT
2595
    AS $_$
2596
DECLARE
2597
    sql text = $$ALTER TABLE $$||table_||$$
2598
$$||NULLIF(array_to_string(ARRAY(
2599
    SELECT
2600
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
2601
    ||$$ USING $$||col_name_sql||$$::$$||target_type
2602
    FROM
2603
    (
2604
        SELECT
2605
          quote_ident(col_name) AS col_name_sql
2606
        , util.col_type((table_, col_name)) AS curr_type
2607
        , type AS target_type
2608
        FROM unnest(col_casts)
2609
    ) s
2610
    WHERE curr_type != target_type
2611
), '
2612
, '), '');
2613
BEGIN
2614
    PERFORM util.debug_print_sql(sql);
2615
    EXECUTE COALESCE(sql, '');
2616
END;
2617
$_$;
2618

    
2619

    
2620
--
2621
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
2622
--
2623

    
2624
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
2625
idempotent
2626
';
2627

    
2628

    
2629
--
2630
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2631
--
2632

    
2633
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
2634
    LANGUAGE sql STRICT
2635
    AS $_$
2636
SELECT util.eval($$COMMENT ON TABLE $$||$1||$$ IS $$||quote_nullable($2))
2637
$_$;
2638

    
2639

    
2640
--
2641
-- Name: show_create_view(regclass); Type: FUNCTION; Schema: util; Owner: -
2642
--
2643

    
2644
CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
2645
    LANGUAGE sql STABLE
2646
    AS $_$
2647
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
2648
$$||util.show_grants_for($1)
2649
$_$;
2650

    
2651

    
2652
--
2653
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
2654
--
2655

    
2656
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
2657
    LANGUAGE sql STABLE
2658
    AS $_$
2659
SELECT string_agg(cmd, '')
2660
FROM
2661
(
2662
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
2663
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
2664
$$ ELSE '' END) AS cmd
2665
	FROM util.grants_users() f (user_)
2666
) s
2667
$_$;
2668

    
2669

    
2670
--
2671
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
2672
--
2673

    
2674
CREATE FUNCTION show_views_like(schema text, name_regexp text) RETURNS SETOF regclass
2675
    LANGUAGE sql STABLE
2676
    AS $_$
2677
SELECT oid FROM pg_class
2678
WHERE relnamespace = util.schema_oid($1) AND relkind = 'v' AND relname ~ $2
2679
ORDER BY relname
2680
$_$;
2681

    
2682

    
2683
--
2684
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
2685
--
2686

    
2687
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
2688
    LANGUAGE plpgsql STABLE STRICT
2689
    AS $_$
2690
DECLARE
2691
    hstore hstore;
2692
BEGIN
2693
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
2694
        table_||$$))$$ INTO STRICT hstore;
2695
    RETURN hstore;
2696
END;
2697
$_$;
2698

    
2699

    
2700
--
2701
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2702
--
2703

    
2704
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
2705
    LANGUAGE sql STABLE STRICT
2706
    AS $_$
2707
SELECT COUNT(*) > 0 FROM pg_constraint
2708
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
2709
$_$;
2710

    
2711

    
2712
--
2713
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
2714
--
2715

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

    
2720

    
2721
--
2722
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2723
--
2724

    
2725
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
2726
    LANGUAGE sql STRICT
2727
    AS $_$
2728
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
2729
||quote_ident($2)||$$ CHECK (true)$$)
2730
$_$;
2731

    
2732

    
2733
--
2734
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
2735
--
2736

    
2737
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
2738
stores a status flag by the presence of a table constraint.
2739
idempotent.
2740
';
2741

    
2742

    
2743
--
2744
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
2745
--
2746

    
2747
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
2748
    LANGUAGE sql STABLE STRICT
2749
    AS $_$
2750
SELECT util.table_flag__get($1, 'nulls_mapped')
2751
$_$;
2752

    
2753

    
2754
--
2755
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2756
--
2757

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

    
2762

    
2763
--
2764
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
2765
--
2766

    
2767
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
2768
    LANGUAGE sql STRICT
2769
    AS $_$
2770
SELECT util.table_flag__set($1, 'nulls_mapped')
2771
$_$;
2772

    
2773

    
2774
--
2775
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2776
--
2777

    
2778
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
2779
sets that a table''s NULL-equivalent strings have been replaced with NULL.
2780
idempotent.
2781
';
2782

    
2783

    
2784
--
2785
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
2786
--
2787

    
2788
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
2789
    LANGUAGE plpgsql STRICT
2790
    AS $_$
2791
DECLARE
2792
    row record;
2793
BEGIN
2794
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
2795
    LOOP
2796
        IF row.global_name != row.name THEN
2797
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
2798
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
2799
        END IF;
2800
    END LOOP;
2801
END;
2802
$_$;
2803

    
2804

    
2805
--
2806
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2807
--
2808

    
2809
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
2810
idempotent
2811
';
2812

    
2813

    
2814
--
2815
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2816
--
2817

    
2818
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
2819
    LANGUAGE sql STRICT
2820
    AS $_$
2821
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
2822
SELECT NULL::void; -- don't fold away functions called in previous query
2823
$_$;
2824

    
2825

    
2826
--
2827
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2828
--
2829

    
2830
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
2831
trims table_ to include only columns in the original data.
2832
idempotent.
2833
';
2834

    
2835

    
2836
--
2837
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
2838
--
2839

    
2840
CREATE FUNCTION truncate(table_ regclass) RETURNS void
2841
    LANGUAGE plpgsql STRICT
2842
    AS $_$
2843
BEGIN
2844
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
2845
END;
2846
$_$;
2847

    
2848

    
2849
--
2850
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2851
--
2852

    
2853
COMMENT ON FUNCTION truncate(table_ regclass) IS '
2854
idempotent
2855
';
2856

    
2857

    
2858
--
2859
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
2860
--
2861

    
2862
CREATE FUNCTION try_create(sql text) RETURNS void
2863
    LANGUAGE plpgsql STRICT
2864
    AS $$
2865
BEGIN
2866
    PERFORM util.eval(sql);
2867
EXCEPTION
2868
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
2869
    WHEN undefined_column THEN NULL;
2870
    WHEN duplicate_column THEN NULL;
2871
END;
2872
$$;
2873

    
2874

    
2875
--
2876
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
2877
--
2878

    
2879
COMMENT ON FUNCTION try_create(sql text) IS '
2880
idempotent
2881
';
2882

    
2883

    
2884
--
2885
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
2886
--
2887

    
2888
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
2889
    LANGUAGE sql STRICT
2890
    AS $_$
2891
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
2892
$_$;
2893

    
2894

    
2895
--
2896
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
2897
--
2898

    
2899
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
2900
idempotent
2901
';
2902

    
2903

    
2904
--
2905
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
2906
--
2907

    
2908
CREATE FUNCTION type_qual(value anyelement) RETURNS text
2909
    LANGUAGE sql IMMUTABLE
2910
    AS $_$
2911
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
2912
$_$;
2913

    
2914

    
2915
--
2916
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
2917
--
2918

    
2919
COMMENT ON FUNCTION type_qual(value anyelement) IS '
2920
a type''s NOT NULL qualifier
2921
';
2922

    
2923

    
2924
--
2925
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
2926
--
2927

    
2928
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
2929
    LANGUAGE plpgsql STABLE
2930
    AS $_$
2931
DECLARE
2932
    type regtype;
2933
BEGIN
2934
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
2935
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
2936
    RETURN type;
2937
END;
2938
$_$;
2939

    
2940

    
2941
--
2942
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
2943
--
2944

    
2945
CREATE AGGREGATE all_same(anyelement) (
2946
    SFUNC = all_same_transform,
2947
    STYPE = anyarray,
2948
    FINALFUNC = all_same_final
2949
);
2950

    
2951

    
2952
--
2953
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
2954
--
2955

    
2956
COMMENT ON AGGREGATE all_same(anyelement) IS '
2957
includes NULLs in comparison
2958
';
2959

    
2960

    
2961
--
2962
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
2963
--
2964

    
2965
CREATE AGGREGATE join_strs(text, text) (
2966
    SFUNC = join_strs_transform,
2967
    STYPE = text
2968
);
2969

    
2970

    
2971
--
2972
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
2973
--
2974

    
2975
CREATE OPERATOR -> (
2976
    PROCEDURE = map_get,
2977
    LEFTARG = regclass,
2978
    RIGHTARG = text
2979
);
2980

    
2981

    
2982
--
2983
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
2984
--
2985

    
2986
CREATE OPERATOR => (
2987
    PROCEDURE = hstore,
2988
    LEFTARG = text[],
2989
    RIGHTARG = text
2990
);
2991

    
2992

    
2993
--
2994
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
2995
--
2996

    
2997
COMMENT ON OPERATOR => (text[], text) IS '
2998
usage: array[''key1'', ...]::text[] => ''value''
2999
';
3000

    
3001

    
3002
--
3003
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
3004
--
3005

    
3006
CREATE OPERATOR ?*>= (
3007
    PROCEDURE = is_populated_more_often_than,
3008
    LEFTARG = anyelement,
3009
    RIGHTARG = anyelement
3010
);
3011

    
3012

    
3013
--
3014
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
3015
--
3016

    
3017
CREATE OPERATOR ?>= (
3018
    PROCEDURE = is_more_complete_than,
3019
    LEFTARG = anyelement,
3020
    RIGHTARG = anyelement
3021
);
3022

    
3023

    
3024
--
3025
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
3026
--
3027

    
3028
CREATE OPERATOR ||% (
3029
    PROCEDURE = concat_esc,
3030
    LEFTARG = text,
3031
    RIGHTARG = text
3032
);
3033

    
3034

    
3035
--
3036
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
3037
--
3038

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

    
3043

    
3044
--
3045
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
3046
--
3047

    
3048
CREATE TABLE map (
3049
    "from" text NOT NULL,
3050
    "to" text,
3051
    filter text,
3052
    notes text
3053
);
3054

    
3055

    
3056
--
3057
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
3058
--
3059

    
3060

    
3061

    
3062
--
3063
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
3064
--
3065

    
3066

    
3067

    
3068
--
3069
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3070
--
3071

    
3072
ALTER TABLE ONLY map
3073
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
3074

    
3075

    
3076
--
3077
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3078
--
3079

    
3080
ALTER TABLE ONLY map
3081
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
3082

    
3083

    
3084
--
3085
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
3086
--
3087

    
3088
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
3089

    
3090

    
3091
--
3092
-- PostgreSQL database dump complete
3093
--
3094

    
(19-19/29)