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: IMMUTABLE 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.
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: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
76
--
77

    
78
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
79
    LANGUAGE sql IMMUTABLE
80
    AS $_$
81
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
82
$_$;
83

    
84

    
85
--
86
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
87
--
88

    
89
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
90
    LANGUAGE sql IMMUTABLE
91
    AS $_$
92
SELECT bool_and(value)
93
FROM
94
(VALUES
95
      ($1)
96
    , ($2)
97
    , ($3)
98
    , ($4)
99
    , ($5)
100
)
101
AS v (value)
102
$_$;
103

    
104

    
105
--
106
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
107
--
108

    
109
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
110
_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.
111
';
112

    
113

    
114
--
115
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
116
--
117

    
118
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
119
    LANGUAGE sql IMMUTABLE
120
    AS $_$
121
SELECT avg(value)
122
FROM
123
(VALUES
124
      ($1)
125
    , ($2)
126
    , ($3)
127
    , ($4)
128
    , ($5)
129
)
130
AS v (value)
131
$_$;
132

    
133

    
134
--
135
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
136
--
137

    
138
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
139
    LANGUAGE sql IMMUTABLE STRICT
140
    AS $_$
141
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)
142
FROM 
143
(
144
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
145
    UNION ALL
146
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
147
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
148
)
149
matches (g)
150
$_$;
151

    
152

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

    
157
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
158
    LANGUAGE sql IMMUTABLE
159
    AS $_$
160
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
161
FROM
162
(VALUES
163
      ($1)
164
    , ($2/60)
165
    , ($3/60/60)
166
)
167
AS v (value)
168
$_$;
169

    
170

    
171
--
172
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
173
--
174

    
175
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
176
    LANGUAGE sql IMMUTABLE
177
    AS $_$
178
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
179
$_$;
180

    
181

    
182
--
183
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
184
--
185

    
186
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
187
    LANGUAGE sql IMMUTABLE
188
    AS $_$
189
SELECT $1 = $2
190
$_$;
191

    
192

    
193
--
194
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
195
--
196

    
197
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
198
    LANGUAGE sql IMMUTABLE
199
    AS $_$
200
-- Fix dates after threshold date
201
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
202
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
203
$_$;
204

    
205

    
206
--
207
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
208
--
209

    
210
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
211
    LANGUAGE sql IMMUTABLE
212
    AS $_$
213
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
214
$_$;
215

    
216

    
217
--
218
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
219
--
220

    
221
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
222
    LANGUAGE sql IMMUTABLE
223
    AS $_$
224
SELECT util._if($1 != '', $2, $3)
225
$_$;
226

    
227

    
228
--
229
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
230
--
231

    
232
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
233
    LANGUAGE sql IMMUTABLE
234
    AS $_$
235
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
236
$_$;
237

    
238

    
239
--
240
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
241
--
242

    
243
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
244
    LANGUAGE sql IMMUTABLE
245
    AS $_$
246
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
247
$_$;
248

    
249

    
250
--
251
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
252
--
253

    
254
CREATE FUNCTION _label(label text, value text) RETURNS text
255
    LANGUAGE sql IMMUTABLE
256
    AS $_$
257
SELECT coalesce($1 || ': ', '') || $2
258
$_$;
259

    
260

    
261
--
262
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
263
--
264

    
265
CREATE FUNCTION _lowercase(value text) RETURNS text
266
    LANGUAGE sql IMMUTABLE
267
    AS $_$
268
SELECT lower($1)
269
$_$;
270

    
271

    
272
--
273
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
274
--
275

    
276
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
277
    LANGUAGE plpgsql IMMUTABLE STRICT
278
    AS $$
279
DECLARE
280
    result value%TYPE := util._map(map, value::text)::unknown;
281
BEGIN
282
    RETURN result;
283
END;
284
$$;
285

    
286

    
287
--
288
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
289
--
290

    
291
CREATE FUNCTION _map(map hstore, value text) RETURNS text
292
    LANGUAGE plpgsql IMMUTABLE STRICT
293
    AS $$
294
DECLARE
295
    match text := map -> value;
296
BEGIN
297
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
298
        match := map -> '*'; -- use default entry
299
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
300
        END IF;
301
    END IF;
302
    
303
    -- Interpret result
304
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
305
    ELSIF match = '*' THEN RETURN value;
306
    ELSE RETURN match;
307
    END IF;
308
END;
309
$$;
310

    
311

    
312
--
313
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
314
--
315

    
316
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
317
    LANGUAGE sql IMMUTABLE
318
    AS $_$
319
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
320
$_$;
321

    
322

    
323
--
324
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
325
--
326

    
327
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
328
    LANGUAGE sql IMMUTABLE
329
    AS $_$
330
SELECT util.join_strs(value, '; ')
331
FROM
332
(
333
    SELECT *
334
    FROM
335
    (
336
        SELECT
337
        DISTINCT ON (value)
338
        *
339
        FROM
340
        (VALUES
341
              (1, $1)
342
            , (2, $2)
343
            , (3, $3)
344
            , (4, $4)
345
            , (5, $5)
346
            , (6, $6)
347
            , (7, $7)
348
            , (8, $8)
349
            , (9, $9)
350
            , (10, $10)
351
        )
352
        AS v (sort_order, value)
353
        WHERE value IS NOT NULL
354
    )
355
    AS v
356
    ORDER BY sort_order
357
)
358
AS v
359
$_$;
360

    
361

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

    
366
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
367
    LANGUAGE sql IMMUTABLE
368
    AS $_$
369
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
370
$_$;
371

    
372

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

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

    
411

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

    
416
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
417
    LANGUAGE sql IMMUTABLE
418
    AS $_$
419
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
420
$_$;
421

    
422

    
423
--
424
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
425
--
426

    
427
CREATE FUNCTION _not(value boolean) RETURNS boolean
428
    LANGUAGE sql IMMUTABLE
429
    AS $_$
430
SELECT NOT $1
431
$_$;
432

    
433

    
434
--
435
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
436
--
437

    
438
CREATE FUNCTION _now() RETURNS timestamp with time zone
439
    LANGUAGE sql STABLE
440
    AS $$
441
SELECT now()
442
$$;
443

    
444

    
445
--
446
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
447
--
448

    
449
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
450
    LANGUAGE sql IMMUTABLE
451
    AS $_$
452
SELECT util."_nullIf"($1, $2, $3::util.datatype)
453
$_$;
454

    
455

    
456
--
457
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
458
--
459

    
460
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
461
    LANGUAGE plpgsql IMMUTABLE
462
    AS $$
463
DECLARE
464
    type util.datatype NOT NULL := type; -- add NOT NULL
465
BEGIN
466
    IF type = 'str' THEN RETURN nullif(value::text, "null");
467
    -- Invalid value is ignored, but invalid null value generates error
468
    ELSIF type = 'float' THEN
469
        DECLARE
470
            -- Outside the try block so that invalid null value generates error
471
            "null" double precision := "null"::double precision;
472
        BEGIN
473
            RETURN nullif(value::double precision, "null");
474
        EXCEPTION
475
            WHEN data_exception THEN RETURN value; -- ignore invalid value
476
        END;
477
    END IF;
478
END;
479
$$;
480

    
481

    
482
--
483
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
484
--
485

    
486
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
487
    LANGUAGE sql IMMUTABLE
488
    AS $_$
489
SELECT bool_or(value)
490
FROM
491
(VALUES
492
      ($1)
493
    , ($2)
494
    , ($3)
495
    , ($4)
496
    , ($5)
497
)
498
AS v (value)
499
$_$;
500

    
501

    
502
--
503
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
504
--
505

    
506
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
507
_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.
508
';
509

    
510

    
511
--
512
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
513
--
514

    
515
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
516
    LANGUAGE sql IMMUTABLE
517
    AS $_$
518
SELECT $2 - $1
519
$_$;
520

    
521

    
522
--
523
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
524
--
525

    
526
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
527
    LANGUAGE sql IMMUTABLE
528
    AS $_$
529
SELECT regexp_split_to_table($1, $2)
530
$_$;
531

    
532

    
533
--
534
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
535
--
536

    
537
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
538
    LANGUAGE sql STABLE STRICT
539
    AS $_$
540
SELECT util.derived_cols($1, $2)
541
UNION
542
SELECT util.eval2set($$
543
SELECT col
544
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
545
JOIN $$||$2||$$ ON "to" = col
546
WHERE "from" LIKE ':%'
547
$$, NULL::text)
548
$_$;
549

    
550

    
551
--
552
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
553
--
554

    
555
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
556
gets table_''s added columns (all the columns not in the original data)
557
';
558

    
559

    
560
--
561
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
562
--
563

    
564
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
565
    LANGUAGE sql IMMUTABLE
566
    AS $_$
