Project

General

Profile

1
--
2
-- PostgreSQL database dump
3
--
4

    
5
SET statement_timeout = 0;
6
SET lock_timeout = 0;
7
SET client_encoding = 'UTF8';
8
SET standard_conforming_strings = on;
9
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11

    
12
--
13
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
14
--
15

    
16
CREATE SCHEMA util;
17

    
18

    
19
--
20
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
21
--
22

    
23
COMMENT ON SCHEMA util IS '
24
IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
25

    
26
NOTE: SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding. avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won''t mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements.
27
';
28

    
29

    
30
SET search_path = util, pg_catalog;
31

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

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

    
41

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

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

    
51

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

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

    
63

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

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

    
73

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

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

    
84

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

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

    
93

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

    
98
CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
99
    LANGUAGE sql IMMUTABLE
100
    AS $_$
101
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
102
$_$;
103

    
104

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

    
109
CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
110
    LANGUAGE sql IMMUTABLE
111
    AS $_$
112
SELECT bool_and(value)
113
FROM
114
(VALUES
115
      ($1)
116
    , ($2)
117
    , ($3)
118
    , ($4)
119
    , ($5)
120
)
121
AS v (value)
122
$_$;
123

    
124

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

    
129
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
130
_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.
131
';
132

    
133

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

    
138
CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
139
    LANGUAGE sql IMMUTABLE
140
    AS $_$
141
SELECT avg(value)
142
FROM
143
(VALUES
144
      ($1)
145
    , ($2)
146
    , ($3)
147
    , ($4)
148
    , ($5)
149
)
150
AS v (value)
151
$_$;
152

    
153

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

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

    
172

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

    
177
CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
178
    LANGUAGE sql IMMUTABLE
179
    AS $_$
180
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
181
FROM
182
(VALUES
183
      ($1)
184
    , ($2/60)
185
    , ($3/60/60)
186
)
187
AS v (value)
188
$_$;
189

    
190

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

    
195
CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
196
    LANGUAGE sql IMMUTABLE
197
    AS $_$
