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: db_item; Type: TYPE; Schema: util; Owner: -
76
--
77

    
78
CREATE TYPE db_item AS (
79
	path text,
80
	def text
81
);
82

    
83

    
84
--
85
-- Name: range; Type: TYPE; Schema: util; Owner: -
86
--
87

    
88
CREATE TYPE range AS (
89
	lower numeric,
90
	upper numeric,
91
	bounds text
92
);
93

    
94

    
95
--
96
-- Name: TYPE range; Type: COMMENT; Schema: util; Owner: -
97
--
98

    
99
COMMENT ON TYPE range IS '
100
allows wraparound ranges (which use a modulus system such as geocoordinates)
101
';
102

    
103

    
104
--
105
-- Name: restore_views_info; Type: TYPE; Schema: util; Owner: -
106
--
107

    
108
CREATE TYPE restore_views_info AS (
109
	views db_item[]
110
);
111

    
112

    
113
--
114
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
115
--
116

    
117
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
118
    LANGUAGE sql IMMUTABLE
119
    AS $_$
120
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
121
$_$;
122

    
123

    
124
--
125
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
126
--
127

    
128
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
129
    LANGUAGE sql IMMUTABLE
130
    AS $_$
131
SELECT bool_and(value)
132
FROM
133
(VALUES
134
      ($1)
135
    , ($2)
136
    , ($3)
137
    , ($4)
138
    , ($5)
139
)
140
AS v (value)
141
$_$;
142

    
143

    
144
--
145
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
146
--
147

    
148
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
149
_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.
150
';
151

    
152

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

    
157
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
158
    LANGUAGE sql IMMUTABLE
159
    AS $_$
160
SELECT avg(value)
161
FROM
162
(VALUES
163
      ($1)
164
    , ($2)
165
    , ($3)
166
    , ($4)
167
    , ($5)
168
)
169
AS v (value)
170
$_$;
171

    
172

    
173
--
174
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
175
--
176

    
177
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
178
    LANGUAGE sql IMMUTABLE
179
    AS $_$
180
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)
181
FROM 
182
(
183
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
184
    UNION ALL
185
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
186
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
187
)
188
matches (g)
189
$_$;
190

    
191

    
192
--
193
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
194
--
195

    
196
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
197
    LANGUAGE sql IMMUTABLE
198
    AS $_$
199
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
200
FROM
201
(VALUES
202
      ($1)
203
    , ($2/60)
204
    , ($3/60/60)
205
)
206
AS v (value)
207
$_$;
208

    
209

    
210
--
211
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
212
--
213

    
214
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
215
    LANGUAGE sql IMMUTABLE
216
    AS $_$
217
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
218
$_$;
219

    
220

    
221
--
222
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
223
--
224

    
225
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
226
    LANGUAGE sql IMMUTABLE
227
    AS $_$
228
SELECT $1 = $2
229
$_$;
230

    
231

    
232
--
233
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
234
--
235

    
236
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
237
    LANGUAGE sql IMMUTABLE
238
    AS $_$
239
-- Fix dates after threshold date
240
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
241
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
242
$_$;
243

    
244

    
245
--
246
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
247
--
248

    
249
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
250
    LANGUAGE sql IMMUTABLE
251
    AS $_$
252
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
253
$_$;
254

    
255

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

    
260
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
261
    LANGUAGE sql IMMUTABLE
262
    AS $_$
263
SELECT util._if($1 != '', $2, $3)
264
$_$;
265

    
266

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

    
271
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
272
    LANGUAGE sql IMMUTABLE
273
    AS $_$
274
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
275
$_$;
276

    
277

    
278
--
279
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
280
--
281

    
282
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
283
    LANGUAGE sql IMMUTABLE
284
    AS $_$
285
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
286
$_$;
287

    
288

    
289
--
290
-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: util; Owner: -
291
--
292

    
293
CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision
294
    LANGUAGE sql IMMUTABLE
295
    AS $_$
296
SELECT $1*1000.
297
$_$;
298

    
299

    
300
--
301
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
302
--
303

    
304
CREATE FUNCTION _label(label text, value text) RETURNS text
305
    LANGUAGE sql IMMUTABLE
306
    AS $_$
307
SELECT coalesce($1 || ': ', '') || $2
308
$_$;
309

    
310

    
311
--
312
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
313
--
314

    
315
CREATE FUNCTION _lowercase(value text) RETURNS text
316
    LANGUAGE sql IMMUTABLE
317
    AS $_$
318
SELECT lower($1)
319
$_$;
320

    
321

    
322
--
323
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
324
--
325

    
326
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
327
    LANGUAGE plpgsql IMMUTABLE STRICT
328
    AS $$
329
DECLARE
330
    result value%TYPE := util._map(map, value::text)::unknown;
331
BEGIN
332
    RETURN result;
333
END;
334
$$;
335

    
336

    
337
--
338
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
339
--
340

    
341
CREATE FUNCTION _map(map hstore, value text) RETURNS text
342
    LANGUAGE plpgsql IMMUTABLE STRICT
343
    AS $$
344
DECLARE
345
    match text := map -> value;
346
BEGIN
347
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
348
        match := map -> '*'; -- use default entry
349
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
350
        END IF;
351
    END IF;
352
    
353
    -- Interpret result
354
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
355
    ELSIF match = '*' THEN RETURN value;
356
    ELSE RETURN match;
357
    END IF;
358
END;
359
$$;
360

    
361

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

    
366
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
367
    LANGUAGE sql IMMUTABLE
368
    AS $_$
369
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
370
$_$;
371

    
372

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

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

    
411

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

    
416
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
417
    LANGUAGE sql IMMUTABLE
418
    AS $_$
419
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
420
$_$;
421

    
422

    
423
--
424
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
425
--
426

    
427
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
428
    LANGUAGE sql IMMUTABLE
429
    AS $_$
430
SELECT util.join_strs(value, ' ')
431
FROM
432
(
433
    SELECT *
434
    FROM
435
    (
436
        SELECT
437
        DISTINCT ON (value)
438
        *
439
        FROM
440
        (VALUES
441
              (1, $1)
442
            , (2, $2)
443
            , (3, $3)
444
            , (4, $4)
445
            , (5, $5)
446
            , (6, $6)
447
            , (7, $7)
448
            , (8, $8)
449
            , (9, $9)
450
            , (10, $10)
451
        )
452
        AS v (sort_order, value)
453
        WHERE value IS NOT NULL
454
    )
455
    AS v
456
    ORDER BY sort_order
457
)
458
AS v
459
$_$;
460

    
461

    
462
--
463
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
464
--
465

    
466
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
467
    LANGUAGE sql IMMUTABLE
468
    AS $_$
469
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
470
$_$;
471

    
472

    
473
--
474
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
475
--
476

    
477
CREATE FUNCTION _not(value boolean) RETURNS boolean
478
    LANGUAGE sql IMMUTABLE
479
    AS $_$
480
SELECT NOT $1
481
$_$;
482

    
483

    
484
--
485
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
486
--
487

    
488
CREATE FUNCTION _now() RETURNS timestamp with time zone
489
    LANGUAGE sql STABLE
490
    AS $$
491
SELECT now()
492
$$;
493

    
494

    
495
--
496
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
497
--
498

    
499
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
500
    LANGUAGE sql IMMUTABLE
501
    AS $_$
502
SELECT util."_nullIf"($1, $2, $3::util.datatype)
503
$_$;
504

    
505

    
506
--
507
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
508
--
509

    
510
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
511
    LANGUAGE plpgsql IMMUTABLE
512
    AS $$
513
DECLARE
514
    type util.datatype NOT NULL := type; -- add NOT NULL
515
BEGIN
516
    IF type = 'str' THEN RETURN nullif(value::text, "null");
517
    -- Invalid value is ignored, but invalid null value generates error
518
    ELSIF type = 'float' THEN
519
        DECLARE
520
            -- Outside the try block so that invalid null value generates error
521
            "null" double precision := "null"::double precision;
522
        BEGIN
523
            RETURN nullif(value::double precision, "null");
524
        EXCEPTION
525
            WHEN data_exception THEN RETURN value; -- ignore invalid value
526
        END;
527
    END IF;
528
END;
529
$$;
530

    
531

    
532
--
533
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
534
--
535

    
536
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
537
    LANGUAGE sql IMMUTABLE
538
    AS $_$
539
SELECT bool_or(value)
540
FROM
541
(VALUES
542
      ($1)
543
    , ($2)
544
    , ($3)
545
    , ($4)
546
    , ($5)
547
)
548
AS v (value)
549
$_$;
550

    
551

    
552
--
553
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
554
--
555

    
556
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
557
_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.
558
';
559

    
560

    
561
--
562
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
563
--
564

    
565
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
566
    LANGUAGE sql IMMUTABLE
567
    AS $_$
568
SELECT $2 - $1
569
$_$;
570

    
571

    
572
--
573
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
574
--
575

    
576
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
577
    LANGUAGE sql IMMUTABLE
578
    AS $_$
579
SELECT regexp_split_to_table($1, $2)
580
$_$;
581

    
582

    
583
--
584
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
585
--
586

    
587
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
588
    LANGUAGE sql STABLE
589
    AS $_$
590
SELECT util.derived_cols($1, $2)
591
UNION
592
SELECT util.eval2set($$
593
SELECT col
594
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
595
JOIN $$||$2||$$ ON "to" = col
596
WHERE "from" LIKE ':%'
597
$$, NULL::text)
598
$_$;
599

    
600

    
601
--
602
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
603
--
604

    
605
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
606
gets table_''s added columns (all the columns not in the original data)
607
';
608

    
609

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

    
614
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
615
    LANGUAGE sql IMMUTABLE
616
    AS $_$
617
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
618
$_$;
619

    
620

    
621
--
622
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
623
--
624

    
625
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
626
    LANGUAGE plpgsql IMMUTABLE
627
    AS $$
628
DECLARE
629
	value_cmp         state%TYPE = ARRAY[value];
630
	state             state%TYPE = COALESCE(state, value_cmp);
631
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
632
BEGIN
633
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
634
END;
635
$$;
636

    
637

    
638
--
639
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
640
--
641

    
642
CREATE FUNCTION analyze_(table_ regclass) RETURNS void
643
    LANGUAGE sql
644
    AS $_$
645
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
646
$_$;
647

    
648

    
649
--
650
-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
651
--
652

    
653
CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
654
    LANGUAGE sql
655
    AS $_$
656
SELECT util.set_comment($1, concat(util.comment($1), $2))
657
$_$;
658

    
659

    
660
--
661
-- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
662
--
663

    
664
COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
665
comment: must start and end with a newline
666
';
667

    
668

    
669
--
670
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
671
--
672

    
673
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
674
    LANGUAGE sql IMMUTABLE
675
    AS $_$
676
SELECT pg_catalog.array_fill($1, ARRAY[$2])
677
$_$;
678

    
679

    
680
--
681
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
682
--
683

    
684
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
685
    LANGUAGE sql IMMUTABLE
686
    AS $_$
687
SELECT util.array_length($1, 1)
688
$_$;
689

    
690

    
691
--
692
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
693
--
694

    
695
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
696
    LANGUAGE sql IMMUTABLE
697
    AS $_$
698
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
699
$_$;
700

    
701

    
702
--
703
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
704
--
705

    
706
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
707
returns 0 instead of NULL for empty arrays
708
';
709

    
710

    
711
--
712
-- Name: array_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
713
--
714

    
715
CREATE FUNCTION array_reverse("array" anyarray) RETURNS anyarray
716
    LANGUAGE sql IMMUTABLE
717
    AS $_$
718
SELECT array(SELECT elem FROM util.in_reverse($1) elem)
719
$_$;
720

    
721

    
722
--
723
-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
724
--
725

    
726
CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
727
    LANGUAGE sql
728
    AS $_$
729
SELECT CASE WHEN util.freq_always_1($1, $2)
730
THEN util.rm_freq($1, $2)
731
ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
732
END
733
$_$;
734

    
735

    
736
--
737
-- Name: bounding_box(range, range); Type: FUNCTION; Schema: util; Owner: -
738
--
739

    
740
CREATE FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geography
741
    LANGUAGE sql IMMUTABLE
742
    AS $_$
743
/* don't use st_makebox2d() because it doesn't support geocoordinate wraparound
744
(it is not SRID-aware) */
745
SELECT postgis.st_makeenvelope(
746
  /*xmin=*/$2.lower, /*ymin=*/$1.lower
747
, /*xmax=*/$2.upper, /*ymax=*/$1.upper
748
, /*WGS84*/4326
749
)::postgis.geography
750
$_$;
751

    
752

    
753
--
754
-- Name: FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range); Type: COMMENT; Schema: util; Owner: -
755
--
756

    
757
COMMENT ON FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) IS '
758
usage:
759
SET search_path = util; -- for ~ operator
760
SELECT util.bounding_box(lower_lat ~ upper_lat, lower_long ~ upper_long);
761

    
762
**WARNING**: the geography type stores all edges as arcs of great circles,
763
resulting in the latitude lines bulging outward from the true bounding box.
764
this will create false positives above and below the bounding box.
765
';
766

    
767

    
768
--
769
-- Name: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
770
--
771

    
772
CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
773
    LANGUAGE plpgsql IMMUTABLE
774
    AS $$
775
/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
776
return value, causing a type mismatch */
777
BEGIN
778
	-- will then be assignment-cast to return type via INOUT
779
	RETURN value::cstring;
