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: 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. avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won''t mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements.
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: %==(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
76
--
77

    
78
CREATE FUNCTION "%=="(left_ anyelement, right_ anyelement) RETURNS boolean
79
    LANGUAGE sql STABLE
80
    AS $_$
81
SELECT keys($1) = keys($2)
82
$_$;
83

    
84

    
85
--
86
-- Name: FUNCTION "%=="(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
87
--
88

    
89
COMMENT ON FUNCTION "%=="(left_ anyelement, right_ anyelement) IS '
90
needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
91
';
92

    
93

    
94
--
95
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
96
--
97

    
98
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
99
    LANGUAGE sql IMMUTABLE
100
    AS $_$
101
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
102
$_$;
103

    
104

    
105
--
106
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
107
--
108

    
109
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
110
    LANGUAGE sql IMMUTABLE
111
    AS $_$
112
SELECT bool_and(value)
113
FROM
114
(VALUES
115
      ($1)
116
    , ($2)
117
    , ($3)
118
    , ($4)
119
    , ($5)
120
)
121
AS v (value)
122
$_$;
123

    
124

    
125
--
126
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
127
--
128

    
129
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
130
_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.
131
';
132

    
133

    
134
--
135
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
136
--
137

    
138
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
139
    LANGUAGE sql IMMUTABLE
140
    AS $_$
141
SELECT avg(value)
142
FROM
143
(VALUES
144
      ($1)
145
    , ($2)
146
    , ($3)
147
    , ($4)
148
    , ($5)
149
)
150
AS v (value)
151
$_$;
152

    
153

    
154
--
155
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
156
--
157

    
158
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
159
    LANGUAGE sql IMMUTABLE
160
    AS $_$
161
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)
162
FROM 
163
(
164
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
165
    UNION ALL
166
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
167
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
168
)
169
matches (g)
170
$_$;
171

    
172

    
173
--
174
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
175
--
176

    
177
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
178
    LANGUAGE sql IMMUTABLE
179
    AS $_$
180
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
181
FROM
182
(VALUES
183
      ($1)
184
    , ($2/60)
185
    , ($3/60/60)
186
)
187
AS v (value)
188
$_$;
189

    
190

    
191
--
192
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
193
--
194

    
195
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
196
    LANGUAGE sql IMMUTABLE
197
    AS $_$
198
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
199
$_$;
200

    
201

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

    
206
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
207
    LANGUAGE sql IMMUTABLE
208
    AS $_$
209
SELECT $1 = $2
210
$_$;
211

    
212

    
213
--
214
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
215
--
216

    
217
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
218
    LANGUAGE sql IMMUTABLE
219
    AS $_$
220
-- Fix dates after threshold date
221
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
222
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
223
$_$;
224

    
225

    
226
--
227
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
228
--
229

    
230
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
231
    LANGUAGE sql IMMUTABLE
232
    AS $_$
233
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
234
$_$;
235

    
236

    
237
--
238
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
239
--
240

    
241
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
242
    LANGUAGE sql IMMUTABLE
243
    AS $_$
244
SELECT util._if($1 != '', $2, $3)
245
$_$;
246

    
247

    
248
--
249
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
250
--
251

    
252
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
253
    LANGUAGE sql IMMUTABLE
254
    AS $_$
255
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
256
$_$;
257

    
258

    
259
--
260
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
261
--
262

    
263
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
264
    LANGUAGE sql IMMUTABLE
265
    AS $_$
266
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
267
$_$;
268

    
269

    
270
--
271
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
272
--
273

    
274
CREATE FUNCTION _label(label text, value text) RETURNS text
275
    LANGUAGE sql IMMUTABLE
276
    AS $_$
277
SELECT coalesce($1 || ': ', '') || $2
278
$_$;
279

    
280

    
281
--
282
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
283
--
284

    
285
CREATE FUNCTION _lowercase(value text) RETURNS text
286
    LANGUAGE sql IMMUTABLE
287
    AS $_$
288
SELECT lower($1)
289
$_$;
290

    
291

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

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

    
306

    
307
--
308
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
309
--
310

    
311
CREATE FUNCTION _map(map hstore, value text) RETURNS text
312
    LANGUAGE plpgsql IMMUTABLE STRICT
313
    AS $$
314
DECLARE
315
    match text := map -> value;
316
BEGIN
317
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
318
        match := map -> '*'; -- use default entry
319
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
320
        END IF;
321
    END IF;
322
    
323
    -- Interpret result
324
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
325
    ELSIF match = '*' THEN RETURN value;
326
    ELSE RETURN match;
327
    END IF;
328
END;
329
$$;
330

    
331

    
332
--
333
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
334
--
335

    
336
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
337
    LANGUAGE sql IMMUTABLE
338
    AS $_$
339
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
340
$_$;
341

    
342

    
343
--
344
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
345
--
346

    
347
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
348
    LANGUAGE sql IMMUTABLE
349
    AS $_$
350
SELECT util.join_strs(value, '; ')
351
FROM
352
(
353
    SELECT *
354
    FROM
355
    (
356
        SELECT
357
        DISTINCT ON (value)
358
        *
359
        FROM
360
        (VALUES
361
              (1, $1)
362
            , (2, $2)
363
            , (3, $3)
364
            , (4, $4)
365
            , (5, $5)
366
            , (6, $6)
367
            , (7, $7)
368
            , (8, $8)
369
            , (9, $9)
370
            , (10, $10)
371
        )
372
        AS v (sort_order, value)
373
        WHERE value IS NOT NULL
374
    )
375
    AS v
376
    ORDER BY sort_order
377
)
378
AS v
379
$_$;
380

    
381

    
382
--
383
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
384
--
385

    
386
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
387
    LANGUAGE sql IMMUTABLE
388
    AS $_$
389
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
390
$_$;
391

    
392

    
393
--
394
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
395
--
396

    
397
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
398
    LANGUAGE sql IMMUTABLE
399
    AS $_$
400
SELECT util.join_strs(value, ' ')
401
FROM
402
(
403
    SELECT *
404
    FROM
405
    (
406
        SELECT
407
        DISTINCT ON (value)
408
        *
409
        FROM
410
        (VALUES
411
              (1, $1)
412
            , (2, $2)
413
            , (3, $3)
414
            , (4, $4)
415
            , (5, $5)
416
            , (6, $6)
417
            , (7, $7)
418
            , (8, $8)
419
            , (9, $9)
420
            , (10, $10)
421
        )
422
        AS v (sort_order, value)
423
        WHERE value IS NOT NULL
424
    )
425
    AS v
426
    ORDER BY sort_order
427
)
428
AS v
429
$_$;
430

    
431

    
432
--
433
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
434
--
435

    
436
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
437
    LANGUAGE sql IMMUTABLE
438
    AS $_$
439
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
440
$_$;
441

    
442

    
443
--
444
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
445
--
446

    
447
CREATE FUNCTION _not(value boolean) RETURNS boolean
448
    LANGUAGE sql IMMUTABLE
449
    AS $_$
450
SELECT NOT $1
451
$_$;
452

    
453

    
454
--
455
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
456
--
457

    
458
CREATE FUNCTION _now() RETURNS timestamp with time zone
459
    LANGUAGE sql STABLE
460
    AS $$
461
SELECT now()
462
$$;
463

    
464

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

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

    
475

    
476
--
477
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
478
--
479

    
480
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
481
    LANGUAGE plpgsql IMMUTABLE
482
    AS $$
483
DECLARE
484
    type util.datatype NOT NULL := type; -- add NOT NULL
485
BEGIN
486
    IF type = 'str' THEN RETURN nullif(value::text, "null");
487
    -- Invalid value is ignored, but invalid null value generates error
488
    ELSIF type = 'float' THEN
489
        DECLARE
490
            -- Outside the try block so that invalid null value generates error
491
            "null" double precision := "null"::double precision;
492
        BEGIN
493
            RETURN nullif(value::double precision, "null");
494
        EXCEPTION
495
            WHEN data_exception THEN RETURN value; -- ignore invalid value
496
        END;
497
    END IF;
498
END;
499
$$;
500

    
501

    
502
--
503
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
504
--
505

    
506
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
507
    LANGUAGE sql IMMUTABLE
508
    AS $_$
509
SELECT bool_or(value)
510
FROM
511
(VALUES
512
      ($1)
513
    , ($2)
514
    , ($3)
515
    , ($4)
516
    , ($5)
517
)
518
AS v (value)
519
$_$;
520

    
521

    
522
--
523
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
524
--
525

    
526
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
527
_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.
528
';
529

    
530

    
531
--
532
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
533
--
534

    
535
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
536
    LANGUAGE sql IMMUTABLE
537
    AS $_$
538
SELECT $2 - $1
539
$_$;
540

    
541

    
542
--
543
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
544
--
545

    
546
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
547
    LANGUAGE sql IMMUTABLE
548
    AS $_$
549
SELECT regexp_split_to_table($1, $2)
550
$_$;
551

    
552

    
553
--
554
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
555
--
556

    
557
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
558
    LANGUAGE sql STABLE
559
    AS $_$
560
SELECT util.derived_cols($1, $2)
561
UNION
562
SELECT util.eval2set($$
563
SELECT col
564
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
565
JOIN $$||$2||$$ ON "to" = col
566
WHERE "from" LIKE ':%'
567
$$, NULL::text)
568
$_$;
569

    
570

    
571
--
572
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
573
--
574

    
575
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
576
gets table_''s added columns (all the columns not in the original data)
577
';
578

    
579

    
580
--
581
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
582
--
583

    
584
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
585
    LANGUAGE sql IMMUTABLE
586
    AS $_$
587
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
588
$_$;
589

    
590

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

    
595
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
596
    LANGUAGE plpgsql IMMUTABLE
597
    AS $$
598
DECLARE
599
	value_cmp         state%TYPE = ARRAY[value];
600
	state             state%TYPE = COALESCE(state, value_cmp);
601
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
602
BEGIN
603
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
604
END;
605
$$;
606

    
607

    
608
--
609
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
610
--
611

    
612
CREATE FUNCTION analyze_(table_ regclass) RETURNS void
613
    LANGUAGE sql
614
    AS $_$
615
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
616
$_$;
617

    
618

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

    
623
CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
624
    LANGUAGE sql
625
    AS $_$
626
SELECT util.set_comment($1, concat(util.comment($1), $2))
627
$_$;
628

    
629

    
630
--
631
-- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
632
--
633

    
634
COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
635
comment: must start and end with a newline
636
';
637

    
638

    
639
--
640
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
641
--
642

    
643
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
644
    LANGUAGE sql IMMUTABLE
645
    AS $_$
646
SELECT pg_catalog.array_fill($1, ARRAY[$2])
647
$_$;
648

    
649

    
650
--
651
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
652
--
653

    
654
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
655
    LANGUAGE sql IMMUTABLE
656
    AS $_$
657
SELECT util.array_length($1, 1)
658
$_$;
659

    
660

    
661
--
662
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
663
--
664

    
665
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
666
    LANGUAGE sql IMMUTABLE
667
    AS $_$
668
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
669
$_$;
670

    
671

    
672
--
673
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
674
--
675

    
676
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
677
returns 0 instead of NULL for empty arrays
678
';
679

    
680

    
681
--
682
-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
683
--
684

    
685
CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
686
    LANGUAGE sql
687
    AS $_$
688
SELECT CASE WHEN util.freq_always_1($1, $2)
689
THEN util.drop_column($1, $2, force := true)
690
ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
691
END
692
$_$;
693

    
694

    
695
--
696
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
697
--
698

    
699
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
700
    LANGUAGE sql STABLE
701
    AS $_$
702
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
703
$_$;
704

    
705

    
706
--
707
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
708
--
709

    
710
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
711
    LANGUAGE plpgsql STRICT
712
    AS $_$
713
BEGIN
714
    -- not yet clustered (ARRAY[] compares NULLs literally)
715
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
716
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
717
    END IF;
718
END;
719
$_$;
720

    
721

    
722
--
723
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
724
--
725

    
726
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
727
idempotent
728
';
729

    
730

    
731
--
732
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
733
--
734

    
735
CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
736
    LANGUAGE sql IMMUTABLE
737
    AS $_$
738
SELECT value
739
FROM unnest($1) value
740
WHERE value IS NOT NULL
741
LIMIT 1
742
$_$;
743

    
744

    
745
--
746
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
747
--
748

    
749
COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
750
uses:
751
* coalescing array elements or rows together
752
* forcing evaluation of all values of a COALESCE()
753
';
754

    
755

    
756
--
757
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
758
--
759

    
760
CREATE FUNCTION col__min(col col_ref) RETURNS integer
761
    LANGUAGE sql STABLE
762
    AS $_$
763
SELECT util.eval2val($$
764
SELECT $$||quote_ident($1.name)||$$
765
FROM $$||$1.table_||$$
766
ORDER BY $$||quote_ident($1.name)||$$ ASC
767
LIMIT 1
768
$$, NULL::integer)
769
$_$;
770

    
771

    
772
--
773
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
774
--
775

    
776
CREATE FUNCTION col_comment(col col_ref) RETURNS text
777
    LANGUAGE plpgsql STABLE STRICT
778
    AS $$
779
DECLARE
780
	comment text;
781
BEGIN
782
	SELECT description
783
	FROM pg_attribute
784
	LEFT JOIN pg_description ON objoid = attrelid
785
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
786
	WHERE attrelid = col.table_ AND attname = col.name
787
	INTO STRICT comment
788
	;
789
	RETURN comment;
790
EXCEPTION
791
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
792
END;
793
$$;
794

    
795

    
796
--
797
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
798
--
799

    
800
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
801
    LANGUAGE plpgsql STABLE STRICT
802
    AS $$
803
DECLARE
804
	default_sql text;
805
BEGIN
806
	SELECT adsrc
807
	FROM pg_attribute
808
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
809
	WHERE attrelid = col.table_ AND attname = col.name
810
	INTO STRICT default_sql
811
	;
812
	RETURN default_sql;
813
EXCEPTION
814
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
815
END;
816
$$;
817

    
818

    
819
--
820
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
821
--
822

    
823
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
824
    LANGUAGE sql STABLE
825
    AS $_$
826
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
827
$_$;
828

    
829

    
830
--
831
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
832
--
833

    
834
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
835
ret_type_null: NULL::ret_type
836
';
837

    
838

    
839
--
840
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
841
--
842

    
843
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
844
    LANGUAGE plpgsql STRICT
845
    AS $$
846
BEGIN
847
    PERFORM util.col_type(col);
848
    RETURN true;
849
EXCEPTION
850
    WHEN undefined_column THEN RETURN false;
851
END;
852
$$;
853

    
854

    
855
--
856
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
857
--
858

    
859
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
860
    LANGUAGE plpgsql STABLE STRICT
861
    AS $$
862
DECLARE
863
    prefix text := util.name(type)||'.';
864
BEGIN
865
    RETURN QUERY
866
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
867
        FROM util.col_names(type) f (name_);
868
END;
869
$$;
870

    
871

    
872
--
873
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
874
--
875

    
876
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
877
    LANGUAGE sql STABLE
878
    AS $_$
879
SELECT attname::text
880
FROM pg_attribute
881
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
882
ORDER BY attnum
883
$_$;
884

    
885

    
886
--
887
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
888
--
889

    
890
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
891
    LANGUAGE plpgsql STABLE STRICT
892
    AS $_$
893
BEGIN
894
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
895
END;
896
$_$;
897

    
898

    
899
--
900
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
901
--
902

    
903
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
904
    LANGUAGE plpgsql STABLE STRICT
905
    AS $$
906
DECLARE
907
    type regtype;
908
BEGIN
909
    SELECT atttypid FROM pg_attribute
910
    WHERE attrelid = col.table_ AND attname = col.name
911
    INTO STRICT type
912
    ;
913
    RETURN type;
914
EXCEPTION
915
    WHEN no_data_found THEN
916
        RAISE undefined_column USING MESSAGE =
917
            concat('undefined column: ', col.name);
918
END;
919
$$;
920

    
921

    
922
--
923
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
924
--
925

    
926
CREATE FUNCTION comment(element oid) RETURNS text
927
    LANGUAGE sql STABLE
928
    AS $_$
929
SELECT description FROM pg_description WHERE objoid = $1
930
$_$;
931

    
932

    
933
--
934
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
935
--
936

    
937
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
938
    LANGUAGE sql IMMUTABLE
939
    AS $_$
940
SELECT util.esc_name__append($2, $1)
941
$_$;
942

    
943

    
944
--
945
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
946
--
947

    
948
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
949
    LANGUAGE sql IMMUTABLE
950
    AS $_$
951
SELECT position($1 in $2) > 0 /*1-based offset*/
952
$_$;
953

    
954

    
955
--
956
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
957
--
958

    
959
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
960
    LANGUAGE sql
961
    AS $_$
962
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
963
$_$;
964

    
965

    
966
--
967
-- Name: copy_types_and_data(regclass, text); Type: FUNCTION; Schema: util; Owner: -
968
--
969

    
970
CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
971
    LANGUAGE sql
972
    AS $_$
973
SELECT util.materialize_view($2, $1)
974
$_$;
975

    
976

    
977
--
978
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
979
--
980

    
981
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
982
    LANGUAGE plpgsql STRICT
983
    AS $$
984
BEGIN
985
	PERFORM util.eval(sql);
986
EXCEPTION
987
WHEN   duplicate_table
988
	OR duplicate_object -- eg. constraint
989
	OR duplicate_column
990
	OR duplicate_function
991
THEN NULL;
992
WHEN invalid_table_definition THEN
993
	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
994
	ELSE RAISE;
995
	END IF;
996
END;
997
$$;
998

    
999

    
1000
--
1001
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
1002
--
1003

    
1004
COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
1005
idempotent
1006
';
1007

    
1008

    
1009
--
1010
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1011
--
1012

    
1013
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
1014
    LANGUAGE sql STABLE
1015
    AS $$
1016
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
1017
$$;
1018

    
1019

    
1020
--
1021
-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
1022
--
1023

    
1024
CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
1025
    LANGUAGE sql IMMUTABLE
1026
    AS $_$
1027
SELECT util.raise('NOTICE', $$SELECT $$||$1)
1028
$_$;
1029

    
1030

    
1031
--
1032
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1033
--
1034

    
1035
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
1036
    LANGUAGE sql IMMUTABLE
1037
    AS $_$
1038
SELECT util.raise('NOTICE', 'returns: '
1039
||(CASE WHEN $2 THEN util.quote_typed($1) ELSE $1::text END));
1040
SELECT $1;
1041
$_$;
1042

    
1043

    
1044
--
1045
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
1046
--
1047

    
1048
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
1049
    LANGUAGE sql IMMUTABLE
1050
    AS $_$
1051
/* newline before so the query starts at the beginning of the line.
1052
newline after to visually separate queries from one another. */
1053
SELECT util.raise('NOTICE', $$
1054
$$||util.runnable_sql($1)||$$
1055
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
1056
$_$;
1057

    
1058

    
1059
--
1060
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1061
--
1062

    
1063
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
1064
    LANGUAGE sql STABLE
1065
    AS $_$
1066
SELECT util.eval2set($$
1067
SELECT col
1068
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
1069
LEFT JOIN $$||$2||$$ ON "to" = col
1070
WHERE "from" IS NULL
1071
$$, NULL::text)
1072
$_$;
1073

    
1074

    
1075
--
1076
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1077
--
1078

    
1079
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
1080
gets table_''s derived columns (all the columns not in the names table)
1081
';
1082

    
1083

    
1084
--
1085
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1086
--
1087

    
1088
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1089
    LANGUAGE sql
1090
    AS $_$
1091
-- create a diff when the # of copies of a row differs between the tables
1092
SELECT util.to_freq($1);
1093
SELECT util.to_freq($2);
1094
SELECT util.auto_rm_freq(ARRAY[$1, $2]);
1095

    
1096
SELECT * FROM util.diff($1, $2, $3, has_freq := true)
1097
$_$;
1098

    
1099

    
1100
--
1101
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1102
--
1103

    
1104
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1105
usage:
1106
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1107

    
1108
col_type_null (*required*): NULL::shared_base_type
1109
';
1110

    
1111

    
1112
--
1113
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1114
--
1115

    
1116
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
1117
    LANGUAGE plpgsql
1118
    SET search_path TO pg_temp
1119
    AS $_$
1120
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
1121
changes of search_path (schema elements are bound at inline time rather than
1122
runtime) */
1123
/* function option search_path is needed to limit the effects of
1124
`SET LOCAL search_path` to the current function */
1125
BEGIN
1126
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
1127
	
1128
	PERFORM util.mk_keys_func(pg_typeof($3));
1129
	RETURN QUERY
1130
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
1131
$$/* need to explicitly cast each side to the return type because this does not
1132
happen automatically even when an implicit cast is available */
1133
  left_::$$||util.typeof($3)||$$
1134
, right_::$$||util.typeof($3)
1135
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
1136
the *same* base type, *even though* this is optional when using a custom %== */
1137
, util._if($4, $$true/*= CROSS JOIN*/$$,
1138
$$ left_::$$||util.typeof($3)||$$
1139
%== right_::$$||util.typeof($3)||$$
1140
	-- refer to EXPLAIN output for expansion of %==$$
1141
)
1142
,     $$         left_::$$||util.typeof($3)||$$
1143
IS DISTINCT FROM right_::$$||util.typeof($3)
1144
), $3)
1145
	;
