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 COALESCE($$
1587
EXPLAIN:
1588
$$||util.fold_explain_msg(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 '
1889
constant
1890
%', false)
1891
$_$;
1892

    
1893

    
1894
--
1895
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1896
--
1897

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

    
1904

    
1905
--
1906
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
1907
--
1908

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

    
1915

    
1916
--
1917
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
1918
--
1919

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

    
1929

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

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

    
1940

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

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

    
1951

    
1952
--
1953
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
1954
--
1955

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

    
1962

    
1963
--
1964
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
1965
--
1966

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

    
1973

    
1974
--
1975
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
1976
--
1977

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

    
1984

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

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

    
1995

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

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

    
2004

    
2005
--
2006
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
2007
--
2008

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

    
2015

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

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

    
2026

    
2027
--
2028
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
2029
--
2030

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

    
2038

    
2039
--
2040
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
2041
--
2042

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

    
2052

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

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

    
2069

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

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

    
2080

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

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

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

    
2097

    
2098
--
2099
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
2100
--
2101

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

    
2110

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

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

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

    
2129

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

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

    
2138

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

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

    
2149

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

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

    
2158

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

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

    
2176

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

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

    
2185

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

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

    
2205

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

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

    
2214

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

    
2219
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
2220
    LANGUAGE sql IMMUTABLE
2221
    AS $_$
2222
SELECT
2223
$$SELECT
2224
$$||$3||$$
2225
FROM      $$||$1||$$ left_
2226
FULL JOIN $$||$2||$$ right_
2227
ON $$||$4||$$
2228
WHERE $$||$5||$$
2229
ORDER BY left_, right_
2230
$$
2231
$_$;
2232

    
2233

    
2234
--
2235
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2236
--
2237

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

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

    
2257

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

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

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

    
2276

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

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

    
2298

    
2299
--
2300
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2301
--
2302

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

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

    
2320

    
2321
--
2322
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
2323
--
2324

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

    
2331

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

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

    
2344

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

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

    
2355

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

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

    
2364

    
2365
--
2366
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2367
--
2368

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

    
2375

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

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

    
2390

    
2391
--
2392
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2393
--
2394

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

    
2401

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

    
2406
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2407
idempotent
2408
';
2409

    
2410

    
2411
--
2412
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2413
--
2414

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

    
2438

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

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

    
2482

    
2483
--
2484
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2485
--
2486

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

    
2515

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

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

    
2524

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

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

    
2537

    
2538
--
2539
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2540
--
2541

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

    
2548

    
2549
--
2550
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2551
--
2552

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

    
2559

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

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

    
2570

    
2571
--
2572
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2573
--
2574

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

    
2581

    
2582
--
2583
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2584
--
2585

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

    
2592

    
2593
--
2594
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2595
--
2596

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

    
2603

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

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

    
2614

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

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

    
2623

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

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

    
2634

    
2635
--
2636
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
2637
--
2638

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

    
2645

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

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

    
2659

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

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

    
2670

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

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

    
2681

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

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

    
2690

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

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

    
2701

    
2702
--
2703
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2704
--
2705

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

    
2713

    
2714
--
2715
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2716
--
2717

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

    
2725

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

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

    
2734

    
2735
--
2736
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2737
--
2738

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

    
2745

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

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

    
2756

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

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

    
2767

    
2768
--
2769
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2770
--
2771

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

    
2778

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

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

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

    
2802

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

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

    
2814

    
2815
--
2816
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
2817
--
2818

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

    
2825

    
2826
--
2827
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
2828
--
2829

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

    
2838

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

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

    
2849

    
2850
--
2851
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
2852
--
2853

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

    
2860

    
2861
--
2862
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
2863
--
2864

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

    
2872

    
2873
--
2874
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
2875
--
2876

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

    
2883

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

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

    
2894

    
2895
--
2896
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
2897
--
2898

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

    
2905

    
2906
--
2907
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
2908
--
2909

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

    
2916

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

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

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

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

    
2948

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

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

    
2957

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

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

    
2969

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

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

    
2978

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

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

    
2990

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

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

    
2999

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

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

    
3013

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

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

    
3022

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

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

    
3035

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

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

    
3049

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

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

    
3058

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

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

    
3069

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

    
3074
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
3075
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 
3076
';
3077

    
3078

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

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

    
3091

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

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

    
3101

    
3102
--
3103
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
3104
--
3105

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

    
3113

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

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

    
3124

    
3125
--
3126
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
3127
--
3128

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

    
3137

    
3138
--
3139
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
3140
--
3141

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

    
3158

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

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

    
3169

    
3170
--
3171
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
3172
--
3173

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

    
3180

    
3181
--
3182
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
3183
--
3184

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

    
3191

    
3192
--
3193
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
3194
--
3195

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

    
3202

    
3203
--
3204
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3205
--
3206

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

    
3213

    
3214
--
3215
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
3216
--
3217

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

    
3224

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

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

    
3233

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

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

    
3247

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

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

    
3264

    
3265
--
3266
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3267
--
3268

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

    
3277

    
3278
--
3279
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3280
--
3281

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

    
3288

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

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

    
3299

    
3300
--
3301
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3302
--
3303

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

    
3310

    
3311
--
3312
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3313
--
3314

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

    
3321

    
3322
--
3323
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3324
--
3325

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

    
3332

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

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

    
3343

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

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

    
3360

    
3361
--
3362
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3363
--
3364

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

    
3371

    
3372
--
3373
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3374
--
3375

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

    
3383

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

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

    
3404

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

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

    
3413

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

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

    
3445

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

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

    
3455

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

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

    
3482

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

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

    
3491

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

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

    
3502

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

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

    
3513

    
3514
--
3515
-- Name: show_create_view(regclass); Type: FUNCTION; Schema: util; Owner: -
3516
--
3517

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

    
3525

    
3526
--
3527
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
3528
--
3529

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

    
3543

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

    
3548
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
3549
    LANGUAGE sql STABLE
3550
    AS $_$
3551
SELECT oid FROM pg_class
3552
WHERE relkind = ANY($3) AND relname ~ $1
3553
AND util.schema_matches(util.schema(relnamespace), $2)
3554
ORDER BY relname
3555
$_$;
3556

    
3557

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

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

    
3571

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

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

    
3582

    
3583
--
3584
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
3585
--
3586

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

    
3593

    
3594
--
3595
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3596
--
3597

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

    
3610

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

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

    
3622

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

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

    
3631

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

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

    
3643

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

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

    
3653

    
3654
--
3655
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3656
--
3657

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

    
3664

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

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

    
3673

    
3674
--
3675
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3676
--
3677

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

    
3684

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

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

    
3694

    
3695
--
3696
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
3697
--
3698

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

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

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

    
3721

    
3722
--
3723
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
3724
--
3725

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

    
3742

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

    
3747
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
3748
idempotent
3749
';
3750

    
3751

    
3752
--
3753
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
3754
--
3755

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

    
3763

    
3764
--
3765
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
3766
--
3767

    
3768
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass, force boolean) IS '
3769
trims table_ to include only columns in the original data
3770

    
3771
by default, cascadingly drops dependent columns so that they don''t prevent
3772
trim() from succeeding. note that this requires the dependent columns to then be
3773
manually re-created.
3774

    
3775
idempotent
3776
';
3777

    
3778

    
3779
--
3780
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
3781
--
3782

    
3783
CREATE FUNCTION truncate(table_ regclass) RETURNS void
3784
    LANGUAGE plpgsql STRICT
3785
    AS $_$
3786
BEGIN
3787
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
3788
END;
3789
$_$;
3790

    
3791

    
3792
--
3793
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3794
--
3795

    
3796
COMMENT ON FUNCTION truncate(table_ regclass) IS '
3797
idempotent
3798
';
3799

    
3800

    
3801
--
3802
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
3803
--
3804

    
3805
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
3806
    LANGUAGE sql IMMUTABLE
3807
    AS $_$
3808
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
3809
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
3810
$_$;
3811

    
3812

    
3813
--
3814
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
3815
--
3816

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

    
3837

    
3838
--
3839
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
3840
--
3841

    
3842
COMMENT ON FUNCTION try_create(sql text) IS '
3843
idempotent
3844
';
3845

    
3846

    
3847
--
3848
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
3849
--
3850

    
3851
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
3852
    LANGUAGE sql
3853
    AS $_$
3854
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
3855
$_$;
3856

    
3857

    
3858
--
3859
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
3860
--
3861

    
3862
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
3863
idempotent
3864
';
3865

    
3866

    
3867
--
3868
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
3869
--
3870

    
3871
CREATE FUNCTION type_qual(value anyelement) RETURNS text
3872
    LANGUAGE sql IMMUTABLE
3873
    AS $_$
3874
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
3875
$_$;
3876

    
3877

    
3878
--
3879
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
3880
--
3881

    
3882
COMMENT ON FUNCTION type_qual(value anyelement) IS '
3883
a type''s NOT NULL qualifier
3884
';
3885

    
3886

    
3887
--
3888
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
3889
--
3890

    
3891
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
3892
    LANGUAGE sql STABLE
3893
    AS $_$
3894
SELECT (attname::text, atttypid)::util.col_cast
3895
FROM pg_attribute
3896
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
3897
ORDER BY attnum
3898
$_$;
3899

    
3900

    
3901
--
3902
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
3903
--
3904

    
3905
CREATE FUNCTION typeof(value anyelement) RETURNS text
3906
    LANGUAGE sql IMMUTABLE
3907
    AS $_$
3908
SELECT util.qual_name(pg_typeof($1))
3909
$_$;
3910

    
3911

    
3912
--
3913
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
3914
--
3915

    
3916
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
3917
    LANGUAGE plpgsql STABLE
3918
    AS $_$
3919
DECLARE
3920
    type regtype;
3921
BEGIN
3922
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
3923
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
3924
    RETURN type;
3925
END;
3926
$_$;
3927

    
3928

    
3929
--
3930
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3931
--
3932

    
3933
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
3934
    LANGUAGE sql
3935
    AS $_$
3936
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
3937
$_$;
3938

    
3939

    
3940
--
3941
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
3942
--
3943

    
3944
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
3945
auto-appends util to the search_path to enable use of util operators
3946
';
3947

    
3948

    
3949
--
3950
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
3951
--
3952

    
3953
CREATE AGGREGATE all_same(anyelement) (
3954
    SFUNC = all_same_transform,
3955
    STYPE = anyarray,
3956
    FINALFUNC = all_same_final
3957
);
3958

    
3959

    
3960
--
3961
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
3962
--
3963

    
3964
COMMENT ON AGGREGATE all_same(anyelement) IS '
3965
includes NULLs in comparison
3966
';
3967

    
3968

    
3969
--
3970
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
3971
--
3972

    
3973
CREATE AGGREGATE join_strs(text, text) (
3974
    SFUNC = join_strs_transform,
3975
    STYPE = text
3976
);
3977

    
3978

    
3979
--
3980
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
3981
--
3982

    
3983
CREATE OPERATOR %== (
3984
    PROCEDURE = "%==",
3985
    LEFTARG = anyelement,
3986
    RIGHTARG = anyelement
3987
);
3988

    
3989

    
3990
--
3991
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
3992
--
3993

    
3994
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
3995
returns whether the map-keys of the compared values are the same
3996
(mnemonic: % is the Perl symbol for a hash map)
3997

    
3998
should be overridden for types that store both keys and values
3999

    
4000
used in a FULL JOIN to select which columns to join on
4001
';
4002

    
4003

    
4004
--
4005
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
4006
--
4007

    
4008
CREATE OPERATOR -> (
4009
    PROCEDURE = map_get,
4010
    LEFTARG = regclass,
4011
    RIGHTARG = text
4012
);
4013

    
4014

    
4015
--
4016
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
4017
--
4018

    
4019
CREATE OPERATOR => (
4020
    PROCEDURE = hstore,
4021
    LEFTARG = text[],
4022
    RIGHTARG = text
4023
);
4024

    
4025

    
4026
--
4027
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
4028
--
4029

    
4030
COMMENT ON OPERATOR => (text[], text) IS '
4031
usage: array[''key1'', ...]::text[] => ''value''
4032
';
4033

    
4034

    
4035
--
4036
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
4037
--
4038

    
4039
CREATE OPERATOR ?*>= (
4040
    PROCEDURE = is_populated_more_often_than,
4041
    LEFTARG = anyelement,
4042
    RIGHTARG = anyelement
4043
);
4044

    
4045

    
4046
--
4047
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
4048
--
4049

    
4050
CREATE OPERATOR ?>= (
4051
    PROCEDURE = is_more_complete_than,
4052
    LEFTARG = anyelement,
4053
    RIGHTARG = anyelement
4054
);
4055

    
4056

    
4057
--
4058
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
4059
--
4060

    
4061
CREATE OPERATOR ||% (
4062
    PROCEDURE = concat_esc,
4063
    LEFTARG = text,
4064
    RIGHTARG = text
4065
);
4066

    
4067

    
4068
--
4069
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
4070
--
4071

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

    
4076

    
4077
--
4078
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
4079
--
4080

    
4081
CREATE TABLE map (
4082
    "from" text NOT NULL,
4083
    "to" text,
4084
    filter text,
4085
    notes text
4086
);
4087

    
4088

    
4089
--
4090
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
4091
--
4092

    
4093

    
4094

    
4095
--
4096
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
4097
--
4098

    
4099

    
4100

    
4101
--
4102
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4103
--
4104

    
4105
ALTER TABLE ONLY map
4106
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
4107

    
4108

    
4109
--
4110
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4111
--
4112

    
4113
ALTER TABLE ONLY map
4114
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
4115

    
4116

    
4117
--
4118
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
4119
--
4120

    
4121
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
4122

    
4123

    
4124
--
4125
-- PostgreSQL database dump complete
4126
--
4127

    
(20-20/30)