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: restore_views_info; Type: TYPE; Schema: util; Owner: -
86
--
87

    
88
CREATE TYPE restore_views_info AS (
89
	views db_item[]
90
);
91

    
92

    
93
--
94
-- Name: %==(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
95
--
96

    
97
CREATE FUNCTION "%=="(left_ anyelement, right_ anyelement) RETURNS boolean
98
    LANGUAGE sql STABLE
99
    AS $_$
100
SELECT keys($1) = keys($2)
101
$_$;
102

    
103

    
104
--
105
-- Name: FUNCTION "%=="(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
106
--
107

    
108
COMMENT ON FUNCTION "%=="(left_ anyelement, right_ anyelement) IS '
109
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**)
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: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
738
--
739

    
740
CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
741
    LANGUAGE plpgsql IMMUTABLE
742
    AS $$
743
/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
744
return value, causing a type mismatch */
745
BEGIN
746
	-- will then be assignment-cast to return type via INOUT
747
	RETURN value::cstring;
748
END;
749
$$;
750

    
751

    
752
--
753
-- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
754
--
755

    
756
COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
757
allows casting to an arbitrary type without eval()
758

    
759
usage:
760
SELECT util.cast(''value'', NULL::integer);
761

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

    
766
ret_type_null: NULL::ret_type
767
';
768

    
769

    
770
--
771
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
772
--
773

    
774
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
775
    LANGUAGE sql STABLE
776
    AS $_$
777
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
778
$_$;
779

    
780

    
781
--
782
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
783
--
784

    
785
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
786
    LANGUAGE plpgsql STRICT
787
    AS $_$
788
BEGIN
789
    -- not yet clustered (ARRAY[] compares NULLs literally)
790
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
791
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
792
    END IF;
793
END;
794
$_$;
795

    
796

    
797
--
798
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
799
--
800

    
801
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
802
idempotent
803
';
804

    
805

    
806
--
807
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
808
--
809

    
810
CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
811
    LANGUAGE sql IMMUTABLE
812
    AS $_$
813
SELECT value
814
FROM unnest($1) value
815
WHERE value IS NOT NULL
816
LIMIT 1
817
$_$;
818

    
819

    
820
--
821
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
822
--
823

    
824
COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
825
uses:
826
* coalescing array elements or rows together
827
* forcing evaluation of all values of a COALESCE()
828
';
829

    
830

    
831
--
832
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
833
--
834

    
835
CREATE FUNCTION col__min(col col_ref) RETURNS integer
836
    LANGUAGE sql STABLE
837
    AS $_$
838
SELECT util.eval2val($$
839
SELECT $$||quote_ident($1.name)||$$
840
FROM $$||$1.table_||$$
841
ORDER BY $$||quote_ident($1.name)||$$ ASC
842
LIMIT 1
843
$$, NULL::integer)
844
$_$;
845

    
846

    
847
--
848
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
849
--
850

    
851
CREATE FUNCTION col_comment(col col_ref) RETURNS text
852
    LANGUAGE plpgsql STABLE STRICT
853
    AS $$
854
DECLARE
855
	comment text;
856
BEGIN
857
	SELECT description
858
	FROM pg_attribute
859
	LEFT JOIN pg_description ON objoid = attrelid
860
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
861
	WHERE attrelid = col.table_ AND attname = col.name
862
	INTO STRICT comment
863
	;
864
	RETURN comment;
865
EXCEPTION
866
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
867
END;
868
$$;
869

    
870

    
871
--
872
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
873
--
874

    
875
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
876
    LANGUAGE plpgsql STABLE STRICT
877
    AS $$
878
DECLARE
879
	default_sql text;
880
BEGIN
881
	SELECT adsrc
882
	FROM pg_attribute
883
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
884
	WHERE attrelid = col.table_ AND attname = col.name
885
	INTO STRICT default_sql
886
	;
887
	RETURN default_sql;
888
EXCEPTION
889
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
890
END;
891
$$;
892

    
893

    
894
--
895
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
896
--
897

    
898
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
899
    LANGUAGE sql STABLE
900
    AS $_$
901
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
902
$_$;
903

    
904

    
905
--
906
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
907
--
908

    
909
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
910
ret_type_null: NULL::ret_type
911
';
912

    
913

    
914
--
915
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
916
--
917

    
918
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
919
    LANGUAGE plpgsql STRICT
920
    AS $$
921
BEGIN
922
    PERFORM util.col_type(col);
923
    RETURN true;
924
EXCEPTION
925
    WHEN undefined_column THEN RETURN false;
926
END;
927
$$;
928

    
929

    
930
--
931
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
932
--
933

    
934
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
935
    LANGUAGE plpgsql STABLE STRICT
936
    AS $$
937
DECLARE
938
    prefix text := util.name(type)||'.';
939
BEGIN
940
    RETURN QUERY
941
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
942
        FROM util.col_names(type) f (name_);
943
END;
944
$$;
945

    
946

    
947
--
948
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
949
--
950

    
951
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
952
    LANGUAGE sql STABLE
953
    AS $_$
954
SELECT attname::text
955
FROM pg_attribute
956
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
957
ORDER BY attnum
958
$_$;
959

    
960

    
961
--
962
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
963
--
964

    
965
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
966
    LANGUAGE plpgsql STABLE STRICT
967
    AS $_$
968
BEGIN
969
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
970
END;
971
$_$;
972

    
973

    
974
--
975
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
976
--
977

    
978
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
979
    LANGUAGE plpgsql STABLE STRICT
980
    AS $$
981
DECLARE
982
    type regtype;
983
BEGIN
984
    SELECT atttypid FROM pg_attribute
985
    WHERE attrelid = col.table_ AND attname = col.name
986
    INTO STRICT type
987
    ;
988
    RETURN type;
989
EXCEPTION
990
    WHEN no_data_found THEN
991
        RAISE undefined_column USING MESSAGE =
992
            concat('undefined column: ', col.name);
993
END;
994
$$;
995

    
996

    
997
--
998
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
999
--
1000

    
1001
CREATE FUNCTION comment(element oid) RETURNS text
1002
    LANGUAGE sql STABLE
1003
    AS $_$
1004
SELECT description FROM pg_description WHERE objoid = $1
1005
$_$;
1006

    
1007

    
1008
--
1009
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
1010
--
1011

    
1012
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
1013
    LANGUAGE sql IMMUTABLE
1014
    AS $_$
1015
SELECT util.esc_name__append($2, $1)
1016
$_$;
1017

    
1018

    
1019
--
1020
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
1021
--
1022

    
1023
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
1024
    LANGUAGE sql IMMUTABLE
1025
    AS $_$
1026
SELECT position($1 in $2) > 0 /*1-based offset*/
1027
$_$;
1028

    
1029

    
1030
--
1031
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1032
--
1033

    
1034
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
1035
    LANGUAGE sql
1036
    AS $_$
1037
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
1038
$_$;
1039

    
1040

    
1041
--
1042
-- Name: copy_types_and_data(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1043
--
1044

    
1045
CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
1046
    LANGUAGE sql
1047
    AS $_$
1048
SELECT util.materialize_view($2, $1)
1049
$_$;
1050

    
1051

    
1052
--
1053
-- Name: create_if_not_exists(text, text); Type: FUNCTION; Schema: util; Owner: -
1054
--
1055

    
1056
CREATE FUNCTION create_if_not_exists(sql text, relation text DEFAULT NULL::text) RETURNS void
1057
    LANGUAGE plpgsql
1058
    AS $$
1059
BEGIN
1060
	/* always generate standard exception if exists, even if table definition
1061
	would be invalid (which generates a variety of exceptions) */
1062
	IF util.relation_exists(relation) THEN RAISE duplicate_table; END IF;
1063
	PERFORM util.eval(sql);
1064
EXCEPTION
1065
WHEN   duplicate_table
1066
	OR duplicate_object -- eg. constraint
1067
	OR duplicate_column
1068
	OR duplicate_function
1069
THEN NULL;
1070
WHEN invalid_table_definition THEN
1071
	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
1072
	ELSE RAISE;
1073
	END IF;
1074
END;
1075
$$;
1076

    
1077

    
1078
--
1079
-- Name: FUNCTION create_if_not_exists(sql text, relation text); Type: COMMENT; Schema: util; Owner: -
1080
--
1081

    
1082
COMMENT ON FUNCTION create_if_not_exists(sql text, relation text) IS '
1083
idempotent
1084
';
1085

    
1086

    
1087
--
1088
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1089
--
1090

    
1091
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
1092
    LANGUAGE sql STABLE
1093
    AS $$
1094
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
1095
$$;
1096

    
1097

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

    
1102
CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
1103
    LANGUAGE sql IMMUTABLE
1104
    AS $_$
1105
SELECT util.raise('NOTICE', $$SELECT $$||$1)
1106
$_$;
1107

    
1108

    
1109
--
1110
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1111
--
1112

    
1113
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
1114
    LANGUAGE sql IMMUTABLE
1115
    AS $_$
1116
SELECT util.debug_print_value('returns: ', $1, $2);
1117
SELECT $1;
1118
$_$;
1119

    
1120

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

    
1125
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
1126
    LANGUAGE sql IMMUTABLE
1127
    AS $_$
1128
/* newline before so the query starts at the beginning of the line.
1129
newline after to visually separate queries from one another. */
1130
SELECT util.raise('NOTICE', $$
1131
$$||util.runnable_sql($1)||$$
1132
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
1133
$_$;
1134

    
1135

    
1136
--
1137
-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1138
--
1139

    
1140
CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
1141
    LANGUAGE sql IMMUTABLE
1142
    AS $_$
1143
SELECT util.raise('NOTICE', concat($1,
1144
(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
1145
$$)
1146
$_$;
1147

    
1148

    
1149
--
1150
-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1151
--
1152

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

    
1162

    
1163
--
1164
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1165
--
1166

    
1167
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
1168
    LANGUAGE sql STABLE
1169
    AS $_$
1170
SELECT util.eval2set($$
1171
SELECT col
1172
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
1173
LEFT JOIN $$||$2||$$ ON "to" = col
1174
WHERE "from" IS NULL
1175
$$, NULL::text)
1176
$_$;
1177

    
1178

    
1179
--
1180
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1181
--
1182

    
1183
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
1184
gets table_''s derived columns (all the columns not in the names table)
1185
';
1186

    
1187

    
1188
--
1189
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1190
--
1191

    
1192
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1193
    LANGUAGE sql
1194
    AS $_$
1195
-- create a diff when the # of copies of a row differs between the tables
1196
SELECT util.to_freq($1);
1197
SELECT util.to_freq($2);
1198
SELECT util.auto_rm_freq(ARRAY[$1, $2]);
1199

    
1200
SELECT * FROM util.diff($1, $2, $3, has_freq := true)
1201
$_$;
1202

    
1203

    
1204
--
1205
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1206
--
1207

    
1208
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1209
usage:
1210
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1211

    
1212
col_type_null (*required*): NULL::shared_base_type
1213
';
1214

    
1215

    
1216
--
1217
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1218
--
1219

    
1220
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
1221
    LANGUAGE plpgsql
1222
    SET search_path TO pg_temp
1223
    AS $_$
1224
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
1225
changes of search_path (schema elements are bound at inline time rather than
1226
runtime) */
1227
/* function option search_path is needed to limit the effects of
1228
`SET LOCAL search_path` to the current function */
1229
BEGIN
1230
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
1231
	
1232
	PERFORM util.mk_keys_func(pg_typeof($3));
1233
	RETURN QUERY
1234
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
1235
$$/* need to explicitly cast each side to the return type because this does not
1236
happen automatically even when an implicit cast is available */
1237
  left_::$$||util.typeof($3)||$$
1238
, right_::$$||util.typeof($3)
1239
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
1240
the *same* base type, *even though* this is optional when using a custom %== */
1241
, util._if($4, $$true/*= CROSS JOIN*/$$,
1242
$$ left_::$$||util.typeof($3)||$$
1243
%== right_::$$||util.typeof($3)||$$
1244
	-- refer to EXPLAIN output for expansion of %==$$
1245
)
1246
,     $$         left_::$$||util.typeof($3)||$$
1247
IS DISTINCT FROM right_::$$||util.typeof($3)
1248
), $3)
1249
	;
1250
END;
1251
$_$;
1252

    
1253

    
1254
--
1255
-- 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: -
1256
--
1257

    
1258
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1259
col_type_null (*required*): NULL::col_type
1260
single_row: whether the tables consist of a single row, which should be
1261
	displayed side-by-side
1262

    
1263
to match up rows using a subset of the columns, create a custom keys() function
1264
which returns this subset as a record:
1265
-- note that OUT parameters for the returned fields are *not* needed
1266
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
1267
  RETURNS record AS
1268
$BODY$
1269
SELECT ($1.key_field_0, $1.key_field_1)
1270
$BODY$
1271
  LANGUAGE sql IMMUTABLE
1272
  COST 100;
1273

    
1274

    
1275
to run EXPLAIN on the FULL JOIN query:
1276
# run this function
1277
# look for a NOTICE containing the expanded query that it ran
1278
# run EXPLAIN on this expanded query
1279
';
1280

    
1281

    
1282
--
1283
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1284
--
1285

    
1286
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
1287
    LANGUAGE sql
1288
    AS $_$
1289
SELECT * FROM util.diff($1::text, $2::text, $3,
1290
	single_row := util.has_single_row($1) AND util.has_single_row($2))
1291
$_$;
1292

    
1293

    
1294
--
1295
-- 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: -
1296
--
1297

    
1298
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1299
helper function used by diff(regclass, regclass)
1300

    
1301
usage:
1302
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
1303

    
1304
col_type_null (*required*): NULL::shared_base_type
1305
';
1306

    
1307

    
1308
--
1309
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1310
--
1311

    
1312
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1313
    LANGUAGE sql
1314
    AS $_$
1315
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1316
$_$;
1317

    
1318

    
1319
--
1320
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1321
--
1322

    
1323
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1324
idempotent
1325
';
1326

    
1327

    
1328
--
1329
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1330
--
1331

    
1332
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1333
    LANGUAGE sql
1334
    AS $_$
1335
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1336
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1337
$_$;
1338

    
1339

    
1340
--
1341
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1342
--
1343

    
1344
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1345
idempotent
1346
';
1347

    
1348

    
1349
--
1350
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
1351
--
1352

    
1353
CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
1354
    LANGUAGE sql
1355
    AS $_$
1356
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
1357
SELECT NULL::void; -- don't fold away functions called in previous query
1358
$_$;
1359

    
1360

    
1361
--
1362
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
1363
--
1364

    
1365
COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
1366
idempotent
1367
';
1368

    
1369

    
1370
--
1371
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1372
--
1373

    
1374
CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
1375
    LANGUAGE sql
1376
    AS $_$
1377
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1378
included in the debug SQL */
1379
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1380
$_$;
1381

    
1382

    
1383
--
1384
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1385
--
1386

    
1387
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1388
    LANGUAGE sql
1389
    AS $_$
1390
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1391
||util._if($3, $$ CASCADE$$, ''::text))
1392
$_$;
1393

    
1394

    
1395
--
1396
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1397
--
1398

    
1399
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1400
idempotent
1401
';
1402

    
1403

    
1404
--
1405
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1406
--
1407

    
1408
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
1409
    LANGUAGE sql
1410
    AS $_$
1411
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
1412
$_$;
1413

    
1414

    
1415
--
1416
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1417
--
1418

    
1419
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
1420
    LANGUAGE sql
1421
    AS $_$
1422
SELECT util.debug_print_func_call(util.quote_func_call(
1423
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
1424
util.quote_typed($3)))
1425
;
1426
SELECT util.drop_relation(relation, $3)
1427
FROM util.show_relations_like($1, $2) relation
1428
;
1429
SELECT NULL::void; -- don't fold away functions called in previous query
1430
$_$;
1431

    
1432

    
1433
--
1434
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1435
--
1436

    
1437
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1438
    LANGUAGE sql
1439
    AS $_$
1440
SELECT util.drop_relation('TABLE', $1, $2)
1441
$_$;
1442

    
1443

    
1444
--
1445
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1446
--
1447

    
1448
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1449
idempotent
1450
';
1451

    
1452

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

    
1457
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1458
    LANGUAGE sql
1459
    AS $_$
1460
SELECT util.drop_relation('VIEW', $1, $2)
1461
$_$;
1462

    
1463

    
1464
--
1465
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1466
--
1467

    
1468
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1469
idempotent
1470
';
1471

    
1472

    
1473
--
1474
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1475
--
1476

    
1477
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1478
    LANGUAGE sql IMMUTABLE
1479
    AS $_$
1480
SELECT util.array_fill($1, 0)
1481
$_$;
1482

    
1483

    
1484
--
1485
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1486
--
1487

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

    
1492

    
1493
--
1494
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1495
--
1496

    
1497
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1498
    LANGUAGE sql IMMUTABLE
1499
    AS $_$
1500
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1501
$_$;
1502

    
1503

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

    
1508
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1509
    LANGUAGE sql IMMUTABLE
1510
    AS $_$
1511
SELECT regexp_replace($2, '("?)$', $1||'\1')
1512
$_$;
1513

    
1514

    
1515
--
1516
-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
1517
--
1518

    
1519
CREATE FUNCTION eval(queries text[]) RETURNS void
1520
    LANGUAGE sql
1521
    AS $_$
1522
SELECT util.eval(query) FROM unnest($1) query;
1523
SELECT NULL::void; -- don't fold away functions called in previous query
1524
$_$;
1525

    
1526

    
1527
--
1528
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1529
--
1530

    
1531
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
1532
    LANGUAGE plpgsql
1533
    AS $$
1534
BEGIN
1535
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1536
	EXECUTE sql;
1537
END;
1538
$$;
1539

    
1540

    
1541
--
1542
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1543
--
1544

    
1545
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1546
    LANGUAGE plpgsql
1547
    AS $$
1548
BEGIN
1549
	PERFORM util.debug_print_sql(sql);
1550
	RETURN QUERY EXECUTE sql;
1551
END;
1552
$$;
1553

    
1554

    
1555
--
1556
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1557
--
1558

    
1559
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1560
col_type_null (*required*): NULL::col_type
1561
';
1562

    
1563

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

    
1568
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1569
    LANGUAGE plpgsql
1570
    AS $$
1571
BEGIN
1572
	PERFORM util.debug_print_sql(sql);
1573
	RETURN QUERY EXECUTE sql;
1574
END;
1575
$$;
1576

    
1577

    
1578
--
1579
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1580
--
1581

    
1582
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
1583
    LANGUAGE plpgsql
1584
    AS $$
1585
BEGIN
1586
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1587
	RETURN QUERY EXECUTE sql;
1588
END;
1589
$$;
1590

    
1591

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

    
1596
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1597
    LANGUAGE plpgsql STABLE
1598
    AS $$
1599
DECLARE
1600
	ret_val ret_type_null%TYPE;
1601
BEGIN
1602
	PERFORM util.debug_print_sql(sql);
1603
	EXECUTE sql INTO STRICT ret_val;
1604
	RETURN ret_val;
1605
END;
1606
$$;
1607

    
1608

    
1609
--
1610
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1611
--
1612

    
1613
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1614
ret_type_null: NULL::ret_type
1615
';
1616

    
1617

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

    
1622
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1623
    LANGUAGE sql
1624
    AS $_$
1625
SELECT util.eval2val($$SELECT $$||$1, $2)
1626
$_$;
1627

    
1628

    
1629
--
1630
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1631
--
1632

    
1633
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1634
ret_type_null: NULL::ret_type
1635
';
1636

    
1637

    
1638
--
1639
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1640
--
1641

    
1642
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1643
    LANGUAGE sql
1644
    AS $_$
1645
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1646
$_$;
1647

    
1648

    
1649
--
1650
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1651
--
1652

    
1653
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1654
sql: can be NULL, which will be passed through
1655
ret_type_null: NULL::ret_type
1656
';
1657

    
1658

    
1659
--
1660
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1661
--
1662

    
1663
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1664
    LANGUAGE sql STABLE
1665
    AS $_$
1666
SELECT col_name
1667
FROM unnest($2) s (col_name)
1668
WHERE util.col_exists(($1, col_name))
1669
$_$;
1670

    
1671

    
1672
--
1673
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1674
--
1675

    
1676
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1677
    LANGUAGE sql
1678
    SET client_min_messages TO 'error'
1679
    AS $_$
1680
/* `client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE functions in
1681
the query, so this prevents displaying any log messages printed by them */
1682
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
1683
$_$;
1684

    
1685

    
1686
--
1687
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1688
--
1689

    
1690
CREATE FUNCTION explain2notice(sql text) RETURNS void
1691
    LANGUAGE sql
1692
    AS $_$
1693
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
1694
$_$;
1695

    
1696

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

    
1701
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
1702
    LANGUAGE sql
1703
    AS $_$
1704
-- newline before and after to visually separate it from other debug info
1705
SELECT COALESCE($$
1706
EXPLAIN:
1707
$$||util.fold_explain_msg(util.explain2str($1))||$$
1708
$$, '')
1709
$_$;
1710

    
1711

    
1712
--
1713
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
1714
--
1715

    
1716
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
1717
    LANGUAGE plpgsql
1718
    AS $$
1719
BEGIN
1720
	RETURN util.explain2notice_msg(sql);
1721
EXCEPTION
1722
WHEN syntax_error THEN RETURN NULL; -- non-explainable query
1723
	/* don't use util.is_explainable() because the list provided by Postgres
1724
	(http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691)
1725
	excludes some query types that are in fact EXPLAIN-able */
1726
END;
1727
$$;
1728

    
1729

    
1730
--
1731
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1732
--
1733

    
1734
CREATE FUNCTION explain2str(sql text) RETURNS text
1735
    LANGUAGE sql
1736
    AS $_$
1737
SELECT util.join_strs(explain, $$
1738
$$) FROM util.explain($1)
1739
$_$;
1740

    
1741

    
1742
SET default_tablespace = '';
1743

    
1744
SET default_with_oids = false;
1745

    
1746
--
1747
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1748
--
1749

    
1750
CREATE TABLE explain (
1751
    line text NOT NULL
1752
);
1753

    
1754

    
1755
--
1756
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1757
--
1758

    
1759
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1760
    LANGUAGE sql
1761
    AS $_$
1762
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1763
$$||quote_nullable($1)||$$
1764
)$$)
1765
$_$;
1766

    
1767

    
1768
--
1769
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1770
--
1771

    
1772
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1773
usage:
1774
PERFORM util.explain2table($$
1775
query
1776
$$);
1777
';
1778

    
1779

    
1780
--
1781
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
1782
--
1783

    
1784
CREATE FUNCTION first_word(str text) RETURNS text
1785
    LANGUAGE sql IMMUTABLE