1146
END;
1147
$_$;
1148

    
1149

    
1150
--
1151
-- 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: -
1152
--
1153

    
1154
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1155
col_type_null (*required*): NULL::col_type
1156
single_row: whether the tables consist of a single row, which should be
1157
	displayed side-by-side
1158

    
1159
to match up rows using a subset of the columns, create a custom keys() function
1160
which returns this subset as a record:
1161
-- note that OUT parameters for the returned fields are *not* needed
1162
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
1163
  RETURNS record AS
1164
$BODY$
1165
SELECT ($1.key_field_0, $1.key_field_1)
1166
$BODY$
1167
  LANGUAGE sql IMMUTABLE
1168
  COST 100;
1169

    
1170

    
1171
to run EXPLAIN on the FULL JOIN query:
1172
# run this function
1173
# look for a NOTICE containing the expanded query that it ran
1174
# run EXPLAIN on this expanded query
1175
';
1176

    
1177

    
1178
--
1179
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1180
--
1181

    
1182
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1183
    LANGUAGE sql
1184
    AS $_$
1185
SELECT * FROM util.diff($1::text, $2::text, $3,
1186
	single_row := util.has_single_row($1) AND util.has_single_row($2))
1187
$_$;
1188

    
1189

    
1190
--
1191
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1192
--
1193

    
1194
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1195
helper function used by diff(regclass, regclass)
1196

    
1197
usage:
1198
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
1199

    
1200
col_type_null (*required*): NULL::shared_base_type
1201
';
1202

    
1203

    
1204
--
1205
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1206
--
1207

    
1208
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1209
    LANGUAGE sql