780
END;
781
$$;
782

    
783

    
784
--
785
-- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
786
--
787

    
788
COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
789
allows casting to an arbitrary type without eval()
790

    
791
usage:
792
SELECT util.cast(''value'', NULL::integer);
793

    
794
note that there does *not* need to be a cast from text to the output type,
795
because an INOUT cast is used instead
796
(http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)
797

    
798
ret_type_null: NULL::ret_type
799
';
800

    
801

    
802
--
803
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
804
--
805

    
806
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
807
    LANGUAGE sql STABLE
808
    AS $_$
809
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
810
$_$;
811

    
812

    
813
--
814
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
815
--
816

    
817
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
818
    LANGUAGE plpgsql STRICT
819
    AS $_$
820
BEGIN
821
    -- not yet clustered (ARRAY[] compares NULLs literally)
822
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
823
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
824
    END IF;
825
END;
826
$_$;
827

    
828

    
829
--
830
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
831
--
832

    
833
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
834
idempotent
835
';
836

    
837

    
838
--
839
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
840
--
841

    
842
CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
843
    LANGUAGE sql IMMUTABLE
844
    AS $_$
845
SELECT value
846
FROM unnest($1) value
847
WHERE value IS NOT NULL
848
LIMIT 1
849
$_$;
850

    
851

    
852
--
853
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
854
--
855

    
856
COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
857
uses:
858
* coalescing array elements or rows together
859
* forcing evaluation of all values of a COALESCE()
860
';
861

    
862

    
863
--
864
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
865
--
866

    
867
CREATE FUNCTION col__min(col col_ref) RETURNS integer
868
    LANGUAGE sql STABLE
869
    AS $_$
870
SELECT util.eval2val($$
871
SELECT $$||quote_ident($1.name)||$$
872
FROM $$||$1.table_||$$
873
ORDER BY $$||quote_ident($1.name)||$$ ASC
874
LIMIT 1
875
$$, NULL::integer)
876
$_$;
877

    
878

    
879
--
880
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
881
--
882

    
883
CREATE FUNCTION col_comment(col col_ref) RETURNS text
884
    LANGUAGE plpgsql STABLE STRICT
885
    AS $$
886
DECLARE
887
	comment text;
888
BEGIN
889
	SELECT description
890
	FROM pg_attribute
891
	LEFT JOIN pg_description ON objoid = attrelid
892
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
893
	WHERE attrelid = col.table_ AND attname = col.name
894
	INTO STRICT comment
895
	;
896
	RETURN comment;
897
EXCEPTION
898
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
899
END;
900
$$;
901

    
902

    
903
--
904
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
905
--
906

    
907
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
908
    LANGUAGE plpgsql STABLE STRICT
909
    AS $$
910
DECLARE
911
	default_sql text;
912
BEGIN
913
	SELECT adsrc
914
	FROM pg_attribute
915
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
916
	WHERE attrelid = col.table_ AND attname = col.name
917
	INTO STRICT default_sql
918
	;
919
	RETURN default_sql;
920
EXCEPTION
921
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
922
END;
923
$$;
924

    
925

    
926
--
927
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
928
--
929

    
930
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
931
    LANGUAGE sql STABLE
932
    AS $_$
933
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
934
$_$;
935

    
936

    
937
--
938
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
939
--
940

    
941
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
942
ret_type_null: NULL::ret_type
943
';
944

    
945

    
946
--
947
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
948
--
949

    
950
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
951
    LANGUAGE plpgsql STRICT
952
    AS $$
953
BEGIN
954
    PERFORM util.col_type(col);
955
    RETURN true;
956
EXCEPTION
957
    WHEN undefined_column THEN RETURN false;
958
END;
959
$$;
960

    
961

    
962
--
963
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
964
--
965

    
966
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
967
    LANGUAGE plpgsql STABLE STRICT
968
    AS $$
969
DECLARE
970
    prefix text := util.name(type)||'.';
971
BEGIN
972
    RETURN QUERY
973
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
974
        FROM util.col_names(type) f (name_);
975
END;
976
$$;
977

    
978

    
979
--
980
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
981
--
982

    
983
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
984
    LANGUAGE sql STABLE
985
    AS $_$
986
SELECT attname::text
987
FROM pg_attribute
988
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
989
ORDER BY attnum
990
$_$;
991

    
992

    
993
--
994
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
995
--
996

    
997
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
998
    LANGUAGE plpgsql STABLE STRICT
999
    AS $_$
1000
BEGIN
1001
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
1002
END;
1003
$_$;
1004

    
1005

    
1006
--
1007
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
1008
--
1009

    
1010
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
1011
    LANGUAGE plpgsql STABLE STRICT
1012
    AS $$
1013
DECLARE
1014
    type regtype;
1015
BEGIN
1016
    SELECT atttypid FROM pg_attribute
1017
    WHERE attrelid = col.table_ AND attname = col.name
1018
    INTO STRICT type
1019
    ;
1020
    RETURN type;
1021
EXCEPTION
1022
    WHEN no_data_found THEN
1023
        RAISE undefined_column USING MESSAGE =
1024
            concat('undefined column: ', col.name);
1025
END;
1026
$$;
1027

    
1028

    
1029
--
1030
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
1031
--
1032

    
1033
CREATE FUNCTION comment(element oid) RETURNS text
1034
    LANGUAGE sql STABLE
1035
    AS $_$
1036
SELECT description FROM pg_description WHERE objoid = $1
1037
$_$;
1038

    
1039

    
1040
--
1041
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
1042
--
1043

    
1044
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
1045
    LANGUAGE sql IMMUTABLE
1046
    AS $_$
1047
SELECT util.esc_name__append($2, $1)
1048
$_$;
1049

    
1050

    
1051
--
1052
-- Name: contained_within(postgis.geography, postgis.geography); Type: FUNCTION; Schema: util; Owner: -
1053
--
1054

    
1055
CREATE FUNCTION contained_within("inner" postgis.geography, "outer" postgis.geography) RETURNS boolean
1056
    LANGUAGE sql IMMUTABLE
1057
    SET search_path TO postgis
1058
    AS $_$
1059
/* search_path: st_coveredby() needs postgis to be in the search_path */
1060
SELECT postgis.st_coveredby($1, $2)
1061
$_$;
1062

    
1063

    
1064
--
1065
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
1066
--
1067

    
1068
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
1069
    LANGUAGE sql IMMUTABLE
1070
    AS $_$