1786
    AS $_$
1787
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
1788
$_$;
1789

    
1790

    
1791
--
1792
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1793
--
1794

    
1795
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1796
    LANGUAGE sql IMMUTABLE
1797
    AS $_$
1798
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1799
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1800
) END
1801
$_$;
1802

    
1803

    
1804
--
1805
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1806
--
1807

    
1808
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1809
ensures that an array will always have proper non-NULL dimensions
1810
';
1811

    
1812

    
1813
--
1814
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
1815
--
1816

    
1817
CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
1818
    LANGUAGE sql IMMUTABLE
1819
    AS $_$
1820
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
1821
$_$;
1822

    
1823

    
1824
--
1825
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
1826
--
1827

    
1828
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1829
    LANGUAGE plpgsql
1830
    AS $_$
1831
DECLARE
1832
	PG_EXCEPTION_DETAIL text;
1833
	restore_views_info util.restore_views_info;
1834
BEGIN
1835
	restore_views_info = util.save_drop_views(users);
1836
	PERFORM util.eval(cmd);
1837
	PERFORM util.restore_views(restore_views_info);
1838
EXCEPTION
1839
WHEN dependent_objects_still_exist THEN
1840
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1841
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1842
	users = array(SELECT * FROM util.regexp_matches_group(
1843
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
1844
		-- will be in forward dependency order
1845
	PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
1846
	PERFORM util.debug_print_var('users', users);
1847
	IF util.is_empty(users) THEN RAISE; END IF;
1848
	PERFORM util.force_recreate(cmd, users);
1849
END;
1850
$_$;
1851

    
1852

    
1853
--
1854
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1855
--
1856

    
1857
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1858
usage:
1859
SELECT util.force_recreate($$
1860
-- trigger the dependent_objects_still_exist exception
1861
DROP VIEW schemas.main_view; -- *not* CASCADE; it must trigger an exception
1862

    
1863
CREATE VIEW schemas.main_view AS _;
1864

    
1865
-- manually restore views that need to be updated for the changes
1866
CREATE VIEW schemas.dependent_view AS _;
1867
$$);
1868

    
1869
idempotent
1870

    
1871
users: not necessary to provide this because it will be autopopulated
1872
';
1873

    
1874

    
1875
--
1876
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1877
--
1878

    
1879
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1880
    LANGUAGE plpgsql STRICT
1881
    AS $_$
1882
DECLARE
1883
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1884
$$||query;
1885
BEGIN
1886
	EXECUTE mk_view;
1887
EXCEPTION
1888
WHEN invalid_table_definition THEN
1889
	IF SQLERRM = 'cannot drop columns from view'
1890
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1891
	THEN
1892
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1893
		EXECUTE mk_view;
1894
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1895
	END IF;
1896
END;
1897
$_$;
1898

    
1899

    
1900
--
1901
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1902
--
1903

    
1904
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1905
idempotent
1906
';
1907

    
1908

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

    
1913
CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
1914
    LANGUAGE sql STABLE
1915
    AS $_$
1916
SELECT util.eval2val(
1917
$$SELECT NOT EXISTS( -- there is no row that is != 1
1918
	SELECT NULL
1919
	FROM $$||$1||$$
1920
	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
1921
	LIMIT 1
1922
)
1923
$$, NULL::boolean)
1924
$_$;
1925

    
1926

    
1927
--
1928
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
1929
--
1930

    
1931
CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
1932
    LANGUAGE sql STABLE
1933
    AS $_$
1934
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
1935
$_$;
1936

    
1937

    
1938
--
1939
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1940
--
1941

    
1942
CREATE FUNCTION grants_users() RETURNS SETOF text
1943
    LANGUAGE sql IMMUTABLE
1944
    AS $$
1945
VALUES ('bien_read'), ('public_')
1946
$$;
1947

    
1948

    
1949
--
1950
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1951
--
1952

    
1953
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1954
    LANGUAGE sql IMMUTABLE
1955
    AS $_$
1956
SELECT substring($2 for length($1)) = $1
1957
$_$;
1958

    
1959

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

    
1964
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1965
    LANGUAGE sql STABLE
1966
    AS $_$
1967
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1968
$_$;
1969

    
1970

    
1971
--
1972
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1973
--
1974

    
1975
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1976
    LANGUAGE sql IMMUTABLE
1977
    AS $_$
1978
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1979
$_$;
1980

    
1981

    
1982
--
1983
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1984
--
1985

    
1986
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1987
avoids repeating the same value for each key
1988
';
1989

    
1990

    
1991
--
1992
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1993
--
1994

    
1995
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1996
    LANGUAGE sql IMMUTABLE
1997
    AS $_$
1998
SELECT COALESCE($1, $2)
1999
$_$;
2000

    
2001

    
2002
--
2003
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
2004
--
2005

    
2006
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
2007
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
2008
';
2009

    
2010

    
2011
--
2012
-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
2013
--
2014

    
2015
CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
2016
    LANGUAGE sql IMMUTABLE
2017
    AS $_$
2018
SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
2019
$_$;
2020

    
2021

    
2022
--
2023
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2024
--
2025

    
2026
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
2027
    LANGUAGE sql
2028
    AS $_$
2029
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
2030
$_$;
2031

    
2032

    
2033
--
2034
-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2035
--
2036

    
2037
CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
2038
    LANGUAGE plpgsql IMMUTABLE
2039
    AS $$
2040
BEGIN
2041
	PERFORM util.cast(value, ret_type_null);
2042
	-- must happen *after* cast check, because NULL is not valid for some types
2043
	IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
2044
	RETURN true;
2045
EXCEPTION
2046
WHEN   data_exception
2047
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
2048
	THEN
2049
	RETURN false;
2050
END;
2051
$$;
2052

    
2053

    
2054
--
2055
-- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
2056
--
2057

    
2058
COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
2059
passes NULL through. however, if NULL is not valid for the type, false will be
2060
returned instead.
2061

    
2062
ret_type_null: NULL::ret_type
2063
';
2064

    
2065

    
2066
--
2067
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
2068
--
2069

    
2070
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
2071
    LANGUAGE sql STABLE
2072
    AS $_$
2073
SELECT COALESCE(util.col_comment($1) LIKE '
2074
constant
2075
%', false)
2076
$_$;
2077

    
2078

    
2079
--
2080
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2081
--
2082

    
2083
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
2084
    LANGUAGE sql IMMUTABLE
2085
    AS $_$
2086
SELECT util.array_length($1) = 0
2087
$_$;
2088

    
2089

    
2090
--
2091
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
2092
--
2093

    
2094
CREATE FUNCTION is_explain(sql text) RETURNS boolean
2095
    LANGUAGE sql IMMUTABLE
2096
    AS $_$
2097
SELECT upper(util.first_word($1)) = 'EXPLAIN'
2098
$_$;
2099

    
2100

    
2101
--
2102
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
2103
--
2104

    
2105
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
2106
    LANGUAGE sql IMMUTABLE
2107
    AS $_$
2108
SELECT upper(util.first_word($1)) = ANY(
2109
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
2110
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
2111
)
2112
$_$;
2113

    
2114

    
2115
--
2116
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2117
--
2118

    
2119
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
2120
    LANGUAGE sql IMMUTABLE
2121
    AS $_$
2122
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
2123
$_$;
2124

    
2125

    
2126
--
2127
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2128
--
2129

    
2130
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
2131
    LANGUAGE sql IMMUTABLE
2132
    AS $_$
2133
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
2134
$_$;
2135

    
2136

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

    
2141
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
2142
    LANGUAGE sql IMMUTABLE
2143
    AS $_$
2144
SELECT upper(util.first_word($1)) = 'SET'
2145
$_$;
2146

    
2147

    
2148
--
2149
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
2150
--
2151

    
2152
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
2153
    LANGUAGE sql STABLE
2154
    AS $_$
2155
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
2156
$_$;
2157

    
2158

    
2159
--
2160
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
2161
--
2162

    
2163
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
2164
    LANGUAGE sql STABLE
2165
    AS $_$
2166
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
2167
$_$;
2168

    
2169

    
2170
--
2171
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
2172
--
2173

    
2174
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
2175
    LANGUAGE sql IMMUTABLE STRICT
2176
    AS $_$
2177
SELECT $1 || $3 || $2
2178
$_$;
2179

    
2180

    
2181
--
2182
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
2183
--
2184

    
2185
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
2186
must be declared STRICT to use the special handling of STRICT aggregating functions
2187
';
2188

    
2189

    
2190
--
2191
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
2192
--
2193

    
2194
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
2195
    LANGUAGE sql IMMUTABLE
2196
    AS $_$
2197
SELECT $1 -- compare on the entire value
2198
$_$;
2199

    
2200

    
2201
--
2202
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
2203
--
2204

    
2205
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
2206
    LANGUAGE sql IMMUTABLE
2207
    AS $_$
2208
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
2209
$_$;
2210

    
2211

    
2212
--
2213
-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2214
--
2215

    
2216
CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
2217
    LANGUAGE plpgsql
2218
    AS $$
2219
DECLARE
2220
	errors_ct integer = 0;
2221
	loop_var loop_type_null%TYPE;
2222
BEGIN
2223
	FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
2224
	LOOP
2225
		BEGIN
2226
			EXECUTE loop_body_sql USING loop_var;
2227
		EXCEPTION
2228
		WHEN OTHERS THEN
2229
			errors_ct = errors_ct+1;
2230
			PERFORM util.raise_error_warning(SQLERRM);
2231
		END;
2232
	END LOOP;
2233
	IF errors_ct > 0 THEN
2234
		-- can't raise exception because this would roll back the transaction
2235
		PERFORM util.raise_error_warning('there were '||errors_ct
2236
			||' errors: see the WARNINGs for details');
2237
	END IF;
2238
END;
2239
$$;
2240

    
2241

    
2242
--
2243
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
2244
--
2245

    
2246
CREATE FUNCTION ltrim_nl(str text) RETURNS text
2247
    LANGUAGE sql IMMUTABLE
2248
    AS $_$
2249
SELECT ltrim($1, $$
2250
$$)
2251
$_$;
2252

    
2253

    
2254
--
2255
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
2256
--
2257

    
2258
CREATE FUNCTION map_filter_insert() RETURNS trigger
2259
    LANGUAGE plpgsql
2260
    AS $$
2261
BEGIN
2262
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
2263
	RETURN new;
2264
END;
2265
$$;
2266

    
2267

    
2268
--
2269
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2270
--
2271

    
2272
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
2273
    LANGUAGE plpgsql STABLE STRICT
2274
    AS $_$
2275
DECLARE
2276
    value text;
2277
BEGIN
2278
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
2279
        INTO value USING key;
2280
    RETURN value;
2281
END;
2282
$_$;
2283

    
2284

    
2285
--
2286
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
2287
--
2288

    
2289
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
2290
    LANGUAGE sql IMMUTABLE
2291
    AS $_$
2292
SELECT util._map(util.nulls_map($1), $2)
2293
$_$;
2294

    
2295

    
2296
--
2297
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
2298
--
2299

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

    
2303
[1] inlining of function calls, which is different from constant folding
2304
[2] _map()''s profiling query
2305
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
2306
and map_nulls()''s profiling query
2307
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
2308
both take ~920 ms.
2309
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.
2310
';
2311

    
2312

    
2313
--
2314
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
2315
--
2316

    
2317
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
2318
    LANGUAGE plpgsql STABLE STRICT
2319
    AS $_$
2320
BEGIN
2321
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
2322
END;
2323
$_$;
2324

    
2325

    
2326
--
2327
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2328
--
2329

    
2330
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
2331
    LANGUAGE sql
2332
    AS $_$
2333
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
2334
$$||util.ltrim_nl($2));
2335
-- make sure the created table has the correct estimated row count
2336
SELECT util.analyze_($1);
2337

    
2338
SELECT util.append_comment($1, '
2339
contents generated from:
2340
'||util.ltrim_nl(util.runnable_sql($2))||';
2341
');
2342
$_$;
2343

    
2344

    
2345
--
2346
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2347
--
2348

    
2349
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
2350
idempotent
2351
';
2352

    
2353

    
2354
--
2355
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2356
--
2357

    
2358
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
2359
    LANGUAGE sql
2360
    AS $_$
2361
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
2362
$_$;
2363

    
2364

    
2365
--
2366
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2367
--
2368

    
2369
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
2370
idempotent
2371
';
2372

    
2373

    
2374
--
2375
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
2376
--
2377

    
2378
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
2379
    LANGUAGE sql
2380
    AS $_$
2381
SELECT util.create_if_not_exists($$
2382
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
2383
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
2384
||quote_literal($2)||$$;
2385
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
2386
constant
2387
';
2388
$$)
2389
$_$;
2390

    
2391

    
2392
--
2393
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
2394
--
2395

    
2396
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
2397
idempotent
2398
';
2399

    
2400

    
2401
--
2402
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
2403
--
2404

    
2405
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
2406
    LANGUAGE plpgsql STRICT
2407
    AS $_$
2408
DECLARE
2409
    type regtype = util.typeof(expr, col.table_::text::regtype);
2410
    col_name_sql text = quote_ident(col.name);
2411
BEGIN
2412
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
2413
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
2414
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
2415
$$||expr||$$;
2416
$$);
2417
END;
2418
$_$;
2419

    
2420

    
2421
--
2422
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
2423
--
2424

    
2425
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
2426
idempotent
2427
';
2428

    
2429

    
2430
--
2431
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
2432
--
2433

    
2434
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
2435
    LANGUAGE sql IMMUTABLE
2436
    AS $_$
2437
SELECT
2438
$$SELECT
2439
$$||$3||$$
2440
FROM      $$||$1||$$ left_
2441
FULL JOIN $$||$2||$$ right_
2442
ON $$||$4||$$
2443
WHERE $$||$5||$$
2444
ORDER BY left_, right_
2445
$$
2446
$_$;
2447

    
2448

    
2449
--
2450
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2451
--
2452

    
2453
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
2454
    LANGUAGE sql
2455
    AS $_$
2456
-- keys()
2457
SELECT util.mk_keys_func($1, ARRAY(
2458
SELECT col FROM util.typed_cols($1) col
2459
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
2460
));
2461

    
2462
-- values_()
2463
SELECT util.mk_keys_func($1, COALESCE(
2464
	NULLIF(ARRAY(
2465
	SELECT col FROM util.typed_cols($1) col
2466
	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
2467
	), ARRAY[]::util.col_cast[])
2468
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
2469
, 'values_');
2470
$_$;
2471

    
2472

    
2473
--
2474
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
2475
--
2476

    
2477
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
2478
    LANGUAGE sql
2479
    AS $_$
2480
SELECT util.create_if_not_exists($$
2481
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
2482
($$||util.mk_typed_cols_list($2)||$$);
2483
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
2484
autogenerated
2485
';
2486
$$);
2487

    
2488
SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
2489
$_$;
2490

    
2491

    
2492
--
2493
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
2494
--
2495

    
2496
CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
2497
    LANGUAGE sql
2498
    AS $_$
2499
SELECT util.create_if_not_exists($$
2500
CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
2501
||util.qual_name($1)||$$)
2502
  RETURNS $$||util.qual_name($2)||$$ AS
2503
$BODY1$
2504
SELECT ROW($$||
2505
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
2506
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
2507
$BODY1$
2508
  LANGUAGE sql IMMUTABLE
2509
  COST 100;
2510
$$);
2511
$_$;
2512

    
2513

    
2514
--
2515
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2516
--
2517

    
2518
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
2519
    LANGUAGE sql
2520
    AS $_$
2521
SELECT util.create_if_not_exists($$
2522
CREATE TABLE $$||$1||$$
2523
(
2524
    LIKE util.map INCLUDING ALL
2525
);
2526

    
2527
CREATE TRIGGER map_filter_insert
2528
  BEFORE INSERT
2529
  ON $$||$1||$$
2530
  FOR EACH ROW
2531
  EXECUTE PROCEDURE util.map_filter_insert();
2532
$$)
2533
$_$;
2534

    
2535

    
2536
--
2537
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
2538
--
2539

    
2540
CREATE FUNCTION mk_not_null(text) RETURNS text
2541
    LANGUAGE sql IMMUTABLE
2542
    AS $_$
2543
SELECT COALESCE($1, '<NULL>')
2544
$_$;
2545

    
2546

    
2547
--
2548
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2549
--
2550

    
2551
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
2552
    LANGUAGE sql IMMUTABLE
2553
    AS $_$
2554
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
2555
util.qual_name((unnest).type), ''), '')
2556
FROM unnest($1)
2557
$_$;
2558

    
2559

    
2560
--
2561
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
2562
--
2563

    
2564
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
2565
    LANGUAGE sql IMMUTABLE
2566
    AS $_$
2567
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
2568
$_$;
2569

    
2570

    
2571
--
2572
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
2573
--
2574

    
2575
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
2576
auto-appends util to the search_path to enable use of util operators
2577
';
2578

    
2579

    
2580
--
2581
-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2582
--
2583

    
2584
CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
2585
    LANGUAGE sql STABLE
2586
    AS $_$
2587
SELECT COALESCE($$COMMENT ON $$||util.relation_type($1)||$$ $$||$1||$$ IS $$
2588
||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
2589
$_$;
2590

    
2591

    
2592
--
2593
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2594
--
2595

    
2596
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
2597
    LANGUAGE sql IMMUTABLE
2598
    AS $_$
2599
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
2600
$_$;
2601

    
2602

    
2603
--
2604
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
2605
--
2606

    
2607
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
2608
    LANGUAGE sql IMMUTABLE
2609
    AS $_$
2610
/* debug_print_return_value() needed because this function is used with EXECUTE
2611
rather than util.eval() (in order to affect the calling function), so the
2612
search_path would not otherwise be printed */
2613
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
2614
||$$ search_path TO $$||$1
2615
$_$;
2616

    
2617

    
2618
--
2619
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2620
--
2621

    
2622
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
2623
    LANGUAGE sql
2624
    AS $_$
2625
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
2626
$_$;
2627

    
2628

    
2629
--
2630
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2631
--
2632

    
2633
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2634
idempotent
2635
';
2636

    
2637

    
2638
--
2639
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2640
--
2641

    
2642
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
2643
    LANGUAGE plpgsql STRICT
2644
    AS $_$
2645
DECLARE
2646
	view_qual_name text = util.qual_name(view_);
2647
BEGIN
2648
	EXECUTE $$
2649
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2650
  RETURNS SETOF $$||view_||$$ AS
2651
$BODY1$
2652
SELECT * FROM $$||view_qual_name||$$
2653
ORDER BY sort_col
2654
LIMIT $1 OFFSET $2
2655
$BODY1$
2656
  LANGUAGE sql STABLE
2657
  COST 100
2658
  ROWS 1000
2659
$$;
2660
	
2661
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2662
END;
2663
$_$;
2664

    
2665

    
2666
--
2667
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2668
--
2669

    
2670
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2671
    LANGUAGE plpgsql STRICT
2672
    AS $_$
2673
DECLARE
2674
	view_qual_name text = util.qual_name(view_);
2675
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2676
BEGIN
2677
	EXECUTE $$
2678
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2679
  RETURNS integer AS
2680
$BODY1$
2681
SELECT $$||quote_ident(row_num_col)||$$
2682
FROM $$||view_qual_name||$$
2683
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2684
LIMIT 1
2685
$BODY1$
2686
  LANGUAGE sql STABLE
2687
  COST 100;
2688
$$;
2689
	
2690
	EXECUTE $$
2691
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2692
  RETURNS SETOF $$||view_||$$ AS
2693
$BODY1$
2694
SELECT * FROM $$||view_qual_name||$$
2695
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2696
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2697
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2698
ORDER BY $$||quote_ident(row_num_col)||$$
2699
$BODY1$
2700
  LANGUAGE sql STABLE
2701
  COST 100
2702
  ROWS 1000
2703
$$;
2704
	
2705
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2706
END;
2707
$_$;
2708

    
2709

    
2710
--
2711
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2712
--
2713

    
2714
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2715
    LANGUAGE plpgsql STRICT
2716
    AS $_$
2717
DECLARE
2718
	view_qual_name text = util.qual_name(view_);
2719
BEGIN
2720
	EXECUTE $$
2721
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2722
  RETURNS SETOF $$||view_||$$
2723
  SET enable_sort TO 'off'
2724
  AS
2725
$BODY1$
2726
SELECT * FROM $$||view_qual_name||$$($2, $3)
2727
$BODY1$
2728
  LANGUAGE sql STABLE
2729
  COST 100
2730
  ROWS 1000
2731
;
2732
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2733
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2734
If you want to run EXPLAIN and get expanded output, use the regular subset
2735
function instead. (When a config param is set on a function, EXPLAIN produces
2736
just a function scan.)
2737
';
2738
$$;
2739
END;
2740
$_$;
2741

    
2742

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

    
2747
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2748
creates subset function which turns off enable_sort
2749
';
2750

    
2751

    
2752
--
2753
-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2754
--
2755

    
2756
CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
2757
    LANGUAGE sql IMMUTABLE
2758
    AS $_$
2759
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
2760
util.qual_name((unnest).type), ', '), '')
2761
FROM unnest($1)
2762
$_$;
2763

    
2764

    
2765
--
2766
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2767
--
2768

    
2769
CREATE FUNCTION name(table_ regclass) RETURNS text
2770
    LANGUAGE sql STABLE