198
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
199
$_$;
200

    
201

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

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

    
212

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

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

    
225

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

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

    
236

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

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

    
247

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

    
252
CREATE FUNCTION _join("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
253
    LANGUAGE sql IMMUTABLE
254
    AS $_$
255
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
256
$_$;
257

    
258

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

    
263
CREATE FUNCTION _join_words("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
264
    LANGUAGE sql IMMUTABLE
265
    AS $_$
266
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
267
$_$;
268

    
269

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

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

    
280

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

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

    
291

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

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

    
306

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

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

    
331

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

    
336
CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
337
    LANGUAGE sql IMMUTABLE
338
    AS $_$
339
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
340
$_$;
341

    
342

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

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

    
381

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

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

    
392

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

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

    
431

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

    
436
CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
437
    LANGUAGE sql IMMUTABLE
438
    AS $_$
439
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
440
$_$;
441

    
442

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

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

    
453

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

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

    
464

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

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

    
475

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

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

    
501

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

    
506
CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
507
    LANGUAGE sql IMMUTABLE
508
    AS $_$
509
SELECT bool_or(value)
510
FROM
511
(VALUES
512
      ($1)
513
    , ($2)
514
    , ($3)
515
    , ($4)
516
    , ($5)
517
)
518
AS v (value)
519
$_$;
520

    
521

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

    
526
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
527
_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.
528
';
529

    
530

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

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

    
541

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

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

    
552

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

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

    
570

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

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

    
579

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

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

    
590

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

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

    
607

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

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

    
618

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

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

    
629

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

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

    
638

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

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

    
649

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

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

    
660

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

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

    
671

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

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

    
680

    
681
--
682
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
683
--
684

    
685
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
686
    LANGUAGE sql STABLE
687
    AS $_$
688
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
689
$_$;
690

    
691

    
692
--
693
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
694
--
695

    
696
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
697
    LANGUAGE plpgsql STRICT
698
    AS $_$
699
BEGIN
700
    -- not yet clustered (ARRAY[] compares NULLs literally)
701
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
702
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
703
    END IF;
704
END;
705
$_$;
706

    
707

    
708
--
709
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
710
--
711

    
712
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
713
idempotent
714
';
715

    
716

    
717
--
718
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
719
--
720

    
721
CREATE FUNCTION col__min(col col_ref) RETURNS integer
722
    LANGUAGE sql STABLE
723
    AS $_$
724
SELECT util.eval2val($$
725
SELECT $$||quote_ident($1.name)||$$
726
FROM $$||$1.table_||$$
727
ORDER BY $$||quote_ident($1.name)||$$ ASC
728
LIMIT 1
729
$$, NULL::integer)
730
$_$;
731

    
732

    
733
--
734
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
735
--
736

    
737
CREATE FUNCTION col_comment(col col_ref) RETURNS text
738
    LANGUAGE plpgsql STABLE STRICT
739
    AS $$
740
DECLARE
741
	comment text;
742
BEGIN
743
	SELECT description
744
	FROM pg_attribute
745
	LEFT JOIN pg_description ON objoid = attrelid
746
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
747
	WHERE attrelid = col.table_ AND attname = col.name
748
	INTO STRICT comment
749
	;
750
	RETURN comment;
751
EXCEPTION
752
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
753
END;
754
$$;
755

    
756

    
757
--
758
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
759
--
760

    
761
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
762
    LANGUAGE plpgsql STABLE STRICT
763
    AS $$
764
DECLARE
765
	default_sql text;
766
BEGIN
767
	SELECT adsrc
768
	FROM pg_attribute
769
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
770
	WHERE attrelid = col.table_ AND attname = col.name
771
	INTO STRICT default_sql
772
	;
773
	RETURN default_sql;
774
EXCEPTION
775
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
776
END;
777
$$;
778

    
779

    
780
--
781
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
782
--
783

    
784
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
785
    LANGUAGE sql STABLE
786
    AS $_$
787
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
788
$_$;
789

    
790

    
791
--
792
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
793
--
794

    
795
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
796
ret_type_null: NULL::ret_type
797
';
798

    
799

    
800
--
801
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
802
--
803

    
804
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
805
    LANGUAGE plpgsql STRICT
806
    AS $$
807
BEGIN
808
    PERFORM util.col_type(col);
809
    RETURN true;
810
EXCEPTION
811
    WHEN undefined_column THEN RETURN false;
812
END;
813
$$;
814

    
815

    
816
--
817
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
818
--
819

    
820
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
821
    LANGUAGE plpgsql STABLE STRICT
822
    AS $$
823
DECLARE
824
    prefix text := util.name(type)||'.';
825
BEGIN
826
    RETURN QUERY
827
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
828
        FROM util.col_names(type) f (name_);
829
END;
830
$$;
831

    
832

    
833
--
834
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
835
--
836

    
837
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
838
    LANGUAGE sql STABLE
839
    AS $_$
840
SELECT attname::text
841
FROM pg_attribute
842
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
843
ORDER BY attnum
844
$_$;
845

    
846

    
847
--
848
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
849
--
850

    
851
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
852
    LANGUAGE plpgsql STABLE STRICT
853
    AS $_$
854
BEGIN
855
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
856
END;
857
$_$;
858

    
859

    
860
--
861
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
862
--
863

    
864
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
865
    LANGUAGE plpgsql STABLE STRICT
866
    AS $$
867
DECLARE
868
    type regtype;
869
BEGIN
870
    SELECT atttypid FROM pg_attribute
871
    WHERE attrelid = col.table_ AND attname = col.name
872
    INTO STRICT type
873
    ;
874
    RETURN type;
875
EXCEPTION
876
    WHEN no_data_found THEN
877
        RAISE undefined_column USING MESSAGE =
878
            concat('undefined column: ', col.name);
879
END;
880
$$;
881

    
882

    
883
--
884
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
885
--
886

    
887
CREATE FUNCTION comment(element oid) RETURNS text
888
    LANGUAGE sql STABLE
889
    AS $_$
890
SELECT description FROM pg_description WHERE objoid = $1
891
$_$;
892

    
893

    
894
--
895
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
896
--
897

    
898
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
899
    LANGUAGE sql IMMUTABLE
900
    AS $_$
901
SELECT util.esc_name__append($2, $1)
902
$_$;
903

    
904

    
905
--
906
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
907
--
908

    
909
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
910
    LANGUAGE sql IMMUTABLE
911
    AS $_$
912
SELECT position($1 in $2) > 0 /*1-based offset*/
913
$_$;
914

    
915

    
916
--
917
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
918
--
919

    
920
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
921
    LANGUAGE sql
922
    AS $_$
923
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
924
$_$;
925

    
926

    
927
--
928
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
929
--
930

    
931
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
932
    LANGUAGE plpgsql STRICT
933
    AS $$
934
BEGIN
935
    PERFORM util.eval(sql);
936
EXCEPTION
937
    WHEN duplicate_table  THEN NULL;
938
    WHEN duplicate_object THEN NULL; -- e.g. constraint
939
    WHEN duplicate_column THEN NULL;
940
    WHEN invalid_table_definition THEN
941
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
942
        ELSE RAISE;
943
        END IF;
944
END;
945
$$;
946

    
947

    
948
--
949
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
950
--
951

    
952
COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
953
idempotent
954
';
955

    
956

    
957
--
958
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
959
--
960

    
961
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
962
    LANGUAGE sql IMMUTABLE
963
    AS $$
964
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
965
$$;
966

    
967

    
968
--
969
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
970
--
971

    
972
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
973
    LANGUAGE sql IMMUTABLE
974
    AS $_$
975
SELECT util.raise('NOTICE', 'returns: '
976
||(CASE WHEN $2 THEN util.quote_typed($1) ELSE $1::text END));
977
SELECT $1;
978
$_$;
979

    
980

    
981
--
982
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
983
--
984

    
985
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
986
    LANGUAGE sql IMMUTABLE
987
    AS $_$
988
/* newline before so the query starts at the beginning of the line.
989
newline after to visually separate queries from one another. */
990
SELECT util.raise('NOTICE', $$
991
$$||util.runnable_sql($1)||$$
992
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
993
$_$;
994

    
995

    
996
--
997
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
998
--
999

    
1000
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
1001
    LANGUAGE sql STABLE
1002
    AS $_$
1003
SELECT util.eval2set($$
1004
SELECT col
1005
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
1006
LEFT JOIN $$||$2||$$ ON "to" = col
1007
WHERE "from" IS NULL
1008
$$, NULL::text)
1009
$_$;
1010

    
1011

    
1012
--
1013
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1014
--
1015

    
1016
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
1017
gets table_''s derived columns (all the columns not in the names table)
1018
';
1019

    
1020

    
1021
--
1022
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1023
--
1024

    
1025
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1026
    LANGUAGE sql
1027
    AS $_$
1028
SELECT * FROM util.diff($1::text, $2::text, $3,
1029
	single_row := util.has_single_row($1) AND util.has_single_row($2))
1030
$_$;
1031

    
1032

    
1033
--
1034
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1035
--
1036

    
1037
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1038
col_type_null (*required*): NULL::shared_base_type
1039
usage:
1040
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1041
';
1042

    
1043

    
1044
--
1045
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1046
--
1047

    
1048
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
1049
    LANGUAGE plpgsql
1050
    SET search_path TO pg_temp
1051
    AS $_$
1052
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
1053
changes of search_path (schema elements are bound at inline time rather than
1054
runtime) */
1055
/* function option search_path is needed to limit the effects of
1056
`SET LOCAL search_path` to the current function */
1057
BEGIN
1058
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
1059
	
1060
	PERFORM util.mk_keys_func(pg_typeof($3));
1061
	RETURN QUERY
1062
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
1063
$$/* need to explicitly cast each side to the return type because this does not
1064
happen automatically even when an implicit cast is available */
1065
  left_::$$||util.typeof($3)||$$
1066
, right_::$$||util.typeof($3)
1067
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
1068
the *same* base type, *even though* this is optional when using a custom %== */
1069
, util._if($4, $$true/*= CROSS JOIN*/$$,
1070
$$ left_::$$||util.typeof($3)||$$
1071
%== right_::$$||util.typeof($3)||$$
1072
	-- refer to EXPLAIN output for expansion of %==$$
1073
)), $3)
1074
	;
1075
END;
1076
$_$;
1077

    
1078

    
1079
--
1080
-- 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: -
1081
--
1082

    
1083
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1084
col_type_null (*required*): NULL::col_type
1085
single_row: whether the tables consist of a single row, which should be
1086
	displayed side-by-side
1087

    
1088
to match up rows using a subset of the columns, create a custom keys() function
1089
which returns this subset as a record:
1090
-- note that OUT parameters for the returned fields are *not* needed
1091
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
1092
  RETURNS record AS
1093
$BODY$
1094
SELECT ($1.key_field_0, $1.key_field_1)
1095
$BODY$
1096
  LANGUAGE sql IMMUTABLE
1097
  COST 100;
1098

    
1099

    
1100
to run EXPLAIN on the FULL JOIN query:
1101
# run this function
1102
# look for a NOTICE containing the expanded query that it ran
1103
# run EXPLAIN on this expanded query
1104
';
1105

    
1106

    
1107
--
1108
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1109
--
1110

    
1111
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1112
    LANGUAGE sql
1113
    AS $_$
1114
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1115
$_$;
1116

    
1117

    
1118
--
1119
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1120
--
1121

    
1122
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1123
idempotent
1124
';
1125

    
1126

    
1127
--
1128
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1129
--
1130

    
1131
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1132
    LANGUAGE sql
1133
    AS $_$
1134
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1135
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1136
$_$;
1137

    
1138

    
1139
--
1140
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1141
--
1142

    
1143
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1144
idempotent
1145
';
1146

    
1147

    
1148
--
1149
-- Name: drop_relation(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
1150
--
1151

    
1152
CREATE FUNCTION drop_relation(relation regclass, force boolean DEFAULT false) RETURNS void
1153
    LANGUAGE sql
1154
    AS $_$
1155
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1156
included in the debug SQL */
1157
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1158
$_$;
1159

    
1160

    
1161
--
1162
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1163
--
1164

    
1165
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1166
    LANGUAGE sql
1167
    AS $_$
1168
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1169
||util._if($3, $$ CASCADE$$, ''::text))
1170
$_$;
1171

    
1172

    
1173
--
1174
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1175
--
1176

    
1177
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1178
idempotent
1179
';
1180

    
1181

    
1182
--
1183
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1184
--
1185

    
1186
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
1187
    LANGUAGE sql
1188
    AS $_$
1189
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
1190
$_$;
1191

    
1192

    
1193
--
1194
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1195
--
1196

    
1197
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
1198
    LANGUAGE sql
1199
    AS $_$
1200
SELECT util.drop_relation(relation, $3)
1201
FROM util.show_relations_like($1, $2) relation
1202
;
1203
SELECT NULL::void; -- don't fold away functions called in previous query
1204
$_$;
1205

    
1206

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

    
1211
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1212
    LANGUAGE sql
1213
    AS $_$
1214
SELECT util.drop_relation('TABLE', $1, $2)
1215
$_$;
1216

    
1217

    
1218
--
1219
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1220
--
1221

    
1222
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1223
idempotent
1224
';
1225

    
1226

    
1227
--
1228
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1229
--
1230

    
1231
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1232
    LANGUAGE sql
1233
    AS $_$
1234
SELECT util.drop_relation('VIEW', $1, $2)
1235
$_$;
1236

    
1237

    
1238
--
1239
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1240
--
1241

    
1242
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1243
idempotent
1244
';
1245

    
1246

    
1247
--
1248
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1249
--
1250

    
1251
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1252
    LANGUAGE sql IMMUTABLE
1253
    AS $_$
1254
SELECT util.array_fill($1, 0)
1255
$_$;
1256

    
1257

    
1258
--
1259
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1260
--
1261

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

    
1266

    
1267
--
1268
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1269
--
1270

    
1271
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1272
    LANGUAGE sql IMMUTABLE
1273
    AS $_$
1274
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1275
$_$;
1276

    
1277

    
1278
--
1279
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
1280
--
1281

    
1282
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1283
    LANGUAGE sql IMMUTABLE
1284
    AS $_$
1285
SELECT regexp_replace($2, '("?)$', $1||'\1')
1286
$_$;
1287

    
1288

    
1289
--
1290
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1291
--
1292

    
1293
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
1294
    LANGUAGE plpgsql
1295
    AS $$
1296
BEGIN
1297
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1298
	EXECUTE sql;
1299
END;
1300
$$;
1301

    
1302

    
1303
--
1304
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1305
--
1306

    
1307
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1308
    LANGUAGE plpgsql
1309
    AS $$
1310
BEGIN
1311
	PERFORM util.debug_print_sql(sql);
1312
	RETURN QUERY EXECUTE sql;
1313
END;
1314
$$;
1315

    
1316

    
1317
--
1318
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1319
--
1320

    
1321
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1322
col_type_null (*required*): NULL::col_type
1323
';
1324

    
1325

    
1326
--
1327
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1328
--
1329

    
1330
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1331
    LANGUAGE plpgsql
1332
    AS $$
1333
BEGIN
1334
	PERFORM util.debug_print_sql(sql);
1335
	RETURN QUERY EXECUTE sql;
1336
END;
1337
$$;
1338

    
1339

    
1340
--
1341
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1342
--
1343

    
1344
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
1345
    LANGUAGE plpgsql
1346
    AS $$
1347
BEGIN
1348
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1349
	RETURN QUERY EXECUTE sql;
1350
END;
1351
$$;
1352

    
1353

    
1354
--
1355
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1356
--
1357

    
1358
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1359
    LANGUAGE plpgsql STABLE
1360
    AS $$
1361
DECLARE
1362
	ret_val ret_type_null%TYPE;
1363
BEGIN
1364
	PERFORM util.debug_print_sql(sql);
1365
	EXECUTE sql INTO STRICT ret_val;
1366
	RETURN ret_val;
1367
END;
1368
$$;
1369

    
1370

    
1371
--
1372
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1373
--
1374

    
1375
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1376
ret_type_null: NULL::ret_type
1377
';
1378

    
1379

    
1380
--
1381
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1382
--
1383

    
1384
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1385
    LANGUAGE sql
1386
    AS $_$
1387
SELECT util.eval2val($$SELECT $$||$1, $2)
1388
$_$;
1389

    
1390

    
1391
--
1392
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1393
--
1394

    
1395
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1396
ret_type_null: NULL::ret_type
1397
';
1398

    
1399

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

    
1404
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1405
    LANGUAGE sql
1406
    AS $_$
1407
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1408
$_$;
1409

    
1410

    
1411
--
1412
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1413
--
1414

    
1415
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1416
sql: can be NULL, which will be passed through
1417
ret_type_null: NULL::ret_type
1418
';
1419

    
1420

    
1421
--
1422
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1423
--
1424

    
1425
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1426
    LANGUAGE sql STABLE
1427
    AS $_$
1428
SELECT col_name
1429
FROM unnest($2) s (col_name)
1430
WHERE util.col_exists(($1, col_name))
1431
$_$;
1432

    
1433

    
1434
--
1435
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1436
--
1437

    
1438
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1439
    LANGUAGE sql
1440
    AS $_$
1441
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
1442
$_$;
1443

    
1444

    
1445
--
1446
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1447
--
1448

    
1449
CREATE FUNCTION explain2notice(sql text) RETURNS void
1450
    LANGUAGE sql
1451
    AS $_$
1452
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
1453
$_$;
1454

    
1455

    
1456
--
1457
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
1458
--
1459

    
1460
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
1461
    LANGUAGE sql
1462
    AS $_$
1463
-- newline before and after to visually separate it from other debug info
1464
SELECT $$
1465
EXPLAIN:
1466
$$||util.explain2str($1)||$$
1467
$$
1468
$_$;
1469

    
1470

    
1471
--
1472
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
1473
--
1474

    
1475
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
1476
    LANGUAGE sql
1477
    AS $_$
1478
SELECT (CASE WHEN util.is_explainable($1) THEN util.explain2notice_msg($1) END)
1479
$_$;
1480

    
1481

    
1482
--
1483
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1484
--
1485

    
1486
CREATE FUNCTION explain2str(sql text) RETURNS text
1487
    LANGUAGE sql
1488
    AS $_$
1489
SELECT util.join_strs(explain, $$
1490
$$) FROM util.explain($1)
1491
$_$;
1492

    
1493

    
1494
SET default_tablespace = '';
1495

    
1496
SET default_with_oids = false;
1497

    
1498
--
1499
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1500
--
1501

    
1502
CREATE TABLE explain (
1503
    line text NOT NULL
1504
);
1505

    
1506

    
1507
--
1508
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1509
--
1510

    
1511
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1512
    LANGUAGE sql
1513
    AS $_$
1514
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1515
$$||quote_nullable($1)||$$
1516
)$$)
1517
$_$;
1518

    
1519

    
1520
--
1521
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1522
--
1523

    
1524
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1525
usage:
1526
PERFORM util.explain2table($$
1527
query
1528
$$);
1529
';
1530

    
1531

    
1532
--
1533
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
1534
--
1535

    
1536
CREATE FUNCTION first_word(str text) RETURNS text
1537
    LANGUAGE sql IMMUTABLE