1071
SELECT position($1 in $2) > 0 /*1-based offset*/
1072
$_$;
1073

    
1074

    
1075
--
1076
-- Name: copy(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1077
--
1078

    
1079
CREATE FUNCTION copy(from_ regclass, to_ text) RETURNS void
1080
    LANGUAGE sql
1081
    AS $_$
1082
SELECT util.copy_struct($1, $2);
1083
SELECT util.copy_data($1, $2);
1084
$_$;
1085

    
1086

    
1087
--
1088
-- Name: copy_data(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1089
--
1090

    
1091
CREATE FUNCTION copy_data(from_ regclass, to_ regclass) RETURNS void
1092
    LANGUAGE sql
1093
    AS $_$
1094
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT * FROM $$||$1)
1095
$_$;
1096

    
1097

    
1098
--
1099
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1100
--
1101

    
1102
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
1103
    LANGUAGE sql
1104
    AS $_$
1105
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
1106
$_$;
1107

    
1108

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

    
1113
CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
1114
    LANGUAGE sql
1115
    AS $_$
1116
SELECT util.materialize_view($2, $1)
1117
$_$;
1118

    
1119

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

    
1124
CREATE FUNCTION create_if_not_exists(sql text, relation text DEFAULT NULL::text) RETURNS void
1125
    LANGUAGE plpgsql
1126
    AS $$
1127
BEGIN
1128
	/* always generate standard exception if exists, even if table definition
1129
	would be invalid (which generates a variety of exceptions) */
1130
	IF util.relation_exists(relation) THEN
1131
		PERFORM util.raise('NOTICE', relation||' already exists, skipping');
1132
		RAISE duplicate_table;
1133
	END IF;
1134
	PERFORM util.eval(sql);
1135
EXCEPTION
1136
WHEN   duplicate_table
1137
	OR duplicate_object -- eg. constraint
1138
	OR duplicate_column
1139
	OR duplicate_function
1140
THEN NULL;
1141
WHEN invalid_table_definition THEN
1142
	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
1143
	ELSE RAISE;
1144
	END IF;
1145
END;
1146
$$;
1147

    
1148

    
1149
--
1150
-- Name: FUNCTION create_if_not_exists(sql text, relation text); Type: COMMENT; Schema: util; Owner: -
1151
--
1152

    
1153
COMMENT ON FUNCTION create_if_not_exists(sql text, relation text) IS '
1154
idempotent
1155
';
1156

    
1157

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

    
1162
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
1163
    LANGUAGE sql STABLE
1164
    AS $$
1165
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
1166
$$;
1167

    
1168

    
1169
--
1170
-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
1171
--
1172

    
1173
CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
1174
    LANGUAGE sql IMMUTABLE
1175
    AS $_$
1176
SELECT util.raise('NOTICE', $$SELECT $$||$1)
1177
$_$;
1178

    
1179

    
1180
--
1181
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1182
--
1183

    
1184
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
1185
    LANGUAGE sql IMMUTABLE
1186
    AS $_$
1187
SELECT util.debug_print_value('returns: ', $1, $2);
1188
SELECT $1;
1189
$_$;
1190

    
1191

    
1192
--
1193
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
1194
--
1195

    
1196
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
1197
    LANGUAGE sql IMMUTABLE
1198
    AS $_$
1199
/* newline before so the query starts at the beginning of the line.
1200
newline after to visually separate queries from one another. */
1201
SELECT util.raise('NOTICE', $$
1202
$$||util.runnable_sql($1)||$$
1203
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
1204
$_$;
1205

    
1206

    
1207
--
1208
-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1209
--
1210

    
1211
CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
1212
    LANGUAGE sql IMMUTABLE
1213
    AS $_$
1214
SELECT util.raise('NOTICE', concat($1,
1215
(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
1216
$$)
1217
$_$;
1218

    
1219

    
1220
--
1221
-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1222
--
1223

    
1224
CREATE FUNCTION debug_print_var(var text, value anyelement, encode boolean DEFAULT false) RETURNS void
1225
    LANGUAGE sql IMMUTABLE
1226
    AS $_$
1227
/* can't use EXECUTE in the caller because "No substitution of PL/pgSQL
1228
variables is done on the computed command string"
1229
(http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) */
1230
SELECT util.debug_print_value($1||' = ', $2, $3)
1231
$_$;
1232

    
1233

    
1234
--
1235
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1236
--
1237

    
1238
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
1239
    LANGUAGE sql STABLE
1240
    AS $_$
1241
SELECT util.eval2set($$
1242
SELECT col
1243
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
1244
LEFT JOIN $$||$2||$$ ON "to" = col
1245
WHERE "from" IS NULL
1246
$$, NULL::text)
1247
$_$;
1248

    
1249

    
1250
--
1251
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1252
--
1253

    
1254
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
1255
gets table_''s derived columns (all the columns not in the names table)
1256
';
1257

    
1258

    
1259
--
1260
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1261
--
1262

    
1263
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1264
    LANGUAGE sql
1265
    AS $_$
1266
-- create a diff when the # of copies of a row differs between the tables
1267
SELECT util.to_freq($1);
1268
SELECT util.to_freq($2);
1269
SELECT util.auto_rm_freq(ARRAY[$1, $2]);
1270

    
1271
SELECT * FROM util.diff($1, $2, $3, has_freq := true)
1272
$_$;
1273

    
1274

    
1275
--
1276
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1277
--
1278

    
1279
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1280
usage:
1281
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1282

    
1283
col_type_null (*required*): NULL::shared_base_type
1284
';
1285

    
1286

    
1287
--
1288
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1289
--
1290

    
1291
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
1292
    LANGUAGE plpgsql
1293
    SET search_path TO pg_temp
1294
    AS $_$
1295
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
1296
changes of search_path (schema elements are bound at inline time rather than
1297
runtime) */
1298
/* function option search_path is needed to limit the effects of
1299
`SET LOCAL search_path` to the current function */
1300
BEGIN
1301
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
1302
	
1303
	PERFORM util.mk_keys_func(pg_typeof($3));
1304
	RETURN QUERY
1305
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
1306
$$/* need to explicitly cast each side to the return type because this does not
1307
happen automatically even when an implicit cast is available */
1308
  left_::$$||util.typeof($3)||$$
1309
, right_::$$||util.typeof($3)
1310
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
1311
the *same* base type, *even though* this is optional when using a custom %== */
1312
, util._if($4, $$true/*= CROSS JOIN*/$$,
1313
$$ left_::$$||util.typeof($3)||$$
1314
%== right_::$$||util.typeof($3)||$$
1315
	-- refer to EXPLAIN output for expansion of %==$$
1316
)
1317
,     $$         left_::$$||util.typeof($3)||$$
1318
IS DISTINCT FROM right_::$$||util.typeof($3)
1319
), $3)
1320
	;
1321
END;
1322
$_$;
1323

    
1324

    
1325
--
1326
-- 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: -
1327
--
1328

    
1329
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1330
col_type_null (*required*): NULL::col_type
1331
single_row: whether the tables consist of a single row, which should be
1332
	displayed side-by-side
1333

    
1334
to match up rows using a subset of the columns, create a custom keys() function
1335
which returns this subset as a record:
1336
-- note that OUT parameters for the returned fields are *not* needed
1337
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
1338
  RETURNS record AS
1339
$BODY$
1340
SELECT ($1.key_field_0, $1.key_field_1)
1341
$BODY$
1342
  LANGUAGE sql IMMUTABLE
1343
  COST 100;
1344

    
1345

    
1346
to run EXPLAIN on the FULL JOIN query:
1347
# run this function
1348
# look for a NOTICE containing the expanded query that it ran
1349
# run EXPLAIN on this expanded query
1350
';
1351

    
1352

    
1353
--
1354
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1355
--
1356

    
1357
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
1358
    LANGUAGE sql
1359
    AS $_$
1360
SELECT * FROM util.diff($1::text, $2::text, $3,
1361
	single_row := util.has_single_row($1) AND util.has_single_row($2))
1362
$_$;
1363

    
1364

    
1365
--
1366
-- 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: -
1367
--
1368

    
1369
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1370
helper function used by diff(regclass, regclass)
1371

    
1372
usage:
1373
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
1374

    
1375
col_type_null (*required*): NULL::shared_base_type
1376
';
1377

    
1378

    
1379
--
1380
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1381
--
1382

    
1383
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1384
    LANGUAGE sql
1385
    AS $_$
1386
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1387
$_$;
1388

    
1389

    
1390
--
1391
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1392
--
1393

    
1394
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1395
idempotent
1396
';
1397

    
1398

    
1399
--
1400
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1401
--
1402

    
1403
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1404
    LANGUAGE sql
1405
    AS $_$
1406
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1407
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1408
$_$;
1409

    
1410

    
1411
--
1412
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1413
--
1414

    
1415
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1416
idempotent
1417
';
1418

    
1419

    
1420
--
1421
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
1422
--
1423

    
1424
CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
1425
    LANGUAGE sql
1426
    AS $_$
1427
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
1428
SELECT NULL::void; -- don't fold away functions called in previous query
1429
$_$;
1430

    
1431

    
1432
--
1433
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
1434
--
1435

    
1436
COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
1437
idempotent
1438
';
1439

    
1440

    
1441
--
1442
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1443
--
1444

    
1445
CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
1446
    LANGUAGE sql
1447
    AS $_$
1448
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1449
included in the debug SQL */
1450
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1451
$_$;
1452

    
1453

    
1454
--
1455
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1456
--
1457

    
1458
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1459
    LANGUAGE sql
1460
    AS $_$
1461
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1462
||util._if($3, $$ CASCADE$$, ''::text))
1463
$_$;
1464

    
1465

    
1466
--
1467
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1468
--
1469

    
1470
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1471
idempotent
1472
';
1473

    
1474

    
1475
--
1476
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1477
--
1478

    
1479
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
1480
    LANGUAGE sql
1481
    AS $_$
1482
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
1483
$_$;
1484

    
1485

    
1486
--
1487
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1488
--
1489

    
1490
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
1491
    LANGUAGE sql
1492
    AS $_$
1493
SELECT util.debug_print_func_call(util.quote_func_call(
1494
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
1495
util.quote_typed($3)))
1496
;
1497
SELECT util.drop_relation(relation, $3)
1498
FROM util.show_relations_like($1, $2) relation
1499
;
1500
SELECT NULL::void; -- don't fold away functions called in previous query
1501
$_$;
1502

    
1503

    
1504
--
1505
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1506
--
1507

    
1508
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1509
    LANGUAGE sql
1510
    AS $_$
1511
SELECT util.drop_relation('TABLE', $1, $2)
1512
$_$;
1513

    
1514

    
1515
--
1516
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1517
--
1518

    
1519
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1520
idempotent
1521
';
1522

    
1523

    
1524
--
1525
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1526
--
1527

    
1528
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1529
    LANGUAGE sql
1530
    AS $_$
1531
SELECT util.drop_relation('VIEW', $1, $2)
1532
$_$;
1533

    
1534

    
1535
--
1536
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1537
--
1538

    
1539
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1540
idempotent
1541
';
1542

    
1543

    
1544
--
1545
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1546
--
1547

    
1548
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1549
    LANGUAGE sql IMMUTABLE
1550
    AS $_$
1551
SELECT util.array_fill($1, 0)
1552
$_$;
1553

    
1554

    
1555
--
1556
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1557
--
1558

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

    
1563

    
1564
--
1565
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1566
--
1567

    
1568
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1569
    LANGUAGE sql IMMUTABLE
1570
    AS $_$
1571
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1572
$_$;
1573

    
1574

    
1575
--
1576
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
1577
--
1578

    
1579
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1580
    LANGUAGE sql IMMUTABLE
1581
    AS $_$
1582
SELECT regexp_replace($2, '("?)$', $1||'\1')
1583
$_$;
1584

    
1585

    
1586
--
1587
-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
1588
--
1589

    
1590
CREATE FUNCTION eval(queries text[]) RETURNS void
1591
    LANGUAGE sql
1592
    AS $_$
1593
SELECT util.eval(query) FROM unnest($1) query;
1594
SELECT NULL::void; -- don't fold away functions called in previous query
1595
$_$;
1596

    
1597

    
1598
--
1599
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1600
--
1601

    
1602
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
1603
    LANGUAGE plpgsql
1604
    AS $$
1605
BEGIN
1606
	sql = util.view_def_to_orig(sql); -- restore user's intent
1607
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1608
	EXECUTE sql;
1609
END;
1610
$$;
1611

    
1612

    
1613
--
1614
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1615
--
1616

    
1617
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1618
    LANGUAGE plpgsql
1619
    AS $$
1620
BEGIN
1621
	PERFORM util.debug_print_sql(sql);
1622
	RETURN QUERY EXECUTE sql;
1623
END;
1624
$$;
1625

    
1626

    
1627
--
1628
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1629
--
1630

    
1631
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1632
col_type_null (*required*): NULL::col_type
1633
';
1634

    
1635

    
1636
--
1637
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1638
--
1639

    
1640
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1641
    LANGUAGE plpgsql
1642
    AS $$
1643
BEGIN
1644
	PERFORM util.debug_print_sql(sql);
1645
	RETURN QUERY EXECUTE sql;
1646
END;
1647
$$;
1648

    
1649

    
1650
--
1651
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1652
--
1653

    
1654
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
1655
    LANGUAGE plpgsql
1656
    AS $$
1657
BEGIN
1658
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1659
	RETURN QUERY EXECUTE sql;
1660
END;
1661
$$;
1662

    
1663

    
1664
--
1665
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1666
--
1667

    
1668
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1669
    LANGUAGE plpgsql STABLE
1670
    AS $$
1671
DECLARE
1672
	ret_val ret_type_null%TYPE;
1673
BEGIN
1674
	PERFORM util.debug_print_sql(sql);
1675
	EXECUTE sql INTO STRICT ret_val;
1676
	RETURN ret_val;
1677
END;
1678
$$;
1679

    
1680

    
1681
--
1682
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1683
--
1684

    
1685
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1686
ret_type_null: NULL::ret_type
1687
';
1688

    
1689

    
1690
--
1691
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1692
--
1693

    
1694
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1695
    LANGUAGE sql
1696
    AS $_$
1697
SELECT util.eval2val($$SELECT $$||$1, $2)
1698
$_$;
1699

    
1700

    
1701
--
1702
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1703
--
1704

    
1705
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1706
ret_type_null: NULL::ret_type
1707
';
1708

    
1709

    
1710
--
1711
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1712
--
1713

    
1714
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1715
    LANGUAGE sql
1716
    AS $_$
1717
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1718
$_$;
1719

    
1720

    
1721
--
1722
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1723
--
1724

    
1725
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1726
sql: can be NULL, which will be passed through
1727
ret_type_null: NULL::ret_type
1728
';
1729

    
1730

    
1731
--
1732
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1733
--
1734

    
1735
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1736
    LANGUAGE sql STABLE
1737
    AS $_$
1738
SELECT col_name
1739
FROM unnest($2) s (col_name)
1740
WHERE util.col_exists(($1, col_name))
1741
$_$;
1742

    
1743

    
1744
--
1745
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1746
--
1747

    
1748
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1749
    LANGUAGE sql
1750
    SET client_min_messages TO 'error'
1751
    AS $_$
1752
/* `client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE functions in
1753
the query, so this prevents displaying any log messages printed by them */
1754
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
1755
$_$;
1756

    
1757

    
1758
--
1759
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1760
--
1761

    
1762
CREATE FUNCTION explain2notice(sql text) RETURNS void
1763
    LANGUAGE sql
1764
    AS $_$
1765
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
1766
$_$;
1767

    
1768

    
1769
--
1770
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
1771
--
1772

    
1773
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
1774
    LANGUAGE sql
1775
    AS $_$
1776
-- newline before and after to visually separate it from other debug info
1777
SELECT COALESCE($$
1778
EXPLAIN:
1779
$$||util.fold_explain_msg(util.explain2str($1))||$$
1780
$$, '')
1781
$_$;
1782

    
1783

    
1784
--
1785
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
1786
--
1787

    
1788
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
1789
    LANGUAGE plpgsql
1790
    AS $$
1791
BEGIN
1792
	RETURN util.explain2notice_msg(sql);
1793
EXCEPTION
1794
WHEN   syntax_error
1795
	OR invalid_cursor_definition -- "cannot open multi-query plan as cursor"
1796
	THEN RETURN NULL; -- non-explainable query
1797
	/* don't use util.is_explainable() because the list provided by Postgres
1798
	(http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691)
1799
	excludes some query types that are in fact EXPLAIN-able */
1800
END;
1801
$$;
1802

    
1803

    
1804
--
1805
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1806
--
1807

    
1808
CREATE FUNCTION explain2str(sql text) RETURNS text
1809
    LANGUAGE sql
1810
    AS $_$
1811
SELECT util.join_strs(explain, $$
1812
$$) FROM util.explain($1)
1813
$_$;
1814

    
1815

    
1816
SET default_tablespace = '';
1817

    
1818
SET default_with_oids = false;
1819

    
1820
--
1821
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1822
--
1823

    
1824
CREATE TABLE explain (
1825
    line text NOT NULL
1826
);
1827

    
1828

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

    
1833
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1834
    LANGUAGE sql
1835
    AS $_$
1836
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1837
$$||quote_nullable($1)||$$
1838
)$$)
1839
$_$;
1840

    
1841

    
1842
--
1843
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1844
--
1845

    
1846
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1847
usage:
1848
PERFORM util.explain2table($$
1849
query
1850
$$);
1851
';
1852

    
1853

    
1854
--
1855
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
1856
--
1857

    
1858
CREATE FUNCTION first_word(str text) RETURNS text
1859
    LANGUAGE sql IMMUTABLE
1860
    AS $_$
1861
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
1862
$_$;
1863

    
1864

    
1865
--
1866
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1867
--
1868

    
1869
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1870
    LANGUAGE sql IMMUTABLE
1871
    AS $_$
1872
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1873
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1874
) END
1875
$_$;
1876

    
1877

    
1878
--
1879
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1880
--
1881

    
1882
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1883
ensures that an array will always have proper non-NULL dimensions
1884
';
1885

    
1886

    
1887
--
1888
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
1889
--
1890

    
1891
CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
1892
    LANGUAGE sql IMMUTABLE
1893
    AS $_$
1894
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
1895
$_$;
1896

    
1897

    
1898
--
1899
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1900
--
1901

    
1902
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1903
    LANGUAGE plpgsql STRICT
1904
    AS $_$
1905
DECLARE
1906
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1907
$$||query;
1908
BEGIN
1909
	EXECUTE mk_view;
1910
EXCEPTION
1911
WHEN invalid_table_definition THEN
1912
	IF SQLERRM = 'cannot drop columns from view'
1913
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1914
	THEN
1915
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1916
		EXECUTE mk_view;
1917
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1918
	END IF;
1919
END;
1920
$_$;
1921

    
1922

    
1923
--
1924
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1925
--
1926

    
1927
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1928
idempotent
1929
';
1930

    
1931

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

    
1936
CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
1937
    LANGUAGE sql STABLE
1938
    AS $_$
1939
SELECT util.eval2val(
1940
$$SELECT NOT EXISTS( -- there is no row that is != 1
1941
	SELECT NULL
1942
	FROM $$||$1||$$
1943
	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
1944
	LIMIT 1
1945
)
1946
$$, NULL::boolean)
1947
$_$;
1948

    
1949

    
1950
--
1951
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
1952
--
1953

    
1954
CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
1955
    LANGUAGE sql STABLE
1956
    AS $_$
1957
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
1958
$_$;
1959

    
1960

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

    
1965
CREATE FUNCTION grants_users() RETURNS SETOF text
1966
    LANGUAGE sql IMMUTABLE
1967
    AS $$
1968
VALUES ('bien_read'), ('public_')
1969
$$;
1970

    
1971

    
1972
--
1973
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1974
--
1975

    
1976
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1977
    LANGUAGE sql IMMUTABLE
1978
    AS $_$
1979
SELECT substring($2 for length($1)) = $1
1980
$_$;
1981

    
1982

    
1983
--
1984
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1985
--
1986

    
1987
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1988
    LANGUAGE sql STABLE
1989
    AS $_$
1990
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1991
$_$;
1992

    
1993

    
1994
--
1995
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1996
--
1997

    
1998
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1999
    LANGUAGE sql IMMUTABLE
2000
    AS $_$
2001
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
2002
$_$;
2003

    
2004

    
2005
--
2006
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
2007
--
2008

    
2009
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
2010
avoids repeating the same value for each key
2011
';
2012

    
2013

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

    
2018
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
2019
    LANGUAGE sql IMMUTABLE
2020
    AS $_$