2771
    AS $_$
2772
SELECT relname::text FROM pg_class WHERE oid = $1
2773
$_$;
2774

    
2775

    
2776
--
2777
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2778
--
2779

    
2780
CREATE FUNCTION name(type regtype) RETURNS text
2781
    LANGUAGE sql STABLE
2782
    AS $_$
2783
SELECT typname::text FROM pg_type WHERE oid = $1
2784
$_$;
2785

    
2786

    
2787
--
2788
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
2789
--
2790

    
2791
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2792
    LANGUAGE sql IMMUTABLE
2793
    AS $_$
2794
SELECT octet_length($1) >= util.namedatalen() - $2
2795
$_$;
2796

    
2797

    
2798
--
2799
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2800
--
2801

    
2802
CREATE FUNCTION namedatalen() RETURNS integer
2803
    LANGUAGE sql IMMUTABLE
2804
    AS $$
2805
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2806
$$;
2807

    
2808

    
2809
--
2810
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2811
--
2812

    
2813
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2814
    LANGUAGE sql IMMUTABLE
2815
    AS $_$
2816
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2817
$_$;
2818

    
2819

    
2820
--
2821
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2822
--
2823

    
2824
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2825
    LANGUAGE sql IMMUTABLE
2826
    AS $_$
2827
SELECT $1 IS NOT NULL
2828
$_$;
2829

    
2830

    
2831
--
2832
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2833
--
2834

    
2835
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2836
    LANGUAGE sql IMMUTABLE