1210
    AS $_$
1211
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1212
$_$;
1213

    
1214

    
1215
--
1216
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1217
--
1218

    
1219
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1220
idempotent
1221
';
1222

    
1223

    
1224
--
1225
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1226
--
1227

    
1228
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1229
    LANGUAGE sql
1230
    AS $_$
1231
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1232
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1233
$_$;
1234

    
1235

    
1236
--
1237
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1238
--
1239

    
1240
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1241
idempotent
1242
';
1243

    
1244

    
1245
--
1246
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
1247
--
1248

    
1249
CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
1250
    LANGUAGE sql
1251
    AS $_$
1252
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
1253
SELECT NULL::void; -- don't fold away functions called in previous query
1254
$_$;
1255

    
1256

    
1257
--
1258
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
1259
--
1260

    
1261
COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
1262
idempotent
1263
';
1264

    
1265

    
1266
--
1267
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1268
--
1269

    
1270
CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
1271
    LANGUAGE sql
1272
    AS $_$
1273
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1274
included in the debug SQL */
1275
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1276
$_$;
1277

    
1278

    
1279
--
1280
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1281
--
1282

    
1283
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1284
    LANGUAGE sql
1285
    AS $_$
1286
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1287
||util._if($3, $$ CASCADE$$, ''::text))
1288
$_$;
1289

    
1290

    
1291
--
1292
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1293
--
1294

    
1295
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1296
idempotent
1297
';
1298

    
1299

    
1300
--
1301
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1302
--
1303

    
1304
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
1305
    LANGUAGE sql
1306
    AS $_$
1307
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
1308
$_$;
1309

    
1310

    
1311
--
1312
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1313
--
1314

    
1315
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
1316
    LANGUAGE sql
1317
    AS $_$
1318
SELECT util.debug_print_func_call(util.quote_func_call(
1319
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
1320
util.quote_typed($3)))
1321
;
1322
SELECT util.drop_relation(relation, $3)
1323
FROM util.show_relations_like($1, $2) relation
1324
;
1325
SELECT NULL::void; -- don't fold away functions called in previous query
1326
$_$;
1327

    
1328

    
1329
--
1330
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1331
--
1332

    
1333
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1334
    LANGUAGE sql
1335
    AS $_$
1336
SELECT util.drop_relation('TABLE', $1, $2)
1337
$_$;
1338

    
1339

    
1340
--
1341
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1342
--
1343

    
1344
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1345
idempotent
1346
';
1347

    
1348

    
1349
--
1350
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1351
--
1352

    
1353
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1354
    LANGUAGE sql
1355
    AS $_$
1356
SELECT util.drop_relation('VIEW', $1, $2)
1357
$_$;
1358

    
1359

    
1360
--
1361
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1362
--
1363

    
1364
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1365
idempotent
1366
';
1367

    
1368

    
1369
--
1370
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1371
--
1372

    
1373
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1374
    LANGUAGE sql IMMUTABLE
1375
    AS $_$
1376
SELECT util.array_fill($1, 0)
1377
$_$;
1378

    
1379

    
1380
--
1381
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1382
--
1383

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

    
1388

    
1389
--
1390
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1391
--
1392

    
1393
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1394
    LANGUAGE sql IMMUTABLE
1395
    AS $_$
1396
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1397
$_$;
1398

    
1399

    
1400
--
1401
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
1402
--
1403

    
1404
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1405
    LANGUAGE sql IMMUTABLE
1406
    AS $_$
1407
SELECT regexp_replace($2, '("?)$', $1||'\1')
1408
$_$;
1409

    
1410

    
1411
--
1412
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1413
--
1414

    
1415
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
1416
    LANGUAGE plpgsql
1417
    AS $$
1418
BEGIN
1419
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1420
	EXECUTE sql;
1421
END;
1422
$$;
1423

    
1424

    
1425
--
1426
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1427
--
1428

    
1429
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1430
    LANGUAGE plpgsql
1431
    AS $$
1432
BEGIN
1433
	PERFORM util.debug_print_sql(sql);
1434
	RETURN QUERY EXECUTE sql;
1435
END;
1436
$$;
1437

    
1438

    
1439
--
1440
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1441
--
1442

    
1443
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1444
col_type_null (*required*): NULL::col_type
1445
';
1446

    
1447

    
1448
--
1449
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1450
--
1451

    
1452
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1453
    LANGUAGE plpgsql
1454
    AS $$
1455
BEGIN
1456
	PERFORM util.debug_print_sql(sql);
1457
	RETURN QUERY EXECUTE sql;
1458
END;
1459
$$;
1460

    
1461

    
1462
--
1463
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1464
--
1465

    
1466
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
1467
    LANGUAGE plpgsql
1468
    AS $$
1469
BEGIN
1470
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1471
	RETURN QUERY EXECUTE sql;
1472
END;
1473
$$;
1474

    
1475

    
1476
--
1477
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1478
--
1479

    
1480
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1481
    LANGUAGE plpgsql STABLE
1482
    AS $$
1483
DECLARE
1484
	ret_val ret_type_null%TYPE;
1485
BEGIN
1486
	PERFORM util.debug_print_sql(sql);
1487
	EXECUTE sql INTO STRICT ret_val;
1488
	RETURN ret_val;
1489
END;
1490
$$;
1491

    
1492

    
1493
--
1494
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1495
--
1496

    
1497
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1498
ret_type_null: NULL::ret_type
1499
';
1500

    
1501

    
1502
--
1503
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1504
--
1505

    
1506
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1507
    LANGUAGE sql
1508
    AS $_$
1509
SELECT util.eval2val($$SELECT $$||$1, $2)
1510
$_$;
1511

    
1512

    
1513
--
1514
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1515
--
1516

    
1517
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1518
ret_type_null: NULL::ret_type
1519
';
1520

    
1521

    
1522
--
1523
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1524
--
1525

    
1526
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1527
    LANGUAGE sql
1528
    AS $_$
1529
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1530
$_$;
1531

    
1532

    
1533
--
1534
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1535
--
1536

    
1537
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1538
sql: can be NULL, which will be passed through
1539
ret_type_null: NULL::ret_type
1540
';
1541

    
1542

    
1543
--
1544
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1545
--
1546

    
1547
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1548
    LANGUAGE sql STABLE
1549
    AS $_$
1550
SELECT col_name
1551
FROM unnest($2) s (col_name)
1552
WHERE util.col_exists(($1, col_name))
1553
$_$;
1554

    
1555

    
1556
--
1557
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1558
--
1559

    
1560
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1561
    LANGUAGE sql
1562
    AS $_$
1563
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
1564
$_$;
1565

    
1566

    
1567
--
1568
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1569
--
1570

    
1571
CREATE FUNCTION explain2notice(sql text) RETURNS void
1572
    LANGUAGE sql
1573
    AS $_$
1574
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
1575
$_$;
1576

    
1577

    
1578
--
1579
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
1580
--
1581

    
1582
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
1583
    LANGUAGE sql
1584
    AS $_$
1585
-- newline before and after to visually separate it from other debug info
1586
SELECT $$
1587
EXPLAIN:
1588
$$||util.explain2str($1)||$$
1589
$$
1590
$_$;
1591

    
1592

    
1593
--
1594
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
1595
--
1596

    
1597
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
1598
    LANGUAGE sql
1599
    AS $_$
1600
SELECT (CASE WHEN util.is_explainable($1) THEN util.explain2notice_msg($1) END)
1601
$_$;
1602

    
1603

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

    
1608
CREATE FUNCTION explain2str(sql text) RETURNS text
1609
    LANGUAGE sql
1610
    AS $_$
1611
SELECT util.join_strs(explain, $$
1612
$$) FROM util.explain($1)
1613
$_$;
1614

    
1615

    
1616
SET default_tablespace = '';
1617

    
1618
SET default_with_oids = false;
1619

    
1620
--
1621
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1622
--
1623

    
1624
CREATE TABLE explain (
1625
    line text NOT NULL
1626
);
1627

    
1628

    
1629
--
1630
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1631
--
1632

    
1633
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1634
    LANGUAGE sql
1635
    AS $_$
1636
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1637
$$||quote_nullable($1)||$$
1638
)$$)
1639
$_$;
1640

    
1641

    
1642
--
1643
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1644
--
1645

    
1646
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1647
usage:
1648
PERFORM util.explain2table($$
1649
query
1650
$$);
1651
';
1652

    
1653

    
1654
--
1655
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
1656
--
1657

    
1658
CREATE FUNCTION first_word(str text) RETURNS text
1659
    LANGUAGE sql IMMUTABLE
1660
    AS $_$
1661
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
1662
$_$;
1663

    
1664

    
1665
--
1666
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1667
--
1668

    
1669
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1670
    LANGUAGE sql IMMUTABLE
1671
    AS $_$