2021
SELECT COALESCE($1, $2)
2022
$_$;
2023

    
2024

    
2025
--
2026
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
2027
--
2028

    
2029
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
2030
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
2031
';
2032

    
2033

    
2034
--
2035
-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
2036
--
2037

    
2038
CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
2039
    LANGUAGE sql IMMUTABLE
2040
    AS $_$
2041
SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
2042
$_$;
2043

    
2044

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

    
2049
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
2050
    LANGUAGE sql
2051
    AS $_$
2052
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
2053
$_$;
2054

    
2055

    
2056
--
2057
-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2058
--
2059

    
2060
CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
2061
    LANGUAGE plpgsql IMMUTABLE
2062
    AS $$
2063
BEGIN
2064
	PERFORM util.cast(value, ret_type_null);
2065
	-- must happen *after* cast check, because NULL is not valid for some types
2066
	IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
2067
	RETURN true;
2068
EXCEPTION
2069
WHEN   data_exception
2070
	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
2071
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
2072
	THEN
2073
	RETURN false;
2074
END;
2075
$$;
2076

    
2077

    
2078
--
2079
-- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
2080
--
2081

    
2082
COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
2083
passes NULL through. however, if NULL is not valid for the type, false will be
2084
returned instead.
2085

    
2086
ret_type_null: NULL::ret_type
2087
';
2088

    
2089

    
2090
--
2091
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
2092
--
2093

    
2094
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
2095
    LANGUAGE sql STABLE
2096
    AS $_$
2097
SELECT COALESCE(util.col_comment($1) LIKE '
2098
constant
2099
%', false)
2100
$_$;
2101

    
2102

    
2103
--
2104
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2105
--
2106

    
2107
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
2108
    LANGUAGE sql IMMUTABLE
2109
    AS $_$
2110
SELECT util.array_length($1) = 0
2111
$_$;
2112

    
2113

    
2114
--
2115
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
2116
--
2117

    
2118
CREATE FUNCTION is_explain(sql text) RETURNS boolean
2119
    LANGUAGE sql IMMUTABLE
2120
    AS $_$
2121
SELECT upper(util.first_word($1)) = 'EXPLAIN'
2122
$_$;
2123

    
2124

    
2125
--
2126
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
2127
--
2128

    
2129
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
2130
    LANGUAGE sql IMMUTABLE
2131
    AS $_$
2132
SELECT upper(util.first_word($1)) = ANY(
2133
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
2134
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
2135
)
2136
$_$;
2137

    
2138

    
2139
--
2140
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2141
--
2142

    
2143
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
2144
    LANGUAGE sql IMMUTABLE
2145
    AS $_$
2146
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
2147
$_$;
2148

    
2149

    
2150
--
2151
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2152
--
2153

    
2154
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
2155
    LANGUAGE sql IMMUTABLE
2156
    AS $_$
2157
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
2158
$_$;
2159

    
2160

    
2161
--
2162
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
2163
--
2164

    
2165
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
2166
    LANGUAGE sql IMMUTABLE
2167
    AS $_$
2168
SELECT upper(util.first_word($1)) = 'SET'
2169
$_$;
2170

    
2171

    
2172
--
2173
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
2174
--
2175

    
2176
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
2177
    LANGUAGE sql STABLE
2178
    AS $_$
2179
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
2180
$_$;
2181

    
2182

    
2183
--
2184
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
2185
--
2186

    
2187
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
2188
    LANGUAGE sql STABLE
2189
    AS $_$
2190
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
2191
$_$;
2192

    
2193

    
2194
--
2195
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
2196
--
2197

    
2198
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
2199
    LANGUAGE sql IMMUTABLE STRICT
2200
    AS $_$
2201
SELECT $1 || $3 || $2
2202
$_$;
2203

    
2204

    
2205
--
2206
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
2207
--
2208

    
2209
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
2210
must be declared STRICT to use the special handling of STRICT aggregating functions
2211
';
2212

    
2213

    
2214
--
2215
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
2216
--
2217

    
2218
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
2219
    LANGUAGE sql IMMUTABLE
2220
    AS $_$
2221
SELECT $1 -- compare on the entire value
2222
$_$;
2223

    
2224

    
2225
--
2226
-- Name: keys_eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2227
--
2228

    
2229
CREATE FUNCTION keys_eq(left_ anyelement, right_ anyelement) RETURNS boolean
2230
    LANGUAGE sql STABLE
2231
    AS $_$
2232
SELECT keys($1) = keys($2)
2233
$_$;
2234

    
2235

    
2236
--
2237
-- Name: FUNCTION keys_eq(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
2238
--
2239

    
2240
COMMENT ON FUNCTION keys_eq(left_ anyelement, right_ anyelement) IS '
2241
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**)
2242
';
2243

    
2244

    
2245
--
2246
-- Name: lat_long_in_new_world(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
2247
--
2248

    
2249
CREATE FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) RETURNS boolean
2250
    LANGUAGE sql IMMUTABLE
2251
    SET search_path TO util, postgis
2252
    AS $_$
2253
SELECT util.point($1, $2) @ util.new_world()
2254
$_$;
2255

    
2256

    
2257
--
2258
-- Name: FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: util; Owner: -
2259
--
2260

    
2261
COMMENT ON FUNCTION lat_long_in_new_world(latitude_deg double precision, longitude_deg double precision) IS '
2262
**WARNING**: this includes false positives above and below the New World
2263
bounding box, as described in util.bounding_box()
2264
';
2265

    
2266

    
2267
--
2268
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
2269
--
2270

    
2271
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
2272
    LANGUAGE sql IMMUTABLE
2273
    AS $_$
2274
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
2275
$_$;
2276

    
2277

    
2278
--
2279
-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2280
--
2281

    
2282
CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
2283
    LANGUAGE plpgsql
2284
    AS $$
2285
DECLARE
2286
	errors_ct integer = 0;
2287
	loop_var loop_type_null%TYPE;
2288
BEGIN
2289
	FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
2290
	LOOP
2291
		BEGIN
2292
			EXECUTE loop_body_sql USING loop_var;
2293
		EXCEPTION
2294
		WHEN OTHERS THEN
2295
			errors_ct = errors_ct+1;
2296
			PERFORM util.raise_error_warning(SQLERRM);
2297
		END;
2298
	END LOOP;
2299
	IF errors_ct > 0 THEN
2300
		-- can't raise exception because this would roll back the transaction
2301
		PERFORM util.raise_error_warning('there were '||errors_ct
2302
			||' errors: see the WARNINGs for details');
2303
	END IF;
2304
END;
2305
$$;
2306

    
2307

    
2308
--
2309
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
2310
--
2311

    
2312
CREATE FUNCTION ltrim_nl(str text) RETURNS text
2313
    LANGUAGE sql IMMUTABLE
2314
    AS $_$
2315
SELECT ltrim($1, $$
2316
$$)
2317
$_$;
2318

    
2319

    
2320
--
2321
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
2322
--
2323

    
2324
CREATE FUNCTION map_filter_insert() RETURNS trigger
2325
    LANGUAGE plpgsql
2326
    AS $$
2327
BEGIN
2328
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
2329
	RETURN new;
2330
END;
2331
$$;
2332

    
2333

    
2334
--
2335
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2336
--
2337

    
2338
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
2339
    LANGUAGE plpgsql STABLE STRICT
2340
    AS $_$
2341
DECLARE
2342
    value text;
2343
BEGIN
2344
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
2345
        INTO value USING key;
2346
    RETURN value;
2347
END;
2348
$_$;
2349

    
2350

    
2351
--
2352
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
2353
--
2354

    
2355
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
2356
    LANGUAGE sql IMMUTABLE
2357
    AS $_$
2358
SELECT util._map(util.nulls_map($1), $2)
2359
$_$;
2360

    
2361

    
2362
--
2363
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
2364
--
2365

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

    
2369
[1] inlining of function calls, which is different from constant folding
2370
[2] _map()''s profiling query
2371
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
2372
and map_nulls()''s profiling query
2373
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
2374
both take ~920 ms.
2375
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.
2376
';
2377

    
2378

    
2379
--
2380
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
2381
--
2382

    
2383
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
2384
    LANGUAGE plpgsql STABLE STRICT
2385
    AS $_$
2386
BEGIN
2387
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
2388
END;
2389
$_$;
2390

    
2391

    
2392
--
2393
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2394
--
2395

    
2396
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
2397
    LANGUAGE sql
2398
    AS $_$
2399
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
2400
$$||util.ltrim_nl($2));
2401
-- make sure the created table has the correct estimated row count
2402
SELECT util.analyze_($1);
2403

    
2404
SELECT util.append_comment($1, '
2405
contents generated from:
2406
'||util.ltrim_nl(util.runnable_sql($2))||';
2407
');
2408
$_$;
2409

    
2410

    
2411
--
2412
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2413
--
2414

    
2415
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
2416
idempotent
2417
';
2418

    
2419

    
2420
--
2421
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2422
--
2423

    
2424
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
2425
    LANGUAGE sql
2426
    AS $_$
2427
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
2428
$_$;
2429

    
2430

    
2431
--
2432
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2433
--
2434

    
2435
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
2436
idempotent
2437
';
2438

    
2439

    
2440
--
2441
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
2442
--
2443

    
2444
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
2445
    LANGUAGE sql
2446
    AS $_$
2447
SELECT util.create_if_not_exists($$
2448
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
2449
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
2450
||quote_literal($2)||$$;
2451
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
2452
constant
2453
';
2454
$$)
2455
$_$;
2456

    
2457

    
2458
--
2459
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
2460
--
2461

    
2462
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
2463
idempotent
2464
';
2465

    
2466

    
2467
--
2468
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
2469
--
2470

    
2471
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
2472
    LANGUAGE plpgsql STRICT
2473
    AS $_$
2474
DECLARE
2475
    type regtype = util.typeof(expr, col.table_::text::regtype);
2476
    col_name_sql text = quote_ident(col.name);
2477
BEGIN
2478
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
2479
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
2480
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
2481
$$||expr||$$;
2482
$$);
2483
END;
2484
$_$;
2485

    
2486

    
2487
--
2488
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
2489
--
2490

    
2491
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
2492
idempotent
2493
';
2494

    
2495

    
2496
--
2497
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
2498
--
2499

    
2500
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
2501
    LANGUAGE sql IMMUTABLE
2502
    AS $_$
2503
SELECT
2504
$$SELECT
2505
$$||$3||$$
2506
FROM      $$||$1||$$ left_
2507
FULL JOIN $$||$2||$$ right_
2508
ON $$||$4||$$
2509
WHERE $$||$5||$$
2510
ORDER BY left_, right_
2511
$$
2512
$_$;
2513

    
2514

    
2515
--
2516
-- Name: mk_drop_from_create(text); Type: FUNCTION; Schema: util; Owner: -
2517
--
2518

    
2519
CREATE FUNCTION mk_drop_from_create(create_cmd text) RETURNS text
2520
    LANGUAGE sql IMMUTABLE
2521
    AS $_$
2522
SELECT $$DROP $$||(util.regexp_match($1,
2523
-- match first CREATE, *if* no DROP came before it
2524
'(?m)\A(?:^(?!DROP\y).*\n)*?^CREATE(?: OR REPLACE)? ((?:)??[[:upper:]]+ .*?)(?: AS(?: (?:SELECT\y.*)?)?)?$'
2525
	/* (?:)?? makes the surrounding () group non-greedy, so that (?: AS ?)?
2526
	works properly (due to nonstandard Postgres regexp behavior:
2527
	http://www.postgresql.org/docs/9.3/static/functions-matching.html#POSIX-MATCHING-RULES) */
2528
))[1]||$$;$$
2529
$_$;
2530

    
2531

    
2532
--
2533
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2534
--
2535

    
2536
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
2537
    LANGUAGE sql
2538
    AS $_$
2539
-- keys()
2540
SELECT util.mk_keys_func($1, ARRAY(
2541
SELECT col FROM util.typed_cols($1) col
2542
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
2543
));
2544

    
2545
-- values_()
2546
SELECT util.mk_keys_func($1, COALESCE(
2547
	NULLIF(ARRAY(
2548
	SELECT col FROM util.typed_cols($1) col
2549
	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
2550
	), ARRAY[]::util.col_cast[])
2551
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
2552
, 'values_');
2553
$_$;
2554

    
2555

    
2556
--
2557
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
2558
--
2559

    
2560
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
2561
    LANGUAGE sql
2562
    AS $_$
2563
SELECT util.create_if_not_exists($$
2564
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
2565
($$||util.mk_typed_cols_list($2)||$$);
2566
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
2567
autogenerated
2568
';
2569
$$);
2570

    
2571
SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
2572
$_$;
2573

    
2574

    
2575
--
2576
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
2577
--
2578

    
2579
CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
2580
    LANGUAGE sql
2581
    AS $_$
2582
SELECT util.create_if_not_exists($$
2583
CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
2584
||util.qual_name($1)||$$)
2585
  RETURNS $$||util.qual_name($2)||$$ AS
2586
$BODY1$
2587
SELECT ROW($$||
2588
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
2589
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
2590
$BODY1$
2591
  LANGUAGE sql IMMUTABLE
2592
  COST 100;
2593
$$);
2594
$_$;
2595

    
2596

    
2597
--
2598
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2599
--
2600

    
2601
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
2602
    LANGUAGE sql
2603
    AS $_$
2604
SELECT util.create_if_not_exists($$
2605
CREATE TABLE $$||$1||$$
2606
(
2607
    LIKE util.map INCLUDING ALL
2608
);
2609

    
2610
CREATE TRIGGER map_filter_insert
2611
  BEFORE INSERT
2612
  ON $$||$1||$$
2613
  FOR EACH ROW
2614
  EXECUTE PROCEDURE util.map_filter_insert();
2615
$$)
2616
$_$;
2617

    
2618

    
2619
--
2620
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
2621
--
2622

    
2623
CREATE FUNCTION mk_not_null(text) RETURNS text
2624
    LANGUAGE sql IMMUTABLE
2625
    AS $_$
2626
SELECT COALESCE($1, '<NULL>')
2627
$_$;
2628

    
2629

    
2630
--
2631
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2632
--
2633

    
2634
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
2635
    LANGUAGE sql IMMUTABLE
2636
    AS $_$
2637
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
2638
util.qual_name((unnest).type), ''), '')
2639
FROM unnest($1)
2640
$_$;
2641

    
2642

    
2643
--
2644
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
2645
--
2646

    
2647
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
2648
    LANGUAGE sql IMMUTABLE