2837
    AS $_$
2838
SELECT util.hstore($1, NULL) || '*=>*'
2839
$_$;
2840

    
2841

    
2842
--
2843
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2844
--
2845

    
2846
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2847
for use with _map()
2848
';
2849

    
2850

    
2851
--
2852
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2853
--
2854

    
2855
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2856
    LANGUAGE sql IMMUTABLE
2857
    AS $_$
2858
SELECT $2 + COALESCE($1, 0)
2859
$_$;
2860

    
2861

    
2862
--
2863
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
2864
--
2865

    
2866
CREATE FUNCTION parent(table_ regclass) RETURNS regclass
2867
    LANGUAGE sql STABLE
2868
    AS $_$
2869
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
2870
$_$;
2871

    
2872

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

    
2877
CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
2878
    LANGUAGE sql
2879
    AS $_$
2880
SELECT util.eval($$INSERT INTO $$||$1||$$
2881
$$||util.ltrim_nl($2));
2882
-- make sure the created table has the correct estimated row count
2883
SELECT util.analyze_($1);
2884
$_$;
2885

    
2886

    
2887
--
2888
-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2889
--
2890

    
2891
CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
2892
    LANGUAGE sql IMMUTABLE
2893
    AS $_$
2894
SELECT util.qual_name(util.schema($2), $1||util.name($2))
2895
$_$;
2896

    
2897

    
2898
--
2899
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2900
--
2901

    
2902
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
2903
    LANGUAGE sql