567
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
568
$_$;
569

    
570

    
571
--
572
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
573
--
574

    
575
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
576
    LANGUAGE plpgsql IMMUTABLE
577
    AS $$
578
DECLARE
579
	value_cmp         state%TYPE = ARRAY[value];
580
	state             state%TYPE = COALESCE(state, value_cmp);
581
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
582
BEGIN
583
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
584
END;
585
$$;
586

    
587

    
588
--
589
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
590
--
591

    
592
CREATE FUNCTION analyze_(table_ regclass) RETURNS void
593
    LANGUAGE sql
594
    AS $_$
595
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
596
$_$;
597

    
598

    
599
--
600
-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
601
--
602

    
603
CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
604
    LANGUAGE sql STRICT
605
    AS $_$
606
SELECT util.set_comment($1, concat(util.comment($1), $2))
607
$_$;
608

    
609

    
610
--
611
-- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
612
--
613

    
614
COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
615
comment: must start and end with a newline
616
';
617

    
618

    
619
--
620
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
621
--
622

    
623
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
624
    LANGUAGE sql IMMUTABLE
625
    AS $_$
626
SELECT pg_catalog.array_fill($1, ARRAY[$2])
627
$_$;
628

    
629

    
630
--
631
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
632
--
633

    
634
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
635
    LANGUAGE sql IMMUTABLE
636
    AS $_$
637
SELECT util.array_length($1, 1)
638
$_$;
639

    
640

    
641
--
642
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
643
--
644

    
645
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
646
    LANGUAGE sql IMMUTABLE
647
    AS $_$
648
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
649
$_$;
650

    
651

    
652
--
653
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
654
--
655

    
656
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
657
returns 0 instead of NULL for empty arrays
658
';
659

    
660

    
661
--
662
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
663
--
664

    
665
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
666
    LANGUAGE sql STABLE STRICT
667
    AS $_$
668
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
669
$_$;
670

    
671

    
672
--
673
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
674
--
675

    
676
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
677
    LANGUAGE plpgsql STRICT
678
    AS $_$
679
BEGIN
680
    -- not yet clustered (ARRAY[] compares NULLs literally)
681
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
682
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
683
    END IF;
684
END;
685
$_$;
686

    
687

    
688
--
689
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
690
--
691

    
692
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
693
idempotent
694
';
695

    
696

    
697
--
698
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
699
--
700

    
701
CREATE FUNCTION col__min(col col_ref) RETURNS integer
702
    LANGUAGE sql STABLE
703
    AS $_$
704
SELECT util.eval2val($$
705
SELECT $$||quote_ident($1.name)||$$
706
FROM $$||$1.table_||$$
707
ORDER BY $$||quote_ident($1.name)||$$ ASC
708
LIMIT 1
709
$$, NULL::integer)
710
$_$;
711

    
712

    
713
--
714
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
715
--
716

    
717
CREATE FUNCTION col_comment(col col_ref) RETURNS text
718
    LANGUAGE plpgsql STABLE STRICT
719
    AS $$
720
DECLARE
721
	comment text;
722
BEGIN
723
	SELECT description
724
	FROM pg_attribute
725
	LEFT JOIN pg_description ON objoid = attrelid
726
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
727
	WHERE attrelid = col.table_ AND attname = col.name
728
	INTO STRICT comment
729
	;
730
	RETURN comment;
731
EXCEPTION
732
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
733
END;
734
$$;
735

    
736

    
737
--
738
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
739
--
740

    
741
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
742
    LANGUAGE plpgsql STABLE STRICT
743
    AS $$
744
DECLARE
745
	default_sql text;
746
BEGIN
747
	SELECT adsrc
748
	FROM pg_attribute
749
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
750
	WHERE attrelid = col.table_ AND attname = col.name
751
	INTO STRICT default_sql
752
	;
753
	RETURN default_sql;
754
EXCEPTION
755
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
756
END;
757
$$;
758

    
759

    
760
--
761
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
762
--
763

    
764
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
765
    LANGUAGE sql STABLE
766
    AS $_$
767
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
768
$_$;
769

    
770

    
771
--
772
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
773
--
774

    
775
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
776
ret_type_null: NULL::ret_type
777
';
778

    
779

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

    
784
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
785
    LANGUAGE plpgsql STRICT
786
    AS $$
787
BEGIN
788
    PERFORM util.col_type(col);
789
    RETURN true;
790
EXCEPTION
791
    WHEN undefined_column THEN RETURN false;
792
END;
793
$$;
794

    
795

    
796
--
797
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
798
--
799

    
800
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
801
    LANGUAGE plpgsql STABLE STRICT
802
    AS $$
803
DECLARE
804
    prefix text := util.name(type)||'.';
805
BEGIN
806
    RETURN QUERY
807
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
808
        FROM util.col_names(type) f (name_);
809
END;
810
$$;
811

    
812

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

    
817
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
818
    LANGUAGE sql STABLE STRICT
819
    AS $_$
820
SELECT attname::text
821
FROM pg_attribute
822
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
823
ORDER BY attnum
824
$_$;
825

    
826

    
827
--
828
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
829
--
830

    
831
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
832
    LANGUAGE plpgsql STABLE STRICT
833
    AS $_$
834
BEGIN
835
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
836
END;
837
$_$;
838

    
839

    
840
--
841
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
842
--
843

    
844
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
845
    LANGUAGE plpgsql STABLE STRICT
846
    AS $$
847
DECLARE
848
    type regtype;
849
BEGIN
850
    SELECT atttypid FROM pg_attribute
851
    WHERE attrelid = col.table_ AND attname = col.name
852
    INTO STRICT type
853
    ;
854
    RETURN type;
855
EXCEPTION
856
    WHEN no_data_found THEN
857
        RAISE undefined_column USING MESSAGE =
858
            concat('undefined column: ', col.name);
859
END;
860
$$;
861

    
862

    
863
--
864
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
865
--
866

    
867
CREATE FUNCTION comment(element oid) RETURNS text
868
    LANGUAGE sql STABLE STRICT
869
    AS $_$
870
SELECT description FROM pg_description WHERE objoid = $1
871
$_$;
872

    
873

    
874
--
875
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
876
--
877

    
878
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
879
    LANGUAGE sql IMMUTABLE
880
    AS $_$
881
SELECT util.esc_name__append($2, $1)
882
$_$;
883

    
884

    
885
--
886
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
887
--
888

    
889
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
890
    LANGUAGE sql IMMUTABLE
891
    AS $_$
892
SELECT position($1 in $2) > 0 /*1-based offset*/
893
$_$;
894

    
895

    
896
--
897
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
898
--
899

    
900
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
901
    LANGUAGE sql
902
    AS $_$
903
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
904
$_$;
905

    
906

    
907
--
908
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
909
--
910

    
911
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
912
    LANGUAGE plpgsql STRICT
913
    AS $$
914
BEGIN
915
    PERFORM util.eval(sql);
916
EXCEPTION
917
    WHEN duplicate_table  THEN NULL;
918
    WHEN duplicate_object THEN NULL; -- e.g. constraint
919
    WHEN duplicate_column THEN NULL;
920
    WHEN invalid_table_definition THEN
921
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
922
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
923
        END IF;
924
END;
925
$$;
926

    
927

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

    
932
COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
933
idempotent
934
';
935

    
936

    
937
--
938
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
939
--
940

    
941
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
942
    LANGUAGE sql IMMUTABLE
943
    AS $$
944
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
945
$$;
946

    
947

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

    
952
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
953
    LANGUAGE sql IMMUTABLE
954
    AS $_$
955
/* newline before so the query starts at the beginning of the line.
956
newline after to visually separate queries from one another. */
957
SELECT util.raise_notice($$
958
$$||$1||$$
959
$$)
960
$_$;
961

    
962

    
963
--
964
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
965
--
966

    
967
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
968
    LANGUAGE sql STABLE STRICT
969
    AS $_$
970
SELECT util.eval2set($$
971
SELECT col
972
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
973
LEFT JOIN $$||$2||$$ ON "to" = col
974
WHERE "from" IS NULL
975
$$, NULL::text)
976
$_$;
977

    
978

    
979
--
980
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
981
--
982

    
983
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
984
gets table_''s derived columns (all the columns not in the names table)
985
';
986

    
987

    
988
--
989
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
990
--
991

    
992
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
993
    LANGUAGE sql STABLE
994
    AS $_$
995
SELECT * FROM util.diff($1::text, $2::text, $3,
996
	util.has_single_row($1) AND util.has_single_row($2))
997
$_$;
998

    
999

    
1000
--
1001
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1002
--
1003

    
1004
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1005
col_type_null (*required*): NULL::shared_base_type
1006
usage:
1007
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1008
';
1009

    
1010

    
1011
--
1012
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1013
--
1014

    
1015
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
1016
    LANGUAGE sql STABLE