2649
    AS $_$
2650
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
2651
$_$;
2652

    
2653

    
2654
--
2655
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
2656
--
2657

    
2658
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
2659
auto-appends util to the search_path to enable use of util operators
2660
';
2661

    
2662

    
2663
--
2664
-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2665
--
2666

    
2667
CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
2668
    LANGUAGE sql STABLE
2669
    AS $_$
2670
SELECT COALESCE($$COMMENT ON $$||util.relation_type($1)||$$ $$||$1||$$ IS $$
2671
||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
2672
$_$;
2673

    
2674

    
2675
--
2676
-- Name: mk_set_relation_metadata(regclass); Type: FUNCTION; Schema: util; Owner: -
2677
--
2678

    
2679
CREATE FUNCTION mk_set_relation_metadata(relation regclass) RETURNS text
2680
    LANGUAGE sql STABLE
2681
    AS $_$
2682
SELECT util.show_grants_for($1)
2683
||util.show_set_comment($1)||$$
2684
$$
2685
$_$;
2686

    
2687

    
2688
--
2689
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2690
--
2691

    
2692
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
2693
    LANGUAGE sql IMMUTABLE
2694
    AS $_$
2695
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
2696
$_$;
2697

    
2698

    
2699
--
2700
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
2701
--
2702

    
2703
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
2704
    LANGUAGE sql IMMUTABLE
2705
    AS $_$
2706
/* debug_print_return_value() needed because this function is used with EXECUTE
2707
rather than util.eval() (in order to affect the calling function), so the
2708
search_path would not otherwise be printed */
2709
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
2710
||$$ search_path TO $$||$1
2711
$_$;
2712

    
2713

    
2714
--
2715
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2716
--
2717

    
2718
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
2719
    LANGUAGE sql
2720
    AS $_$
2721
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
2722
$_$;
2723

    
2724

    
2725
--
2726
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2727
--
2728

    
2729
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2730
idempotent
2731
';
2732

    
2733

    
2734
--
2735
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2736
--
2737

    
2738
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
2739
    LANGUAGE plpgsql STRICT
2740
    AS $_$
2741
DECLARE
2742
	view_qual_name text = util.qual_name(view_);
2743
BEGIN
2744
	EXECUTE $$
2745
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2746
  RETURNS SETOF $$||view_||$$ AS
2747
$BODY1$
2748
SELECT * FROM $$||view_qual_name||$$
2749
ORDER BY sort_col
2750
LIMIT $1 OFFSET $2
2751
$BODY1$
2752
  LANGUAGE sql STABLE
2753
  COST 100
2754
  ROWS 1000
2755
$$;
2756
	
2757
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2758
END;
2759
$_$;
2760

    
2761

    
2762
--
2763
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2764
--
2765

    
2766
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2767
    LANGUAGE plpgsql STRICT
2768
    AS $_$
2769
DECLARE
2770
	view_qual_name text = util.qual_name(view_);
2771
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2772
BEGIN
2773
	EXECUTE $$
2774
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2775
  RETURNS integer AS
2776
$BODY1$
2777
SELECT $$||quote_ident(row_num_col)||$$
2778
FROM $$||view_qual_name||$$
2779
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2780
LIMIT 1
2781
$BODY1$
2782
  LANGUAGE sql STABLE
2783
  COST 100;
2784
$$;
2785
	
2786
	EXECUTE $$
2787
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2788
  RETURNS SETOF $$||view_||$$ AS
2789
$BODY1$
2790
SELECT * FROM $$||view_qual_name||$$
2791
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2792
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2793
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2794
ORDER BY $$||quote_ident(row_num_col)||$$
2795
$BODY1$
2796
  LANGUAGE sql STABLE
2797
  COST 100
2798
  ROWS 1000
2799
$$;
2800
	
2801
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2802
END;
2803
$_$;
2804

    
2805

    
2806
--
2807
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2808
--
2809

    
2810
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2811
    LANGUAGE plpgsql STRICT
2812
    AS $_$
2813
DECLARE
2814
	view_qual_name text = util.qual_name(view_);
2815
BEGIN
2816
	EXECUTE $$
2817
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2818
  RETURNS SETOF $$||view_||$$
2819
  SET enable_sort TO 'off'
2820
  AS
2821
$BODY1$
2822
SELECT * FROM $$||view_qual_name||$$($2, $3)
2823
$BODY1$
2824
  LANGUAGE sql STABLE
2825
  COST 100
2826
  ROWS 1000
2827
;
2828
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2829
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2830
If you want to run EXPLAIN and get expanded output, use the regular subset
2831
function instead. (When a config param is set on a function, EXPLAIN produces
2832
just a function scan.)
2833
';
2834
$$;
2835
END;
2836
$_$;
2837

    
2838

    
2839
--
2840
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
2841
--
2842

    
2843
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2844
creates subset function which turns off enable_sort
2845
';
2846

    
2847

    
2848
--
2849
-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2850
--
2851

    
2852
CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
2853
    LANGUAGE sql IMMUTABLE
2854
    AS $_$
2855
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
2856
util.qual_name((unnest).type), ', '), '')
2857
FROM unnest($1)
2858
$_$;
2859

    
2860

    
2861
--
2862
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2863
--
2864

    
2865
CREATE FUNCTION name(table_ regclass) RETURNS text
2866
    LANGUAGE sql STABLE
2867
    AS $_$
2868
SELECT relname::text FROM pg_class WHERE oid = $1
2869
$_$;
2870

    
2871

    
2872
--
2873
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2874
--
2875

    
2876
CREATE FUNCTION name(type regtype) RETURNS text
2877
    LANGUAGE sql STABLE
2878
    AS $_$
2879
SELECT typname::text FROM pg_type WHERE oid = $1
2880
$_$;
2881

    
2882

    
2883
--
2884
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
2885
--
2886

    
2887
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2888
    LANGUAGE sql IMMUTABLE
2889
    AS $_$
2890
SELECT octet_length($1) >= util.namedatalen() - $2
2891
$_$;
2892

    
2893

    
2894
--
2895
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2896
--
2897

    
2898
CREATE FUNCTION namedatalen() RETURNS integer
2899
    LANGUAGE sql IMMUTABLE
2900
    AS $$
2901
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2902
$$;
2903

    
2904

    
2905
--
2906
-- Name: new_world(); Type: FUNCTION; Schema: util; Owner: -
2907
--
2908

    
2909
CREATE FUNCTION new_world() RETURNS postgis.geography
2910
    LANGUAGE sql IMMUTABLE
2911
    SET search_path TO util
2912
    AS $$
2913
SELECT util.bounding_box(-56 ~ 83, 172 ~ -34)
2914
$$;
2915

    
2916

    
2917
--
2918
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2919
--
2920

    
2921
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2922
    LANGUAGE sql IMMUTABLE
2923
    AS $_$
2924
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2925
$_$;
2926

    
2927

    
2928
--
2929
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2930
--
2931

    
2932
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2933
    LANGUAGE sql IMMUTABLE
2934
    AS $_$
2935
SELECT $1 IS NOT NULL
2936
$_$;
2937

    
2938

    
2939
--
2940
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2941
--
2942

    
2943
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2944
    LANGUAGE sql IMMUTABLE
2945
    AS $_$
2946
SELECT util.hstore($1, NULL) || '*=>*'
2947
$_$;
2948

    
2949

    
2950
--
2951
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2952
--
2953

    
2954
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2955
for use with _map()
2956
';
2957

    
2958

    
2959
--
2960
-- Name: numrange(range); Type: FUNCTION; Schema: util; Owner: -
2961
--
2962

    
2963
CREATE FUNCTION numrange(value range) RETURNS numrange
2964
    LANGUAGE sql IMMUTABLE
2965
    AS $_$
2966
SELECT numrange($1.lower, $1.upper, $1.bounds)
2967
$_$;
2968

    
2969

    
2970
--
2971
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2972
--
2973

    
2974
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2975
    LANGUAGE sql IMMUTABLE
2976
    AS $_$
2977
SELECT $2 + COALESCE($1, 0)
2978
$_$;
2979

    
2980

    
2981
--
2982
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
2983
--
2984

    
2985
CREATE FUNCTION parent(table_ regclass) RETURNS regclass
2986
    LANGUAGE sql STABLE
2987
    AS $_$
2988
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
2989
$_$;
2990

    
2991

    
2992
--
2993
-- Name: pg_get_viewdef(regclass); Type: FUNCTION; Schema: util; Owner: -
2994
--
2995

    
2996
CREATE FUNCTION pg_get_viewdef(view_ regclass) RETURNS text
2997
    LANGUAGE sql STABLE
2998
    AS $_$