2904
    AS $_$
2905
SELECT util.set_comment($1, concat($2, util.comment($1)))
2906
$_$;
2907

    
2908

    
2909
--
2910
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
2911
--
2912

    
2913
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
2914
comment: must start and end with a newline
2915
';
2916

    
2917

    
2918
--
2919
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2920
--
2921

    
2922
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2923
    LANGUAGE sql IMMUTABLE
2924
    AS $_$
2925
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2926
$_$;
2927

    
2928

    
2929
--
2930
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2931
--
2932

    
2933
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2934
    LANGUAGE sql STABLE
2935
    SET search_path TO pg_temp
2936
    AS $_$
2937
SELECT $1::text
2938
$_$;
2939

    
2940

    
2941
--
2942
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2943
--
2944

    
2945
CREATE FUNCTION qual_name(type regtype) RETURNS text
2946
    LANGUAGE sql STABLE
2947
    SET search_path TO pg_temp
2948
    AS $_$
2949
SELECT $1::text
2950
$_$;
2951

    
2952

    
2953
--
2954
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2955
--
2956

    
2957
COMMENT ON FUNCTION qual_name(type regtype) IS '
2958
a type''s schema-qualified name
2959
';
2960

    
2961

    
2962
--
2963
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2964
--
2965

    
2966
CREATE FUNCTION qual_name(type unknown) RETURNS text
2967
    LANGUAGE sql STABLE
2968
    AS $_$
2969
SELECT util.qual_name($1::text::regtype)
2970
$_$;
2971

    
2972

    
2973
--
2974
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
2975
--
2976

    
2977
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
2978
    LANGUAGE sql IMMUTABLE
2979
    AS $_$
2980
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
2981
$_$;
2982

    
2983

    
2984
--
2985
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
2986
--
2987

    
2988
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
2989
    LANGUAGE sql IMMUTABLE
2990
    AS $_$
2991
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
2992
$_$;
2993

    
2994

    
2995
--
2996
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2997
--
2998

    
2999
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
3000
    LANGUAGE sql IMMUTABLE
3001
    AS $_$
3002
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
3003
$_$;
3004

    
3005

    
3006
--
3007
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
3008
--
3009

    
3010
CREATE FUNCTION raise(type text, msg text) RETURNS void
3011
    LANGUAGE sql IMMUTABLE
3012
    AS $_X$
3013
SELECT util.eval($$
3014
CREATE OR REPLACE FUNCTION pg_temp.__raise()
3015
  RETURNS void AS
3016
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
3017
$__BODY1$
3018
BEGIN
3019
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
3020
END;
3021
$__BODY1$
3022
  LANGUAGE plpgsql IMMUTABLE
3023
  COST 100;
3024
$$, verbose_ := false);
3025

    
3026
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
3027
$_X$;
3028

    
3029

    
3030
--
3031
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
3032
--
3033

    
3034
COMMENT ON FUNCTION raise(type text, msg text) IS '
3035
type: a log level from
3036
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
3037
or a condition name from
3038
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
3039
';
3040

    
3041

    
3042
--
3043
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
3044
--
3045

    
3046
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
3047
    LANGUAGE sql IMMUTABLE
3048
    AS $_$
3049
SELECT util.raise('WARNING', 'ERROR:  '||$1)
3050
$_$;
3051

    
3052

    
3053
--
3054
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
3055
--
3056

    
3057
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
3058
    LANGUAGE plpgsql IMMUTABLE STRICT
3059
    AS $$
3060
BEGIN
3061
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
3062
END;
3063
$$;
3064

    
3065

    
3066
--
3067
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3068
--
3069

    
3070
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
3071
    LANGUAGE sql IMMUTABLE
3072
    AS $_$