1017
    AS $_$
1018
SELECT * FROM
1019
util.eval2col_pair($$
1020
/* need to explicitly cast each side to the return type because this does not
1021
happen automatically even when an implicit cast is available */
1022
SELECT left_::$$||pg_typeof($3)||$$, right_::$$||pg_typeof($3)||$$
1023
FROM $$||$1||$$ left_
1024
$$||util._if($4, 'CROSS'::text, 'FULL')||$$ JOIN $$||$2||$$ right_
1025
$$||util._if($4, ''::text, $$ON left_ = right_
1026
$$)||
1027
$$WHERE left_ IS DISTINCT FROM right_
1028
ORDER BY left_, right_
1029
$$, $3)
1030
$_$;
1031

    
1032

    
1033
--
1034
-- 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: -
1035
--
1036

    
1037
COMMENT ON FUNCTION diff(left_ text, right_ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1038
col_type_null (*required*): NULL::col_type
1039
single_row: whether the tables consist of a single row, which should be
1040
	displayed side-by-side
1041

    
1042
to run EXPLAIN on the FULL JOIN query:
1043
# run this function
1044
# look for a NOTICE containing the expanded query that it ran
1045
# run EXPLAIN on this expanded query
1046
';
1047

    
1048

    
1049
--
1050
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1051
--
1052

    
1053
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1054
    LANGUAGE sql STRICT
1055
    AS $_$
1056
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1057
$_$;
1058

    
1059

    
1060
--
1061
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1062
--
1063

    
1064
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1065
idempotent
1066
';
1067

    
1068

    
1069
--
1070
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1071
--
1072

    
1073
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1074
    LANGUAGE sql STRICT
1075
    AS $_$
1076
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1077
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1078
$_$;
1079

    
1080

    
1081
--
1082
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1083
--
1084

    
1085
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1086
idempotent
1087
';
1088

    
1089

    
1090
--
1091
-- Name: drop_relation(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
1092
--
1093

    
1094
CREATE FUNCTION drop_relation(relation regclass, force boolean DEFAULT false) RETURNS void
1095
    LANGUAGE sql
1096
    AS $_$
1097
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1098
included in the debug SQL */
1099
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1100
$_$;
1101

    
1102

    
1103
--
1104
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1105
--
1106

    
1107
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1108
    LANGUAGE sql
1109
    AS $_$
1110
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1111
||util._if($3, $$ CASCADE$$, ''::text))
1112
$_$;
1113

    
1114

    
1115
--
1116
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1117
--
1118

    
1119
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1120
idempotent
1121
';
1122

    
1123

    
1124
--
1125
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1126
--
1127

    
1128
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1129
    LANGUAGE sql STRICT
1130
    AS $_$
1131
SELECT util.drop_relation('TABLE', $1, $2)
1132
$_$;
1133

    
1134

    
1135
--
1136
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1137
--
1138

    
1139
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1140
idempotent
1141
';
1142

    
1143

    
1144
--
1145
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1146
--
1147

    
1148
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1149
    LANGUAGE sql STRICT
1150
    AS $_$
1151
SELECT util.drop_relation('VIEW', $1, $2)
1152
$_$;
1153

    
1154

    
1155
--
1156
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1157
--
1158

    
1159
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1160
idempotent
1161
';
1162

    
1163

    
1164
--
1165
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1166
--
1167

    
1168
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1169
    LANGUAGE sql IMMUTABLE
1170
    AS $_$
1171
SELECT util.array_fill($1, 0)
1172
$_$;
1173

    
1174

    
1175
--
1176
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1177
--
1178

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

    
1183

    
1184
--
1185
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1186
--
1187

    
1188
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1189
    LANGUAGE sql IMMUTABLE
1190
    AS $_$
1191
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1192
$_$;
1193

    
1194

    
1195
--
1196
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
1197
--
1198

    
1199
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1200
    LANGUAGE sql IMMUTABLE
1201
    AS $_$
1202
SELECT regexp_replace($2, '("?)$', $1||'\1')
1203
$_$;
1204

    
1205

    
1206
--
1207
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
1208
--
1209

    
1210
CREATE FUNCTION eval(sql text) RETURNS void
1211
    LANGUAGE plpgsql STRICT
1212
    AS $$
1213
BEGIN
1214
	PERFORM util.debug_print_sql(sql);
1215
	EXECUTE sql;
1216
END;
1217
$$;
1218

    
1219

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

    
1224
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1225
    LANGUAGE plpgsql
1226
    AS $$
1227
BEGIN
1228
	PERFORM util.debug_print_sql(sql);
1229
	RETURN QUERY EXECUTE sql;
1230
END;
1231
$$;
1232

    
1233

    
1234
--
1235
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1236
--
1237

    
1238
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1239
col_type_null (*required*): NULL::col_type
1240
';
1241

    
1242

    
1243
--
1244
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1245
--
1246

    
1247
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1248
    LANGUAGE plpgsql
1249
    AS $$
1250
BEGIN
1251
	PERFORM util.debug_print_sql(sql);
1252
	RETURN QUERY EXECUTE sql;
1253
END;
1254
$$;
1255

    
1256

    
1257
--
1258
-- Name: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1259
--
1260

    
1261
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS SETOF anyelement
1262
    LANGUAGE plpgsql
1263
    AS $$
1264
BEGIN
1265
	PERFORM util.debug_print_sql(sql);
1266
	RETURN QUERY EXECUTE sql;
1267
END;
1268
$$;
1269

    
1270

    
1271
--
1272
-- Name: FUNCTION eval2set(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1273
--
1274

    
1275
COMMENT ON FUNCTION eval2set(sql text, ret_type_null anyelement) IS '
1276
ret_type_null: NULL::ret_type
1277
';
1278

    
1279

    
1280
--
1281
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1282
--
1283

    
1284
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1285
    LANGUAGE plpgsql
1286
    AS $$
1287
DECLARE
1288
	ret_val ret_type_null%TYPE;
1289
BEGIN
1290
	PERFORM util.debug_print_sql(sql);
1291
	EXECUTE sql INTO STRICT ret_val;
1292
	RETURN ret_val;
1293
END;
1294
$$;
1295

    
1296

    
1297
--
1298
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1299
--
1300

    
1301
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1302
ret_type_null: NULL::ret_type
1303
';
1304

    
1305

    
1306
--
1307
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1308
--
1309

    
1310
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1311
    LANGUAGE sql
1312
    AS $_$
1313
SELECT util.eval2val($$SELECT $$||$1, $2)
1314
$_$;
1315

    
1316

    
1317
--
1318
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1319
--
1320

    
1321
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1322
ret_type_null: NULL::ret_type
1323
';
1324

    
1325

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

    
1330
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1331
    LANGUAGE sql
1332
    AS $_$
1333
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1334
$_$;
1335

    
1336

    
1337
--
1338
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1339
--
1340

    
1341
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1342
sql: can be NULL, which will be passed through
1343
ret_type_null: NULL::ret_type
1344
';
1345

    
1346

    
1347
--
1348
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1349
--
1350

    
1351
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1352
    LANGUAGE sql STABLE STRICT
1353
    AS $_$
1354
SELECT col_name
1355
FROM unnest($2) s (col_name)
1356
WHERE util.col_exists(($1, col_name))
1357
$_$;
1358

    
1359

    
1360
--
1361
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1362
--
1363

    
1364
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1365
    LANGUAGE sql
1366
    AS $_$
1367
SELECT util.eval2set($$EXPLAIN $$||$1)
1368
$_$;
1369

    
1370

    
1371
--
1372
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1373
--
1374

    
1375
CREATE FUNCTION explain2notice(sql text) RETURNS void
1376
    LANGUAGE plpgsql
1377
    AS $_$
1378
BEGIN
1379
	RAISE NOTICE '%', $$EXPLAIN:
1380
$$||util.explain2str(sql);
1381
END;
1382
$_$;
1383

    
1384

    
1385
--
1386
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1387
--
1388

    
1389
CREATE FUNCTION explain2str(sql text) RETURNS text
1390
    LANGUAGE sql
1391
    AS $_$
1392
SELECT util.join_strs(explain, $$
1393
$$) FROM util.explain($1)
1394
$_$;
1395

    
1396

    
1397
SET default_tablespace = '';
1398

    
1399
SET default_with_oids = false;
1400

    
1401
--
1402
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1403
--
1404

    
1405
CREATE TABLE explain (
1406
    line text NOT NULL
1407
);
1408

    
1409

    
1410
--
1411
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1412
--
1413

    
1414
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1415
    LANGUAGE sql
1416
    AS $_$
1417
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1418
$$||quote_nullable($1)||$$
1419
)$$)
1420
$_$;
1421

    
1422

    
1423
--
1424
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1425
--
1426

    
1427
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1428
usage:
1429
PERFORM util.explain2table($$
1430
query
1431
$$);
1432
';
1433

    
1434

    
1435
--
1436
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1437
--
1438

    
1439
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1440
    LANGUAGE sql IMMUTABLE