2999
SELECT util.view_def_to_orig(pg_catalog.pg_get_viewdef($1))
3000
$_$;
3001

    
3002

    
3003
--
3004
-- Name: point(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
3005
--
3006

    
3007
CREATE FUNCTION point(latitude_deg double precision, longitude_deg double precision) RETURNS postgis.geography
3008
    LANGUAGE sql IMMUTABLE
3009
    SET client_min_messages TO 'warning'
3010
    AS $_$
3011
SELECT postgis.st_setsrid(postgis.st_point(/*x_lon=*/$2, /*y_lat=*/$1),
3012
/*WGS84*/4326)::postgis.geography
3013
$_$;
3014

    
3015

    
3016
--
3017
-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3018
--
3019

    
3020
CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
3021
    LANGUAGE sql
3022
    AS $_$
3023
SELECT util.eval($$INSERT INTO $$||$1||$$
3024
$$||util.ltrim_nl($2));
3025
-- make sure the created table has the correct estimated row count
3026
SELECT util.analyze_($1);
3027
$_$;
3028

    
3029

    
3030
--
3031
-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
3032
--
3033

    
3034
CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
3035
    LANGUAGE sql IMMUTABLE
3036
    AS $_$
3037
SELECT util.qual_name(util.schema($2), $1||util.name($2))
3038
$_$;
3039

    
3040

    
3041
--
3042
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3043
--
3044

    
3045
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
3046
    LANGUAGE sql
3047
    AS $_$
3048
SELECT util.set_comment($1, concat($2, util.comment($1)))
3049
$_$;
3050

    
3051

    
3052
--
3053
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
3054
--
3055

    
3056
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
3057
comment: must start and end with a newline
3058
';
3059

    
3060

    
3061
--
3062
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
3063
--
3064

    
3065
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
3066
    LANGUAGE sql IMMUTABLE
3067
    AS $_$
3068
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
3069
$_$;
3070

    
3071

    
3072
--
3073
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
3074
--
3075

    
3076
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
3077
    LANGUAGE sql STABLE
3078
    SET search_path TO pg_temp
3079
    AS $_$
3080
SELECT $1::text
3081
$_$;
3082

    
3083

    
3084
--
3085
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
3086
--
3087

    
3088
CREATE FUNCTION qual_name(type regtype) RETURNS text
3089
    LANGUAGE sql STABLE
3090
    SET search_path TO pg_temp
3091
    AS $_$
3092
SELECT $1::text
3093
$_$;
3094

    
3095

    
3096
--
3097
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
3098
--
3099

    
3100
COMMENT ON FUNCTION qual_name(type regtype) IS '
3101
a type''s schema-qualified name
3102
';
3103

    
3104

    
3105
--
3106
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
3107
--
3108

    
3109
CREATE FUNCTION qual_name(type unknown) RETURNS text
3110
    LANGUAGE sql STABLE
3111
    AS $_$
3112
SELECT util.qual_name($1::text::regtype)
3113
$_$;
3114

    
3115

    
3116
--
3117
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
3118
--
3119

    
3120
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
3121
    LANGUAGE sql IMMUTABLE
3122
    AS $_$
3123
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
3124
$_$;
3125

    
3126

    
3127
--
3128
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
3129
--
3130

    
3131
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
3132
    LANGUAGE sql IMMUTABLE
3133
    AS $_$
3134
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
3135
$_$;
3136

    
3137

    
3138
--
3139
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
3140
--
3141

    
3142
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
3143
    LANGUAGE sql IMMUTABLE
3144
    AS $_$
3145
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
3146
$_$;
3147

    
3148

    
3149
--
3150
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
3151
--
3152

    
3153
CREATE FUNCTION raise(type text, msg text) RETURNS void
3154
    LANGUAGE sql IMMUTABLE
3155
    AS $_X$
3156
SELECT util.eval($$
3157
CREATE OR REPLACE FUNCTION pg_temp.__raise()
3158
  RETURNS void AS
3159
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
3160
$__BODY1$
3161
BEGIN
3162
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
3163
END;
3164
$__BODY1$
3165
  LANGUAGE plpgsql IMMUTABLE
3166
  COST 100;
3167
$$, verbose_ := false);
3168

    
3169
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
3170
$_X$;
3171

    
3172

    
3173
--
3174
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
3175
--
3176

    
3177
COMMENT ON FUNCTION raise(type text, msg text) IS '
3178
type: a log level from
3179
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
3180
or a condition name from
3181
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
3182
';
3183

    
3184

    
3185
--
3186
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
3187
--
3188

    
3189
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
3190
    LANGUAGE sql IMMUTABLE
3191
    AS $_$
3192
SELECT util.raise('WARNING', 'ERROR:  '||$1)
3193
$_$;
3194

    
3195

    
3196
--
3197
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
3198
--
3199

    
3200
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
3201
    LANGUAGE plpgsql IMMUTABLE STRICT
3202
    AS $$
3203
BEGIN
3204
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
3205
END;
3206
$$;
3207

    
3208

    
3209
--
3210
-- Name: range(numeric, numeric); Type: FUNCTION; Schema: util; Owner: -
3211
--
3212

    
3213
CREATE FUNCTION range(lower numeric, upper numeric) RETURNS range
3214
    LANGUAGE sql IMMUTABLE
3215
    AS $_$
3216
SELECT ($1, $2, '[]')::util.range
3217
$_$;
3218

    
3219

    
3220
--
3221
-- Name: recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
3222
--
3223

    
3224
CREATE FUNCTION recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
3225
    LANGUAGE plpgsql
3226
    AS $_$
3227
DECLARE
3228
	PG_EXCEPTION_DETAIL text;
3229
	restore_views_info util.restore_views_info;
3230
BEGIN
3231
	restore_views_info = util.save_drop_views(users);
3232
	
3233
	-- trigger the dependent_objects_still_exist exception
3234
	PERFORM util.eval(COALESCE(util.mk_drop_from_create(cmd), '')||cmd);
3235
		-- *not* CASCADE; it must trigger an exception
3236
	
3237
	PERFORM util.restore_views(restore_views_info);
3238
EXCEPTION
3239
WHEN dependent_objects_still_exist THEN
3240
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
3241
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
3242
	users = array(SELECT * FROM util.regexp_matches_group(
3243
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
3244
		-- will be in forward dependency order
3245
	PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
3246
	PERFORM util.debug_print_var('users', users);
3247
	IF util.is_empty(users) THEN RAISE; END IF;
3248
	PERFORM util.recreate(cmd, users);
3249
END;
3250
$_$;
3251

    
3252

    
3253
--
3254
-- Name: FUNCTION recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
3255
--
3256

    
3257
COMMENT ON FUNCTION recreate(cmd text, users text[]) IS '
3258
the appropriate drop statement will be added automatically.
3259

    
3260
usage:
3261
SELECT util.recreate($$
3262
CREATE VIEW schema.main_view AS _;
3263

    
3264
-- manually restore views that need to be updated for the changes
3265
CREATE VIEW schema.dependent_view AS _;
3266
$$);
3267

    
3268
idempotent
3269

    
3270
users: not necessary to provide this because it will be autopopulated
3271
';
3272

    
3273

    
3274
--
3275
-- Name: recreate_view(regclass, text, text); Type: FUNCTION; Schema: util; Owner: -
3276
--
3277

    
3278
CREATE FUNCTION recreate_view(view_ regclass, view_query text DEFAULT NULL::text, dependent_view_changes text DEFAULT ''::text) RETURNS void
3279
    LANGUAGE sql
3280
    AS $_$
3281
SELECT util.recreate($$
3282
CREATE VIEW $$||$1||$$ AS 
3283
$$||COALESCE($2, pg_get_viewdef($1))||$$;
3284
$$||util.mk_set_relation_metadata($1)||$$
3285

    
3286
-- manually restore views that need to be updated for the changes
3287
$$||$3||$$
3288
$$);
3289
$_$;
3290

    
3291

    
3292
--
3293
-- Name: FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text); Type: COMMENT; Schema: util; Owner: -
3294
--
3295

    
3296
COMMENT ON FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text) IS '
3297
usage:
3298
SELECT util.recreate_view(''schema.main_view'', $$
3299
SELECT __
3300
$$, $$
3301
CREATE VIEW schema.dependent_view AS 
3302
__;
3303
$$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
3304
$$);
3305

    
3306
if view has already been modified:
3307
SELECT util.recreate_view(''schema.main_view'', dependent_view_changes := $$
3308
CREATE VIEW schema.dependent_view AS 
3309
__;
3310
$$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
3311
$$);
3312

    
3313
idempotent
3314
';
3315

    
3316

    
3317
--
3318
-- Name: regexp_match(text, text); Type: FUNCTION; Schema: util; Owner: -
3319
--
3320

    
3321
CREATE FUNCTION regexp_match(str text, re text) RETURNS text[]
3322
    LANGUAGE sql IMMUTABLE
3323
    AS $_$
3324
SELECT match FROM regexp_matches($1, $2) match LIMIT 1/*only 1st match*/
3325
$_$;
3326

    
3327

    
3328
--
3329
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3330
--
3331

    
3332
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
3333
    LANGUAGE sql IMMUTABLE
3334
    AS $_$
3335
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
3336
$_$;
3337

    
3338

    
3339
--
3340
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
3341
--
3342

    
3343
CREATE FUNCTION regexp_quote(str text) RETURNS text
3344
    LANGUAGE sql IMMUTABLE
3345
    AS $_$
3346
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
3347
$_$;
3348

    
3349

    
3350
--
3351
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
3352
--
3353

    
3354
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
3355
    LANGUAGE sql IMMUTABLE
3356
    AS $_$
3357
SELECT (CASE WHEN right($1, 1) = ')'
3358
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
3359
$_$;
3360

    
3361

    
3362
--
3363
-- Name: relation_exists(text); Type: FUNCTION; Schema: util; Owner: -
3364
--
3365

    
3366
CREATE FUNCTION relation_exists(relation text) RETURNS boolean
3367
    LANGUAGE sql STABLE
3368
    AS $_$
3369
SELECT $1 IS NOT NULL AND util.is_castable($1, NULL::regclass)
3370
$_$;
3371

    
3372

    
3373
--
3374
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
3375
--
3376

    
3377
CREATE FUNCTION relation_type(relation regclass) RETURNS text
3378
    LANGUAGE sql STABLE
3379
    AS $_$
3380
SELECT util.relation_type(util.relation_type_char($1))
3381
$_$;
3382

    
3383

    
3384
--
3385
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
3386
--
3387

    
3388
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
3389
    LANGUAGE sql IMMUTABLE
3390
    AS $_$
3391
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
3392
$_$;
3393

    
3394

    
3395
--
3396
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
3397
--
3398

    
3399
CREATE FUNCTION relation_type(type regtype) RETURNS text
3400
    LANGUAGE sql IMMUTABLE
3401
    AS $$
3402
SELECT 'TYPE'::text
3403
$$;
3404

    
3405

    
3406
--
3407
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
3408
--
3409

    
3410
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
3411
    LANGUAGE sql STABLE
3412
    AS $_$
3413
SELECT relkind FROM pg_class WHERE oid = $1
3414
$_$;
3415

    
3416

    
3417
--
3418
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
3419
--
3420

    
3421
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
3422
    LANGUAGE sql
3423
    AS $_$
3424
/* can't have in_table/out_table inherit from *each other*, because inheritance
3425
also causes the rows of the parent table to be included in the child table.
3426
instead, they need to inherit from a common, empty table. */
3427
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
3428
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
3429
SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
3430
SELECT util.inherit($2, $4);
3431
SELECT util.inherit($3, $4);
3432

    
3433
SELECT util.rematerialize_query($1, $$
3434
SELECT * FROM util.diff(
3435
  $$||util.quote_typed($2)||$$
3436
, $$||util.quote_typed($3)||$$
3437
, NULL::$$||$4||$$)
3438
$$);
3439

    
3440
/* the table unfortunately cannot be *materialized* in human-readable form,
3441
because this would create column name collisions between the two sides */
3442
SELECT util.prepend_comment($1, '
3443
to view this table in human-readable form (with each side''s tuple column
3444
expanded to its component fields):
3445
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
3446

    
3447
to display NULL values that are extra or missing:
3448
SELECT * FROM '||$1||';
3449
');
3450
$_$;
3451

    
3452

    
3453
--
3454
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
3455
--
3456

    
3457
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
3458
type_table (*required*): table to create as the shared base type
3459
';
3460

    
3461

    
3462
--
3463
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
3464
--
3465

    
3466
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
3467
    LANGUAGE sql
3468
    AS $_$
3469
SELECT util.drop_table($1);
3470
SELECT util.materialize_query($1, $2);
3471
$_$;
3472

    
3473

    
3474
--
3475
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
3476
--
3477

    
3478
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
3479
idempotent, but repeats action each time
3480
';
3481

    
3482

    
3483
--
3484
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
3485
--
3486

    
3487
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
3488
    LANGUAGE sql
3489
    AS $_$
3490
SELECT util.drop_table($1);
3491
SELECT util.materialize_view($1, $2);
3492
$_$;
3493

    
3494

    
3495
--
3496
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
3497
--
3498

    
3499
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
3500
idempotent, but repeats action each time
3501
';
3502

    
3503

    
3504
--
3505
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
3506
--
3507

    
3508
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
3509
    LANGUAGE sql
3510
    AS $_$
3511
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
3512
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
3513
FROM util.col_names($1::text::regtype) f (name);
3514
SELECT NULL::void; -- don't fold away functions called in previous query
3515
$_$;
3516

    
3517

    
3518
--
3519
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
3520
--
3521

    
3522
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
3523
idempotent
3524
';
3525

    
3526

    
3527
--
3528
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3529
--
3530

    
3531
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
3532
    LANGUAGE sql
3533
    AS $_$
3534
/* use util.qual_name() instead of ::text so that the schema qualifier is always
3535
included in the debug SQL */
3536
SELECT util.rename_relation(util.qual_name($1), $2)
3537
$_$;
3538

    
3539

    
3540
--
3541
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
3542
--
3543

    
3544
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
3545
    LANGUAGE sql
3546
    AS $_$
3547
/* 'ALTER TABLE can be used with views too'
3548
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
3549
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
3550
||quote_ident($2))
3551
$_$;
3552

    
3553

    
3554
--
3555
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
3556
--
3557

    
3558
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
3559
idempotent
3560
';
3561

    
3562

    
3563
--
3564
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3565
--
3566

    
3567
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
3568
    LANGUAGE sql IMMUTABLE
3569
    AS $_$
3570
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
3571
$_$;
3572

    
3573

    
3574
--
3575
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
3576
--
3577

    
3578
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
3579
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 
3580
';
3581

    
3582

    
3583
--
3584
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3585
--
3586

    
3587
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
3588
    LANGUAGE sql
3589
    AS $_$
3590
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
3591
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
3592
SELECT util.set_col_names($1, $2);
3593
$_$;
3594

    
3595

    
3596
--
3597
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3598
--
3599

    
3600
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
3601
idempotent.
3602
alters the names table, so it will need to be repopulated after running this function.
3603
';
3604

    
3605

    
3606
--
3607
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
3608
--
3609

    
3610
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
3611
    LANGUAGE sql
3612
    AS $_$
3613
SELECT util.drop_table($1);
3614
SELECT util.mk_map_table($1);
3615
$_$;
3616

    
3617

    
3618
--
3619
-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
3620
--
3621

    
3622
CREATE FUNCTION restore_views(restore_views_info) RETURNS void
3623
    LANGUAGE sql
3624
    AS $_$
3625
SELECT util.debug_print_var('views', $1);
3626
SELECT util.create_if_not_exists((view_).def, (view_).path)
3627
	/* need to specify view name for manual existence check, in case view def
3628
	becomes invalid, which would produce nonstandard (uncatchable) exception */
3629
FROM unnest($1.views) view_; -- in forward dependency order
3630
	/* create_if_not_exists() rather than eval(), because cmd might manually
3631
	re-create a deleted dependent view, causing it to already exist */
3632
SELECT NULL::void; -- don't fold away functions called in previous query
3633
$_$;
3634

    
3635

    
3636
--
3637
-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
3638
--
3639

    
3640
CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
3641
    LANGUAGE sql
3642
    AS $_$
3643
SELECT util.drop_column($1, $2, force := true)
3644
$_$;
3645

    
3646

    
3647
--
3648
-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
3649
--
3650

    
3651
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
3652
    LANGUAGE sql IMMUTABLE
3653
    AS $_$
3654
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
3655
$_$;
3656

    
3657

    
3658
--
3659
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
3660
--
3661

    
3662
CREATE FUNCTION runnable_sql(sql text) RETURNS text
3663
    LANGUAGE sql IMMUTABLE
3664
    AS $_$
3665
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
3666
ELSE util.mk_set_search_path(for_printing := true)||$$;
3667
$$ END)||$1
3668
$_$;
3669

    
3670

    
3671
--
3672
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
3673
--
3674

    
3675
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
3676
    LANGUAGE plpgsql STRICT
3677
    AS $$
3678
DECLARE
3679
	result text = NULL;
3680
BEGIN
3681
	BEGIN
3682
		result = util.show_create_view(view_, replace := false);
3683
			/* replace: no `OR REPLACE` because that causes nonuniform errors
3684
			(eg. invalid_table_definition), instead of the standard
3685
			duplicate_table exception caught by util.create_if_not_exists() */
3686
		PERFORM util.drop_view(view_);
3687
	EXCEPTION
3688
		WHEN undefined_table THEN NULL;
3689
	END;
3690
	RETURN result;
3691
END;
3692
$$;
3693

    
3694

    
3695
--
3696
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
3697
--
3698

    
3699
CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
3700
    LANGUAGE sql
3701
    AS $_$
3702
SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
3703
SELECT (view_, util.save_drop_view(view_))::util.db_item
3704
FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
3705
)))::util.restore_views_info
3706
$_$;
3707

    
3708

    
3709
--
3710
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
3711
--
3712

    
3713
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
3714
    LANGUAGE sql STABLE