1672
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1673
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1674
) END
1675
$_$;
1676

    
1677

    
1678
--
1679
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1680
--
1681

    
1682
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1683
ensures that an array will always have proper non-NULL dimensions
1684
';
1685

    
1686

    
1687
--
1688
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
1689
--
1690

    
1691
CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
1692
    LANGUAGE sql IMMUTABLE
1693
    AS $_$
1694
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
1695
$_$;
1696

    
1697

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

    
1702
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1703
    LANGUAGE plpgsql
1704
    AS $_$
1705
DECLARE
1706
	PG_EXCEPTION_DETAIL text;
1707
	recreate_users_cmd text = util.save_drop_views(users);
1708
BEGIN
1709
	PERFORM util.eval(cmd);
1710
	PERFORM util.eval(recreate_users_cmd);
1711
EXCEPTION
1712
WHEN dependent_objects_still_exist THEN
1713
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1714
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1715
	users = array(SELECT * FROM util.regexp_matches_group(
1716
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on table .*$'));
1717
	IF util.is_empty(users) THEN RAISE; END IF;
1718
	PERFORM util.force_recreate(cmd, users);
1719
END;
1720
$_$;
1721

    
1722

    
1723
--
1724
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1725
--
1726

    
1727
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1728
idempotent
1729

    
1730
users: not necessary to provide this because it will be autopopulated
1731
';
1732

    
1733

    
1734
--
1735
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1736
--
1737

    
1738
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1739
    LANGUAGE plpgsql STRICT
1740
    AS $_$
1741
DECLARE
1742
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1743
$$||query;
1744
BEGIN
1745
	EXECUTE mk_view;
1746
EXCEPTION
1747
WHEN invalid_table_definition THEN
1748
	IF SQLERRM = 'cannot drop columns from view'
1749
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1750
	THEN
1751
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1752
		EXECUTE mk_view;
1753
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1754
	END IF;
1755
END;
1756
$_$;
1757

    
1758

    
1759
--
1760
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1761
--
1762

    
1763
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1764
idempotent
1765
';
1766

    
1767

    
1768
--
1769
-- Name: freq_always_1(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1770
--
1771

    
1772
CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
1773
    LANGUAGE sql STABLE
1774
    AS $_$
1775
SELECT util.eval2val(
1776
$$SELECT NOT EXISTS( -- there is no row that is != 1
1777
	SELECT NULL
1778
	FROM $$||$1||$$
1779
	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
1780
	LIMIT 1
1781
)
1782
$$, NULL::boolean)
1783
$_$;
1784

    
1785

    
1786
--
1787
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
1788
--
1789

    
1790
CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
1791
    LANGUAGE sql STABLE
1792
    AS $_$
1793
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
1794
$_$;
1795

    
1796

    
1797
--
1798
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1799
--
1800

    
1801
CREATE FUNCTION grants_users() RETURNS SETOF text
1802
    LANGUAGE sql IMMUTABLE
1803
    AS $$
1804
VALUES ('bien_read'), ('public_')
1805
$$;
1806

    
1807

    
1808
--
1809
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1810
--
1811

    
1812
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1813
    LANGUAGE sql IMMUTABLE
1814
    AS $_$
1815
SELECT substring($2 for length($1)) = $1
1816
$_$;
1817

    
1818

    
1819
--
1820
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1821
--
1822

    
1823
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1824
    LANGUAGE sql STABLE
1825
    AS $_$
1826
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1827
$_$;
1828

    
1829

    
1830
--
1831
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1832
--
1833

    
1834
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1835
    LANGUAGE sql IMMUTABLE
1836
    AS $_$
1837
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1838
$_$;
1839

    
1840

    
1841
--
1842
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1843
--
1844

    
1845
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1846
avoids repeating the same value for each key
1847
';
1848

    
1849

    
1850
--
1851
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1852
--
1853

    
1854
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1855
    LANGUAGE sql IMMUTABLE
1856
    AS $_$
1857
SELECT COALESCE($1, $2)
1858
$_$;
1859

    
1860

    
1861
--
1862
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
1863
--
1864

    
1865
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
1866
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
1867
';
1868

    
1869

    
1870
--
1871
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1872
--
1873

    
1874
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
1875
    LANGUAGE sql
1876
    AS $_$
1877
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
1878
$_$;
1879

    
1880

    
1881
--
1882
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1883
--
1884

    
1885
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1886
    LANGUAGE sql STABLE
1887
    AS $_$
1888
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1889
$_$;
1890

    
1891

    
1892
--
1893
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1894
--
1895

    
1896
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
1897
    LANGUAGE sql IMMUTABLE
1898
    AS $_$
1899
SELECT util.array_length($1) = 0
1900
$_$;
1901

    
1902

    
1903
--
1904
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
1905
--
1906

    
1907
CREATE FUNCTION is_explain(sql text) RETURNS boolean
1908
    LANGUAGE sql IMMUTABLE
1909
    AS $_$
1910
SELECT upper(util.first_word($1)) = 'EXPLAIN'
1911
$_$;
1912

    
1913

    
1914
--
1915
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
1916
--
1917

    
1918
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
1919
    LANGUAGE sql IMMUTABLE
1920
    AS $_$
1921
SELECT upper(util.first_word($1)) = ANY(
1922
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
1923
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
1924
)
1925
$_$;
1926

    
1927

    
1928
--
1929
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1930
--
1931

    
1932
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1933
    LANGUAGE sql IMMUTABLE
1934
    AS $_$
1935
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1936
$_$;
1937

    
1938

    
1939
--
1940
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1941
--
1942

    
1943
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1944
    LANGUAGE sql IMMUTABLE
1945
    AS $_$
1946
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1947
$_$;
1948

    
1949

    
1950
--
1951
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
1952
--
1953

    
1954
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
1955
    LANGUAGE sql IMMUTABLE
1956
    AS $_$
1957
SELECT upper(util.first_word($1)) = 'SET'
1958
$_$;
1959

    
1960

    
1961
--
1962
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
1963
--
1964

    
1965
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
1966
    LANGUAGE sql STABLE
1967
    AS $_$
1968
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
1969
$_$;
1970

    
1971

    
1972
--
1973
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
1974
--
1975

    
1976
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
1977
    LANGUAGE sql STABLE
1978
    AS $_$
1979
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
1980
$_$;
1981

    
1982

    
1983
--
1984
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1985
--
1986

    
1987
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1988
    LANGUAGE sql IMMUTABLE STRICT
1989
    AS $_$
1990
SELECT $1 || $3 || $2
1991
$_$;
1992

    
1993

    
1994
--
1995
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
1996
--
1997

    
1998
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
1999
must be declared STRICT to use the special handling of STRICT aggregating functions
2000
';
2001

    
2002

    
2003
--
2004
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
2005
--
2006

    
2007
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
2008
    LANGUAGE sql IMMUTABLE
2009
    AS $_$
2010
SELECT $1 -- compare on the entire value
2011
$_$;
2012

    
2013

    
2014
--
2015
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
2016
--
2017

    
2018
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
2019
    LANGUAGE sql IMMUTABLE
2020
    AS $_$
2021
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
2022
$_$;
2023

    
2024

    
2025
--
2026
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
2027
--
2028

    
2029
CREATE FUNCTION ltrim_nl(str text) RETURNS text
2030
    LANGUAGE sql IMMUTABLE
2031
    AS $_$
2032
SELECT ltrim($1, $$
2033
$$)
2034
$_$;
2035

    
2036

    
2037
--
2038
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
2039
--
2040

    
2041
CREATE FUNCTION map_filter_insert() RETURNS trigger
2042
    LANGUAGE plpgsql
2043
    AS $$
2044
BEGIN
2045
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
2046
	RETURN new;
2047
END;
2048
$$;
2049

    
2050

    
2051
--
2052
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2053
--
2054

    
2055
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
2056
    LANGUAGE plpgsql STABLE STRICT
2057
    AS $_$
2058
DECLARE
2059
    value text;
2060
BEGIN
2061
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
2062
        INTO value USING key;
2063
    RETURN value;
2064
END;
2065
$_$;
2066

    
2067

    
2068
--
2069
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
2070
--
2071

    
2072
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
2073
    LANGUAGE sql IMMUTABLE
2074
    AS $_$
2075
SELECT util._map(util.nulls_map($1), $2)
2076
$_$;
2077

    
2078

    
2079
--
2080
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
2081
--
2082

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

    
2086
[1] inlining of function calls, which is different from constant folding
2087
[2] _map()''s profiling query
2088
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
2089
and map_nulls()''s profiling query
2090
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
2091
both take ~920 ms.
2092
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.
2093
';
2094

    
2095

    
2096
--
2097
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
2098
--
2099

    
2100
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
2101
    LANGUAGE plpgsql STABLE STRICT
2102
    AS $_$
2103
BEGIN
2104
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
2105
END;
2106
$_$;
2107

    
2108

    
2109
--
2110
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2111
--
2112

    
2113
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
2114
    LANGUAGE sql
2115
    AS $_$
2116
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
2117
$$||util.ltrim_nl($2));
2118
-- make sure the created table has the correct estimated row count
2119
SELECT util.analyze_($1);
2120

    
2121
SELECT util.append_comment($1, '
2122
contents generated from:
2123
'||util.ltrim_nl($2)||';
2124
');
2125
$_$;
2126

    
2127

    
2128
--
2129
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2130
--
2131

    
2132
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
2133
idempotent
2134
';
2135

    
2136

    
2137
--
2138
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2139
--
2140

    
2141
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
2142
    LANGUAGE sql
2143
    AS $_$
2144
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
2145
$_$;
2146

    
2147

    
2148
--
2149
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2150
--
2151

    
2152
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
2153
idempotent
2154
';
2155

    
2156

    
2157
--
2158
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
2159
--
2160

    
2161
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
2162
    LANGUAGE sql
2163
    AS $_$
2164
SELECT util.create_if_not_exists($$
2165
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
2166
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
2167
||quote_literal($2)||$$;
2168
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
2169
constant
2170
';
2171
$$)
2172
$_$;
2173

    
2174

    
2175
--
2176
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
2177
--
2178

    
2179
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
2180
idempotent
2181
';
2182

    
2183

    
2184
--
2185
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
2186
--
2187

    
2188
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
2189
    LANGUAGE plpgsql STRICT
2190
    AS $_$
2191
DECLARE
2192
    type regtype = util.typeof(expr, col.table_::text::regtype);
2193
    col_name_sql text = quote_ident(col.name);
2194
BEGIN
2195
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
2196
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
2197
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
2198
$$||expr||$$;
2199
$$);
2200
END;
2201
$_$;
2202

    
2203

    
2204
--
2205
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
2206
--
2207

    
2208
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
2209
idempotent
2210
';
2211

    
2212

    
2213
--
2214
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
2215
--
2216

    
2217
CREATE FUNCTION mk_diff_query(left_ text, right_ text, cols text DEFAULT 'left_, right_'::text, join_cond text DEFAULT 'left_ %== right_ -- refer to EXPLAIN output for expansion of %=='::text, filter text DEFAULT 'left_ IS DISTINCT FROM right_'::text) RETURNS text
2218
    LANGUAGE sql IMMUTABLE
2219
    AS $_$
2220
SELECT
2221
$$SELECT
2222
$$||$3||$$
2223
FROM      $$||$1||$$ left_
2224
FULL JOIN $$||$2||$$ right_
2225
ON $$||$4||$$
2226
WHERE $$||$5||$$
2227
ORDER BY left_, right_
2228
$$
2229
$_$;
2230

    
2231

    
2232
--
2233
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2234
--
2235

    
2236
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
2237
    LANGUAGE sql
2238
    AS $_$
2239
-- keys()
2240
SELECT util.mk_keys_func($1, ARRAY(
2241
SELECT col FROM util.typed_cols($1) col
2242
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
2243
));
2244

    
2245
-- values_()
2246
SELECT util.mk_keys_func($1, COALESCE(
2247
	NULLIF(ARRAY(
2248
	SELECT col FROM util.typed_cols($1) col
2249
	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
2250
	), ARRAY[]::util.col_cast[])
2251
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
2252
, 'values_');
2253
$_$;
2254

    
2255

    
2256
--
2257
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
2258
--
2259

    
2260
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
2261
    LANGUAGE sql
2262
    AS $_$
2263
SELECT util.create_if_not_exists($$
2264
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
2265
($$||util.mk_typed_cols_list($2)||$$);
2266
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
2267
autogenerated
2268
';
2269
$$);
2270

    
2271
SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
2272
$_$;
2273

    
2274

    
2275
--
2276
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
2277
--
2278

    
2279
CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
2280
    LANGUAGE sql
2281
    AS $_$
2282
SELECT util.create_if_not_exists($$
2283
CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
2284
||util.qual_name($1)||$$)
2285
  RETURNS $$||util.qual_name($2)||$$ AS
2286
$BODY1$
2287
SELECT ROW($$||
2288
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
2289
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
2290
$BODY1$
2291
  LANGUAGE sql IMMUTABLE
2292
  COST 100;
2293
$$);
2294
$_$;
2295

    
2296

    
2297
--
2298
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2299
--
2300

    
2301
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
2302
    LANGUAGE sql
2303
    AS $_$
2304
SELECT util.create_if_not_exists($$
2305
CREATE TABLE $$||$1||$$
2306
(
2307
    LIKE util.map INCLUDING ALL
2308
);
2309

    
2310
CREATE TRIGGER map_filter_insert
2311
  BEFORE INSERT
2312
  ON $$||$1||$$
2313
  FOR EACH ROW
2314
  EXECUTE PROCEDURE util.map_filter_insert();
2315
$$)
2316
$_$;
2317

    
2318

    
2319
--
2320
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
2321
--
2322

    
2323
CREATE FUNCTION mk_not_null(text) RETURNS text
2324
    LANGUAGE sql IMMUTABLE
2325
    AS $_$
2326
SELECT COALESCE($1, '<NULL>')
2327
$_$;
2328

    
2329

    
2330
--
2331
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2332
--
2333

    
2334
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
2335
    LANGUAGE sql IMMUTABLE
2336
    AS $_$
2337
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
2338
util.qual_name((unnest).type), ''), '')
2339
FROM unnest($1)
2340
$_$;
2341

    
2342

    
2343
--
2344
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
2345
--
2346

    
2347
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
2348
    LANGUAGE sql IMMUTABLE
2349
    AS $_$
2350
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
2351
$_$;
2352

    
2353

    
2354
--
2355
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
2356
--
2357

    
2358
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
2359
auto-appends util to the search_path to enable use of util operators
2360
';
2361

    
2362

    
2363
--
2364
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2365
--
2366

    
2367
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
2368
    LANGUAGE sql IMMUTABLE
2369
    AS $_$
2370
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
2371
$_$;
2372

    
2373

    
2374
--
2375
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
2376
--
2377

    
2378
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
2379
    LANGUAGE sql IMMUTABLE
2380
    AS $_$
2381
/* debug_print_return_value() needed because this function is used with EXECUTE
2382
rather than util.eval() (in order to affect the calling function), so the
2383
search_path would not otherwise be printed */
2384
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
2385
||$$ search_path TO $$||$1
2386
$_$;
2387

    
2388

    
2389
--
2390
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2391
--
2392

    
2393
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
2394
    LANGUAGE sql
2395
    AS $_$
2396
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
2397
$_$;
2398

    
2399

    
2400
--
2401
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2402
--
2403

    
2404
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2405
idempotent
2406
';
2407

    
2408

    
2409
--
2410
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2411
--
2412

    
2413
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
2414
    LANGUAGE plpgsql STRICT
2415
    AS $_$
2416
DECLARE
2417
	view_qual_name text = util.qual_name(view_);
2418
BEGIN
2419
	EXECUTE $$
2420
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2421
  RETURNS SETOF $$||view_||$$ AS
2422
$BODY1$
2423
SELECT * FROM $$||view_qual_name||$$
2424
ORDER BY sort_col
2425
LIMIT $1 OFFSET $2
2426
$BODY1$
2427
  LANGUAGE sql STABLE
2428
  COST 100
2429
  ROWS 1000
2430
$$;
2431
	
2432
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2433
END;
2434
$_$;
2435

    
2436

    
2437
--
2438
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2439
--
2440

    
2441
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2442
    LANGUAGE plpgsql STRICT
2443
    AS $_$
2444
DECLARE
2445
	view_qual_name text = util.qual_name(view_);
2446
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2447
BEGIN
2448
	EXECUTE $$
2449
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2450
  RETURNS integer AS
2451
$BODY1$
2452
SELECT $$||quote_ident(row_num_col)||$$
2453
FROM $$||view_qual_name||$$
2454
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2455
LIMIT 1
2456
$BODY1$
2457
  LANGUAGE sql STABLE
2458
  COST 100;
2459
$$;
2460
	
2461
	EXECUTE $$
2462
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2463
  RETURNS SETOF $$||view_||$$ AS
2464
$BODY1$
2465
SELECT * FROM $$||view_qual_name||$$
2466
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2467
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2468
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2469
ORDER BY $$||quote_ident(row_num_col)||$$
2470
$BODY1$
2471
  LANGUAGE sql STABLE
2472
  COST 100
2473
  ROWS 1000
2474
$$;
2475
	
2476
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2477
END;
2478
$_$;
2479

    
2480

    
2481
--
2482
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2483
--
2484

    
2485
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2486
    LANGUAGE plpgsql STRICT
2487
    AS $_$
2488
DECLARE
2489
	view_qual_name text = util.qual_name(view_);
2490
BEGIN
2491
	EXECUTE $$
2492
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2493
  RETURNS SETOF $$||view_||$$
2494
  SET enable_sort TO 'off'
2495
  AS
2496
$BODY1$
2497
SELECT * FROM $$||view_qual_name||$$($2, $3)
2498
$BODY1$
2499
  LANGUAGE sql STABLE
2500
  COST 100
2501
  ROWS 1000
2502
;
2503
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2504
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2505
If you want to run EXPLAIN and get expanded output, use the regular subset
2506
function instead. (When a config param is set on a function, EXPLAIN produces
2507
just a function scan.)
2508
';
2509
$$;
2510
END;
2511
$_$;
2512

    
2513

    
2514
--
2515
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
2516
--
2517

    
2518
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2519
creates subset function which turns off enable_sort
2520
';
2521

    
2522

    
2523
--
2524
-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2525
--
2526

    
2527
CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
2528
    LANGUAGE sql IMMUTABLE
2529
    AS $_$
2530
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
2531
util.qual_name((unnest).type), ', '), '')
2532
FROM unnest($1)
2533
$_$;
2534

    
2535

    
2536
--
2537
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2538
--
2539

    
2540
CREATE FUNCTION name(table_ regclass) RETURNS text
2541
    LANGUAGE sql STABLE