1441
    AS $_$
1442
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1443
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1444
) END
1445
$_$;
1446

    
1447

    
1448
--
1449
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1450
--
1451

    
1452
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1453
ensures that an array will always have proper non-NULL dimensions
1454
';
1455

    
1456

    
1457
--
1458
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
1459
--
1460

    
1461
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1462
    LANGUAGE plpgsql
1463
    AS $_$
1464
DECLARE
1465
	PG_EXCEPTION_DETAIL text;
1466
	recreate_users_cmd text = util.save_drop_views(users);
1467
BEGIN
1468
	PERFORM util.eval(cmd);
1469
	PERFORM util.eval(recreate_users_cmd);
1470
EXCEPTION
1471
WHEN dependent_objects_still_exist THEN
1472
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1473
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1474
	users = array(SELECT * FROM util.regexp_matches_group(
1475
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on table .*$'));
1476
	IF util.is_empty(users) THEN RAISE; END IF;
1477
	PERFORM util.force_recreate(cmd, users);
1478
END;
1479
$_$;
1480

    
1481

    
1482
--
1483
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1484
--
1485

    
1486
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1487
idempotent
1488

    
1489
users: not necessary to provide this because it will be autopopulated
1490
';
1491

    
1492

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

    
1497
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1498
    LANGUAGE plpgsql STRICT
1499
    AS $_$
1500
DECLARE
1501
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1502
$$||query;
1503
BEGIN
1504
	EXECUTE mk_view;
1505
EXCEPTION
1506
WHEN invalid_table_definition THEN
1507
	IF SQLERRM = 'cannot drop columns from view'
1508
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1509
	THEN
1510
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1511
		EXECUTE mk_view;
1512
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1513
	END IF;
1514
END;
1515
$_$;
1516

    
1517

    
1518
--
1519
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1520
--
1521

    
1522
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1523
idempotent
1524
';
1525

    
1526

    
1527
--
1528
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1529
--
1530

    
1531
CREATE FUNCTION grants_users() RETURNS SETOF text
1532
    LANGUAGE sql IMMUTABLE
1533
    AS $$
1534
VALUES ('bien_read'), ('public_')
1535
$$;
1536

    
1537

    
1538
--
1539
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1540
--
1541

    
1542
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1543
    LANGUAGE sql IMMUTABLE
1544
    AS $_$
1545
SELECT substring($2 for length($1)) = $1
1546
$_$;
1547

    
1548

    
1549
--
1550
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1551
--
1552

    
1553
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1554
    LANGUAGE sql STABLE
1555
    AS $_$
1556
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1557
$_$;
1558

    
1559

    
1560
--
1561
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1562
--
1563

    
1564
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1565
    LANGUAGE sql IMMUTABLE
1566
    AS $_$
1567
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1568
$_$;
1569

    
1570

    
1571
--
1572
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1573
--
1574

    
1575
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1576
avoids repeating the same value for each key
1577
';
1578

    
1579

    
1580
--
1581
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1582
--
1583

    
1584
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1585
    LANGUAGE sql IMMUTABLE
1586
    AS $_$
1587
SELECT COALESCE($1, $2)
1588
$_$;
1589

    
1590

    
1591
--
1592
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
1593
--
1594

    
1595
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
1596
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
1597
';
1598

    
1599

    
1600
--
1601
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1602
--
1603

    
1604
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
1605
    LANGUAGE sql
1606
    AS $_$
1607
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
1608
$_$;
1609

    
1610

    
1611
--
1612
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1613
--
1614

    
1615
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1616
    LANGUAGE sql STABLE STRICT
1617
    AS $_$
1618
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1619
$_$;
1620

    
1621

    
1622
--
1623
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1624
--
1625

    
1626
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
1627
    LANGUAGE sql IMMUTABLE
1628
    AS $_$
1629
SELECT util.array_length($1) = 0
1630
$_$;
1631

    
1632

    
1633
--
1634
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1635
--
1636

    
1637
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
1638
    LANGUAGE sql IMMUTABLE
1639
    AS $_$
1640
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
1641
$_$;
1642

    
1643

    
1644
--
1645
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1646
--
1647

    
1648
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
1649
    LANGUAGE sql IMMUTABLE
1650
    AS $_$
1651
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
1652
$_$;
1653

    
1654

    
1655
--
1656
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
1657
--
1658

    
1659
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
1660
    LANGUAGE sql STABLE
1661
    AS $_$
1662
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
1663
$_$;
1664

    
1665

    
1666
--
1667
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
1668
--
1669

    
1670
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
1671
    LANGUAGE sql STABLE
1672
    AS $_$
1673
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
1674
$_$;
1675

    
1676

    
1677
--
1678
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1679
--
1680

    
1681
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1682
    LANGUAGE sql IMMUTABLE STRICT
1683
    AS $_$
1684
SELECT $1 || $3 || $2
1685
$_$;
1686

    
1687

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

    
1692
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
1693
    LANGUAGE sql IMMUTABLE
1694
    AS $_$
1695
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
1696
$_$;
1697

    
1698

    
1699
--
1700
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
1701
--
1702

    
1703
CREATE FUNCTION ltrim_nl(str text) RETURNS text
1704
    LANGUAGE sql IMMUTABLE
1705
    AS $_$
1706
SELECT ltrim($1, $$
1707
$$)
1708
$_$;
1709

    
1710

    
1711
--
1712
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1713
--
1714

    
1715
CREATE FUNCTION map_filter_insert() RETURNS trigger
1716
    LANGUAGE plpgsql
1717
    AS $$
1718
BEGIN
1719
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1720
	RETURN new;
1721
END;
1722
$$;
1723

    
1724

    
1725
--
1726
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1727
--
1728

    
1729
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1730
    LANGUAGE plpgsql STABLE STRICT
1731
    AS $_$
1732
DECLARE
1733
    value text;
1734
BEGIN
1735
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1736
        INTO value USING key;
1737
    RETURN value;
1738
END;
1739
$_$;
1740

    
1741

    
1742
--
1743
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1744
--
1745

    
1746
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1747
    LANGUAGE sql IMMUTABLE
1748
    AS $_$
1749
SELECT util._map(util.nulls_map($1), $2)
1750
$_$;
1751

    
1752

    
1753
--
1754
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1755
--
1756

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

    
1760
[1] inlining of function calls, which is different from constant folding
1761
[2] _map()''s profiling query
1762
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1763
and map_nulls()''s profiling query
1764
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1765
both take ~920 ms.
1766
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.
1767
';
1768

    
1769

    
1770
--
1771
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1772
--
1773

    
1774
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1775
    LANGUAGE plpgsql STABLE STRICT
1776
    AS $_$
1777
BEGIN
1778
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1779
END;
1780
$_$;
1781

    
1782

    
1783
--
1784
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
1785
--
1786

    
1787
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
1788
    LANGUAGE sql
1789
    AS $_$
1790
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
1791
$$||util.ltrim_nl($2));
1792
-- make sure the created table has the correct estimated row count
1793
SELECT util.analyze_($1);
1794
$_$;
1795

    
1796

    
1797
--
1798
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
1799
--
1800

    
1801
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
1802
idempotent
1803
';
1804

    
1805

    
1806
--
1807
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1808
--
1809

    
1810
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
1811
    LANGUAGE sql
1812
    AS $_$
1813
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
1814
$_$;
1815

    
1816

    
1817
--
1818
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
1819
--
1820

    
1821
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
1822
idempotent
1823
';
1824

    
1825

    
1826
--
1827
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1828
--
1829

    
1830
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1831
    LANGUAGE sql STRICT
1832
    AS $_$
1833
SELECT util.create_if_not_exists($$
1834
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1835
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1836
||quote_literal($2)||$$;
1837
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
1838
constant
1839
';
1840
$$)
1841
$_$;
1842

    
1843

    
1844
--
1845
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1846
--
1847

    
1848
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
1849
idempotent
1850
';
1851

    
1852

    
1853
--
1854
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1855
--
1856

    
1857
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1858
    LANGUAGE plpgsql STRICT
1859
    AS $_$
1860
DECLARE
1861
    type regtype = util.typeof(expr, col.table_::text::regtype);
1862
    col_name_sql text = quote_ident(col.name);
1863
BEGIN
1864
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1865
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1866
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1867
$$||expr||$$;
1868
$$);
1869
END;
1870
$_$;
1871

    
1872

    
1873
--
1874
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1875
--
1876

    
1877
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
1878
idempotent
1879
';
1880

    
1881

    
1882
--
1883
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1884
--
1885

    
1886
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1887
    LANGUAGE sql STRICT