3715
    AS $_$
3716
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
3717
$_$;
3718

    
3719

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

    
3724
CREATE FUNCTION schema(table_ regclass) RETURNS text
3725
    LANGUAGE sql STABLE
3726
    AS $_$
3727
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
3728
$_$;
3729

    
3730

    
3731
--
3732
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
3733
--
3734

    
3735
CREATE FUNCTION schema(type regtype) RETURNS text
3736
    LANGUAGE sql STABLE
3737
    AS $_$
3738
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
3739
$_$;
3740

    
3741

    
3742
--
3743
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3744
--
3745

    
3746
CREATE FUNCTION schema(type_null anyelement) RETURNS text
3747
    LANGUAGE sql STABLE
3748
    AS $_$
3749
SELECT util.schema(pg_typeof($1))
3750
$_$;
3751

    
3752

    
3753
--
3754
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
3755
--
3756

    
3757
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
3758
    LANGUAGE sql STABLE
3759
    AS $_$
3760
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
3761
$_$;
3762

    
3763

    
3764
--
3765
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
3766
--
3767

    
3768
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
3769
a schema bundle is a group of schemas with a common prefix
3770
';
3771

    
3772

    
3773
--
3774
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3775
--
3776

    
3777
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
3778
    LANGUAGE sql
3779
    AS $_$
3780
SELECT util.schema_rename(old_schema,
3781
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
3782
FROM util.schema_bundle_get_schemas($1) f (old_schema);
3783
SELECT NULL::void; -- don't fold away functions called in previous query
3784
$_$;
3785

    
3786

    
3787
--
3788
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3789
--
3790

    
3791
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
3792
    LANGUAGE plpgsql
3793
    AS $$
3794
BEGIN
3795
	-- don't schema_bundle_rm() the schema_bundle to keep!
3796
	IF replace = with_ THEN RETURN; END IF;
3797
	
3798
	PERFORM util.schema_bundle_rm(replace);
3799
	PERFORM util.schema_bundle_rename(with_, replace);
3800
END;
3801
$$;
3802

    
3803

    
3804
--
3805
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3806
--
3807

    
3808
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
3809
    LANGUAGE sql
3810
    AS $_$
3811
SELECT util.schema_rm(schema)
3812
FROM util.schema_bundle_get_schemas($1) f (schema);
3813
SELECT NULL::void; -- don't fold away functions called in previous query
3814
$_$;
3815

    
3816

    
3817
--
3818
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3819
--
3820

    
3821
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
3822
    LANGUAGE sql STABLE
3823
    AS $_$
3824
SELECT quote_ident(util.schema($1))
3825
$_$;
3826

    
3827

    
3828
--
3829
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
3830
--
3831

    
3832
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
3833
    LANGUAGE sql IMMUTABLE
3834
    AS $_$
3835
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
3836
$_$;
3837

    
3838

    
3839
--
3840
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3841
--
3842

    
3843
CREATE FUNCTION schema_oid(schema text) RETURNS oid
3844
    LANGUAGE sql STABLE
3845
    AS $_$
3846
SELECT oid FROM pg_namespace WHERE nspname = $1
3847
$_$;
3848

    
3849

    
3850
--
3851
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3852
--
3853

    
3854
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
3855
    LANGUAGE sql IMMUTABLE
3856
    AS $_$
3857
SELECT util.schema_regexp(schema_anchor := $1)
3858
$_$;
3859

    
3860

    
3861
--
3862
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3863
--
3864

    
3865
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
3866
    LANGUAGE sql IMMUTABLE
3867
    AS $_$
3868
SELECT util.str_equality_regexp(util.schema($1))
3869
$_$;
3870

    
3871

    
3872
--
3873
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3874
--
3875

    
3876
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
3877
    LANGUAGE sql
3878
    AS $_$
3879
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
3880
$_$;
3881

    
3882

    
3883
--
3884
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3885
--
3886

    
3887
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
3888
    LANGUAGE plpgsql
3889
    AS $$
3890
BEGIN
3891
	-- don't schema_rm() the schema to keep!
3892
	IF replace = with_ THEN RETURN; END IF;
3893
	
3894
	PERFORM util.schema_rm(replace);
3895
	PERFORM util.schema_rename(with_, replace);
3896
END;
3897
$$;
3898

    
3899

    
3900
--
3901
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3902
--
3903

    
3904
CREATE FUNCTION schema_rm(schema text) RETURNS void
3905
    LANGUAGE sql
3906
    AS $_$
3907
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
3908
$_$;
3909

    
3910

    
3911
--
3912
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3913
--
3914

    
3915
CREATE FUNCTION search_path_append(schemas text) RETURNS void
3916
    LANGUAGE sql
3917
    AS $_$
3918
SELECT util.eval(
3919
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
3920
$_$;
3921

    
3922

    
3923
--
3924
-- Name: seq__create(text, integer); Type: FUNCTION; Schema: util; Owner: -
3925
--
3926

    
3927
CREATE FUNCTION seq__create(seq text, start integer DEFAULT 0) RETURNS void
3928
    LANGUAGE sql
3929
    AS $_$
3930
SELECT util.create_if_not_exists($$CREATE SEQUENCE $$||$1||$$ MINVALUE $$||$2,
3931
$1)
3932
$_$;
3933

    
3934

    
3935
--
3936
-- Name: FUNCTION seq__create(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
3937
--
3938

    
3939
COMMENT ON FUNCTION seq__create(seq text, start integer) IS '
3940
idempotent
3941
';
3942

    
3943

    
3944
--
3945
-- Name: seq__reset(text, integer); Type: FUNCTION; Schema: util; Owner: -
3946
--
3947

    
3948
CREATE FUNCTION seq__reset(seq text, start integer DEFAULT 0) RETURNS void
3949
    LANGUAGE sql
3950
    AS $_$
3951
SELECT util.seq__create($1, $2);
3952
SELECT util.eval($$ALTER SEQUENCE $$||$1||$$ RESTART$$);
3953
$_$;
3954

    
3955

    
3956
--
3957
-- Name: FUNCTION seq__reset(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
3958
--
3959

    
3960
COMMENT ON FUNCTION seq__reset(seq text, start integer) IS '
3961
creates sequence if doesn''t exist
3962

    
3963
idempotent
3964

    
3965
start: *note*: only used if sequence doesn''t exist
3966
';
3967

    
3968

    
3969
--
3970
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3971
--
3972

    
3973
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
3974
    LANGUAGE plpgsql STRICT
3975
    AS $_$
3976
DECLARE
3977
    old text[] = ARRAY(SELECT util.col_names(table_));
3978
    new text[] = ARRAY(SELECT util.map_values(names));
3979
BEGIN
3980
    old = old[1:array_length(new, 1)]; -- truncate to same length
3981
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
3982
||$$ TO $$||quote_ident(value))
3983
    FROM each(hstore(old, new))
3984
    WHERE value != key -- not same name
3985
    ;
3986
END;
3987
$_$;
3988

    
3989

    
3990
--
3991
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3992
--
3993

    
3994
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
3995
idempotent
3996
';
3997

    
3998

    
3999
--
4000
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
4001
--
4002

    
4003
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
4004
    LANGUAGE plpgsql STRICT
4005
    AS $_$
4006
DECLARE
4007
	row_ util.map;
4008
BEGIN
4009
	-- rename any metadata cols rather than re-adding them with new names
4010
	BEGIN
4011
		PERFORM util.set_col_names(table_, names);
4012
	EXCEPTION
4013
		WHEN array_subscript_error THEN -- selective suppress
4014
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
4015
				-- metadata cols not yet added
4016
			ELSE RAISE;
4017
			END IF;
4018
	END;
4019
	
4020
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
4021
	LOOP
4022
		PERFORM util.mk_const_col((table_, row_."to"),
4023
			substring(row_."from" from 2));
4024
	END LOOP;
4025
	
4026
	PERFORM util.set_col_names(table_, names);
4027
END;
4028
$_$;
4029

    
4030

    
4031
--
4032
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
4033
--
4034

    
4035
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
4036
idempotent.
4037
the metadata mappings must be *last* in the names table.
4038
';
4039

    
4040

    
4041
--
4042
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
4043
--
4044

    
4045
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
4046
    LANGUAGE sql
4047
    AS $_$
4048
SELECT util.eval(COALESCE(
4049
$$ALTER TABLE $$||$1||$$
4050
$$||(
4051
	SELECT
4052
	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
4053
	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
4054
, $$)
4055
	FROM
4056
	(
4057
		SELECT
4058
		  quote_ident(col_name) AS col_name_sql
4059
		, util.col_type(($1, col_name)) AS curr_type
4060
		, type AS target_type
4061
		FROM unnest($2)
4062
	) s
4063
	WHERE curr_type != target_type
4064
), ''))
4065
$_$;
4066

    
4067

    
4068
--
4069
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
4070
--
4071

    
4072
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
4073
idempotent
4074
';
4075

    
4076

    
4077
--
4078
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
4079
--
4080

    
4081
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
4082
    LANGUAGE sql
4083
    AS $_$
4084
SELECT util.eval(util.mk_set_comment($1, $2))
4085
$_$;
4086

    
4087

    
4088
--
4089
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
4090
--
4091

    
4092
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
4093
    LANGUAGE sql
4094
    AS $_$
4095
SELECT util.eval(util.mk_set_search_path($1, $2))
4096
$_$;
4097

    
4098

    
4099
--
4100
-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
4101
--
4102

    
4103
CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
4104
    LANGUAGE sql STABLE
4105
    AS $_$
4106
SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
4107
||$1||$$ AS
4108
$$||pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
4109
$$||util.mk_set_relation_metadata($1)
4110
$_$;
4111

    
4112

    
4113
--
4114
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
4115
--
4116

    
4117
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
4118
    LANGUAGE sql STABLE
4119
    AS $_$
4120
SELECT string_agg(cmd, '')
4121
FROM
4122
(
4123
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
4124
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
4125
$$ ELSE '' END) AS cmd
4126
	FROM util.grants_users() f (user_)
4127
) s
4128
$_$;
4129

    
4130

    
4131
--
4132
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
4133
--
4134

    
4135
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
4136
    LANGUAGE sql STABLE
4137
    AS $_$
4138
SELECT oid FROM pg_class
4139
WHERE relkind = ANY($3) AND relname ~ $1
4140
AND util.schema_matches(util.schema(relnamespace), $2)
4141
ORDER BY relname
4142
$_$;
4143

    
4144

    
4145
--
4146
-- Name: show_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
4147
--
4148

    
4149
CREATE FUNCTION show_set_comment(table_ regclass) RETURNS text
4150
    LANGUAGE sql STABLE
4151
    AS $_$
4152
SELECT util.mk_set_comment($1, util.comment($1))
4153
$_$;
4154

    
4155

    
4156
--
4157
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
4158
--
4159

    
4160
CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
4161
    LANGUAGE sql STABLE
4162
    AS $_$
4163
SELECT oid
4164
FROM pg_type
4165
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
4166
ORDER BY typname
4167
$_$;
4168

    
4169

    
4170
--
4171
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
4172
--
4173

    
4174
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
4175
    LANGUAGE sql STABLE
4176
    AS $_$
4177
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
4178
$_$;
4179

    
4180

    
4181
--
4182
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
4183
--
4184

    
4185
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
4186
    LANGUAGE sql IMMUTABLE
4187
    AS $_$
4188
SELECT '^'||util.regexp_quote($1)||'$'
4189
$_$;
4190

    
4191

    
4192
--
4193
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
4194
--
4195

    
4196
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
4197
    LANGUAGE plpgsql STABLE STRICT
4198
    AS $_$
4199
DECLARE
4200
    hstore hstore;
4201
BEGIN
4202
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
4203
        table_||$$))$$ INTO STRICT hstore;
4204
    RETURN hstore;
4205
END;
4206
$_$;
4207

    
4208

    
4209
--
4210
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
4211
--
4212

    
4213
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
4214
    LANGUAGE sql STABLE
4215
    AS $_$
4216
SELECT COUNT(*) > 0 FROM pg_constraint
4217
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
4218
$_$;
4219

    
4220

    
4221
--
4222
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
4223
--
4224

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

    
4229

    
4230
--
4231
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
4232
--
4233

    
4234
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
4235
    LANGUAGE sql
4236
    AS $_$
4237
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
4238
||quote_ident($2)||$$ CHECK (true)$$)
4239
$_$;
4240

    
4241

    
4242
--
4243
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
4244
--
4245

    
4246
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
4247
stores a status flag by the presence of a table constraint.
4248
idempotent.
4249
';
4250

    
4251

    
4252
--
4253
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
4254
--
4255

    
4256
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
4257
    LANGUAGE sql STABLE
4258
    AS $_$
4259
SELECT util.table_flag__get($1, 'nulls_mapped')
4260
$_$;
4261

    
4262

    
4263
--
4264
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4265
--
4266

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

    
4271

    
4272
--
4273
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
4274
--
4275

    
4276
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
4277
    LANGUAGE sql
4278
    AS $_$