3073
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
3074
$_$;
3075

    
3076

    
3077
--
3078
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
3079
--
3080

    
3081
CREATE FUNCTION regexp_quote(str text) RETURNS text
3082
    LANGUAGE sql IMMUTABLE
3083
    AS $_$
3084
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
3085
$_$;
3086

    
3087

    
3088
--
3089
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
3090
--
3091

    
3092
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
3093
    LANGUAGE sql IMMUTABLE
3094
    AS $_$
3095
SELECT (CASE WHEN right($1, 1) = ')'
3096
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
3097
$_$;
3098

    
3099

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

    
3104
CREATE FUNCTION relation_exists(relation text) RETURNS boolean
3105
    LANGUAGE sql STABLE
3106
    AS $_$
3107
SELECT $1 IS NOT NULL AND util.is_castable($1, NULL::regclass)
3108
$_$;
3109

    
3110

    
3111
--
3112
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
3113
--
3114

    
3115
CREATE FUNCTION relation_type(relation regclass) RETURNS text
3116
    LANGUAGE sql STABLE
3117
    AS $_$
3118
SELECT util.relation_type(util.relation_type_char($1))
3119
$_$;
3120

    
3121

    
3122
--
3123
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
3124
--
3125

    
3126
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
3127
    LANGUAGE sql IMMUTABLE
3128
    AS $_$
3129
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
3130
$_$;
3131

    
3132

    
3133
--
3134
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
3135
--
3136

    
3137
CREATE FUNCTION relation_type(type regtype) RETURNS text
3138
    LANGUAGE sql IMMUTABLE
3139
    AS $$
3140
SELECT 'TYPE'::text
3141
$$;
3142

    
3143

    
3144
--
3145
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
3146
--
3147

    
3148
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
3149
    LANGUAGE sql STABLE
3150
    AS $_$
3151
SELECT relkind FROM pg_class WHERE oid = $1
3152
$_$;
3153

    
3154

    
3155
--
3156
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
3157
--
3158

    
3159
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
3160
    LANGUAGE sql
3161
    AS $_$
3162
/* can't have in_table/out_table inherit from *each other*, because inheritance
3163
also causes the rows of the parent table to be included in the child table.
3164
instead, they need to inherit from a common, empty table. */
3165
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
3166
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
3167
SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
3168
SELECT util.inherit($2, $4);
3169
SELECT util.inherit($3, $4);
3170

    
3171
SELECT util.rematerialize_query($1, $$
3172
SELECT * FROM util.diff(
3173
  $$||util.quote_typed($2)||$$
3174
, $$||util.quote_typed($3)||$$
3175
, NULL::$$||$4||$$)
3176
$$);
3177

    
3178
/* the table unfortunately cannot be *materialized* in human-readable form,
3179
because this would create column name collisions between the two sides */
3180
SELECT util.prepend_comment($1, '
3181
to view this table in human-readable form (with each side''s tuple column
3182
expanded to its component fields):
3183
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
3184

    
3185
to display NULL values that are extra or missing:
3186
SELECT * FROM '||$1||';
3187
');
3188
$_$;
3189

    
3190

    
3191
--
3192
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
3193
--
3194

    
3195
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
3196
type_table (*required*): table to create as the shared base type
3197
';
3198

    
3199

    
3200
--
3201
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
3202
--
3203

    
3204
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
3205
    LANGUAGE sql
3206
    AS $_$
3207
SELECT util.drop_table($1);
3208
SELECT util.materialize_query($1, $2);
3209
$_$;
3210

    
3211

    
3212
--
3213
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
3214
--
3215

    
3216
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
3217
idempotent, but repeats action each time
3218
';
3219

    
3220

    
3221
--
3222
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
3223
--
3224

    
3225
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
3226
    LANGUAGE sql
3227
    AS $_$
3228
SELECT util.drop_table($1);
3229
SELECT util.materialize_view($1, $2);
3230
$_$;
3231

    
3232

    
3233
--
3234
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
3235
--
3236

    
3237
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
3238
idempotent, but repeats action each time
3239
';
3240

    
3241

    
3242
--
3243
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
3244
--
3245

    
3246
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
3247
    LANGUAGE sql
3248
    AS $_$
3249
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
3250
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
3251
FROM util.col_names($1::text::regtype) f (name);
3252
SELECT NULL::void; -- don't fold away functions called in previous query
3253
$_$;
3254

    
3255

    
3256
--
3257
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
3258
--
3259

    
3260
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
3261
idempotent
3262
';
3263

    
3264

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

    
3269
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
3270
    LANGUAGE sql
3271
    AS $_$
3272
/* use util.qual_name() instead of ::text so that the schema qualifier is always
3273
included in the debug SQL */
3274
SELECT util.rename_relation(util.qual_name($1), $2)
3275
$_$;
3276

    
3277

    
3278
--
3279
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
3280
--
3281

    
3282
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
3283
    LANGUAGE sql
3284
    AS $_$
3285
/* 'ALTER TABLE can be used with views too'
3286
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
3287
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
3288
||quote_ident($2))
3289
$_$;
3290

    
3291

    
3292
--
3293
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
3294
--
3295

    
3296
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
3297
idempotent
3298
';
3299

    
3300

    
3301
--
3302
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3303
--
3304

    
3305
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
3306
    LANGUAGE sql IMMUTABLE
3307
    AS $_$
3308
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
3309
$_$;
3310

    
3311

    
3312
--
3313
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
3314
--
3315

    
3316
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
3317
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 
3318
';
3319

    
3320

    
3321
--
3322
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3323
--
3324

    
3325
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
3326
    LANGUAGE sql
3327
    AS $_$
3328
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
3329
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
3330
SELECT util.set_col_names($1, $2);
3331
$_$;
3332

    
3333

    
3334
--
3335
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3336
--
3337

    
3338
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
3339
idempotent.
3340
alters the names table, so it will need to be repopulated after running this function.
3341
';
3342

    
3343

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

    
3348
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
3349
    LANGUAGE sql
3350
    AS $_$
3351
SELECT util.drop_table($1);
3352
SELECT util.mk_map_table($1);
3353
$_$;
3354

    
3355

    
3356
--
3357
-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
3358
--
3359

    
3360
CREATE FUNCTION restore_views(restore_views_info) RETURNS void
3361
    LANGUAGE sql
3362
    AS $_$
3363
SELECT util.debug_print_var('views', $1);
3364
SELECT util.create_if_not_exists((view_).def, (view_).path)
3365
	/* need to specify view name for manual existence check, in case view def
3366
	becomes invalid, which would produce nonstandard (uncatchable) exception */
3367
FROM unnest($1.views) view_; -- in forward dependency order
3368
	/* create_if_not_exists() rather than eval(), because cmd might manually
3369
	re-create a deleted dependent view, causing it to already exist */
3370
SELECT NULL::void; -- don't fold away functions called in previous query
3371
$_$;
3372

    
3373

    
3374
--
3375
-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
3376
--
3377

    
3378
CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
3379
    LANGUAGE sql
3380
    AS $_$
3381
SELECT util.drop_column($1, $2, force := true)
3382
$_$;
3383

    
3384

    
3385
--
3386
-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
3387
--
3388

    
3389
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
3390
    LANGUAGE sql IMMUTABLE
3391
    AS $_$
3392
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
3393
$_$;
3394

    
3395

    
3396
--
3397
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
3398
--
3399

    
3400
CREATE FUNCTION runnable_sql(sql text) RETURNS text
3401
    LANGUAGE sql IMMUTABLE
3402
    AS $_$
3403
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
3404
ELSE util.mk_set_search_path(for_printing := true)||$$;
3405
$$ END)||$1
3406
$_$;
3407

    
3408

    
3409
--
3410
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
3411
--
3412

    
3413
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
3414
    LANGUAGE plpgsql STRICT
3415
    AS $$
3416
DECLARE
3417
	result text = NULL;
3418
BEGIN
3419
	BEGIN
3420
		result = util.show_create_view(view_, replace := false);
3421
			/* replace: no `OR REPLACE` because that causes nonuniform errors
3422
			(eg. invalid_table_definition), instead of the standard
3423
			duplicate_table exception caught by util.create_if_not_exists() */
3424
		PERFORM util.drop_view(view_);
3425
	EXCEPTION
3426
		WHEN undefined_table THEN NULL;
3427
	END;
3428
	RETURN result;
3429
END;
3430
$$;
3431

    
3432

    
3433
--
3434
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
3435
--
3436

    
3437
CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
3438
    LANGUAGE sql
3439
    AS $_$
3440
SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
3441
SELECT (view_, util.save_drop_view(view_))::util.db_item
3442
FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
3443
)))::util.restore_views_info
3444
$_$;
3445

    
3446

    
3447
--
3448
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
3449
--
3450

    
3451
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
3452
    LANGUAGE sql STABLE
3453
    AS $_$
3454
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
3455
$_$;
3456

    
3457

    
3458
--
3459
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
3460
--
3461

    
3462
CREATE FUNCTION schema(table_ regclass) RETURNS text
3463
    LANGUAGE sql STABLE
3464
    AS $_$
3465
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
3466
$_$;
3467

    
3468

    
3469
--
3470
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
3471
--
3472

    
3473
CREATE FUNCTION schema(type regtype) RETURNS text
3474
    LANGUAGE sql STABLE
3475
    AS $_$
3476
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
3477
$_$;
3478

    
3479

    
3480
--
3481
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3482
--
3483

    
3484
CREATE FUNCTION schema(type_null anyelement) RETURNS text
3485
    LANGUAGE sql STABLE
3486
    AS $_$
3487
SELECT util.schema(pg_typeof($1))
3488
$_$;
3489

    
3490

    
3491
--
3492
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
3493
--
3494

    
3495
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
3496
    LANGUAGE sql STABLE
3497
    AS $_$
3498
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
3499
$_$;
3500

    
3501

    
3502
--
3503
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
3504
--
3505

    
3506
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
3507
a schema bundle is a group of schemas with a common prefix
3508
';
3509

    
3510

    
3511
--
3512
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3513
--
3514

    
3515
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
3516
    LANGUAGE sql
3517
    AS $_$