1888
    AS $_$
1889
SELECT util.create_if_not_exists($$
1890
CREATE TABLE $$||$1||$$
1891
(
1892
    LIKE util.map INCLUDING ALL
1893
);
1894

    
1895
CREATE TRIGGER map_filter_insert
1896
  BEFORE INSERT
1897
  ON $$||$1||$$
1898
  FOR EACH ROW
1899
  EXECUTE PROCEDURE util.map_filter_insert();
1900
$$)
1901
$_$;
1902

    
1903

    
1904
--
1905
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
1906
--
1907

    
1908
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
1909
    LANGUAGE sql IMMUTABLE
1910
    AS $_$
1911
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1)
1912
$_$;
1913

    
1914

    
1915
--
1916
-- Name: mk_set_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
1917
--
1918

    
1919
CREATE FUNCTION mk_set_search_path(VARIADIC schemas text[]) RETURNS text
1920
    LANGUAGE sql IMMUTABLE
1921
    AS $_$
1922
SELECT util.mk_set_search_path(util.mk_search_path(VARIADIC $1))
1923
$_$;
1924

    
1925

    
1926
--
1927
-- Name: FUNCTION mk_set_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
1928
--
1929

    
1930
COMMENT ON FUNCTION mk_set_search_path(VARIADIC schemas text[]) IS '
1931
usage:
1932
for *1* schema arg:
1933
SELECT util.mk_set_search_path(''schema'', NULL) AS search_path;
1934
	-- 2 params are needed to use the correct variant of mk_set_search_path()
1935
';
1936

    
1937

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

    
1942
CREATE FUNCTION mk_set_search_path(search_path text) RETURNS text
1943
    LANGUAGE sql IMMUTABLE
1944
    AS $_$
1945
SELECT $$SET LOCAL search_path TO $$||$1
1946
$_$;
1947

    
1948

    
1949
--
1950
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1951
--
1952

    
1953
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1954
    LANGUAGE sql STRICT
1955
    AS $_$
1956
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
1957
$_$;
1958

    
1959

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

    
1964
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
1965
idempotent
1966
';
1967

    
1968

    
1969
--
1970
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
1971
--
1972

    
1973
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
1974
    LANGUAGE plpgsql STRICT
1975
    AS $_$
1976
DECLARE
1977
	view_qual_name text = util.qual_name(view_);
1978
BEGIN
1979
	EXECUTE $$
1980
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1981
  RETURNS SETOF $$||view_||$$ AS
1982
$BODY1$
1983
SELECT * FROM $$||view_qual_name||$$
1984
ORDER BY sort_col
1985
LIMIT $1 OFFSET $2
1986
$BODY1$
1987
  LANGUAGE sql STABLE
1988
  COST 100
1989
  ROWS 1000
1990
$$;
1991
	
1992
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
1993
END;
1994
$_$;
1995

    
1996

    
1997
--
1998
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1999
--
2000

    
2001
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2002
    LANGUAGE plpgsql STRICT
2003
    AS $_$
2004
DECLARE
2005
	view_qual_name text = util.qual_name(view_);
2006
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2007
BEGIN
2008
	EXECUTE $$
2009
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2010
  RETURNS integer AS
2011
$BODY1$
2012
SELECT $$||quote_ident(row_num_col)||$$
2013
FROM $$||view_qual_name||$$
2014
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2015
LIMIT 1
2016
$BODY1$
2017
  LANGUAGE sql STABLE
2018
  COST 100;
2019
$$;
2020
	
2021
	EXECUTE $$
2022
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2023
  RETURNS SETOF $$||view_||$$ AS
2024
$BODY1$
2025
SELECT * FROM $$||view_qual_name||$$
2026
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2027
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2028
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2029
ORDER BY $$||quote_ident(row_num_col)||$$
2030
$BODY1$
2031
  LANGUAGE sql STABLE
2032
  COST 100
2033
  ROWS 1000
2034
$$;
2035
	
2036
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2037
END;
2038
$_$;
2039

    
2040

    
2041
--
2042
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2043
--
2044

    
2045
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2046
    LANGUAGE plpgsql STRICT
2047
    AS $_$
2048
DECLARE
2049
	view_qual_name text = util.qual_name(view_);
2050
BEGIN
2051
	EXECUTE $$
2052
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2053
  RETURNS SETOF $$||view_||$$
2054
  SET enable_sort TO 'off'
2055
  AS
2056
$BODY1$
2057
SELECT * FROM $$||view_qual_name||$$($2, $3)
2058
$BODY1$
2059
  LANGUAGE sql STABLE
2060
  COST 100
2061
  ROWS 1000
2062
;
2063
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2064
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2065
If you want to run EXPLAIN and get expanded output, use the regular subset
2066
function instead. (When a config param is set on a function, EXPLAIN produces
2067
just a function scan.)
2068
';
2069
$$;
2070
END;
2071
$_$;
2072

    
2073

    
2074
--
2075
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
2076
--
2077

    
2078
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2079
creates subset function which turns off enable_sort
2080
';
2081

    
2082

    
2083
--
2084
-- Name: mk_use_own_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
2085
--
2086

    
2087
CREATE FUNCTION mk_use_own_schema(schema_anchor anyelement) RETURNS text
2088
    LANGUAGE sql IMMUTABLE
2089
    AS $_$
2090
SELECT util.mk_set_search_path(util.schema_esc($1))
2091
$_$;
2092

    
2093

    
2094
--
2095
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2096
--
2097

    
2098
CREATE FUNCTION name(table_ regclass) RETURNS text
2099
    LANGUAGE sql STABLE
2100
    AS $_$
2101
SELECT relname::text FROM pg_class WHERE oid = $1
2102
$_$;
2103

    
2104

    
2105
--
2106
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2107
--
2108

    
2109
CREATE FUNCTION name(type regtype) RETURNS text
2110
    LANGUAGE sql STABLE STRICT
2111
    AS $_$
2112
SELECT typname::text FROM pg_type WHERE oid = $1
2113
$_$;
2114

    
2115

    
2116
--
2117
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
2118
--
2119

    
2120
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2121
    LANGUAGE sql IMMUTABLE
2122
    AS $_$
2123
SELECT octet_length($1) >= util.namedatalen() - $2
2124
$_$;
2125

    
2126

    
2127
--
2128
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2129
--
2130

    
2131
CREATE FUNCTION namedatalen() RETURNS integer
2132
    LANGUAGE sql IMMUTABLE
2133
    AS $$
2134
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2135
$$;
2136

    
2137

    
2138
--
2139
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2140
--
2141

    
2142
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2143
    LANGUAGE sql IMMUTABLE
2144
    AS $_$
2145
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2146
$_$;
2147

    
2148

    
2149
--
2150
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2151
--
2152

    
2153
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2154
    LANGUAGE sql IMMUTABLE
2155
    AS $_$
2156
SELECT $1 IS NOT NULL
2157
$_$;
2158

    
2159

    
2160
--
2161
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2162
--
2163

    
2164
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2165
    LANGUAGE sql IMMUTABLE
2166
    AS $_$
2167
SELECT util.hstore($1, NULL) || '*=>*'
2168
$_$;
2169

    
2170

    
2171
--
2172
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2173
--
2174

    
2175
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2176
for use with _map()
2177
';
2178

    
2179

    
2180
--
2181
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2182
--
2183

    
2184
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2185
    LANGUAGE sql IMMUTABLE
2186
    AS $_$
2187
SELECT $2 + COALESCE($1, 0)
2188
$_$;
2189

    
2190

    
2191
--
2192
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2193
--
2194

    
2195
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2196
    LANGUAGE sql IMMUTABLE
2197
    AS $_$
2198
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2199
$_$;
2200

    
2201

    
2202
--
2203
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2204
--
2205

    
2206
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2207
    LANGUAGE sql STABLE STRICT
2208
    SET search_path TO pg_temp
2209
    AS $_$
2210
SELECT $1::text
2211
$_$;
2212

    
2213

    
2214
--
2215
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2216
--
2217

    
2218
CREATE FUNCTION qual_name(type regtype) RETURNS text
2219
    LANGUAGE sql STABLE STRICT
2220
    SET search_path TO pg_temp
2221
    AS $_$
2222
SELECT $1::text
2223
$_$;
2224

    
2225

    
2226
--
2227
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2228
--
2229

    
2230
COMMENT ON FUNCTION qual_name(type regtype) IS '
2231
a type''s schema-qualified name
2232
';
2233

    
2234

    
2235
--
2236
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2237
--
2238

    
2239
CREATE FUNCTION qual_name(type unknown) RETURNS text
2240
    LANGUAGE sql STABLE STRICT
2241
    AS $_$