4279
SELECT util.table_flag__set($1, 'nulls_mapped')
4280
$_$;
4281

    
4282

    
4283
--
4284
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4285
--
4286

    
4287
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
4288
sets that a table''s NULL-equivalent strings have been replaced with NULL.
4289
idempotent.
4290
';
4291

    
4292

    
4293
--
4294
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
4295
--
4296

    
4297
CREATE FUNCTION to_freq(table_ regclass) RETURNS void
4298
    LANGUAGE sql
4299
    AS $_$
4300
-- save data before truncating main table
4301
SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
4302

    
4303
-- repopulate main table w/ copies column
4304
SELECT util.truncate($1);
4305
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
4306
SELECT util.populate_table($1, $$
4307
SELECT (table_).*, copies
4308
FROM (
4309
	SELECT table_, COUNT(*) AS copies
4310
	FROM pg_temp.__copy table_
4311
	GROUP BY table_
4312
) s
4313
$$);
4314

    
4315
-- delete temp table so it doesn't stay around until end of connection
4316
SELECT util.drop_table('pg_temp.__copy');
4317
$_$;
4318

    
4319

    
4320
--
4321
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
4322
--
4323

    
4324
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
4325
    LANGUAGE plpgsql STRICT
4326
    AS $_$
4327
DECLARE
4328
    row record;
4329
BEGIN
4330
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
4331
    LOOP
4332
        IF row.global_name != row.name THEN
4333
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
4334
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
4335
        END IF;
4336
    END LOOP;
4337
END;
4338
$_$;
4339

    
4340

    
4341
--
4342
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4343
--
4344

    
4345
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
4346
idempotent
4347
';
4348

    
4349

    
4350
--
4351
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
4352
--
4353

    
4354
CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
4355
    LANGUAGE sql
4356
    AS $_$
4357
SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
4358
SELECT NULL::void; -- don't fold away functions called in previous query
4359
$_$;
4360

    
4361

    
4362
--
4363
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
4364
--
4365

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

    
4369
by default, cascadingly drops dependent columns so that they don''t prevent
4370
trim() from succeeding. note that this requires the dependent columns to then be
4371
manually re-created.
4372

    
4373
idempotent
4374
';
4375

    
4376

    
4377
--
4378
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
4379
--
4380

    
4381
CREATE FUNCTION truncate(table_ regclass) RETURNS void
4382
    LANGUAGE plpgsql STRICT
4383
    AS $_$
4384
BEGIN
4385
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
4386
END;
4387
$_$;
4388

    
4389

    
4390
--
4391
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4392
--
4393

    
4394
COMMENT ON FUNCTION truncate(table_ regclass) IS '
4395
idempotent
4396
';
4397

    
4398

    
4399
--
4400
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
4401
--
4402

    
4403
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
4404
    LANGUAGE sql IMMUTABLE
4405
    AS $_$
4406
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
4407
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
4408
$_$;
4409

    
4410

    
4411
--
4412
-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
4413
--
4414

    
4415
CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
4416
    LANGUAGE plpgsql IMMUTABLE
4417
    AS $$
4418
BEGIN
4419
	/* need explicit cast because some types not implicitly-castable, and also
4420
	to make the cast happen inside the try block. (*implicit* casts to the
4421
	return type happen at the end of the function, outside any block.) */
4422
	RETURN util.cast(value, ret_type_null);
4423
EXCEPTION
4424
WHEN   data_exception
4425
	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
4426
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
4427
	THEN
4428
	PERFORM util.raise('WARNING', SQLERRM);
4429
	RETURN NULL;
4430
END;
4431
$$;
4432

    
4433

    
4434
--
4435
-- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
4436
--
4437

    
4438
COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
4439
ret_type_null: NULL::ret_type
4440
';
4441

    
4442

    
4443
--
4444
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
4445
--
4446

    
4447
CREATE FUNCTION try_create(sql text) RETURNS void
4448
    LANGUAGE plpgsql STRICT
4449
    AS $$
4450
BEGIN
4451
	PERFORM util.eval(sql);
4452
EXCEPTION
4453
WHEN   not_null_violation
4454
		/* trying to add NOT NULL column to parent table, which cascades to
4455
		child table whose values for the new column will be NULL */
4456
	OR wrong_object_type -- trying to alter a view's columns
4457
	OR undefined_column
4458
	OR duplicate_column
4459
THEN NULL;
4460
WHEN datatype_mismatch THEN
4461
	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
4462
	ELSE RAISE; -- rethrow
4463
	END IF;
4464
END;
4465
$$;
4466

    
4467

    
4468
--
4469
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
4470
--
4471

    
4472
COMMENT ON FUNCTION try_create(sql text) IS '
4473
idempotent
4474
';
4475

    
4476

    
4477
--
4478
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
4479
--
4480

    
4481
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
4482
    LANGUAGE sql
4483
    AS $_$
4484
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
4485
$_$;
4486

    
4487

    
4488
--
4489
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
4490
--
4491

    
4492
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
4493
idempotent
4494
';
4495

    
4496

    
4497
--
4498
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
4499
--
4500

    
4501
CREATE FUNCTION type_qual(value anyelement) RETURNS text
4502
    LANGUAGE sql IMMUTABLE
4503
    AS $_$
4504
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
4505
$_$;
4506

    
4507

    
4508
--
4509
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
4510
--
4511

    
4512
COMMENT ON FUNCTION type_qual(value anyelement) IS '
4513
a type''s NOT NULL qualifier
4514
';
4515

    
4516

    
4517
--
4518
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
4519
--
4520

    
4521
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
4522
    LANGUAGE sql STABLE
4523
    AS $_$
4524
SELECT (attname::text, atttypid)::util.col_cast
4525
FROM pg_attribute
4526
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
4527
ORDER BY attnum
4528
$_$;
4529

    
4530

    
4531
--
4532
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
4533
--
4534

    
4535
CREATE FUNCTION typeof(value anyelement) RETURNS text
4536
    LANGUAGE sql IMMUTABLE
4537
    AS $_$
4538
SELECT util.qual_name(pg_typeof($1))
4539
$_$;
4540

    
4541

    
4542
--
4543
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
4544
--
4545

    
4546
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
4547
    LANGUAGE plpgsql STABLE
4548
    AS $_$
4549
DECLARE
4550
    type regtype;
4551
BEGIN
4552
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
4553
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
4554
    RETURN type;
4555
END;
4556
$_$;
4557

    
4558

    
4559
--
4560
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
4561
--
4562

    
4563
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
4564
    LANGUAGE sql
4565
    AS $_$
4566
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
4567
$_$;
4568

    
4569

    
4570
--
4571
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
4572
--
4573

    
4574
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
4575
auto-appends util to the search_path to enable use of util operators
4576
';
4577

    
4578

    
4579
--
4580
-- Name: view_def_to_orig(text); Type: FUNCTION; Schema: util; Owner: -
4581
--
4582

    
4583
CREATE FUNCTION view_def_to_orig(view_def text) RETURNS text
4584
    LANGUAGE sql IMMUTABLE
4585
    AS $_$
4586
SELECT
4587
regexp_replace(
4588
regexp_replace(
4589
$1
4590
,
4591
/* unexpand expanded * expressions. any list of 6+ cols from the same table is
4592
treated as a * expression. */
4593
'((?:"[^"\s]+"|\w+)\.)(?:"[^"\s]+"|\w+)'||
4594
	/* 1st col, which lacks separator before.
4595
	*note*: can't prepend \y because it considers only \w chars, not " */
4596
'(,[[:blank:]]*
4597
[[:blank:]]*)\1(?:"[^"\s]+"|\w+)'|| -- 2nd col, which has separator before
4598
'(?:\2\1(?:"[^"\s]+"|\w+)){4,}', -- later cols, w/ same table name and separator
4599
'\1*'/*prefix w/ table*/,
4600
'g')
4601
,
4602
/* merge .* expressions resulting from a SELECT * of a join. any list of
4603
multiple .* expressions is treated as a SELECT * . */
4604
'(?:"[^"\s]+"|\w+)\.\*'||
4605
	/* 1st table, which lacks separator before.
4606
	*note*: can't prepend \y because it considers only \w chars, not " */
4607
'(,[[:blank:]]*
4608
[[:blank:]]*)(?:"[^"\s]+"|\w+)\.\*'|| -- 2nd table, which has separator before
4609
'(?:\1(?:"[^"\s]+"|\w+)\.\*)*', -- later tables, w/ same separator
4610
'*',
4611
'g')
4612
$_$;
4613

    
4614

    
4615
--
4616
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
4617
--
4618

    
4619
CREATE AGGREGATE all_same(anyelement) (
4620
    SFUNC = all_same_transform,
4621
    STYPE = anyarray,
4622
    FINALFUNC = all_same_final
4623
);
4624

    
4625

    
4626
--
4627
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
4628
--
4629

    
4630
COMMENT ON AGGREGATE all_same(anyelement) IS '
4631
includes NULLs in comparison
4632
';
4633

    
4634

    
4635
--
4636
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
4637
--
4638

    
4639
CREATE AGGREGATE join_strs(text, text) (
4640
    SFUNC = join_strs_transform,
4641
    STYPE = text
4642
);
4643

    
4644

    
4645
--
4646
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
4647
--
4648

    
4649
CREATE OPERATOR %== (
4650
    PROCEDURE = keys_eq,
4651
    LEFTARG = anyelement,
4652
    RIGHTARG = anyelement
4653
);
4654

    
4655

    
4656
--
4657
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
4658
--
4659

    
4660
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
4661
returns whether the map-keys of the compared values are the same
4662
(mnemonic: % is the Perl symbol for a hash map)
4663

    
4664
should be overridden for types that store both keys and values
4665

    
4666
used in a FULL JOIN to select which columns to join on
4667
';
4668

    
4669

    
4670
--
4671
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
4672
--
4673

    
4674
CREATE OPERATOR -> (
4675
    PROCEDURE = map_get,
4676
    LEFTARG = regclass,
4677
    RIGHTARG = text
4678
);
4679

    
4680

    
4681
--
4682
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
4683
--
4684

    
4685
CREATE OPERATOR => (
4686
    PROCEDURE = hstore,
4687
    LEFTARG = text[],
4688
    RIGHTARG = text
4689
);
4690

    
4691

    
4692
--
4693
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
4694
--
4695

    
4696
COMMENT ON OPERATOR => (text[], text) IS '
4697
usage: array[''key1'', ...]::text[] => ''value''
4698
';
4699

    
4700

    
4701
--
4702
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
4703
--
4704

    
4705
CREATE OPERATOR ?*>= (
4706
    PROCEDURE = is_populated_more_often_than,
4707
    LEFTARG = anyelement,
4708
    RIGHTARG = anyelement
4709
);
4710

    
4711

    
4712
--
4713
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
4714
--
4715

    
4716
CREATE OPERATOR ?>= (
4717
    PROCEDURE = is_more_complete_than,
4718
    LEFTARG = anyelement,
4719
    RIGHTARG = anyelement
4720
);
4721

    
4722

    
4723
--
4724
-- Name: @; Type: OPERATOR; Schema: util; Owner: -
4725
--
4726

    
4727
CREATE OPERATOR @ (
4728
    PROCEDURE = contained_within,
4729
    LEFTARG = postgis.geography,
4730
    RIGHTARG = postgis.geography
4731
);
4732

    
4733

    
4734
--
4735
-- Name: OPERATOR @ (postgis.geography, postgis.geography); Type: COMMENT; Schema: util; Owner: -
4736
--
4737

    
4738
COMMENT ON OPERATOR @ (postgis.geography, postgis.geography) IS '
4739
can''t use && because it only compares 2D bounding boxes (which are geometry
4740
objects that do not support geocoordinate wraparound)
4741
';
4742

    
4743

    
4744
--
4745
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
4746
--
4747

    
4748
CREATE OPERATOR ||% (
4749
    PROCEDURE = concat_esc,
4750
    LEFTARG = text,
4751
    RIGHTARG = text
4752
);
4753

    
4754

    
4755
--
4756
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
4757
--
4758

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

    
4763

    
4764
--
4765
-- Name: ~; Type: OPERATOR; Schema: util; Owner: -
4766
--
4767

    
4768
CREATE OPERATOR ~ (
4769
    PROCEDURE = range,
4770
    LEFTARG = numeric,
4771
    RIGHTARG = numeric
4772
);
4773

    
4774

    
4775
SET search_path = pg_catalog;
4776

    
4777
--
4778
-- Name: CAST (util.range AS numrange); Type: CAST; Schema: pg_catalog; Owner: -
4779
--
4780

    
4781
CREATE CAST (util.range AS numrange) WITH FUNCTION util.numrange(util.range) AS IMPLICIT;
4782

    
4783

    
4784
SET search_path = util, pg_catalog;
4785

    
4786
--
4787
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
4788
--
4789

    
4790
CREATE TABLE map (
4791
    "from" text NOT NULL,
4792
    "to" text,
4793
    filter text,
4794
    notes text
4795
);
4796

    
4797

    
4798
--
4799
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
4800
--
4801

    
4802

    
4803

    
4804
--
4805
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
4806
--
4807

    
4808

    
4809

    
4810
--
4811
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4812
--
4813

    
4814
ALTER TABLE ONLY map
4815
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
4816

    
4817

    
4818
--
4819
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4820
--
4821

    
4822
ALTER TABLE ONLY map
4823
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
4824

    
4825

    
4826
--
4827
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
4828
--
4829

    
4830
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
4831

    
4832

    
4833
--
4834
-- PostgreSQL database dump complete
4835
--
4836

    
(21-21/31)