1538
    AS $_$
1539
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
1540
$_$;
1541

    
1542

    
1543
--
1544
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1545
--
1546

    
1547
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1548
    LANGUAGE sql IMMUTABLE
1549
    AS $_$
1550
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1551
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1552
) END
1553
$_$;
1554

    
1555

    
1556
--
1557
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1558
--
1559

    
1560
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1561
ensures that an array will always have proper non-NULL dimensions
1562
';
1563

    
1564

    
1565
--
1566
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
1567
--
1568

    
1569
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1570
    LANGUAGE plpgsql
1571
    AS $_$
1572
DECLARE
1573
	PG_EXCEPTION_DETAIL text;
1574
	recreate_users_cmd text = util.save_drop_views(users);
1575
BEGIN
1576
	PERFORM util.eval(cmd);
1577
	PERFORM util.eval(recreate_users_cmd);
1578
EXCEPTION
1579
WHEN dependent_objects_still_exist THEN
1580
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1581
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1582
	users = array(SELECT * FROM util.regexp_matches_group(
1583
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on table .*$'));
1584
	IF util.is_empty(users) THEN RAISE; END IF;
1585
	PERFORM util.force_recreate(cmd, users);
1586
END;
1587
$_$;
1588

    
1589

    
1590
--
1591
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1592
--
1593

    
1594
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1595
idempotent
1596

    
1597
users: not necessary to provide this because it will be autopopulated
1598
';
1599

    
1600

    
1601
--
1602
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1603
--
1604

    
1605
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1606
    LANGUAGE plpgsql STRICT
1607
    AS $_$
1608
DECLARE
1609
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1610
$$||query;
1611
BEGIN
1612
	EXECUTE mk_view;
1613
EXCEPTION
1614
WHEN invalid_table_definition THEN
1615
	IF SQLERRM = 'cannot drop columns from view'
1616
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1617
	THEN
1618
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1619
		EXECUTE mk_view;
1620
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1621
	END IF;
1622
END;
1623
$_$;
1624

    
1625

    
1626
--
1627
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1628
--
1629

    
1630
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1631
idempotent
1632
';
1633

    
1634

    
1635
--
1636
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1637
--
1638

    
1639
CREATE FUNCTION grants_users() RETURNS SETOF text
1640
    LANGUAGE sql IMMUTABLE
1641
    AS $$
1642
VALUES ('bien_read'), ('public_')
1643
$$;
1644

    
1645

    
1646
--
1647
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1648
--
1649

    
1650
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1651
    LANGUAGE sql IMMUTABLE
1652
    AS $_$
1653
SELECT substring($2 for length($1)) = $1
1654
$_$;
1655

    
1656

    
1657
--
1658
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1659
--
1660

    
1661
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1662
    LANGUAGE sql STABLE
1663
    AS $_$
1664
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1665
$_$;
1666

    
1667

    
1668
--
1669
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1670
--
1671

    
1672
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1673
    LANGUAGE sql IMMUTABLE
1674
    AS $_$
1675
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1676
$_$;
1677

    
1678

    
1679
--
1680
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1681
--
1682

    
1683
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1684
avoids repeating the same value for each key
1685
';
1686

    
1687

    
1688
--
1689
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1690
--
1691

    
1692
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1693
    LANGUAGE sql IMMUTABLE
1694
    AS $_$
1695
SELECT COALESCE($1, $2)
1696
$_$;
1697

    
1698

    
1699
--
1700
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
1701
--
1702

    
1703
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
1704
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
1705
';
1706

    
1707

    
1708
--
1709
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1710
--
1711

    
1712
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
1713
    LANGUAGE sql
1714
    AS $_$
1715
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
1716
$_$;
1717

    
1718

    
1719
--
1720
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1721
--
1722

    
1723
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1724
    LANGUAGE sql STABLE
1725
    AS $_$
1726
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1727
$_$;
1728

    
1729

    
1730
--
1731
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1732
--
1733

    
1734
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
1735
    LANGUAGE sql IMMUTABLE
1736
    AS $_$
1737
SELECT util.array_length($1) = 0
1738
$_$;
1739

    
1740

    
1741
--
1742
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
1743
--
1744

    
1745
CREATE FUNCTION is_explain(sql text) RETURNS boolean
1746
    LANGUAGE sql IMMUTABLE
1747
    AS $_$
1748
SELECT upper(util.first_word($1)) = 'EXPLAIN'
1749
$_$;
1750

    
1751

    
1752
--
1753
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
1754
--
1755

    
1756
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
1757
    LANGUAGE sql IMMUTABLE
1758
    AS $_$
1759
SELECT upper(util.first_word($1)) = ANY(
1760
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
1761
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
1762
)
1763
$_$;
1764

    
1765

    
1766
--
1767
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1768
--
1769

    
1770
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1771
    LANGUAGE sql IMMUTABLE
1772
    AS $_$
1773
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1774
$_$;
1775

    
1776

    
1777
--
1778
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1779
--
1780

    
1781
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1782
    LANGUAGE sql IMMUTABLE
1783
    AS $_$
1784
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1785
$_$;
1786

    
1787

    
1788
--
1789
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
1790
--
1791

    
1792
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
1793
    LANGUAGE sql IMMUTABLE
1794
    AS $_$
1795
SELECT upper(util.first_word($1)) = 'SET'
1796
$_$;
1797

    
1798

    
1799
--
1800
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
1801
--
1802

    
1803
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
1804
    LANGUAGE sql STABLE
1805
    AS $_$
1806
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
1807
$_$;
1808

    
1809

    
1810
--
1811
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
1812
--
1813

    
1814
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
1815
    LANGUAGE sql STABLE
1816
    AS $_$
1817
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
1818
$_$;
1819

    
1820

    
1821
--
1822
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1823
--
1824

    
1825
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1826
    LANGUAGE sql IMMUTABLE STRICT
1827
    AS $_$
1828
SELECT $1 || $3 || $2
1829
$_$;
1830

    
1831

    
1832
--
1833
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
1834
--
1835

    
1836
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
1837
must be declared STRICT to use the special handling of STRICT aggregating functions
1838
';
1839

    
1840

    
1841
--
1842
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
1843
--
1844

    
1845
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
1846
    LANGUAGE sql IMMUTABLE
1847
    AS $_$
1848
SELECT $1 -- compare on the entire value
1849
$_$;
1850

    
1851

    
1852
--
1853
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
1854
--
1855

    
1856
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
1857
    LANGUAGE sql IMMUTABLE
1858
    AS $_$
1859
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
1860
$_$;
1861

    
1862

    
1863
--
1864
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
1865
--
1866

    
1867
CREATE FUNCTION ltrim_nl(str text) RETURNS text
1868
    LANGUAGE sql IMMUTABLE
1869
    AS $_$
1870
SELECT ltrim($1, $$
1871
$$)
1872
$_$;
1873

    
1874

    
1875
--
1876
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1877
--
1878

    
1879
CREATE FUNCTION map_filter_insert() RETURNS trigger
1880
    LANGUAGE plpgsql
1881
    AS $$
1882
BEGIN
1883
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1884
	RETURN new;
1885
END;
1886
$$;
1887

    
1888

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

    
1893
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1894
    LANGUAGE plpgsql STABLE STRICT
1895
    AS $_$
1896
DECLARE
1897
    value text;
1898
BEGIN
1899
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1900
        INTO value USING key;
1901
    RETURN value;
1902
END;
1903
$_$;
1904

    
1905

    
1906
--
1907
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1908
--
1909

    
1910
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1911
    LANGUAGE sql IMMUTABLE
1912
    AS $_$
1913
SELECT util._map(util.nulls_map($1), $2)
1914
$_$;
1915

    
1916

    
1917
--
1918
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1919
--
1920

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

    
1924
[1] inlining of function calls, which is different from constant folding
1925
[2] _map()''s profiling query
1926
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1927
and map_nulls()''s profiling query
1928
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1929
both take ~920 ms.
1930
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.
1931
';
1932

    
1933

    
1934
--
1935
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1936
--
1937

    
1938
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1939
    LANGUAGE plpgsql STABLE STRICT
1940
    AS $_$
1941
BEGIN
1942
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1943
END;
1944
$_$;
1945

    
1946

    
1947
--
1948
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
1949
--
1950

    
1951
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
1952
    LANGUAGE sql
1953
    AS $_$
1954
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
1955
$$||util.ltrim_nl($2));
1956
-- make sure the created table has the correct estimated row count
1957
SELECT util.analyze_($1);
1958

    
1959
SELECT util.append_comment($1, '
1960
contents generated from:
1961
'||util.ltrim_nl($2)||';
1962
');
1963
$_$;
1964

    
1965

    
1966
--
1967
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
1968
--
1969

    
1970
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
1971
idempotent
1972
';
1973

    
1974

    
1975
--
1976
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1977
--
1978

    
1979
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
1980
    LANGUAGE sql
1981
    AS $_$
1982
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
1983
$_$;
1984

    
1985

    
1986
--
1987
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
1988
--
1989

    
1990
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
1991
idempotent
1992
';
1993

    
1994

    
1995
--
1996
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1997
--
1998

    
1999
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
2000
    LANGUAGE sql
2001
    AS $_$
2002
SELECT util.create_if_not_exists($$
2003
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
2004
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
2005
||quote_literal($2)||$$;
2006
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
2007
constant
2008
';
2009
$$)
2010
$_$;
2011

    
2012

    
2013
--
2014
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
2015
--
2016

    
2017
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
2018
idempotent
2019
';
2020

    
2021

    
2022
--
2023
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
2024
--
2025

    
2026
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
2027
    LANGUAGE plpgsql STRICT
2028
    AS $_$
2029
DECLARE
2030
    type regtype = util.typeof(expr, col.table_::text::regtype);
2031
    col_name_sql text = quote_ident(col.name);
2032
BEGIN
2033
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
2034
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
2035
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
2036
$$||expr||$$;
2037
$$);
2038
END;
2039
$_$;
2040

    
2041

    
2042
--
2043
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
2044
--
2045

    
2046
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
2047
idempotent
2048
';
2049

    
2050

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

    
2055
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
2056
    LANGUAGE sql IMMUTABLE
2057
    AS $_$
2058
SELECT
2059
$$SELECT
2060
$$||$3||$$
2061
FROM      $$||$1||$$ left_
2062
FULL JOIN $$||$2||$$ right_
2063
ON $$||$4||$$
2064
WHERE $$||$5||$$
2065
ORDER BY left_, right_
2066
$$
2067
$_$;
2068

    
2069

    
2070
--
2071
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2072
--
2073

    
2074
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
2075
    LANGUAGE sql IMMUTABLE
2076
    AS $_$
2077
-- keys()
2078
SELECT util.mk_keys_func($1, ARRAY(
2079
SELECT col FROM util.typed_cols($1) col
2080
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
2081
));
2082

    
2083
-- values_()
2084
SELECT util.mk_keys_func($1, COALESCE(
2085
	NULLIF(ARRAY(
2086
	SELECT col FROM util.typed_cols($1) col
2087
	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
2088
	), ARRAY[]::util.col_cast[])
2089
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
2090
, 'values_');
2091
$_$;
2092

    
2093

    
2094
--
2095
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
2096
--
2097

    
2098
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
2099
    LANGUAGE sql IMMUTABLE
2100
    AS $_$
2101
SELECT util.create_if_not_exists($$
2102
CREATE OR REPLACE FUNCTION $$||util.schema($1)||$$.$$||$3||$$(value $$
2103
||util.qual_name($1)||util.mk_out_params($2)||$$)
2104
  RETURNS $$||util._if(util.array_length($2) = 1, $2[1].type::text, $$record$$)
2105
||$$ AS
2106
$BODY1$
2107
SELECT $$|| util._if(util.array_length($2) = 1, ''::text, $$ROW$$) ||$$($$||
2108
(SELECT COALESCE(string_agg($$$1.$$||(unnest).col_name, ', '), '')
2109
FROM unnest($2)) ||$$)
2110
$BODY1$
2111
  LANGUAGE sql IMMUTABLE
2112
  COST 100;
2113
$$)
2114
$_$;
2115

    
2116

    
2117
--
2118
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2119
--
2120

    
2121
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
2122
    LANGUAGE sql
2123
    AS $_$
2124
SELECT util.create_if_not_exists($$
2125
CREATE TABLE $$||$1||$$
2126
(
2127
    LIKE util.map INCLUDING ALL
2128
);
2129

    
2130
CREATE TRIGGER map_filter_insert
2131
  BEFORE INSERT
2132
  ON $$||$1||$$
2133
  FOR EACH ROW
2134
  EXECUTE PROCEDURE util.map_filter_insert();
2135
$$)
2136
$_$;
2137

    
2138

    
2139
--
2140
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2141
--
2142

    
2143
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
2144
    LANGUAGE sql IMMUTABLE
2145
    AS $_$
2146
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
2147
util.qual_name((unnest).type), ''), '')
2148
FROM unnest($1)
2149
$_$;
2150

    
2151

    
2152
--
2153
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
2154
--
2155

    
2156
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
2157
    LANGUAGE sql IMMUTABLE