2242
SELECT util.qual_name($1::text::regtype)
2243
$_$;
2244

    
2245

    
2246
--
2247
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
2248
--
2249

    
2250
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
2251
    LANGUAGE sql IMMUTABLE
2252
    AS $_$
2253
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
2254
$_$;
2255

    
2256

    
2257
--
2258
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
2259
--
2260

    
2261
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
2262
    LANGUAGE sql IMMUTABLE
2263
    AS $_$
2264
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
2265
$_$;
2266

    
2267

    
2268
--
2269
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2270
--
2271

    
2272
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
2273
    LANGUAGE sql IMMUTABLE
2274
    AS $_$
2275
SELECT quote_nullable($1)||$$::$$||pg_typeof($1)
2276
$_$;
2277

    
2278

    
2279
--
2280
-- Name: raise_error_notice(text); Type: FUNCTION; Schema: util; Owner: -
2281
--
2282

    
2283
CREATE FUNCTION raise_error_notice(msg text) RETURNS void
2284
    LANGUAGE sql IMMUTABLE STRICT
2285
    AS $_$
2286
SELECT util.raise_notice('ERROR:  '||$1)
2287
$_$;
2288

    
2289

    
2290
--
2291
-- Name: raise_notice(text); Type: FUNCTION; Schema: util; Owner: -
2292
--
2293

    
2294
CREATE FUNCTION raise_notice(msg text) RETURNS void
2295
    LANGUAGE plpgsql IMMUTABLE STRICT
2296
    AS $$
2297
BEGIN
2298
	RAISE NOTICE '%', msg;
2299
END;
2300
$$;
2301

    
2302

    
2303
--
2304
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
2305
--
2306

    
2307
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
2308
    LANGUAGE plpgsql IMMUTABLE STRICT
2309
    AS $$
2310
BEGIN
2311
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
2312
END;
2313
$$;
2314

    
2315

    
2316
--
2317
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
2318
--
2319

    
2320
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
2321
    LANGUAGE sql IMMUTABLE
2322
    AS $_$
2323
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
2324
$_$;
2325

    
2326

    
2327
--
2328
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
2329
--
2330

    
2331
CREATE FUNCTION regexp_quote(str text) RETURNS text
2332
    LANGUAGE sql IMMUTABLE
2333
    AS $_$
2334
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
2335
$_$;
2336

    
2337

    
2338
--
2339
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
2340
--
2341

    
2342
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
2343
    LANGUAGE sql IMMUTABLE
2344
    AS $_$