2542
    AS $_$
2543
SELECT relname::text FROM pg_class WHERE oid = $1
2544
$_$;
2545

    
2546

    
2547
--
2548
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2549
--
2550

    
2551
CREATE FUNCTION name(type regtype) RETURNS text
2552
    LANGUAGE sql STABLE
2553
    AS $_$
2554
SELECT typname::text FROM pg_type WHERE oid = $1
2555
$_$;
2556

    
2557

    
2558
--
2559
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
2560
--
2561

    
2562
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2563
    LANGUAGE sql IMMUTABLE
2564
    AS $_$
2565
SELECT octet_length($1) >= util.namedatalen() - $2
2566
$_$;
2567

    
2568

    
2569
--
2570
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2571
--
2572

    
2573
CREATE FUNCTION namedatalen() RETURNS integer
2574
    LANGUAGE sql IMMUTABLE
2575
    AS $$
2576
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2577
$$;
2578

    
2579

    
2580
--
2581
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2582
--
2583

    
2584
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2585
    LANGUAGE sql IMMUTABLE
2586
    AS $_$
2587
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2588
$_$;
2589

    
2590

    
2591
--
2592
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2593
--
2594

    
2595
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2596
    LANGUAGE sql IMMUTABLE
2597
    AS $_$
2598
SELECT $1 IS NOT NULL
2599
$_$;
2600

    
2601

    
2602
--
2603
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2604
--
2605

    
2606
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2607
    LANGUAGE sql IMMUTABLE
2608
    AS $_$
2609
SELECT util.hstore($1, NULL) || '*=>*'
2610
$_$;
2611

    
2612

    
2613
--
2614
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2615
--
2616

    
2617
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2618
for use with _map()
2619
';
2620

    
2621

    
2622
--
2623
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2624
--
2625

    
2626
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2627
    LANGUAGE sql IMMUTABLE
2628
    AS $_$
2629
SELECT $2 + COALESCE($1, 0)
2630
$_$;
2631

    
2632

    
2633
--
2634
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
2635
--
2636

    
2637
CREATE FUNCTION parent(table_ regclass) RETURNS regclass
2638
    LANGUAGE sql STABLE
2639
    AS $_$
2640
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
2641
$_$;
2642

    
2643

    
2644
--
2645
-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2646
--
2647

    
2648
CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
2649
    LANGUAGE sql
2650
    AS $_$
2651
SELECT util.eval($$INSERT INTO $$||$1||$$
2652
$$||util.ltrim_nl($2));
2653
-- make sure the created table has the correct estimated row count
2654
SELECT util.analyze_($1);
2655
$_$;
2656

    
2657

    
2658
--
2659
-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2660
--
2661

    
2662
CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
2663
    LANGUAGE sql IMMUTABLE