2158
    AS $_$
2159
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
2160
$_$;
2161

    
2162

    
2163
--
2164
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
2165
--
2166

    
2167
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
2168
auto-appends util to the search_path to enable use of util operators
2169
';
2170

    
2171

    
2172
--
2173
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2174
--
2175

    
2176
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
2177
    LANGUAGE sql IMMUTABLE
2178
    AS $_$
2179
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
2180
$_$;
2181

    
2182

    
2183
--
2184
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
2185
--
2186

    
2187
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
2188
    LANGUAGE sql IMMUTABLE
2189
    AS $_$
2190
/* debug_print_return_value() needed because this function is used with EXECUTE
2191
rather than util.eval() (in order to affect the calling function), so the
2192
search_path would not otherwise be printed */
2193
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
2194
||$$ search_path TO $$||$1
2195
$_$;
2196

    
2197

    
2198
--
2199
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2200
--
2201

    
2202
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
2203
    LANGUAGE sql
2204
    AS $_$
2205
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
2206
$_$;
2207

    
2208

    
2209
--
2210
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2211
--
2212

    
2213
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2214
idempotent
2215
';
2216

    
2217

    
2218
--
2219
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2220
--
2221

    
2222
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
2223
    LANGUAGE plpgsql STRICT
2224
    AS $_$