2345
SELECT (CASE WHEN right($1, 1) = ')'
2346
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
2347
$_$;
2348

    
2349

    
2350
--
2351
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
2352
--
2353

    
2354
CREATE FUNCTION relation_type(relation regclass) RETURNS text
2355
    LANGUAGE sql STABLE
2356
    AS $_$
2357
SELECT util.relation_type(util.relation_type_char($1))
2358
$_$;
2359

    
2360

    
2361
--
2362
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
2363
--
2364

    
2365
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
2366
    LANGUAGE sql IMMUTABLE
2367
    AS $_$
2368
SELECT 'r=>TABLE, v=>VIEW'::hstore -> $1
2369
$_$;
2370

    
2371

    
2372
--
2373
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
2374
--
2375

    
2376
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
2377
    LANGUAGE sql STABLE
2378
    AS $_$
2379
SELECT relkind FROM pg_class WHERE oid = $1
2380
$_$;
2381

    
2382

    
2383
--
2384
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
2385
--
2386

    
2387
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
2388
    LANGUAGE sql
2389
    AS $_$
2390
/* can't have in_table/out_table inherit from *each other*, because inheritance
2391
also causes the rows of the parent table to be included in the child table.
2392
instead, they need to inherit from a common, empty table. */
2393
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
2394
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
2395
SELECT util.inherit($2, $4);
2396
SELECT util.inherit($3, $4);
2397

    
2398
SELECT util.rematerialize_query($1, $$
2399
SELECT * FROM util.diff(
2400
  $$||quote_nullable($2)||$$::regclass
2401
, $$||quote_nullable($3)||$$::regclass
2402
, NULL::$$||$4||$$)
2403
$$);
2404

    
2405
/* the table unfortunately cannot be *materialized* in human-readable form,
2406
because this would create column name collisions between the two sides */
2407
SELECT util.append_comment($1, '
2408
to view this table in human-readable form (with each side''s tuple column
2409
expanded to its component fields):
2410
SELECT (left_).*, (right_).* FROM '||$1||';
2411
');
2412
$_$;
2413

    
2414

    
2415
--
2416
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
2417
--
2418

    
2419
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
2420
type_table (*required*): table to create as the shared base type
2421
';
2422

    
2423

    
2424
--
2425
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2426
--
2427

    
2428
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
2429
    LANGUAGE sql
2430
    AS $_$
2431
SELECT util.drop_table($1);
2432
SELECT util.materialize_query($1, $2);
2433
$_$;
2434

    
2435

    
2436
--
2437
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2438
--
2439

    
2440
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
2441
idempotent, but repeats action each time
2442
';
2443

    
2444

    
2445
--
2446
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2447
--
2448

    
2449
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
2450
    LANGUAGE sql
2451
    AS $_$
2452
SELECT util.drop_table($1);
2453
SELECT util.materialize_view($1, $2);
2454
$_$;
2455

    
2456

    
2457
--
2458
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2459
--
2460

    
2461
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
2462
idempotent, but repeats action each time
2463
';
2464

    
2465

    
2466
--
2467
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
2468
--
2469

    
2470
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
2471
    LANGUAGE sql STRICT
2472
    AS $_$
2473
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
2474
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
2475
FROM util.col_names($1::text::regtype) f (name);
2476
SELECT NULL::void; -- don't fold away functions called in previous query
2477
$_$;
2478

    
2479

    
2480
--
2481
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
2482
--
2483

    
2484
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
2485
idempotent
2486
';
2487

    
2488

    
2489
--
2490
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2491
--
2492

    
2493
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
2494
    LANGUAGE sql
2495
    AS $_$
2496
/* use util.qual_name() instead of ::text so that the schema qualifier is always
2497
included in the debug SQL */
2498
SELECT util.rename_relation(util.qual_name($1), $2)
2499
$_$;
2500

    
2501

    
2502
--
2503
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
2504
--
2505

    
2506
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
2507
    LANGUAGE sql
2508
    AS $_$
2509
/* 'ALTER TABLE can be used with views too'
2510
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
2511
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
2512
||quote_ident($2))
2513
$_$;
2514

    
2515

    
2516
--
2517
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
2518
--
2519

    
2520
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
2521
idempotent
2522
';
2523

    
2524

    
2525
--
2526
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
2527
--
2528

    
2529
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
2530
    LANGUAGE sql IMMUTABLE
2531
    AS $_$
2532
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
2533
$_$;
2534

    
2535

    
2536
--
2537
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
2538
--
2539

    
2540
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
2541
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 
2542
';
2543

    
2544

    
2545
--
2546
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2547
--
2548

    
2549
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
2550
    LANGUAGE sql STRICT
2551
    AS $_$
2552
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
2553
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
2554
SELECT util.set_col_names($1, $2);
2555
$_$;
2556

    
2557

    
2558
--
2559
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2560
--
2561

    
2562
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
2563
idempotent.
2564
alters the names table, so it will need to be repopulated after running this function.
2565
';
2566

    
2567

    
2568
--
2569
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2570
--
2571

    
2572
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
2573
    LANGUAGE sql STRICT
2574
    AS $_$
2575
SELECT util.drop_table($1);
2576
SELECT util.mk_map_table($1);
2577
$_$;
2578

    
2579

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

    
2584
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
2585
    LANGUAGE sql IMMUTABLE
2586
    AS $_$
2587
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
2588
$_$;
2589

    
2590

    
2591
--
2592
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
2593
--
2594

    
2595
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
2596
    LANGUAGE plpgsql STRICT
2597
    AS $_$
2598
DECLARE
2599
	result text = NULL;
2600
BEGIN
2601
	BEGIN
2602
		result = util.show_create_view(view_);
2603
		PERFORM util.eval($$DROP VIEW $$||view_);
2604
	EXCEPTION
2605
		WHEN undefined_table THEN NULL;
2606
	END;
2607
	RETURN result;
2608
END;
2609
$_$;
2610

    
2611

    
2612
--
2613
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
2614
--
2615

    
2616
CREATE FUNCTION save_drop_views(views text[]) RETURNS text
2617
    LANGUAGE sql
2618
    AS $_$
2619
SELECT string_agg(util.save_drop_view(unnest), '') FROM unnest($1)
2620
$_$;
2621

    
2622

    
2623
--
2624
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
2625
--
2626

    
2627
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
2628
    LANGUAGE sql STABLE
2629
    AS $_$
2630
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
2631
$_$;
2632

    
2633

    
2634
--
2635
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
2636
--
2637

    
2638
CREATE FUNCTION schema(table_ regclass) RETURNS text
2639
    LANGUAGE sql STABLE
2640
    AS $_$
2641
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
2642
$_$;
2643

    
2644

    
2645
--
2646
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
2647
--
2648

    
2649
CREATE FUNCTION schema(type regtype) RETURNS text
2650
    LANGUAGE sql STABLE
2651
    AS $_$
2652
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
2653
$_$;
2654

    
2655

    
2656
--
2657
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
2658
--
2659

    
2660
CREATE FUNCTION schema(type_null anyelement) RETURNS text
2661
    LANGUAGE sql STABLE
2662
    AS $_$
2663
SELECT util.schema(pg_typeof($1))
2664
$_$;
2665

    
2666

    
2667
--
2668
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
2669
--
2670

    
2671
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
2672
    LANGUAGE sql STABLE
2673
    AS $_$
2674
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
2675
$_$;
2676

    
2677

    
2678
--
2679
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
2680
--
2681

    
2682
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
2683
a schema bundle is a group of schemas with a common prefix
2684
';
2685

    
2686

    
2687
--
2688
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
2689
--
2690

    
2691
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
2692
    LANGUAGE sql
2693
    AS $_$
2694
SELECT util.schema_rename(old_schema,
2695
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
2696
FROM util.schema_bundle_get_schemas($1) f (old_schema);
2697
SELECT NULL::void; -- don't fold away functions called in previous query
2698
$_$;
2699

    
2700

    
2701
--
2702
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
2703
--
2704

    
2705
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
2706
    LANGUAGE plpgsql
2707
    AS $$
2708
BEGIN
2709
	-- don't schema_bundle_rm() the schema_bundle to keep!
2710
	IF replace = with_ THEN RETURN; END IF;
2711
	
2712
	PERFORM util.schema_bundle_rm(replace);
2713
	PERFORM util.schema_bundle_rename(with_, replace);
2714
END;
2715
$$;
2716

    
2717

    
2718
--
2719
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
2720
--
2721

    
2722
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
2723
    LANGUAGE sql
2724
    AS $_$
2725
SELECT util.schema_rm(schema)
2726
FROM util.schema_bundle_get_schemas($1) f (schema);
2727
SELECT NULL::void; -- don't fold away functions called in previous query
2728
$_$;
2729

    
2730

    
2731
--
2732
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
2733
--
2734

    
2735
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
2736
    LANGUAGE sql STABLE
2737
    AS $_$
2738
SELECT quote_ident(util.schema($1))
2739
$_$;
2740

    
2741

    
2742
--
2743
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
2744
--
2745

    
2746
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
2747
    LANGUAGE sql IMMUTABLE
2748
    AS $_$
2749
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
2750
$_$;
2751

    
2752

    
2753
--
2754
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
2755
--
2756

    
2757
CREATE FUNCTION schema_oid(schema text) RETURNS oid
2758
    LANGUAGE sql STABLE
2759
    AS $_$
2760
SELECT oid FROM pg_namespace WHERE nspname = $1
2761
$_$;
2762

    
2763

    
2764
--
2765
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
2766
--
2767

    
2768
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
2769
    LANGUAGE sql
2770
    AS $_$
2771
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
2772
$_$;
2773

    
2774

    
2775
--
2776
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
2777
--
2778

    
2779
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
2780
    LANGUAGE plpgsql
2781
    AS $$
2782
BEGIN
2783
	-- don't schema_rm() the schema to keep!
2784
	IF replace = with_ THEN RETURN; END IF;
2785
	
2786
	PERFORM util.schema_rm(replace);
2787
	PERFORM util.schema_rename(with_, replace);
2788
END;
2789
$$;
2790

    
2791

    
2792
--
2793
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
2794
--
2795

    
2796
CREATE FUNCTION schema_rm(schema text) RETURNS void
2797
    LANGUAGE sql
2798
    AS $_$
2799
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
2800
$_$;
2801

    
2802

    
2803
--
2804
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
2805
--
2806

    
2807
CREATE FUNCTION search_path_append(schemas text) RETURNS void
2808
    LANGUAGE sql STRICT
2809
    AS $_$
2810
SELECT util.eval(
2811
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
2812
$_$;
2813

    
2814

    
2815
--
2816
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2817
--
2818

    
2819
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
2820
    LANGUAGE plpgsql STRICT
2821
    AS $_$
2822
DECLARE
2823
    old text[] = ARRAY(SELECT util.col_names(table_));
2824
    new text[] = ARRAY(SELECT util.map_values(names));
2825
BEGIN
2826
    old = old[1:array_length(new, 1)]; -- truncate to same length
2827
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
2828
||$$ TO $$||quote_ident(value))
2829
    FROM each(hstore(old, new))
2830
    WHERE value != key -- not same name
2831
    ;
2832
END;
2833
$_$;
2834

    
2835

    
2836
--
2837
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2838
--
2839

    
2840
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
2841
idempotent
2842
';
2843

    
2844

    
2845
--
2846
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2847
--
2848

    
2849
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
2850
    LANGUAGE plpgsql STRICT
2851
    AS $_$
2852
DECLARE
2853
	row_ util.map;
2854
BEGIN
2855
	-- rename any metadata cols rather than re-adding them with new names
2856
	BEGIN
2857
		PERFORM util.set_col_names(table_, names);
2858
	EXCEPTION
2859
		WHEN array_subscript_error THEN -- selective suppress
2860
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
2861
				-- metadata cols not yet added
2862
			ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
2863
			END IF;
2864
	END;
2865
	
2866
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
2867
	LOOP
2868
		PERFORM util.mk_const_col((table_, row_."to"),
2869
			substring(row_."from" from 2));
2870
	END LOOP;
2871
	
2872
	PERFORM util.set_col_names(table_, names);
2873
END;
2874
$_$;
2875

    
2876

    
2877
--
2878
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
2879
--
2880

    
2881
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
2882
idempotent.
2883
the metadata mappings must be *last* in the names table.
2884
';
2885

    
2886

    
2887
--
2888
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2889
--
2890

    
2891
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
2892
    LANGUAGE plpgsql STRICT
2893
    AS $_$
2894
DECLARE
2895
    sql text = $$ALTER TABLE $$||table_||$$
2896
$$||NULLIF(array_to_string(ARRAY(
2897
    SELECT
2898
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
2899
    ||$$ USING $$||col_name_sql||$$::$$||target_type
2900
    FROM
2901
    (
2902
        SELECT
2903
          quote_ident(col_name) AS col_name_sql
2904
        , util.col_type((table_, col_name)) AS curr_type
2905
        , type AS target_type
2906
        FROM unnest(col_casts)
2907
    ) s
2908
    WHERE curr_type != target_type
2909
), '
2910
, '), '');
2911
BEGIN
2912
    PERFORM util.debug_print_sql(sql);
2913
    EXECUTE COALESCE(sql, '');
2914
END;
2915
$_$;
2916

    
2917

    
2918
--
2919
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
2920
--
2921

    
2922
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
2923
idempotent
2924
';
2925

    
2926

    
2927
--
2928
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2929
--
2930

    
2931
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
2932
    LANGUAGE sql STRICT
2933
    AS $_$
2934
SELECT util.eval($$COMMENT ON TABLE $$||$1||$$ IS $$||quote_nullable($2))
2935
$_$;
2936

    
2937

    
2938
--
2939
-- Name: show_create_view(regclass); Type: FUNCTION; Schema: util; Owner: -
2940
--
2941

    
2942
CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
2943
    LANGUAGE sql STABLE
2944
    AS $_$
2945
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
2946
$$||util.show_grants_for($1)
2947
$_$;
2948

    
2949

    
2950
--
2951
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
2952
--
2953

    
2954
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
2955
    LANGUAGE sql STABLE
2956
    AS $_$
2957
SELECT string_agg(cmd, '')
2958
FROM
2959
(
2960
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
2961
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
2962
$$ ELSE '' END) AS cmd
2963
	FROM util.grants_users() f (user_)
2964
) s
2965
$_$;
2966

    
2967

    
2968
--
2969
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
2970
--
2971

    
2972
CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['r'::text, 'v'::text]) RETURNS SETOF regclass
2973
    LANGUAGE sql STABLE
2974
    AS $_$
2975
SELECT oid FROM pg_class
2976
WHERE relkind = ANY($3) AND relname ~ $1
2977
AND util.schema_matches(util.schema(relnamespace), $2)
2978
ORDER BY relname
2979
$_$;
2980

    
2981

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

    
2986
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
2987
    LANGUAGE sql STABLE
2988
    AS $_$
2989
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
2990
$_$;
2991

    
2992

    
2993
--
2994
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
2995
--
2996

    
2997
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
2998
    LANGUAGE sql IMMUTABLE
2999
    AS $_$
3000
SELECT '^'||util.regexp_quote($1)||'$'
3001
$_$;
3002

    
3003

    
3004
--
3005
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3006
--
3007

    
3008
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
3009
    LANGUAGE plpgsql STABLE STRICT
3010
    AS $_$
3011
DECLARE
3012
    hstore hstore;
3013
BEGIN
3014
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
3015
        table_||$$))$$ INTO STRICT hstore;
3016
    RETURN hstore;
3017
END;
3018
$_$;
3019

    
3020

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

    
3025
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
3026
    LANGUAGE sql STABLE STRICT
3027
    AS $_$
3028
SELECT COUNT(*) > 0 FROM pg_constraint
3029
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
3030
$_$;
3031

    
3032

    
3033
--
3034
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3035
--
3036

    
3037
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
3038
gets whether a status flag is set by the presence of a table constraint
3039
';
3040

    
3041

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

    
3046
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
3047
    LANGUAGE sql STRICT
3048
    AS $_$
3049
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
3050
||quote_ident($2)||$$ CHECK (true)$$)
3051
$_$;
3052

    
3053

    
3054
--
3055
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3056
--
3057

    
3058
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
3059
stores a status flag by the presence of a table constraint.
3060
idempotent.
3061
';
3062

    
3063

    
3064
--
3065
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3066
--
3067

    
3068
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
3069
    LANGUAGE sql STABLE STRICT
3070
    AS $_$
3071
SELECT util.table_flag__get($1, 'nulls_mapped')
3072
$_$;
3073

    
3074

    
3075
--
3076
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3077
--
3078

    
3079
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
3080
gets whether a table''s NULL-equivalent strings have been replaced with NULL
3081
';
3082

    
3083

    
3084
--
3085
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3086
--
3087

    
3088
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
3089
    LANGUAGE sql STRICT
3090
    AS $_$
3091
SELECT util.table_flag__set($1, 'nulls_mapped')
3092
$_$;
3093

    
3094

    
3095
--
3096
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3097
--
3098

    
3099
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
3100
sets that a table''s NULL-equivalent strings have been replaced with NULL.
3101
idempotent.
3102
';
3103

    
3104

    
3105
--
3106
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
3107
--
3108

    
3109
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
3110
    LANGUAGE plpgsql STRICT
3111
    AS $_$
3112
DECLARE
3113
    row record;
3114
BEGIN
3115
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
3116
    LOOP
3117
        IF row.global_name != row.name THEN
3118
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
3119
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
3120
        END IF;
3121
    END LOOP;
3122
END;
3123
$_$;
3124

    
3125

    
3126
--
3127
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3128
--
3129

    
3130
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
3131
idempotent
3132
';
3133

    
3134

    
3135
--
3136
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3137
--
3138

    
3139
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
3140
    LANGUAGE sql STRICT
3141
    AS $_$
3142
SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
3143
SELECT NULL::void; -- don't fold away functions called in previous query
3144
$_$;
3145

    
3146

    
3147
--
3148
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3149
--
3150

    
3151
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
3152
trims table_ to include only columns in the original data.
3153
idempotent.
3154
';
3155

    
3156

    
3157
--
3158
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
3159
--
3160

    
3161
CREATE FUNCTION truncate(table_ regclass) RETURNS void
3162
    LANGUAGE plpgsql STRICT
3163
    AS $_$
3164
BEGIN
3165
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
3166
END;
3167
$_$;
3168

    
3169

    
3170
--
3171
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3172
--
3173

    
3174
COMMENT ON FUNCTION truncate(table_ regclass) IS '
3175
idempotent
3176
';
3177

    
3178

    
3179
--
3180
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
3181
--
3182

    
3183
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
3184
    LANGUAGE sql IMMUTABLE
3185
    AS $_$
3186
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
3187
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
3188
$_$;
3189

    
3190

    
3191
--
3192
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
3193
--
3194

    
3195
CREATE FUNCTION try_create(sql text) RETURNS void
3196
    LANGUAGE plpgsql STRICT
3197
    AS $$
3198
BEGIN
3199
    PERFORM util.eval(sql);
3200
EXCEPTION
3201
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
3202
    WHEN undefined_column THEN NULL;
3203
    WHEN duplicate_column THEN NULL;
3204
END;
3205
$$;
3206

    
3207

    
3208
--
3209
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
3210
--
3211

    
3212
COMMENT ON FUNCTION try_create(sql text) IS '
3213
idempotent
3214
';
3215

    
3216

    
3217
--
3218
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
3219
--
3220

    
3221
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
3222
    LANGUAGE sql STRICT
3223
    AS $_$
3224
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
3225
$_$;
3226

    
3227

    
3228
--
3229
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
3230
--
3231

    
3232
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
3233
idempotent
3234
';
3235

    
3236

    
3237
--
3238
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
3239
--
3240

    
3241
CREATE FUNCTION type_qual(value anyelement) RETURNS text
3242
    LANGUAGE sql IMMUTABLE
3243
    AS $_$
3244
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
3245
$_$;
3246

    
3247

    
3248
--
3249
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
3250
--
3251

    
3252
COMMENT ON FUNCTION type_qual(value anyelement) IS '
3253
a type''s NOT NULL qualifier
3254
';
3255

    
3256

    
3257
--
3258
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
3259
--
3260

    
3261
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
3262
    LANGUAGE plpgsql STABLE
3263
    AS $_$
3264
DECLARE
3265
    type regtype;
3266
BEGIN
3267
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
3268
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
3269
    RETURN type;
3270
END;
3271
$_$;
3272

    
3273

    
3274
--
3275
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
3276
--
3277

    
3278
CREATE AGGREGATE all_same(anyelement) (
3279
    SFUNC = all_same_transform,
3280
    STYPE = anyarray,
3281
    FINALFUNC = all_same_final
3282
);
3283

    
3284

    
3285
--
3286
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
3287
--
3288

    
3289
COMMENT ON AGGREGATE all_same(anyelement) IS '
3290
includes NULLs in comparison
3291
';
3292

    
3293

    
3294
--
3295
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
3296
--
3297

    
3298
CREATE AGGREGATE join_strs(text, text) (
3299
    SFUNC = join_strs_transform,
3300
    STYPE = text
3301
);
3302

    
3303

    
3304
--
3305
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
3306
--
3307

    
3308
CREATE OPERATOR -> (
3309
    PROCEDURE = map_get,
3310
    LEFTARG = regclass,
3311
    RIGHTARG = text
3312
);
3313

    
3314

    
3315
--
3316
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
3317
--
3318

    
3319
CREATE OPERATOR => (
3320
    PROCEDURE = hstore,
3321
    LEFTARG = text[],
3322
    RIGHTARG = text
3323
);
3324

    
3325

    
3326
--
3327
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
3328
--
3329

    
3330
COMMENT ON OPERATOR => (text[], text) IS '
3331
usage: array[''key1'', ...]::text[] => ''value''
3332
';
3333

    
3334

    
3335
--
3336
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
3337
--
3338

    
3339
CREATE OPERATOR ?*>= (
3340
    PROCEDURE = is_populated_more_often_than,
3341
    LEFTARG = anyelement,
3342
    RIGHTARG = anyelement
3343
);
3344

    
3345

    
3346
--
3347
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
3348
--
3349

    
3350
CREATE OPERATOR ?>= (
3351
    PROCEDURE = is_more_complete_than,
3352
    LEFTARG = anyelement,
3353
    RIGHTARG = anyelement
3354
);
3355

    
3356

    
3357
--
3358
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
3359
--
3360

    
3361
CREATE OPERATOR ||% (
3362
    PROCEDURE = concat_esc,
3363
    LEFTARG = text,
3364
    RIGHTARG = text
3365
);
3366

    
3367

    
3368
--
3369
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
3370
--
3371

    
3372
COMMENT ON OPERATOR ||% (text, text) IS '
3373
% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
3374
';
3375

    
3376

    
3377
--
3378
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
3379
--
3380

    
3381
CREATE TABLE map (
3382
    "from" text NOT NULL,
3383
    "to" text,
3384
    filter text,
3385
    notes text
3386
);
3387

    
3388

    
3389
--
3390
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
3391
--
3392

    
3393

    
3394

    
3395
--
3396
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
3397
--
3398

    
3399

    
3400

    
3401
--
3402
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3403
--
3404

    
3405
ALTER TABLE ONLY map
3406
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
3407

    
3408

    
3409
--
3410
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
3411
--
3412

    
3413
ALTER TABLE ONLY map
3414
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
3415

    
3416

    
3417
--
3418
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
3419
--
3420

    
3421
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
3422

    
3423

    
3424
--
3425
-- PostgreSQL database dump complete
3426
--
3427

    
(19-19/29)