3518
SELECT util.schema_rename(old_schema,
3519
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
3520
FROM util.schema_bundle_get_schemas($1) f (old_schema);
3521
SELECT NULL::void; -- don't fold away functions called in previous query
3522
$_$;
3523

    
3524

    
3525
--
3526
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3527
--
3528

    
3529
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
3530
    LANGUAGE plpgsql
3531
    AS $$
3532
BEGIN
3533
	-- don't schema_bundle_rm() the schema_bundle to keep!
3534
	IF replace = with_ THEN RETURN; END IF;
3535
	
3536
	PERFORM util.schema_bundle_rm(replace);
3537
	PERFORM util.schema_bundle_rename(with_, replace);
3538
END;
3539
$$;
3540

    
3541

    
3542
--
3543
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3544
--
3545

    
3546
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
3547
    LANGUAGE sql
3548
    AS $_$
3549
SELECT util.schema_rm(schema)
3550
FROM util.schema_bundle_get_schemas($1) f (schema);
3551
SELECT NULL::void; -- don't fold away functions called in previous query
3552
$_$;
3553

    
3554

    
3555
--
3556
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3557
--
3558

    
3559
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
3560
    LANGUAGE sql STABLE
3561
    AS $_$
3562
SELECT quote_ident(util.schema($1))
3563
$_$;
3564

    
3565

    
3566
--
3567
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
3568
--
3569

    
3570
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
3571
    LANGUAGE sql IMMUTABLE
3572
    AS $_$
3573
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
3574
$_$;
3575

    
3576

    
3577
--
3578
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3579
--
3580

    
3581
CREATE FUNCTION schema_oid(schema text) RETURNS oid
3582
    LANGUAGE sql STABLE
3583
    AS $_$
3584
SELECT oid FROM pg_namespace WHERE nspname = $1
3585
$_$;
3586

    
3587

    
3588
--
3589
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3590
--
3591

    
3592
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
3593
    LANGUAGE sql IMMUTABLE
3594
    AS $_$
3595
SELECT util.schema_regexp(schema_anchor := $1)
3596
$_$;
3597

    
3598

    
3599
--
3600
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3601
--
3602

    
3603
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
3604
    LANGUAGE sql IMMUTABLE
3605
    AS $_$
3606
SELECT util.str_equality_regexp(util.schema($1))
3607
$_$;
3608

    
3609

    
3610
--
3611
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3612
--
3613

    
3614
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
3615
    LANGUAGE sql
3616
    AS $_$
3617
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
3618
$_$;
3619

    
3620

    
3621
--
3622
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3623
--
3624

    
3625
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
3626
    LANGUAGE plpgsql
3627
    AS $$
3628
BEGIN
3629
	-- don't schema_rm() the schema to keep!
3630
	IF replace = with_ THEN RETURN; END IF;
3631
	
3632
	PERFORM util.schema_rm(replace);
3633
	PERFORM util.schema_rename(with_, replace);
3634
END;
3635
$$;
3636

    
3637

    
3638
--
3639
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3640
--
3641

    
3642
CREATE FUNCTION schema_rm(schema text) RETURNS void
3643
    LANGUAGE sql
3644
    AS $_$
3645
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
3646
$_$;
3647

    
3648

    
3649
--
3650
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3651
--
3652

    
3653
CREATE FUNCTION search_path_append(schemas text) RETURNS void
3654
    LANGUAGE sql
3655
    AS $_$
3656
SELECT util.eval(
3657
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
3658
$_$;
3659

    
3660

    
3661
--
3662
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3663
--
3664

    
3665
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
3666
    LANGUAGE plpgsql STRICT
3667
    AS $_$
3668
DECLARE
3669
    old text[] = ARRAY(SELECT util.col_names(table_));
3670
    new text[] = ARRAY(SELECT util.map_values(names));
3671
BEGIN
3672
    old = old[1:array_length(new, 1)]; -- truncate to same length
3673
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
3674
||$$ TO $$||quote_ident(value))
3675
    FROM each(hstore(old, new))
3676
    WHERE value != key -- not same name
3677
    ;
3678
END;
3679
$_$;
3680

    
3681

    
3682
--
3683
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3684
--
3685

    
3686
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
3687
idempotent
3688
';
3689

    
3690

    
3691
--
3692
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3693
--
3694

    
3695
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
3696
    LANGUAGE plpgsql STRICT
3697
    AS $_$
3698
DECLARE
3699
	row_ util.map;
3700
BEGIN
3701
	-- rename any metadata cols rather than re-adding them with new names
3702
	BEGIN
3703
		PERFORM util.set_col_names(table_, names);
3704
	EXCEPTION
3705
		WHEN array_subscript_error THEN -- selective suppress
3706
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
3707
				-- metadata cols not yet added
3708
			ELSE RAISE;
3709
			END IF;
3710
	END;
3711
	
3712
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
3713
	LOOP
3714
		PERFORM util.mk_const_col((table_, row_."to"),
3715
			substring(row_."from" from 2));
3716
	END LOOP;
3717
	
3718
	PERFORM util.set_col_names(table_, names);
3719
END;
3720
$_$;
3721

    
3722

    
3723
--
3724
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3725
--
3726

    
3727
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
3728
idempotent.
3729
the metadata mappings must be *last* in the names table.
3730
';
3731

    
3732

    
3733
--
3734
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
3735
--
3736

    
3737
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
3738
    LANGUAGE sql
3739
    AS $_$
3740
SELECT util.eval(COALESCE(
3741
$$ALTER TABLE $$||$1||$$
3742
$$||(
3743
	SELECT
3744
	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
3745
	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
3746
, $$)
3747
	FROM
3748
	(
3749
		SELECT
3750
		  quote_ident(col_name) AS col_name_sql
3751
		, util.col_type(($1, col_name)) AS curr_type
3752
		, type AS target_type
3753
		FROM unnest($2)
3754
	) s
3755
	WHERE curr_type != target_type
3756
), ''))
3757
$_$;
3758

    
3759

    
3760
--
3761
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
3762
--
3763

    
3764
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
3765
idempotent
3766
';
3767

    
3768

    
3769
--
3770
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3771
--
3772

    
3773
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
3774
    LANGUAGE sql
3775
    AS $_$
3776
SELECT util.eval(util.mk_set_comment($1, $2))
3777
$_$;
3778

    
3779

    
3780
--
3781
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
3782
--
3783

    
3784
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
3785
    LANGUAGE sql
3786
    AS $_$
3787
SELECT util.eval(util.mk_set_search_path($1, $2))
3788
$_$;
3789

    
3790

    
3791
--
3792
-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
3793
--
3794

    
3795
CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
3796
    LANGUAGE sql STABLE
3797
    AS $_$
3798
SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
3799
||$1||$$ AS
3800
$$||pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
3801
$$||util.show_grants_for($1)
3802
||util.show_set_comment($1)||$$
3803
$$
3804
$_$;
3805

    
3806

    
3807
--
3808
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
3809
--
3810

    
3811
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
3812
    LANGUAGE sql STABLE
3813
    AS $_$
3814
SELECT string_agg(cmd, '')
3815
FROM
3816
(
3817
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
3818
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
3819
$$ ELSE '' END) AS cmd
3820
	FROM util.grants_users() f (user_)
3821
) s
3822
$_$;
3823

    
3824

    
3825
--
3826
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
3827
--
3828

    
3829
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
3830
    LANGUAGE sql STABLE
3831
    AS $_$
3832
SELECT oid FROM pg_class
3833
WHERE relkind = ANY($3) AND relname ~ $1
3834
AND util.schema_matches(util.schema(relnamespace), $2)
3835
ORDER BY relname
3836
$_$;
3837

    
3838

    
3839
--
3840
-- Name: show_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
3841
--
3842

    
3843
CREATE FUNCTION show_set_comment(table_ regclass) RETURNS text
3844
    LANGUAGE sql STABLE
3845
    AS $_$
3846
SELECT util.mk_set_comment($1, util.comment($1))
3847
$_$;
3848

    
3849

    
3850
--
3851
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3852
--
3853

    
3854
CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
3855
    LANGUAGE sql STABLE
3856
    AS $_$
3857
SELECT oid
3858
FROM pg_type
3859
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
3860
ORDER BY typname
3861
$_$;
3862

    
3863

    
3864
--
3865
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3866
--
3867

    
3868
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
3869
    LANGUAGE sql STABLE
3870
    AS $_$
3871
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
3872
$_$;
3873

    
3874

    
3875
--
3876
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
3877
--
3878

    
3879
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
3880
    LANGUAGE sql IMMUTABLE
3881
    AS $_$
3882
SELECT '^'||util.regexp_quote($1)||'$'
3883
$_$;
3884

    
3885

    
3886
--
3887
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3888
--
3889

    
3890
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
3891
    LANGUAGE plpgsql STABLE STRICT
3892
    AS $_$
3893
DECLARE
3894
    hstore hstore;
3895
BEGIN
3896
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
3897
        table_||$$))$$ INTO STRICT hstore;
3898
    RETURN hstore;
3899
END;
3900
$_$;
3901

    
3902

    
3903
--
3904
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3905
--
3906

    
3907
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
3908
    LANGUAGE sql STABLE
3909
    AS $_$
3910
SELECT COUNT(*) > 0 FROM pg_constraint
3911
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
3912
$_$;
3913

    
3914

    
3915
--
3916
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3917
--
3918

    
3919
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
3920
gets whether a status flag is set by the presence of a table constraint
3921
';
3922

    
3923

    
3924
--
3925
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3926
--
3927

    
3928
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
3929
    LANGUAGE sql
3930
    AS $_$
3931
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
3932
||quote_ident($2)||$$ CHECK (true)$$)
3933
$_$;
3934

    
3935

    
3936
--
3937
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3938
--
3939

    
3940
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
3941
stores a status flag by the presence of a table constraint.
3942
idempotent.
3943
';
3944

    
3945

    
3946
--
3947
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3948
--
3949

    
3950
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
3951
    LANGUAGE sql STABLE
3952
    AS $_$
3953
SELECT util.table_flag__get($1, 'nulls_mapped')
3954
$_$;
3955

    
3956

    
3957
--
3958
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3959
--
3960

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

    
3965

    
3966
--
3967
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3968
--
3969

    
3970
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
3971
    LANGUAGE sql
3972
    AS $_$