2225
DECLARE
2226
	view_qual_name text = util.qual_name(view_);
2227
BEGIN
2228
	EXECUTE $$
2229
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2230
  RETURNS SETOF $$||view_||$$ AS
2231
$BODY1$
2232
SELECT * FROM $$||view_qual_name||$$
2233
ORDER BY sort_col
2234
LIMIT $1 OFFSET $2
2235
$BODY1$
2236
  LANGUAGE sql STABLE
2237
  COST 100
2238
  ROWS 1000
2239
$$;
2240
	
2241
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2242
END;
2243
$_$;
2244

    
2245

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

    
2250
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2251
    LANGUAGE plpgsql STRICT
2252
    AS $_$
2253
DECLARE
2254
	view_qual_name text = util.qual_name(view_);
2255
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2256
BEGIN
2257
	EXECUTE $$
2258
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2259
  RETURNS integer AS
2260
$BODY1$
2261
SELECT $$||quote_ident(row_num_col)||$$
2262
FROM $$||view_qual_name||$$
2263
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2264
LIMIT 1
2265
$BODY1$
2266
  LANGUAGE sql STABLE
2267
  COST 100;
2268
$$;
2269
	
2270
	EXECUTE $$
2271
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2272
  RETURNS SETOF $$||view_||$$ AS
2273
$BODY1$
2274
SELECT * FROM $$||view_qual_name||$$
2275
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2276
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2277
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2278
ORDER BY $$||quote_ident(row_num_col)||$$
2279
$BODY1$
2280
  LANGUAGE sql STABLE
2281
  COST 100
2282
  ROWS 1000
2283
$$;
2284
	
2285
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2286
END;
2287
$_$;
2288

    
2289

    
2290
--
2291
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2292
--
2293

    
2294
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2295
    LANGUAGE plpgsql STRICT
2296
    AS $_$
2297
DECLARE
2298
	view_qual_name text = util.qual_name(view_);
2299
BEGIN
2300
	EXECUTE $$
2301
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2302
  RETURNS SETOF $$||view_||$$
2303
  SET enable_sort TO 'off'
2304
  AS
2305
$BODY1$
2306
SELECT * FROM $$||view_qual_name||$$($2, $3)
2307
$BODY1$
2308
  LANGUAGE sql STABLE
2309
  COST 100
2310
  ROWS 1000
2311
;
2312
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2313
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2314
If you want to run EXPLAIN and get expanded output, use the regular subset
2315
function instead. (When a config param is set on a function, EXPLAIN produces
2316
just a function scan.)
2317
';
2318
$$;
2319
END;
2320
$_$;
2321

    
2322

    
2323
--
2324
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
2325
--
2326

    
2327
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2328
creates subset function which turns off enable_sort
2329
';
2330

    
2331

    
2332
--
2333
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2334
--
2335

    
2336
CREATE FUNCTION name(table_ regclass) RETURNS text
2337
    LANGUAGE sql STABLE
2338
    AS $_$
2339
SELECT relname::text FROM pg_class WHERE oid = $1
2340
$_$;
2341

    
2342

    
2343
--
2344
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2345
--
2346

    
2347
CREATE FUNCTION name(type regtype) RETURNS text
2348
    LANGUAGE sql STABLE
2349
    AS $_$
2350
SELECT typname::text FROM pg_type WHERE oid = $1
2351
$_$;
2352

    
2353

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

    
2358
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2359
    LANGUAGE sql IMMUTABLE
2360
    AS $_$
2361
SELECT octet_length($1) >= util.namedatalen() - $2
2362
$_$;
2363

    
2364

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

    
2369
CREATE FUNCTION namedatalen() RETURNS integer
2370
    LANGUAGE sql IMMUTABLE
2371
    AS $$
2372
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2373
$$;
2374

    
2375

    
2376
--
2377
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2378
--
2379

    
2380
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2381
    LANGUAGE sql IMMUTABLE
2382
    AS $_$
2383
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2384
$_$;
2385

    
2386

    
2387
--
2388
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2389
--
2390

    
2391
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2392
    LANGUAGE sql IMMUTABLE
2393
    AS $_$
2394
SELECT $1 IS NOT NULL
2395
$_$;
2396

    
2397

    
2398
--
2399
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2400
--
2401

    
2402
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2403
    LANGUAGE sql IMMUTABLE
2404
    AS $_$
2405
SELECT util.hstore($1, NULL) || '*=>*'
2406
$_$;
2407

    
2408

    
2409
--
2410
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2411
--
2412

    
2413
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2414
for use with _map()
2415
';
2416

    
2417

    
2418
--
2419
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2420
--
2421

    
2422
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2423
    LANGUAGE sql IMMUTABLE
2424
    AS $_$
2425
SELECT $2 + COALESCE($1, 0)
2426
$_$;
2427

    
2428

    
2429
--
2430
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2431
--
2432

    
2433
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
2434
    LANGUAGE sql
2435
    AS $_$
2436
SELECT util.set_comment($1, concat($2, util.comment($1)))
2437
$_$;
2438

    
2439

    
2440
--
2441
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
2442
--
2443

    
2444
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
2445
comment: must start and end with a newline
2446
';
2447

    
2448

    
2449
--
2450
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2451
--
2452

    
2453
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2454
    LANGUAGE sql IMMUTABLE
2455
    AS $_$
2456
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2457
$_$;
2458

    
2459

    
2460
--
2461
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2462
--
2463

    
2464
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2465
    LANGUAGE sql STABLE
2466
    SET search_path TO pg_temp
2467
    AS $_$
2468
SELECT $1::text
2469
$_$;
2470

    
2471

    
2472
--
2473
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2474
--
2475

    
2476
CREATE FUNCTION qual_name(type regtype) RETURNS text
2477
    LANGUAGE sql STABLE
2478
    SET search_path TO pg_temp
2479
    AS $_$
2480
SELECT $1::text
2481
$_$;
2482

    
2483

    
2484
--
2485
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2486
--
2487

    
2488
COMMENT ON FUNCTION qual_name(type regtype) IS '
2489
a type''s schema-qualified name
2490
';
2491

    
2492

    
2493
--
2494
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2495
--
2496

    
2497
CREATE FUNCTION qual_name(type unknown) RETURNS text
2498
    LANGUAGE sql STABLE
2499
    AS $_$
2500
SELECT util.qual_name($1::text::regtype)
2501
$_$;
2502

    
2503

    
2504
--
2505
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
2506
--
2507

    
2508
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
2509
    LANGUAGE sql IMMUTABLE
2510
    AS $_$
2511
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
2512
$_$;
2513

    
2514

    
2515
--
2516
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
2517
--
2518

    
2519
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
2520
    LANGUAGE sql IMMUTABLE
2521
    AS $_$
2522
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
2523
$_$;
2524

    
2525

    
2526
--
2527
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2528
--
2529

    
2530
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
2531
    LANGUAGE sql IMMUTABLE
2532
    AS $_$
2533
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
2534
$_$;
2535

    
2536

    
2537
--
2538
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
2539
--
2540

    
2541
CREATE FUNCTION raise(type text, msg text) RETURNS void
2542
    LANGUAGE sql IMMUTABLE
2543
    AS $_X$
2544
SELECT util.eval($$
2545
CREATE OR REPLACE FUNCTION pg_temp.__raise()
2546
  RETURNS void AS
2547
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
2548
$__BODY1$
2549
BEGIN
2550
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
2551
END;
2552
$__BODY1$
2553
  LANGUAGE plpgsql IMMUTABLE
2554
  COST 100;
2555
$$, verbose_ := false);
2556

    
2557
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
2558
$_X$;
2559

    
2560

    
2561
--
2562
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
2563
--
2564

    
2565
COMMENT ON FUNCTION raise(type text, msg text) IS '
2566
type: a log level from
2567
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
2568
or a condition name from
2569
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
2570
';
2571

    
2572

    
2573
--
2574
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
2575
--
2576

    
2577
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
2578
    LANGUAGE sql IMMUTABLE
2579
    AS $_$
2580
SELECT util.raise('WARNING', 'ERROR:  '||$1)
2581
$_$;
2582

    
2583

    
2584
--
2585
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
2586
--
2587

    
2588
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
2589
    LANGUAGE plpgsql IMMUTABLE STRICT
2590
    AS $$
2591
BEGIN
2592
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
2593
END;
2594
$$;
2595

    
2596

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

    
2601
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
2602
    LANGUAGE sql IMMUTABLE
2603
    AS $_$