2664
    AS $_$
2665
SELECT util.qual_name(util.schema($2), $1||util.name($2))
2666
$_$;
2667

    
2668

    
2669
--
2670
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2671
--
2672

    
2673
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
2674
    LANGUAGE sql
2675
    AS $_$
2676
SELECT util.set_comment($1, concat($2, util.comment($1)))
2677
$_$;
2678

    
2679

    
2680
--
2681
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
2682
--
2683

    
2684
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
2685
comment: must start and end with a newline
2686
';
2687

    
2688

    
2689
--
2690
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2691
--
2692

    
2693
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2694
    LANGUAGE sql IMMUTABLE
2695
    AS $_$
2696
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2697
$_$;
2698

    
2699

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

    
2704
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2705
    LANGUAGE sql STABLE
2706
    SET search_path TO pg_temp
2707
    AS $_$
2708
SELECT $1::text
2709
$_$;
2710

    
2711

    
2712
--
2713
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2714
--
2715

    
2716
CREATE FUNCTION qual_name(type regtype) RETURNS text
2717
    LANGUAGE sql STABLE
2718
    SET search_path TO pg_temp
2719
    AS $_$
2720
SELECT $1::text
2721
$_$;
2722

    
2723

    
2724
--
2725
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2726
--
2727

    
2728
COMMENT ON FUNCTION qual_name(type regtype) IS '
2729
a type''s schema-qualified name
2730
';
2731

    
2732

    
2733
--
2734
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2735
--
2736

    
2737
CREATE FUNCTION qual_name(type unknown) RETURNS text
2738
    LANGUAGE sql STABLE
2739
    AS $_$
2740
SELECT util.qual_name($1::text::regtype)
2741
$_$;
2742

    
2743

    
2744
--
2745
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
2746
--
2747

    
2748
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
2749
    LANGUAGE sql IMMUTABLE
2750
    AS $_$
2751
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
2752
$_$;
2753

    
2754

    
2755
--
2756
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
2757
--
2758

    
2759
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
2760
    LANGUAGE sql IMMUTABLE
2761
    AS $_$
2762
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
2763
$_$;
2764

    
2765

    
2766
--
2767
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2768
--
2769

    
2770
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
2771
    LANGUAGE sql IMMUTABLE
2772
    AS $_$
2773
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
2774
$_$;
2775

    
2776

    
2777
--
2778
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
2779
--
2780

    
2781
CREATE FUNCTION raise(type text, msg text) RETURNS void
2782
    LANGUAGE sql IMMUTABLE
2783
    AS $_X$
2784
SELECT util.eval($$
2785
CREATE OR REPLACE FUNCTION pg_temp.__raise()
2786
  RETURNS void AS
2787
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
2788
$__BODY1$
2789
BEGIN
2790
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
2791
END;
2792
$__BODY1$
2793
  LANGUAGE plpgsql IMMUTABLE
2794
  COST 100;
2795
$$, verbose_ := false);
2796

    
2797
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
2798
$_X$;
2799

    
2800

    
2801
--
2802
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
2803
--
2804

    
2805
COMMENT ON FUNCTION raise(type text, msg text) IS '
2806
type: a log level from
2807
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
2808
or a condition name from
2809
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
2810
';
2811

    
2812

    
2813
--
2814
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
2815
--
2816

    
2817
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
2818
    LANGUAGE sql IMMUTABLE
2819
    AS $_$
2820
SELECT util.raise('WARNING', 'ERROR:  '||$1)
2821
$_$;
2822

    
2823

    
2824
--
2825
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
2826
--
2827

    
2828
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
2829
    LANGUAGE plpgsql IMMUTABLE STRICT
2830
    AS $$
2831
BEGIN
2832
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
2833
END;
2834
$$;
2835

    
2836

    
2837
--
2838
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
2839
--
2840

    
2841
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
2842
    LANGUAGE sql IMMUTABLE
2843
    AS $_$
2844
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
2845
$_$;
2846

    
2847

    
2848
--
2849
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
2850
--
2851

    
2852
CREATE FUNCTION regexp_quote(str text) RETURNS text
2853
    LANGUAGE sql IMMUTABLE
2854
    AS $_$
2855
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
2856
$_$;
2857

    
2858

    
2859
--
2860
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
2861
--
2862

    
2863
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
2864
    LANGUAGE sql IMMUTABLE
2865
    AS $_$