3973
SELECT util.table_flag__set($1, 'nulls_mapped')
3974
$_$;
3975

    
3976

    
3977
--
3978
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3979
--
3980

    
3981
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
3982
sets that a table''s NULL-equivalent strings have been replaced with NULL.
3983
idempotent.
3984
';
3985

    
3986

    
3987
--
3988
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
3989
--
3990

    
3991
CREATE FUNCTION to_freq(table_ regclass) RETURNS void
3992
    LANGUAGE sql
3993
    AS $_$
3994
-- save data before truncating main table
3995
SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
3996

    
3997
-- repopulate main table w/ copies column
3998
SELECT util.truncate($1);
3999
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
4000
SELECT util.populate_table($1, $$
4001
SELECT (table_).*, copies
4002
FROM (
4003
	SELECT table_, COUNT(*) AS copies
4004
	FROM pg_temp.__copy table_
4005
	GROUP BY table_
4006
) s
4007
$$);
4008

    
4009
-- delete temp table so it doesn't stay around until end of connection
4010
SELECT util.drop_table('pg_temp.__copy');
4011
$_$;
4012

    
4013

    
4014
--
4015
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
4016
--
4017

    
4018
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
4019
    LANGUAGE plpgsql STRICT
4020
    AS $_$
4021
DECLARE
4022
    row record;
4023
BEGIN
4024
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
4025
    LOOP
4026
        IF row.global_name != row.name THEN
4027
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
4028
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
4029
        END IF;
4030
    END LOOP;
4031
END;
4032
$_$;
4033

    
4034

    
4035
--
4036
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4037
--
4038

    
4039
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
4040
idempotent
4041
';
4042

    
4043

    
4044
--
4045
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
4046
--
4047

    
4048
CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
4049
    LANGUAGE sql
4050
    AS $_$
4051
SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
4052
SELECT NULL::void; -- don't fold away functions called in previous query
4053
$_$;
4054

    
4055

    
4056
--
4057
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
4058
--
4059

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

    
4063
by default, cascadingly drops dependent columns so that they don''t prevent
4064
trim() from succeeding. note that this requires the dependent columns to then be
4065
manually re-created.
4066

    
4067
idempotent
4068
';
4069

    
4070

    
4071
--
4072
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
4073
--
4074

    
4075
CREATE FUNCTION truncate(table_ regclass) RETURNS void
4076
    LANGUAGE plpgsql STRICT
4077
    AS $_$
4078
BEGIN
4079
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
4080
END;
4081
$_$;
4082

    
4083

    
4084
--
4085
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4086
--
4087

    
4088
COMMENT ON FUNCTION truncate(table_ regclass) IS '
4089
idempotent
4090
';
4091

    
4092

    
4093
--
4094
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
4095
--
4096

    
4097
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
4098
    LANGUAGE sql IMMUTABLE
4099
    AS $_$
4100
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
4101
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
4102
$_$;
4103

    
4104

    
4105
--
4106
-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
4107
--
4108

    
4109
CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
4110
    LANGUAGE plpgsql IMMUTABLE
4111
    AS $$
4112
BEGIN
4113
	/* need explicit cast because some types not implicitly-castable, and also
4114
	to make the cast happen inside the try block. (*implicit* casts to the
4115
	return type happen at the end of the function, outside any block.) */
4116
	RETURN util.cast(value, ret_type_null);
4117
EXCEPTION
4118
WHEN   data_exception
4119
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
4120
	THEN
4121
	PERFORM util.raise('WARNING', SQLERRM);
4122
	RETURN NULL;
4123
END;
4124
$$;
4125

    
4126

    
4127
--
4128
-- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
4129
--
4130

    
4131
COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
4132
ret_type_null: NULL::ret_type
4133
';
4134

    
4135

    
4136
--
4137
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
4138
--
4139

    
4140
CREATE FUNCTION try_create(sql text) RETURNS void
4141
    LANGUAGE plpgsql STRICT
4142
    AS $$
4143
BEGIN
4144
	PERFORM util.eval(sql);
4145
EXCEPTION
4146
WHEN   not_null_violation
4147
		/* trying to add NOT NULL column to parent table, which cascades to
4148
		child table whose values for the new column will be NULL */
4149
	OR wrong_object_type -- trying to alter a view's columns
4150
	OR undefined_column
4151
	OR duplicate_column
4152
THEN NULL;
4153
WHEN datatype_mismatch THEN
4154
	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
4155
	ELSE RAISE; -- rethrow
4156
	END IF;
4157
END;
4158
$$;
4159

    
4160

    
4161
--
4162
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
4163
--
4164

    
4165
COMMENT ON FUNCTION try_create(sql text) IS '
4166
idempotent
4167
';
4168

    
4169

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

    
4174
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
4175
    LANGUAGE sql
4176
    AS $_$
4177
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
4178
$_$;
4179

    
4180

    
4181
--
4182
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
4183
--
4184

    
4185
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
4186
idempotent
4187
';
4188

    
4189

    
4190
--
4191
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
4192
--
4193

    
4194
CREATE FUNCTION type_qual(value anyelement) RETURNS text
4195
    LANGUAGE sql IMMUTABLE
4196
    AS $_$
4197
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
4198
$_$;
4199

    
4200

    
4201
--
4202
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
4203
--
4204

    
4205
COMMENT ON FUNCTION type_qual(value anyelement) IS '
4206
a type''s NOT NULL qualifier
4207
';
4208

    
4209

    
4210
--
4211
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
4212
--
4213

    
4214
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
4215
    LANGUAGE sql STABLE
4216
    AS $_$
4217
SELECT (attname::text, atttypid)::util.col_cast
4218
FROM pg_attribute
4219
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
4220
ORDER BY attnum
4221
$_$;
4222

    
4223

    
4224
--
4225
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
4226
--
4227

    
4228
CREATE FUNCTION typeof(value anyelement) RETURNS text
4229
    LANGUAGE sql IMMUTABLE
4230
    AS $_$
4231
SELECT util.qual_name(pg_typeof($1))
4232
$_$;
4233

    
4234

    
4235
--
4236
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
4237
--
4238

    
4239
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
4240
    LANGUAGE plpgsql STABLE
4241
    AS $_$
4242
DECLARE
4243
    type regtype;
4244
BEGIN
4245
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
4246
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
4247
    RETURN type;
4248
END;
4249
$_$;
4250

    
4251

    
4252
--
4253
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
4254
--
4255

    
4256
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
4257
    LANGUAGE sql
4258
    AS $_$
4259
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
4260
$_$;
4261

    
4262

    
4263
--
4264
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
4265
--
4266

    
4267
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
4268
auto-appends util to the search_path to enable use of util operators
4269
';
4270

    
4271

    
4272
--
4273
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
4274
--
4275

    
4276
CREATE AGGREGATE all_same(anyelement) (
4277
    SFUNC = all_same_transform,
4278
    STYPE = anyarray,
4279
    FINALFUNC = all_same_final
4280
);
4281

    
4282

    
4283
--
4284
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
4285
--
4286

    
4287
COMMENT ON AGGREGATE all_same(anyelement) IS '
4288
includes NULLs in comparison
4289
';
4290

    
4291

    
4292
--
4293
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
4294
--
4295

    
4296
CREATE AGGREGATE join_strs(text, text) (
4297
    SFUNC = join_strs_transform,
4298
    STYPE = text
4299
);
4300

    
4301

    
4302
--
4303
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
4304
--
4305

    
4306
CREATE OPERATOR %== (
4307
    PROCEDURE = "%==",
4308
    LEFTARG = anyelement,
4309
    RIGHTARG = anyelement
4310
);
4311

    
4312

    
4313
--
4314
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
4315
--
4316

    
4317
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
4318
returns whether the map-keys of the compared values are the same
4319
(mnemonic: % is the Perl symbol for a hash map)
4320

    
4321
should be overridden for types that store both keys and values
4322

    
4323
used in a FULL JOIN to select which columns to join on
4324
';
4325

    
4326

    
4327
--
4328
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
4329
--
4330

    
4331
CREATE OPERATOR -> (
4332
    PROCEDURE = map_get,
4333
    LEFTARG = regclass,
4334
    RIGHTARG = text
4335
);
4336

    
4337

    
4338
--
4339
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
4340
--
4341

    
4342
CREATE OPERATOR => (
4343
    PROCEDURE = hstore,
4344
    LEFTARG = text[],
4345
    RIGHTARG = text
4346
);
4347

    
4348

    
4349
--
4350
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
4351
--
4352

    
4353
COMMENT ON OPERATOR => (text[], text) IS '
4354
usage: array[''key1'', ...]::text[] => ''value''
4355
';
4356

    
4357

    
4358
--
4359
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
4360
--
4361

    
4362
CREATE OPERATOR ?*>= (
4363
    PROCEDURE = is_populated_more_often_than,
4364
    LEFTARG = anyelement,
4365
    RIGHTARG = anyelement
4366
);
4367

    
4368

    
4369
--
4370
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
4371
--
4372

    
4373
CREATE OPERATOR ?>= (
4374
    PROCEDURE = is_more_complete_than,
4375
    LEFTARG = anyelement,
4376
    RIGHTARG = anyelement
4377
);
4378

    
4379

    
4380
--
4381
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
4382
--
4383

    
4384
CREATE OPERATOR ||% (
4385
    PROCEDURE = concat_esc,
4386
    LEFTARG = text,
4387
    RIGHTARG = text
4388
);
4389

    
4390

    
4391
--
4392
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
4393
--
4394

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

    
4399

    
4400
--
4401
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
4402
--
4403

    
4404
CREATE TABLE map (
4405
    "from" text NOT NULL,
4406
    "to" text,
4407
    filter text,
4408
    notes text
4409
);
4410

    
4411

    
4412
--
4413
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
4414
--
4415

    
4416

    
4417

    
4418
--
4419
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
4420
--
4421

    
4422

    
4423

    
4424
--
4425
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4426
--
4427

    
4428
ALTER TABLE ONLY map
4429
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
4430

    
4431

    
4432
--
4433
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
4434
--
4435

    
4436
ALTER TABLE ONLY map
4437
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
4438

    
4439

    
4440
--
4441
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
4442
--
4443

    
4444
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
4445

    
4446

    
4447
--
4448
-- PostgreSQL database dump complete
4449
--
4450

    
(21-21/31)