2604
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
2605
$_$;
2606

    
2607

    
2608
--
2609
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
2610
--
2611

    
2612
CREATE FUNCTION regexp_quote(str text) RETURNS text
2613
    LANGUAGE sql IMMUTABLE
2614
    AS $_$
2615
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
2616
$_$;
2617

    
2618

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

    
2623
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
2624
    LANGUAGE sql IMMUTABLE
2625
    AS $_$
2626
SELECT (CASE WHEN right($1, 1) = ')'
2627
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
2628
$_$;
2629

    
2630

    
2631
--
2632
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
2633
--
2634

    
2635
CREATE FUNCTION relation_type(relation regclass) RETURNS text
2636
    LANGUAGE sql STABLE
2637
    AS $_$
2638
SELECT util.relation_type(util.relation_type_char($1))
2639
$_$;
2640

    
2641

    
2642
--
2643
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
2644
--
2645

    
2646
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
2647
    LANGUAGE sql IMMUTABLE
2648
    AS $_$
2649
SELECT 'r=>TABLE, v=>VIEW'::hstore -> $1
2650
$_$;
2651

    
2652

    
2653
--
2654
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
2655
--
2656

    
2657
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
2658
    LANGUAGE sql STABLE
2659
    AS $_$
2660
SELECT relkind FROM pg_class WHERE oid = $1
2661
$_$;
2662

    
2663

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

    
2668
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
2669
    LANGUAGE sql
2670
    AS $_$
2671
/* can't have in_table/out_table inherit from *each other*, because inheritance
2672
also causes the rows of the parent table to be included in the child table.
2673
instead, they need to inherit from a common, empty table. */
2674
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
2675
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
2676
SELECT util.inherit($2, $4);
2677
SELECT util.inherit($3, $4);
2678

    
2679
SELECT util.rematerialize_query($1, $$
2680
SELECT * FROM util.diff(
2681
  $$||util.quote_typed($2)||$$
2682
, $$||util.quote_typed($3)||$$
2683
, NULL::$$||$4||$$)
2684
$$);
2685

    
2686
/* the table unfortunately cannot be *materialized* in human-readable form,
2687
because this would create column name collisions between the two sides */
2688
SELECT util.prepend_comment($1, '
2689
to view this table in human-readable form (with each side''s tuple column
2690
expanded to its component fields):
2691
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
2692
');
2693
$_$;
2694

    
2695

    
2696
--
2697
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
2698
--
2699

    
2700
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
2701
type_table (*required*): table to create as the shared base type
2702
';
2703

    
2704

    
2705
--
2706
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2707
--
2708

    
2709
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
2710
    LANGUAGE sql
2711
    AS $_$
2712
SELECT util.drop_table($1);
2713
SELECT util.materialize_query($1, $2);
2714
$_$;
2715

    
2716

    
2717
--
2718
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2719
--
2720

    
2721
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
2722
idempotent, but repeats action each time
2723
';
2724

    
2725

    
2726
--
2727
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2728
--
2729

    
2730
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
2731
    LANGUAGE sql
2732
    AS $_$
2733
SELECT util.drop_table($1);
2734
SELECT util.materialize_view($1, $2);
2735
$_$;
2736

    
2737

    
2738
--
2739
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2740
--
2741

    
2742
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
2743
idempotent, but repeats action each time
2744
';
2745

    
2746

    
2747
--
2748
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
2749
--
2750

    
2751
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
2752
    LANGUAGE sql
2753
    AS $_$
2754
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
2755
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
2756
FROM util.col_names($1::text::regtype) f (name);
2757
SELECT NULL::void; -- don't fold away functions called in previous query
2758
$_$;
2759

    
2760

    
2761
--
2762
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
2763
--
2764

    
2765
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
2766
idempotent
2767
';
2768

    
2769

    
2770
--
2771
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2772
--
2773

    
2774
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
2775
    LANGUAGE sql
2776
    AS $_$
2777
/* use util.qual_name() instead of ::text so that the schema qualifier is always
2778
included in the debug SQL */
2779
SELECT util.rename_relation(util.qual_name($1), $2)
2780
$_$;
2781

    
2782

    
2783
--
2784
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
2785
--
2786

    
2787
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
2788
    LANGUAGE sql
2789
    AS $_$
2790
/* 'ALTER TABLE can be used with views too'
2791
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
2792
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
2793
||quote_ident($2))
2794
$_$;
2795

    
2796

    
2797
--
2798
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
2799
--
2800

    
2801
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
2802
idempotent
2803
';
2804

    
2805

    
2806
--
2807
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
2808
--
2809

    
2810
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
2811
    LANGUAGE sql IMMUTABLE
2812
    AS $_$
2813
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
2814
$_$;
2815

    
2816

    
2817
--
2818
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
2819
--
2820

    
2821
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
2822
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 
2823
';
2824

    
2825

    
2826
--
2827
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2828
--
2829

    
2830
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
2831
    LANGUAGE sql
2832
    AS $_$
2833
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
2834
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
2835
SELECT util.set_col_names($1, $2);
2836
$_$;
2837

    
2838

    
2839
--
2840
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2841
--
2842

    
2843
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
2844
idempotent.
2845
alters the names table, so it will need to be repopulated after running this function.
2846
';
2847

    
2848

    
2849
--
2850
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2851
--
2852

    
2853
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
2854
    LANGUAGE sql
2855
    AS $_$
2856
SELECT util.drop_table($1);
2857
SELECT util.mk_map_table($1);
2858
$_$;
2859

    
2860

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

    
2865
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
2866
    LANGUAGE sql IMMUTABLE
2867
    AS $_$
2868
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
2869
$_$;
2870

    
2871

    
2872
--
2873
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
2874
--
2875

    
2876
CREATE FUNCTION runnable_sql(sql text) RETURNS text
2877
    LANGUAGE sql IMMUTABLE
2878
    AS $_$
2879
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
2880
ELSE util.mk_set_search_path(for_printing := true)||$$;
2881
$$ END)||$1
2882
$_$;
2883

    
2884

    
2885
--
2886
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
2887
--
2888

    
2889
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
2890
    LANGUAGE plpgsql STRICT
2891
    AS $_$
2892
DECLARE
2893
	result text = NULL;
2894
BEGIN
2895
	BEGIN
2896
		result = util.show_create_view(view_);
2897
		PERFORM util.eval($$DROP VIEW $$||view_);
2898
	EXCEPTION
2899
		WHEN undefined_table THEN NULL;
2900
	END;
2901
	RETURN result;
2902
END;
2903
$_$;
2904

    
2905

    
2906
--
2907
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
2908
--
2909

    
2910
CREATE FUNCTION save_drop_views(views text[]) RETURNS text
2911
    LANGUAGE sql
2912
    AS $_$
2913
SELECT string_agg(util.save_drop_view(unnest), '') FROM unnest($1)
2914
$_$;
2915

    
2916

    
2917
--
2918
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
2919
--
2920

    
2921
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
2922
    LANGUAGE sql STABLE
2923
    AS $_$
2924
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
2925
$_$;
2926

    
2927

    
2928
--
2929
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
2930
--
2931

    
2932
CREATE FUNCTION schema(table_ regclass) RETURNS text
2933
    LANGUAGE sql STABLE
2934
    AS $_$
2935
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
2936
$_$;
2937

    
2938

    
2939
--
2940
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
2941
--
2942

    
2943
CREATE FUNCTION schema(type regtype) RETURNS text
2944
    LANGUAGE sql STABLE
2945
    AS $_$
2946
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
2947
$_$;
2948

    
2949

    
2950
--
2951
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
2952
--
2953

    
2954
CREATE FUNCTION schema(type_null anyelement) RETURNS text
2955
    LANGUAGE sql STABLE
2956
    AS $_$
2957
SELECT util.schema(pg_typeof($1))
2958
$_$;
2959

    
2960

    
2961
--
2962
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
2963
--
2964

    
2965
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
2966
    LANGUAGE sql STABLE
2967
    AS $_$
2968
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
2969
$_$;
2970

    
2971

    
2972
--
2973
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
2974
--
2975

    
2976
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
2977
a schema bundle is a group of schemas with a common prefix
2978
';
2979

    
2980

    
2981
--
2982
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
2983
--
2984

    
2985
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
2986
    LANGUAGE sql
2987
    AS $_$
2988
SELECT util.schema_rename(old_schema,
2989
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
2990
FROM util.schema_bundle_get_schemas($1) f (old_schema);
2991
SELECT NULL::void; -- don't fold away functions called in previous query
2992
$_$;
2993

    
2994

    
2995
--
2996
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
2997
--
2998

    
2999
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
3000
    LANGUAGE plpgsql
3001
    AS $$
3002
BEGIN
3003
	-- don't schema_bundle_rm() the schema_bundle to keep!
3004
	IF replace = with_ THEN RETURN; END IF;
3005
	
3006
	PERFORM util.schema_bundle_rm(replace);
3007
	PERFORM util.schema_bundle_rename(with_, replace);
3008
END;
3009
$$;
3010

    
3011

    
3012
--
3013
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3014
--
3015

    
3016
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
3017
    LANGUAGE sql
3018
    AS $_$
3019
SELECT util.schema_rm(schema)
3020
FROM util.schema_bundle_get_schemas($1) f (schema);
3021
SELECT NULL::void; -- don't fold away functions called in previous query
3022
$_$;
3023

    
3024

    
3025
--
3026
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3027
--
3028

    
3029
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
3030
    LANGUAGE sql STABLE
3031
    AS $_$
3032
SELECT quote_ident(util.schema($1))
3033
$_$;
3034

    
3035

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

    
3040
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
3041
    LANGUAGE sql IMMUTABLE
3042
    AS $_$
3043
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
3044
$_$;
3045

    
3046

    
3047
--
3048
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3049
--
3050

    
3051
CREATE FUNCTION schema_oid(schema text) RETURNS oid
3052
    LANGUAGE sql STABLE
3053
    AS $_$
3054
SELECT oid FROM pg_namespace WHERE nspname = $1
3055
$_$;
3056

    
3057

    
3058
--
3059
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3060
--
3061

    
3062
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
3063
    LANGUAGE sql IMMUTABLE
3064
    AS $_$
3065
SELECT util.schema_regexp(schema_anchor := $1)
3066
$_$;
3067

    
3068

    
3069
--
3070
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3071
--
3072

    
3073
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
3074
    LANGUAGE sql IMMUTABLE
3075
    AS $_$
3076
SELECT util.str_equality_regexp(util.schema($1))
3077
$_$;
3078

    
3079

    
3080
--
3081
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3082
--
3083

    
3084
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
3085
    LANGUAGE sql
3086
    AS $_$
3087
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
3088
$_$;
3089

    
3090

    
3091
--
3092
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3093
--
3094

    
3095
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
3096
    LANGUAGE plpgsql
3097
    AS $$
3098
BEGIN
3099
	-- don't schema_rm() the schema to keep!
3100
	IF replace = with_ THEN RETURN; END IF;
3101
	
3102
	PERFORM util.schema_rm(replace);
3103
	PERFORM util.schema_rename(with_, replace);
3104
END;
3105
$$;
3106

    
3107

    
3108
--
3109
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3110
--
3111

    
3112
CREATE FUNCTION schema_rm(schema text) RETURNS void
3113
    LANGUAGE sql
3114
    AS $_$
3115
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
3116
$_$;
3117

    
3118

    
3119
--
3120
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3121
--
3122

    
3123
CREATE FUNCTION search_path_append(schemas text) RETURNS void
3124
    LANGUAGE sql
3125
    AS $_$
3126
SELECT util.eval(
3127
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
3128
$_$;
3129

    
3130

    
3131
--
3132
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3133
--
3134

    
3135
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
3136
    LANGUAGE plpgsql STRICT
3137
    AS $_$
3138
DECLARE
3139
    old text[] = ARRAY(SELECT util.col_names(table_));
3140
    new text[] = ARRAY(SELECT util.map_values(names));
3141
BEGIN
3142
    old = old[1:array_length(new, 1)]; -- truncate to same length
3143
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
3144
||$$ TO $$||quote_ident(value))
3145
    FROM each(hstore(old, new))
3146
    WHERE value != key -- not same name
3147
    ;
3148
END;
3149
$_$;
3150

    
3151

    
3152
--
3153
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3154
--
3155

    
3156
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
3157
idempotent
3158
';
3159

    
3160

    
3161
--
3162
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3163
--
3164

    
3165
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
3166
    LANGUAGE plpgsql STRICT
3167
    AS $_$
3168
DECLARE
3169
	row_ util.map;
3170
BEGIN
3171
	-- rename any metadata cols rather than re-adding them with new names
3172
	BEGIN
3173
		PERFORM util.set_col_names(table_, names);
3174
	EXCEPTION
3175
		WHEN array_subscript_error THEN -- selective suppress
3176
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
3177
				-- metadata cols not yet added
3178
			ELSE RAISE;
3179
			END IF;
3180
	END;
3181
	
3182
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
3183
	LOOP
3184
		PERFORM util.mk_const_col((table_, row_."to"),
3185
			substring(row_."from" from 2));
3186
	END LOOP;
3187
	
3188
	PERFORM util.set_col_names(table_, names);
3189
END;
3190
$_$;
3191

    
3192

    
3193
--
3194
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3195
--
3196

    
3197
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
3198
idempotent.
3199
the metadata mappings must be *last* in the names table.
3200
';
3201

    
3202

    
3203
--
3204
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
3205
--
3206

    
3207
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
3208
    LANGUAGE plpgsql STRICT
3209
    AS $_$
3210
DECLARE
3211
    sql text = $$ALTER TABLE $$||table_||$$
3212
$$||NULLIF(array_to_string(ARRAY(
3213
    SELECT
3214
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
3215
    ||$$ USING $$||col_name_sql||$$::$$||target_type
3216
    FROM
3217
    (
3218
        SELECT
3219
          quote_ident(col_name) AS col_name_sql
3220
        , util.col_type((table_, col_name)) AS curr_type
3221
        , type AS target_type
3222
        FROM unnest(col_casts)
3223
    ) s
3224
    WHERE curr_type != target_type
3225
), '
3226
, '), '');
3227
BEGIN
3228
    PERFORM util.debug_print_sql(sql);
3229
    EXECUTE COALESCE(sql, '');
3230
END;
3231
$_$;
3232

    
3233

    
3234
--
3235
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
3236
--
3237

    
3238
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
3239
idempotent
3240
';
3241

    
3242

    
3243
--
3244
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3245
--
3246

    
3247
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
3248
    LANGUAGE sql
3249
    AS $_$
3250
SELECT util.eval($$COMMENT ON TABLE $$||$1||$$ IS $$||quote_nullable($2))
3251
$_$;
3252

    
3253

    
3254
--
3255
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
3256
--
3257

    
3258
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
3259
    LANGUAGE sql
3260
    AS $_$
3261
SELECT util.eval(util.mk_set_search_path($1, $2))
3262
$_$;
3263

    
3264

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

    
3269
CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
3270
    LANGUAGE sql STABLE
3271
    AS $_$
3272
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
3273
$$||util.show_grants_for($1)
3274
$_$;
3275

    
3276

    
3277
--
3278
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
3279
--
3280

    
3281
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
3282
    LANGUAGE sql STABLE
3283
    AS $_$
3284
SELECT string_agg(cmd, '')
3285
FROM
3286
(
3287
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
3288
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
3289
$$ ELSE '' END) AS cmd
3290
	FROM util.grants_users() f (user_)
3291
) s
3292
$_$;
3293

    
3294

    
3295
--
3296
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
3297
--
3298

    
3299
CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['r'::text, 'v'::text]) RETURNS SETOF regclass
3300
    LANGUAGE sql STABLE
3301
    AS $_$
3302
SELECT oid FROM pg_class
3303
WHERE relkind = ANY($3) AND relname ~ $1
3304
AND util.schema_matches(util.schema(relnamespace), $2)
3305
ORDER BY relname
3306
$_$;
3307

    
3308

    
3309
--
3310
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3311
--
3312

    
3313
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
3314
    LANGUAGE sql STABLE
3315
    AS $_$
3316
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
3317
$_$;
3318

    
3319

    
3320
--
3321
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
3322
--
3323

    
3324
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
3325
    LANGUAGE sql IMMUTABLE
3326
    AS $_$
3327
SELECT '^'||util.regexp_quote($1)||'$'
3328
$_$;
3329

    
3330

    
3331
--
3332
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3333
--
3334

    
3335
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
3336
    LANGUAGE plpgsql STABLE STRICT
3337
    AS $_$
3338
DECLARE
3339
    hstore hstore;
3340
BEGIN
3341
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
3342
        table_||$$))$$ INTO STRICT hstore;
3343
    RETURN hstore;
3344
END;
3345
$_$;
3346

    
3347

    
3348
--
3349
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3350
--
3351

    
3352
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
3353
    LANGUAGE sql STABLE
3354
    AS $_$
3355
SELECT COUNT(*) > 0 FROM pg_constraint
3356
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
3357
$_$;
3358

    
3359

    
3360
--
3361
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3362
--
3363

    
3364
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
3365
gets whether a status flag is set by the presence of a table constraint
3366
';
3367

    
3368

    
3369
--
3370
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3371
--
3372

    
3373
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
3374
    LANGUAGE sql
3375
    AS $_$
3376
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
3377
||quote_ident($2)||$$ CHECK (true)$$)
3378
$_$;
3379

    
3380

    
3381
--
3382
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3383
--
3384

    
3385
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
3386
stores a status flag by the presence of a table constraint.
3387
idempotent.
3388
';
3389

    
3390

    
3391
--
3392
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3393
--
3394

    
3395
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
3396
    LANGUAGE sql STABLE
3397
    AS $_$
3398
SELECT util.table_flag__get($1, 'nulls_mapped')
3399
$_$;
3400

    
3401

    
3402
--
3403
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3404
--
3405

    
3406
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
3407
gets whether a table''s NULL-equivalent strings have been replaced with NULL
3408
';
3409

    
3410

    
3411
--
3412
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3413
--
3414

    
3415
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
3416
    LANGUAGE sql
3417
    AS $_$
3418
SELECT util.table_flag__set($1, 'nulls_mapped')
3419
$_$;
3420

    
3421

    
3422
--
3423
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3424
--
3425

    
3426
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
3427
sets that a table''s NULL-equivalent strings have been replaced with NULL.
3428
idempotent.
3429
';
3430

    
3431

    
3432
--
3433
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
3434
--
3435

    
3436
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
3437
    LANGUAGE plpgsql STRICT
3438
    AS $_$
3439
DECLARE
3440
    row record;
3441
BEGIN
3442
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
3443
    LOOP
3444
        IF row.global_name != row.name THEN
3445
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
3446
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
3447
        END IF;
3448
    END LOOP;
3449
END;
3450
$_$;
3451

    
3452

    
3453
--
3454
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3455
--
3456

    
3457
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
3458
idempotent
3459
';
3460

    
3461

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

    
3466
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
3467
    LANGUAGE sql
3468
    AS $_$
3469
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
3470
SELECT NULL::void; -- don't fold away functions called in previous query
3471
$_$;
3472

    
3473

    
3474
--
3475
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3476
--
3477

    
3478
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
3479
trims table_ to include only columns in the original data.
3480
idempotent.
3481
';
3482

    
3483

    
3484
--
3485
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
3486
--
3487

    
3488
CREATE FUNCTION truncate(table_ regclass) RETURNS void
3489
    LANGUAGE plpgsql STRICT
3490
    AS $_$
3491
BEGIN
3492
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
3493
END;
3494
$_$;
3495

    
3496

    
3497
--
3498
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3499
--
3500

    
3501
COMMENT ON FUNCTION truncate(table_ regclass) IS '
3502
idempotent
3503
';
3504

    
3505

    
3506
--
3507
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
3508
--
3509

    
3510
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
3511
    LANGUAGE sql IMMUTABLE
3512
    AS $_$
3513
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
3514
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
3515
$_$;
3516

    
3517

    
3518
--
3519
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
3520
--
3521

    
3522
CREATE FUNCTION try_create(sql text) RETURNS void
3523
    LANGUAGE plpgsql STRICT
3524
    AS $$
3525
BEGIN
3526
    PERFORM util.eval(sql);
3527
EXCEPTION
3528
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
3529
    WHEN undefined_column THEN NULL;
3530
    WHEN duplicate_column THEN NULL;
3531
END;
3532
$$;
3533

    
3534

    
3535
--
3536
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
3537
--
3538

    
3539
COMMENT ON FUNCTION try_create(sql text) IS '
3540
idempotent
3541
';
3542

    
3543

    
3544
--
3545
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
3546
--
3547

    
3548
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
3549
    LANGUAGE sql
3550
    AS $_$
3551
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
3552
$_$;
3553

    
3554

    
3555
--
3556
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
3557
--
3558

    
3559
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
3560
idempotent
3561
';
3562

    
3563

    
3564
--
3565
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
3566
--
3567

    
3568
CREATE FUNCTION type_qual(value anyelement) RETURNS text
3569
    LANGUAGE sql IMMUTABLE
3570
    AS $_$
3571
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
3572
$_$;
3573

    
3574

    
3575
--
3576
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
3577
--
3578

    
3579
COMMENT ON FUNCTION type_qual(value anyelement) IS '
3580
a type''s NOT NULL qualifier
3581
';
3582

    
3583

    
3584
--
3585
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
3586
--
3587

    
3588
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
3589
    LANGUAGE sql IMMUTABLE
3590
    AS $_$
3591
SELECT (attname::text, atttypid)::util.col_cast
3592
FROM pg_attribute
3593
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
3594
ORDER BY attnum
3595
$_$;
3596

    
3597

    
3598
--
3599
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
3600
--
3601

    
3602
CREATE FUNCTION typeof(value anyelement) RETURNS text
3603
    LANGUAGE sql IMMUTABLE
3604
    AS $_$
3605
SELECT util.qual_name(pg_typeof($1))
3606
$_$;
3607

    
3608

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

    
3613
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
3614
    LANGUAGE plpgsql STABLE
3615
    AS $_$
3616
DECLARE
3617
    type regtype;
3618
BEGIN
3619
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
3620
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
3621
    RETURN type;
3622
END;
3623
$_$;
3624

    
3625

    
3626
--
3627
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3628
--
3629

    
3630
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
3631
    LANGUAGE sql
3632
    AS $_$
3633
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
3634
$_$;
3635

    
3636

    
3637
--
3638
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
3639
--
3640

    
3641
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
3642
auto-appends util to the search_path to enable use of util operators
3643
';
3644

    
3645

    
3646
--
3647
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
3648
--
3649

    
3650
CREATE AGGREGATE all_same(anyelement) (
3651
    SFUNC = all_same_transform,
3652
    STYPE = anyarray,
3653
    FINALFUNC = all_same_final
3654
);
3655

    
3656

    
3657
--
3658
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
3659
--
3660

    
3661
COMMENT ON AGGREGATE all_same(anyelement) IS '
3662
includes NULLs in comparison
3663
';
3664

    
3665

    
3666
--
3667
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
3668
--
3669

    
3670
CREATE AGGREGATE join_strs(text, text) (
3671
    SFUNC = join_strs_transform,
3672
    STYPE = text
3673
);
3674

    
3675

    
3676
--
3677
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
3678
--
3679

    
3680
CREATE OPERATOR %== (
3681
    PROCEDURE = "%==",
3682
    LEFTARG = anyelement,
3683
    RIGHTARG = anyelement
3684
);
3685

    
3686

    
3687
--
3688
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
3689
--
3690

    
3691
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
3692
returns whether the map-keys of the compared values are the same
3693
(mnemonic: % is the Perl symbol for a hash map)
3694

    
3695
should be overridden for types that store both keys and values
3696

    
3697
used in a FULL JOIN to select which columns to join on
3698
';
3699

    
3700

    
3701
--
3702
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
3703
--
3704

    
3705
CREATE OPERATOR -> (
3706
    PROCEDURE = map_get,
3707
    LEFTARG = regclass,
3708
    RIGHTARG = text
3709
);
3710

    
3711

    
3712
--
3713
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
3714
--
3715

    
3716
CREATE OPERATOR => (
3717
    PROCEDURE = hstore,
3718
    LEFTARG = text[],
3719
    RIGHTARG = text
3720
);
3721

    
3722

    
3723
--
3724
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
3725
--
3726

    
3727
COMMENT ON OPERATOR => (text[], text) IS '
3728
usage: array[''key1'', ...]::text[] => ''value''
3729
';
3730

    
3731

    
3732
--
3733
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
3734
--
3735

    
3736
CREATE OPERATOR ?*>= (
3737
    PROCEDURE = is_populated_more_often_than,
3738
    LEFTARG = anyelement,
3739
    RIGHTARG = anyelement
3740
);
3741

    
3742

    
3743
--
3744
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
3745
--
3746

    
3747
CREATE OPERATOR ?>= (
3748
    PROCEDURE = is_more_complete_than,
3749
    LEFTARG = anyelement,
3750
    RIGHTARG = anyelement
3751
);
3752

    
3753

    
3754
--
3755
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
3756
--
3757

    
3758
CREATE OPERATOR ||% (
3759
    PROCEDURE = concat_esc,
3760
    LEFTARG = text,
3761
    RIGHTARG = text
3762
);
3763

    
3764

    
3765
--
3766
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
3767
--
3768

    
3769
COMMENT ON OPERATOR ||% (text, text) IS '
3770
% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
3771
';
3772

    
3773

    
3774
--
3775
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
3776
--
3777

    
3778
CREATE TABLE map (
3779
    "from" text NOT NULL,
3780
    "to" text,
3781
    filter text,
3782
    notes text
3783
);
3784

    
3785

    
3786
--
3787
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
3788
--
3789

    
3790

    
3791

    
3792
--
3793
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
3794
--
3795

    
3796

    
3797

    
3798
--
3799
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3800
--
3801

    
3802
ALTER TABLE ONLY map
3803
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
3804

    
3805

    
3806
--
3807
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3808
--
3809

    
3810
ALTER TABLE ONLY map
3811
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
3812

    
3813

    
3814
--
3815
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
3816
--
3817

    
3818
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
3819

    
3820

    
3821
--
3822
-- PostgreSQL database dump complete
3823
--
3824

    
(19-19/29)