2866
SELECT (CASE WHEN right($1, 1) = ')'
2867
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
2868
$_$;
2869

    
2870

    
2871
--
2872
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
2873
--
2874

    
2875
CREATE FUNCTION relation_type(relation regclass) RETURNS text
2876
    LANGUAGE sql STABLE
2877
    AS $_$
2878
SELECT util.relation_type(util.relation_type_char($1))
2879
$_$;
2880

    
2881

    
2882
--
2883
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
2884
--
2885

    
2886
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
2887
    LANGUAGE sql IMMUTABLE
2888
    AS $_$
2889
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
2890
$_$;
2891

    
2892

    
2893
--
2894
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
2895
--
2896

    
2897
CREATE FUNCTION relation_type(type regtype) RETURNS text
2898
    LANGUAGE sql IMMUTABLE
2899
    AS $$
2900
SELECT 'TYPE'::text
2901
$$;
2902

    
2903

    
2904
--
2905
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
2906
--
2907

    
2908
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
2909
    LANGUAGE sql STABLE
2910
    AS $_$
2911
SELECT relkind FROM pg_class WHERE oid = $1
2912
$_$;
2913

    
2914

    
2915
--
2916
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
2917
--
2918

    
2919
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
2920
    LANGUAGE sql
2921
    AS $_$
2922
/* can't have in_table/out_table inherit from *each other*, because inheritance
2923
also causes the rows of the parent table to be included in the child table.
2924
instead, they need to inherit from a common, empty table. */
2925
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
2926
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
2927
SELECT util.inherit($2, $4);
2928
SELECT util.inherit($3, $4);
2929

    
2930
SELECT util.rematerialize_query($1, $$
2931
SELECT * FROM util.diff(
2932
  $$||util.quote_typed($2)||$$
2933
, $$||util.quote_typed($3)||$$
2934
, NULL::$$||$4||$$)
2935
$$);
2936

    
2937
/* the table unfortunately cannot be *materialized* in human-readable form,
2938
because this would create column name collisions between the two sides */
2939
SELECT util.prepend_comment($1, '
2940
to view this table in human-readable form (with each side''s tuple column
2941
expanded to its component fields):
2942
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
2943
');
2944
$_$;
2945

    
2946

    
2947
--
2948
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
2949
--
2950

    
2951
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
2952
type_table (*required*): table to create as the shared base type
2953
';
2954

    
2955

    
2956
--
2957
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2958
--
2959

    
2960
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
2961
    LANGUAGE sql
2962
    AS $_$
2963
SELECT util.drop_table($1);
2964
SELECT util.materialize_query($1, $2);
2965
$_$;
2966

    
2967

    
2968
--
2969
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2970
--
2971

    
2972
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
2973
idempotent, but repeats action each time
2974
';
2975

    
2976

    
2977
--
2978
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2979
--
2980

    
2981
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
2982
    LANGUAGE sql
2983
    AS $_$
2984
SELECT util.drop_table($1);
2985
SELECT util.materialize_view($1, $2);
2986
$_$;
2987

    
2988

    
2989
--
2990
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2991
--
2992

    
2993
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
2994
idempotent, but repeats action each time
2995
';
2996

    
2997

    
2998
--
2999
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
3000
--
3001

    
3002
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
3003
    LANGUAGE sql
3004
    AS $_$
3005
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
3006
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
3007
FROM util.col_names($1::text::regtype) f (name);
3008
SELECT NULL::void; -- don't fold away functions called in previous query
3009
$_$;
3010

    
3011

    
3012
--
3013
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
3014
--
3015

    
3016
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
3017
idempotent
3018
';
3019

    
3020

    
3021
--
3022
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3023
--
3024

    
3025
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
3026
    LANGUAGE sql
3027
    AS $_$
3028
/* use util.qual_name() instead of ::text so that the schema qualifier is always
3029
included in the debug SQL */
3030
SELECT util.rename_relation(util.qual_name($1), $2)
3031
$_$;
3032

    
3033

    
3034
--
3035
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
3036
--
3037

    
3038
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
3039
    LANGUAGE sql
3040
    AS $_$
3041
/* 'ALTER TABLE can be used with views too'
3042
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
3043
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
3044
||quote_ident($2))
3045
$_$;
3046

    
3047

    
3048
--
3049
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
3050
--
3051

    
3052
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
3053
idempotent
3054
';
3055

    
3056

    
3057
--
3058
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3059
--
3060

    
3061
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
3062
    LANGUAGE sql IMMUTABLE
3063
    AS $_$
3064
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
3065
$_$;
3066

    
3067

    
3068
--
3069
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
3070
--
3071

    
3072
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
3073
max_prefix_len: when str may have been truncated (eg. as a table name) due to the prepending of a prefix, support prefixes up to this length 
3074
';
3075

    
3076

    
3077
--
3078
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3079
--
3080

    
3081
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
3082
    LANGUAGE sql
3083
    AS $_$
3084
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
3085
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
3086
SELECT util.set_col_names($1, $2);
3087
$_$;
3088

    
3089

    
3090
--
3091
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3092
--
3093

    
3094
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
3095
idempotent.
3096
alters the names table, so it will need to be repopulated after running this function.
3097
';
3098

    
3099

    
3100
--
3101
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
3102
--
3103

    
3104
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
3105
    LANGUAGE sql
3106
    AS $_$
3107
SELECT util.drop_table($1);
3108
SELECT util.mk_map_table($1);
3109
$_$;
3110

    
3111

    
3112
--
3113
-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
3114
--
3115

    
3116
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
3117
    LANGUAGE sql IMMUTABLE
3118
    AS $_$
3119
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
3120
$_$;
3121

    
3122

    
3123
--
3124
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
3125
--
3126

    
3127
CREATE FUNCTION runnable_sql(sql text) RETURNS text
3128
    LANGUAGE sql IMMUTABLE
3129
    AS $_$
3130
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
3131
ELSE util.mk_set_search_path(for_printing := true)||$$;
3132
$$ END)||$1
3133
$_$;
3134

    
3135

    
3136
--
3137
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
3138
--
3139

    
3140
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
3141
    LANGUAGE plpgsql STRICT
3142
    AS $_$
3143
DECLARE
3144
	result text = NULL;
3145
BEGIN
3146
	BEGIN
3147
		result = util.show_create_view(view_);
3148
		PERFORM util.eval($$DROP VIEW $$||view_);
3149
	EXCEPTION
3150
		WHEN undefined_table THEN NULL;
3151
	END;
3152
	RETURN result;
3153
END;
3154
$_$;
3155

    
3156

    
3157
--
3158
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
3159
--
3160

    
3161
CREATE FUNCTION save_drop_views(views text[]) RETURNS text
3162
    LANGUAGE sql
3163
    AS $_$
3164
SELECT string_agg(util.save_drop_view(unnest), '') FROM unnest($1)
3165
$_$;
3166

    
3167

    
3168
--
3169
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
3170
--
3171

    
3172
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
3173
    LANGUAGE sql STABLE
3174
    AS $_$
3175
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
3176
$_$;
3177

    
3178

    
3179
--
3180
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
3181
--
3182

    
3183
CREATE FUNCTION schema(table_ regclass) RETURNS text
3184
    LANGUAGE sql STABLE
3185
    AS $_$
3186
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
3187
$_$;
3188

    
3189

    
3190
--
3191
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
3192
--
3193

    
3194
CREATE FUNCTION schema(type regtype) RETURNS text
3195
    LANGUAGE sql STABLE
3196
    AS $_$
3197
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
3198
$_$;
3199

    
3200

    
3201
--
3202
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3203
--
3204

    
3205
CREATE FUNCTION schema(type_null anyelement) RETURNS text
3206
    LANGUAGE sql STABLE
3207
    AS $_$
3208
SELECT util.schema(pg_typeof($1))
3209
$_$;
3210

    
3211

    
3212
--
3213
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
3214
--
3215

    
3216
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
3217
    LANGUAGE sql STABLE
3218
    AS $_$
3219
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
3220
$_$;
3221

    
3222

    
3223
--
3224
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
3225
--
3226

    
3227
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
3228
a schema bundle is a group of schemas with a common prefix
3229
';
3230

    
3231

    
3232
--
3233
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3234
--
3235

    
3236
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
3237
    LANGUAGE sql
3238
    AS $_$
3239
SELECT util.schema_rename(old_schema,
3240
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
3241
FROM util.schema_bundle_get_schemas($1) f (old_schema);
3242
SELECT NULL::void; -- don't fold away functions called in previous query
3243
$_$;
3244

    
3245

    
3246
--
3247
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3248
--
3249

    
3250
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
3251
    LANGUAGE plpgsql
3252
    AS $$
3253
BEGIN
3254
	-- don't schema_bundle_rm() the schema_bundle to keep!
3255
	IF replace = with_ THEN RETURN; END IF;
3256
	
3257
	PERFORM util.schema_bundle_rm(replace);
3258
	PERFORM util.schema_bundle_rename(with_, replace);
3259
END;
3260
$$;
3261

    
3262

    
3263
--
3264
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3265
--
3266

    
3267
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
3268
    LANGUAGE sql
3269
    AS $_$
3270
SELECT util.schema_rm(schema)
3271
FROM util.schema_bundle_get_schemas($1) f (schema);
3272
SELECT NULL::void; -- don't fold away functions called in previous query
3273
$_$;
3274

    
3275

    
3276
--
3277
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3278
--
3279

    
3280
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
3281
    LANGUAGE sql STABLE
3282
    AS $_$
3283
SELECT quote_ident(util.schema($1))
3284
$_$;
3285

    
3286

    
3287
--
3288
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
3289
--
3290

    
3291
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
3292
    LANGUAGE sql IMMUTABLE
3293
    AS $_$
3294
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
3295
$_$;
3296

    
3297

    
3298
--
3299
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3300
--
3301

    
3302
CREATE FUNCTION schema_oid(schema text) RETURNS oid
3303
    LANGUAGE sql STABLE
3304
    AS $_$
3305
SELECT oid FROM pg_namespace WHERE nspname = $1
3306
$_$;
3307

    
3308

    
3309
--
3310
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3311
--
3312

    
3313
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
3314
    LANGUAGE sql IMMUTABLE
3315
    AS $_$
3316
SELECT util.schema_regexp(schema_anchor := $1)
3317
$_$;
3318

    
3319

    
3320
--
3321
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3322
--
3323

    
3324
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
3325
    LANGUAGE sql IMMUTABLE
3326
    AS $_$
3327
SELECT util.str_equality_regexp(util.schema($1))
3328
$_$;
3329

    
3330

    
3331
--
3332
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3333
--
3334

    
3335
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
3336
    LANGUAGE sql
3337
    AS $_$
3338
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
3339
$_$;
3340

    
3341

    
3342
--
3343
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3344
--
3345

    
3346
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
3347
    LANGUAGE plpgsql
3348
    AS $$
3349
BEGIN
3350
	-- don't schema_rm() the schema to keep!
3351
	IF replace = with_ THEN RETURN; END IF;
3352
	
3353
	PERFORM util.schema_rm(replace);
3354
	PERFORM util.schema_rename(with_, replace);
3355
END;
3356
$$;
3357

    
3358

    
3359
--
3360
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3361
--
3362

    
3363
CREATE FUNCTION schema_rm(schema text) RETURNS void
3364
    LANGUAGE sql
3365
    AS $_$
3366
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
3367
$_$;
3368

    
3369

    
3370
--
3371
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3372
--
3373

    
3374
CREATE FUNCTION search_path_append(schemas text) RETURNS void
3375
    LANGUAGE sql
3376
    AS $_$
3377
SELECT util.eval(
3378
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
3379
$_$;
3380

    
3381

    
3382
--
3383
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3384
--
3385

    
3386
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
3387
    LANGUAGE plpgsql STRICT
3388
    AS $_$
3389
DECLARE
3390
    old text[] = ARRAY(SELECT util.col_names(table_));
3391
    new text[] = ARRAY(SELECT util.map_values(names));
3392
BEGIN
3393
    old = old[1:array_length(new, 1)]; -- truncate to same length
3394
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
3395
||$$ TO $$||quote_ident(value))
3396
    FROM each(hstore(old, new))
3397
    WHERE value != key -- not same name
3398
    ;
3399
END;
3400
$_$;
3401

    
3402

    
3403
--
3404
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3405
--
3406

    
3407
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
3408
idempotent
3409
';
3410

    
3411

    
3412
--
3413
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3414
--
3415

    
3416
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
3417
    LANGUAGE plpgsql STRICT
3418
    AS $_$
3419
DECLARE
3420
	row_ util.map;
3421
BEGIN
3422
	-- rename any metadata cols rather than re-adding them with new names
3423
	BEGIN
3424
		PERFORM util.set_col_names(table_, names);
3425
	EXCEPTION
3426
		WHEN array_subscript_error THEN -- selective suppress
3427
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
3428
				-- metadata cols not yet added
3429
			ELSE RAISE;
3430
			END IF;
3431
	END;
3432
	
3433
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
3434
	LOOP
3435
		PERFORM util.mk_const_col((table_, row_."to"),
3436
			substring(row_."from" from 2));
3437
	END LOOP;
3438
	
3439
	PERFORM util.set_col_names(table_, names);
3440
END;
3441
$_$;
3442

    
3443

    
3444
--
3445
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3446
--
3447

    
3448
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
3449
idempotent.
3450
the metadata mappings must be *last* in the names table.
3451
';
3452

    
3453

    
3454
--
3455
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
3456
--
3457

    
3458
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
3459
    LANGUAGE sql
3460
    AS $_$
3461
SELECT util.eval(COALESCE(
3462
$$ALTER TABLE $$||$1||$$
3463
$$||(
3464
	SELECT
3465
	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
3466
	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
3467
, $$)
3468
	FROM
3469
	(
3470
		SELECT
3471
		  quote_ident(col_name) AS col_name_sql
3472
		, util.col_type(($1, col_name)) AS curr_type
3473
		, type AS target_type
3474
		FROM unnest($2)
3475
	) s
3476
	WHERE curr_type != target_type
3477
), ''))
3478
$_$;
3479

    
3480

    
3481
--
3482
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
3483
--
3484

    
3485
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
3486
idempotent
3487
';
3488

    
3489

    
3490
--
3491
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3492
--
3493

    
3494
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
3495
    LANGUAGE sql
3496
    AS $_$
3497
SELECT util.eval($$COMMENT ON TABLE $$||$1||$$ IS $$||quote_nullable($2))
3498
$_$;
3499

    
3500

    
3501
--
3502
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
3503
--
3504

    
3505
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
3506
    LANGUAGE sql
3507
    AS $_$
3508
SELECT util.eval(util.mk_set_search_path($1, $2))
3509
$_$;
3510

    
3511

    
3512
--
3513
-- Name: show_create_view(regclass); Type: FUNCTION; Schema: util; Owner: -
3514
--
3515

    
3516
CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
3517
    LANGUAGE sql STABLE
3518
    AS $_$
3519
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
3520
$$||util.show_grants_for($1)
3521
$_$;
3522

    
3523

    
3524
--
3525
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
3526
--
3527

    
3528
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
3529
    LANGUAGE sql STABLE
3530
    AS $_$
3531
SELECT string_agg(cmd, '')
3532
FROM
3533
(
3534
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
3535
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
3536
$$ ELSE '' END) AS cmd
3537
	FROM util.grants_users() f (user_)
3538
) s
3539
$_$;
3540

    
3541

    
3542
--
3543
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
3544
--
3545

    
3546
CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['c'::text, 'r'::text, 'v'::text]) RETURNS SETOF regclass
3547
    LANGUAGE sql STABLE
3548
    AS $_$
3549
SELECT oid FROM pg_class
3550
WHERE relkind = ANY($3) AND relname ~ $1
3551
AND util.schema_matches(util.schema(relnamespace), $2)
3552
ORDER BY relname
3553
$_$;
3554

    
3555

    
3556
--
3557
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3558
--
3559

    
3560
CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
3561
    LANGUAGE sql STABLE
3562
    AS $_$
3563
SELECT oid
3564
FROM pg_type
3565
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
3566
ORDER BY typname
3567
$_$;
3568

    
3569

    
3570
--
3571
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3572
--
3573

    
3574
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
3575
    LANGUAGE sql STABLE
3576
    AS $_$
3577
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
3578
$_$;
3579

    
3580

    
3581
--
3582
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
3583
--
3584

    
3585
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
3586
    LANGUAGE sql IMMUTABLE
3587
    AS $_$
3588
SELECT '^'||util.regexp_quote($1)||'$'
3589
$_$;
3590

    
3591

    
3592
--
3593
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3594
--
3595

    
3596
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
3597
    LANGUAGE plpgsql STABLE STRICT
3598
    AS $_$
3599
DECLARE
3600
    hstore hstore;
3601
BEGIN
3602
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
3603
        table_||$$))$$ INTO STRICT hstore;
3604
    RETURN hstore;
3605
END;
3606
$_$;
3607

    
3608

    
3609
--
3610
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3611
--
3612

    
3613
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
3614
    LANGUAGE sql STABLE
3615
    AS $_$
3616
SELECT COUNT(*) > 0 FROM pg_constraint
3617
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
3618
$_$;
3619

    
3620

    
3621
--
3622
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3623
--
3624

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

    
3629

    
3630
--
3631
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3632
--
3633

    
3634
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
3635
    LANGUAGE sql
3636
    AS $_$
3637
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
3638
||quote_ident($2)||$$ CHECK (true)$$)
3639
$_$;
3640

    
3641

    
3642
--
3643
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3644
--
3645

    
3646
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
3647
stores a status flag by the presence of a table constraint.
3648
idempotent.
3649
';
3650

    
3651

    
3652
--
3653
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3654
--
3655

    
3656
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
3657
    LANGUAGE sql STABLE
3658
    AS $_$
3659
SELECT util.table_flag__get($1, 'nulls_mapped')
3660
$_$;
3661

    
3662

    
3663
--
3664
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3665
--
3666

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

    
3671

    
3672
--
3673
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3674
--
3675

    
3676
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
3677
    LANGUAGE sql
3678
    AS $_$
3679
SELECT util.table_flag__set($1, 'nulls_mapped')
3680
$_$;
3681

    
3682

    
3683
--
3684
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3685
--
3686

    
3687
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
3688
sets that a table''s NULL-equivalent strings have been replaced with NULL.
3689
idempotent.
3690
';
3691

    
3692

    
3693
--
3694
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
3695
--
3696

    
3697
CREATE FUNCTION to_freq(table_ regclass) RETURNS void
3698
    LANGUAGE sql
3699
    AS $_$
3700
-- save data before truncating main table
3701
SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
3702

    
3703
-- repopulate main table w/ copies column
3704
SELECT util.truncate($1);
3705
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
3706
SELECT util.populate_table($1, $$
3707
SELECT (table_).*, copies
3708
FROM (
3709
	SELECT table_, COUNT(*) AS copies
3710
	FROM pg_temp.__copy table_
3711
	GROUP BY table_
3712
) s
3713
$$);
3714

    
3715
-- delete temp table so it doesn't stay around until end of connection
3716
SELECT util.drop_table('pg_temp.__copy');
3717
$_$;
3718

    
3719

    
3720
--
3721
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
3722
--
3723

    
3724
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
3725
    LANGUAGE plpgsql STRICT
3726
    AS $_$
3727
DECLARE
3728
    row record;
3729
BEGIN
3730
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
3731
    LOOP
3732
        IF row.global_name != row.name THEN
3733
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
3734
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
3735
        END IF;
3736
    END LOOP;
3737
END;
3738
$_$;
3739

    
3740

    
3741
--
3742
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3743
--
3744

    
3745
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
3746
idempotent
3747
';
3748

    
3749

    
3750
--
3751
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3752
--
3753

    
3754
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
3755
    LANGUAGE sql
3756
    AS $_$
3757
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
3758
SELECT NULL::void; -- don't fold away functions called in previous query
3759
$_$;
3760

    
3761

    
3762
--
3763
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3764
--
3765

    
3766
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
3767
trims table_ to include only columns in the original data.
3768
idempotent.
3769
';
3770

    
3771

    
3772
--
3773
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
3774
--
3775

    
3776
CREATE FUNCTION truncate(table_ regclass) RETURNS void
3777
    LANGUAGE plpgsql STRICT
3778
    AS $_$
3779
BEGIN
3780
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
3781
END;
3782
$_$;
3783

    
3784

    
3785
--
3786
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3787
--
3788

    
3789
COMMENT ON FUNCTION truncate(table_ regclass) IS '
3790
idempotent
3791
';
3792

    
3793

    
3794
--
3795
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
3796
--
3797

    
3798
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
3799
    LANGUAGE sql IMMUTABLE
3800
    AS $_$
3801
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
3802
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
3803
$_$;
3804

    
3805

    
3806
--
3807
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
3808
--
3809

    
3810
CREATE FUNCTION try_create(sql text) RETURNS void
3811
    LANGUAGE plpgsql STRICT
3812
    AS $$
3813
BEGIN
3814
	PERFORM util.eval(sql);
3815
EXCEPTION
3816
WHEN   not_null_violation
3817
		/* trying to add NOT NULL column to parent table, which cascades to
3818
		child table whose values for the new column will be NULL */
3819
	OR wrong_object_type -- trying to alter a view's columns
3820
	OR undefined_column
3821
	OR duplicate_column
3822
THEN NULL;
3823
WHEN datatype_mismatch THEN
3824
	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
3825
	ELSE RAISE; -- rethrow
3826
	END IF;
3827
END;
3828
$$;
3829

    
3830

    
3831
--
3832
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
3833
--
3834

    
3835
COMMENT ON FUNCTION try_create(sql text) IS '
3836
idempotent
3837
';
3838

    
3839

    
3840
--
3841
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
3842
--
3843

    
3844
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
3845
    LANGUAGE sql
3846
    AS $_$
3847
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
3848
$_$;
3849

    
3850

    
3851
--
3852
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
3853
--
3854

    
3855
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
3856
idempotent
3857
';
3858

    
3859

    
3860
--
3861
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
3862
--
3863

    
3864
CREATE FUNCTION type_qual(value anyelement) RETURNS text
3865
    LANGUAGE sql IMMUTABLE
3866
    AS $_$
3867
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
3868
$_$;
3869

    
3870

    
3871
--
3872
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
3873
--
3874

    
3875
COMMENT ON FUNCTION type_qual(value anyelement) IS '
3876
a type''s NOT NULL qualifier
3877
';
3878

    
3879

    
3880
--
3881
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
3882
--
3883

    
3884
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
3885
    LANGUAGE sql STABLE
3886
    AS $_$
3887
SELECT (attname::text, atttypid)::util.col_cast
3888
FROM pg_attribute
3889
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
3890
ORDER BY attnum
3891
$_$;
3892

    
3893

    
3894
--
3895
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
3896
--
3897

    
3898
CREATE FUNCTION typeof(value anyelement) RETURNS text
3899
    LANGUAGE sql IMMUTABLE
3900
    AS $_$
3901
SELECT util.qual_name(pg_typeof($1))
3902
$_$;
3903

    
3904

    
3905
--
3906
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
3907
--
3908

    
3909
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
3910
    LANGUAGE plpgsql STABLE
3911
    AS $_$
3912
DECLARE
3913
    type regtype;
3914
BEGIN
3915
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
3916
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
3917
    RETURN type;
3918
END;
3919
$_$;
3920

    
3921

    
3922
--
3923
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3924
--
3925

    
3926
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
3927
    LANGUAGE sql
3928
    AS $_$
3929
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
3930
$_$;
3931

    
3932

    
3933
--
3934
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
3935
--
3936

    
3937
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
3938
auto-appends util to the search_path to enable use of util operators
3939
';
3940

    
3941

    
3942
--
3943
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
3944
--
3945

    
3946
CREATE AGGREGATE all_same(anyelement) (
3947
    SFUNC = all_same_transform,
3948
    STYPE = anyarray,
3949
    FINALFUNC = all_same_final
3950
);
3951

    
3952

    
3953
--
3954
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
3955
--
3956

    
3957
COMMENT ON AGGREGATE all_same(anyelement) IS '
3958
includes NULLs in comparison
3959
';
3960

    
3961

    
3962
--
3963
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
3964
--
3965

    
3966
CREATE AGGREGATE join_strs(text, text) (
3967
    SFUNC = join_strs_transform,
3968
    STYPE = text
3969
);
3970

    
3971

    
3972
--
3973
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
3974
--
3975

    
3976
CREATE OPERATOR %== (
3977
    PROCEDURE = "%==",
3978
    LEFTARG = anyelement,
3979
    RIGHTARG = anyelement
3980
);
3981

    
3982

    
3983
--
3984
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
3985
--
3986

    
3987
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
3988
returns whether the map-keys of the compared values are the same
3989
(mnemonic: % is the Perl symbol for a hash map)
3990

    
3991
should be overridden for types that store both keys and values
3992

    
3993
used in a FULL JOIN to select which columns to join on
3994
';
3995

    
3996

    
3997
--
3998
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
3999
--
4000

    
4001
CREATE OPERATOR -> (
4002
    PROCEDURE = map_get,
4003
    LEFTARG = regclass,
4004
    RIGHTARG = text
4005
);
4006

    
4007

    
4008
--
4009
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
4010
--
4011

    
4012
CREATE OPERATOR => (
4013
    PROCEDURE = hstore,
4014
    LEFTARG = text[],
4015
    RIGHTARG = text
4016
);
4017

    
4018

    
4019
--
4020
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
4021
--
4022

    
4023
COMMENT ON OPERATOR => (text[], text) IS '
4024
usage: array[''key1'', ...]::text[] => ''value''
4025
';
4026

    
4027

    
4028
--
4029
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
4030
--
4031

    
4032
CREATE OPERATOR ?*>= (
4033
    PROCEDURE = is_populated_more_often_than,
4034
    LEFTARG = anyelement,
4035
    RIGHTARG = anyelement
4036
);
4037

    
4038

    
4039
--
4040
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
4041
--
4042

    
4043
CREATE OPERATOR ?>= (
4044
    PROCEDURE = is_more_complete_than,
4045
    LEFTARG = anyelement,
4046
    RIGHTARG = anyelement
4047
);
4048

    
4049

    
4050
--
4051
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
4052
--
4053

    
4054
CREATE OPERATOR ||% (
4055
    PROCEDURE = concat_esc,
4056
    LEFTARG = text,
4057
    RIGHTARG = text
4058
);
4059

    
4060

    
4061
--
4062
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
4063
--
4064

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

    
4069

    
4070
--
4071
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
4072
--
4073

    
4074
CREATE TABLE map (
4075
    "from" text NOT NULL,
4076
    "to" text,
4077
    filter text,
4078
    notes text
4079
);
4080

    
4081

    
4082
--
4083
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
4084
--
4085

    
4086

    
4087

    
4088
--
4089
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
4090
--
4091

    
4092

    
4093

    
4094
--
4095
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4096
--
4097

    
4098
ALTER TABLE ONLY map
4099
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
4100

    
4101

    
4102
--
4103
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4104
--
4105

    
4106
ALTER TABLE ONLY map
4107
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
4108

    
4109

    
4110
--
4111
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
4112
--
4113

    
4114
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
4115

    
4116

    
4117
--
4118
-- PostgreSQL database dump complete
4119
--
4120

    
(19-19/29)