Project

General

Profile

1 2094 aaronmk
--
2
-- PostgreSQL database dump
3
--
4
5
SET statement_timeout = 0;
6 11667 aaronmk
SET lock_timeout = 0;
7 2094 aaronmk
SET client_encoding = 'UTF8';
8 6213 aaronmk
SET standard_conforming_strings = on;
9 2094 aaronmk
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11
12
--
13 8183 aaronmk
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
14 2094 aaronmk
--
15
16 8183 aaronmk
CREATE SCHEMA util;
17 2094 aaronmk
18
19 4982 aaronmk
--
20 8183 aaronmk
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
21 4982 aaronmk
--
22
23 12235 aaronmk
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 4982 aaronmk
26 12447 aaronmk
NOTE: SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding. avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won''t mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements.
27 12235 aaronmk
';
28 4982 aaronmk
29 10378 aaronmk
30 8183 aaronmk
SET search_path = util, pg_catalog;
31 2107 aaronmk
32 2094 aaronmk
--
33 8183 aaronmk
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
34 8107 aaronmk
--
35
36
CREATE TYPE col_cast AS (
37
	col_name text,
38
	type regtype
39
);
40
41
42
--
43 8183 aaronmk
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
44 8106 aaronmk
--
45
46
CREATE TYPE col_ref AS (
47
	table_ regclass,
48
	name text
49
);
50
51
52
--
53 8183 aaronmk
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
54 7673 aaronmk
--
55
56
CREATE TYPE compass_dir AS ENUM (
57
    'N',
58
    'E',
59
    'S',
60
    'W'
61
);
62
63
64
--
65 8183 aaronmk
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
66 2610 aaronmk
--
67
68
CREATE TYPE datatype AS ENUM (
69
    'str',
70
    'float'
71
);
72
73
74
--
75 13491 aaronmk
-- Name: db_item; Type: TYPE; Schema: util; Owner: -
76
--
77
78
CREATE TYPE db_item AS (
79
	path text,
80
	def text
81
);
82
83
84
--
85 13488 aaronmk
-- Name: restore_views_info; Type: TYPE; Schema: util; Owner: -
86
--
87
88
CREATE TYPE restore_views_info AS (
89 13491 aaronmk
	views db_item[]
90 13488 aaronmk
);
91
92
93
--
94 12423 aaronmk
-- Name: %==(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
95
--
96
97
CREATE FUNCTION "%=="(left_ anyelement, right_ anyelement) RETURNS boolean
98 12443 aaronmk
    LANGUAGE sql STABLE
99 12423 aaronmk
    AS $_$
100 12436 aaronmk
SELECT keys($1) = keys($2)
101 12423 aaronmk
$_$;
102
103
104
--
105 12443 aaronmk
-- Name: FUNCTION "%=="(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
106
--
107
108
COMMENT ON FUNCTION "%=="(left_ anyelement, right_ anyelement) IS '
109
needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
110
';
111
112
113
--
114 8183 aaronmk
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
115 2596 aaronmk
--
116
117 4501 aaronmk
CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
118 3422 aaronmk
    LANGUAGE sql IMMUTABLE
119
    AS $_$
120 4501 aaronmk
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
121 3422 aaronmk
$_$;
122
123
124
--
125 8183 aaronmk
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
126 5937 aaronmk
--
127
128 5956 aaronmk
CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
129 5937 aaronmk
    LANGUAGE sql IMMUTABLE
130
    AS $_$
131
SELECT bool_and(value)
132
FROM
133
(VALUES
134
      ($1)
135
    , ($2)
136 5956 aaronmk
    , ($3)
137
    , ($4)
138
    , ($5)
139 5937 aaronmk
)
140
AS v (value)
141
$_$;
142
143
144
--
145 8183 aaronmk
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
146 5937 aaronmk
--
147
148 12235 aaronmk
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
149
_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.
150
';
151 5937 aaronmk
152
153
--
154 8183 aaronmk
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
155 7704 aaronmk
--
156
157
CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
158
    LANGUAGE sql IMMUTABLE
159
    AS $_$
160
SELECT avg(value)
161
FROM
162
(VALUES
163
      ($1)
164
    , ($2)
165
    , ($3)
166
    , ($4)
167
    , ($5)
168
)
169
AS v (value)
170
$_$;
171
172
173
--
174 8183 aaronmk
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
175 7679 aaronmk
--
176
177
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
178 12441 aaronmk
    LANGUAGE sql IMMUTABLE
179 7679 aaronmk
    AS $_$
180 8183 aaronmk
SELECT (g[1]||'1')::integer*util._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::util.compass_dir)
181 7698 aaronmk
FROM
182
(
183
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
184
    UNION ALL
185 7702 aaronmk
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
186
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
187 7698 aaronmk
)
188
matches (g)
189 7679 aaronmk
$_$;
190
191
192
--
193 8183 aaronmk
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
194 7672 aaronmk
--
195
196 7674 aaronmk
CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
197 7672 aaronmk
    LANGUAGE sql IMMUTABLE
198
    AS $_$
199 8183 aaronmk
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
200 7672 aaronmk
FROM
201
(VALUES
202 7677 aaronmk
      ($1)
203 7672 aaronmk
    , ($2/60)
204
    , ($3/60/60)
205
)
206
AS v (value)
207
$_$;
208
209
210
--
211 8183 aaronmk
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
212 7723 aaronmk
--
213
214
CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
215
    LANGUAGE sql IMMUTABLE
216
    AS $_$
217 8183 aaronmk
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
218 7723 aaronmk
$_$;
219
220
221
--
222 8183 aaronmk
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
223 4142 aaronmk
--
224
225
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
226
    LANGUAGE sql IMMUTABLE
227
    AS $_$
228
SELECT $1 = $2
229
$_$;
230
231
232
--
233 8183 aaronmk
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
234 7396 aaronmk
--
235
236
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
237
    LANGUAGE sql IMMUTABLE
238
    AS $_$
239
-- Fix dates after threshold date
240
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
241
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
242
$_$;
243
244
245
--
246 8183 aaronmk
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
247 4147 aaronmk
--
248
249
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
250
    LANGUAGE sql IMMUTABLE
251
    AS $_$
252
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
253
$_$;
254
255
256
--
257 8183 aaronmk
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
258 4147 aaronmk
--
259
260
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
261
    LANGUAGE sql IMMUTABLE
262
    AS $_$
263 8183 aaronmk
SELECT util._if($1 != '', $2, $3)
264 4147 aaronmk
$_$;
265
266
267
--
268 10699 aaronmk
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
269 4325 aaronmk
--
270
271 10699 aaronmk
CREATE FUNCTION _join("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
272 4325 aaronmk
    LANGUAGE sql IMMUTABLE
273
    AS $_$
274 7848 aaronmk
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
275 4325 aaronmk
$_$;
276
277
278
--
279 10699 aaronmk
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
280 5009 aaronmk
--
281
282 10699 aaronmk
CREATE FUNCTION _join_words("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
283 5009 aaronmk
    LANGUAGE sql IMMUTABLE
284
    AS $_$
285 7848 aaronmk
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
286 5009 aaronmk
$_$;
287
288
289
--
290 13355 aaronmk
-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: util; Owner: -
291
--
292
293
CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision
294
    LANGUAGE sql IMMUTABLE
295
    AS $_$
296
SELECT $1*1000.
297
$_$;
298
299
300
--
301 10699 aaronmk
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
302 3422 aaronmk
--
303
304 10699 aaronmk
CREATE FUNCTION _label(label text, value text) RETURNS text
305 4682 aaronmk
    LANGUAGE sql IMMUTABLE
306
    AS $_$
307
SELECT coalesce($1 || ': ', '') || $2
308
$_$;
309 2596 aaronmk
310
311
--
312 8825 aaronmk
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
313
--
314
315
CREATE FUNCTION _lowercase(value text) RETURNS text
316 10388 aaronmk
    LANGUAGE sql IMMUTABLE
317 8825 aaronmk
    AS $_$
318
SELECT lower($1)
319
$_$;
320
321
322
--
323 11667 aaronmk
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
324
--
325
326
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
327
    LANGUAGE plpgsql IMMUTABLE STRICT
328
    AS $$
329
DECLARE
330
    result value%TYPE := util._map(map, value::text)::unknown;
331
BEGIN
332
    RETURN result;
333
END;
334
$$;
335
336
337
--
338 8183 aaronmk
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
339 6222 aaronmk
--
340
341
CREATE FUNCTION _map(map hstore, value text) RETURNS text
342 7820 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
343 6222 aaronmk
    AS $$
344
DECLARE
345 6271 aaronmk
    match text := map -> value;
346 6222 aaronmk
BEGIN
347 6271 aaronmk
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
348
        match := map -> '*'; -- use default entry
349
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
350
        END IF;
351
    END IF;
352
353
    -- Interpret result
354 6243 aaronmk
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
355
    ELSIF match = '*' THEN RETURN value;
356
    ELSE RETURN match;
357 6222 aaronmk
    END IF;
358
END;
359
$$;
360
361
362
--
363 8183 aaronmk
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
364 5408 aaronmk
--
365
366
CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
367
    LANGUAGE sql IMMUTABLE
368
    AS $_$
369 7289 aaronmk
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
370 5408 aaronmk
$_$;
371
372
373
--
374 8183 aaronmk
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
375 2940 aaronmk
--
376
377 4150 aaronmk
CREATE FUNCTION _merge("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
378 2940 aaronmk
    LANGUAGE sql IMMUTABLE
379
    AS $_$
380 8183 aaronmk
SELECT util.join_strs(value, '; ')
381 2940 aaronmk
FROM
382
(
383
    SELECT *
384
    FROM
385
    (
386
        SELECT
387
        DISTINCT ON (value)
388
        *
389
        FROM
390
        (VALUES
391 4012 aaronmk
              (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 2940 aaronmk
        )
402
        AS v (sort_order, value)
403 4011 aaronmk
        WHERE value IS NOT NULL
404 2940 aaronmk
    )
405
    AS v
406
    ORDER BY sort_order
407
)
408
AS v
409
$_$;
410
411
412
--
413 8183 aaronmk
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
414 7140 aaronmk
--
415
416
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
417
    LANGUAGE sql IMMUTABLE
418
    AS $_$
419
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
420
$_$;
421
422
423
--
424 8183 aaronmk
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
425 6354 aaronmk
--
426
427
CREATE FUNCTION _merge_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
428
    LANGUAGE sql IMMUTABLE
429
    AS $_$
430 8183 aaronmk
SELECT util.join_strs(value, ' ')
431 6354 aaronmk
FROM
432
(
433
    SELECT *
434
    FROM
435
    (
436
        SELECT
437
        DISTINCT ON (value)
438
        *
439
        FROM
440
        (VALUES
441
              (1, $1)
442
            , (2, $2)
443
            , (3, $3)
444
            , (4, $4)
445
            , (5, $5)
446
            , (6, $6)
447
            , (7, $7)
448
            , (8, $8)
449
            , (9, $9)
450
            , (10, $10)
451
        )
452
        AS v (sort_order, value)
453
        WHERE value IS NOT NULL
454
    )
455
    AS v
456
    ORDER BY sort_order
457
)
458
AS v
459
$_$;
460
461
462
--
463 8183 aaronmk
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
464 5408 aaronmk
--
465
466
CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
467
    LANGUAGE sql IMMUTABLE
468
    AS $_$
469 7289 aaronmk
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
470 5408 aaronmk
$_$;
471
472
473
--
474 8183 aaronmk
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
475 6316 aaronmk
--
476
477
CREATE FUNCTION _not(value boolean) RETURNS boolean
478 10388 aaronmk
    LANGUAGE sql IMMUTABLE
479 6316 aaronmk
    AS $_$
480
SELECT NOT $1
481
$_$;
482
483
484
--
485 8183 aaronmk
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
486 7104 aaronmk
--
487
488
CREATE FUNCTION _now() RETURNS timestamp with time zone
489
    LANGUAGE sql STABLE
490
    AS $$
491
SELECT now()
492
$$;
493
494
495
--
496 11667 aaronmk
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
497
--
498
499
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
500
    LANGUAGE sql IMMUTABLE
501
    AS $_$
502
SELECT util."_nullIf"($1, $2, $3::util.datatype)
503
$_$;
504
505
506
--
507 8183 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
508 2949 aaronmk
--
509
510 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
511 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
512
    AS $$
513
DECLARE
514 8183 aaronmk
    type util.datatype NOT NULL := type; -- add NOT NULL
515 2949 aaronmk
BEGIN
516 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
517 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
518 2949 aaronmk
    ELSIF type = 'float' THEN
519 2722 aaronmk
        DECLARE
520
            -- Outside the try block so that invalid null value generates error
521 2949 aaronmk
            "null" double precision := "null"::double precision;
522 2722 aaronmk
        BEGIN
523 2949 aaronmk
            RETURN nullif(value::double precision, "null");
524 2722 aaronmk
        EXCEPTION
525 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
526 2722 aaronmk
        END;
527 2610 aaronmk
    END IF;
528
END;
529
$$;
530
531
532
--
533 8183 aaronmk
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
534 6355 aaronmk
--
535
536
CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
537
    LANGUAGE sql IMMUTABLE
538
    AS $_$
539
SELECT bool_or(value)
540
FROM
541
(VALUES
542
      ($1)
543
    , ($2)
544
    , ($3)
545
    , ($4)
546
    , ($5)
547
)
548
AS v (value)
549
$_$;
550
551
552
--
553 8183 aaronmk
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
554 6437 aaronmk
--
555
556 12235 aaronmk
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
557
_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.
558
';
559 6437 aaronmk
560
561
--
562 8183 aaronmk
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
563 7706 aaronmk
--
564
565
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
566
    LANGUAGE sql IMMUTABLE
567
    AS $_$
568
SELECT $2 - $1
569
$_$;
570
571
572
--
573 8183 aaronmk
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
574 6793 aaronmk
--
575
576
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
577 10388 aaronmk
    LANGUAGE sql IMMUTABLE
578 6793 aaronmk
    AS $_$
579
SELECT regexp_split_to_table($1, $2)
580
$_$;
581
582
583
--
584 10594 aaronmk
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
585
--
586
587
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
588 12446 aaronmk
    LANGUAGE sql STABLE
589 10594 aaronmk
    AS $_$
590
SELECT util.derived_cols($1, $2)
591
UNION
592
SELECT util.eval2set($$
593
SELECT col
594
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
595
JOIN $$||$2||$$ ON "to" = col
596
WHERE "from" LIKE ':%'
597
$$, NULL::text)
598
$_$;
599
600
601
--
602
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
603
--
604
605 12235 aaronmk
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
606
gets table_''s added columns (all the columns not in the original data)
607
';
608 10594 aaronmk
609
610
--
611 9959 aaronmk
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
612
--
613
614
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
615
    LANGUAGE sql IMMUTABLE
616
    AS $_$
617
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
618
$_$;
619
620
621
--
622
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
623
--
624
625
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
626
    LANGUAGE plpgsql IMMUTABLE
627
    AS $$
628
DECLARE
629
	value_cmp         state%TYPE = ARRAY[value];
630
	state             state%TYPE = COALESCE(state, value_cmp);
631
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
632
BEGIN
633
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
634
END;
635
$$;
636
637
638
--
639 12320 aaronmk
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
640
--
641
642
CREATE FUNCTION analyze_(table_ regclass) RETURNS void
643
    LANGUAGE sql
644
    AS $_$
645
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
646
$_$;
647
648
649
--
650 12369 aaronmk
-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
651
--
652
653
CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
654 12446 aaronmk
    LANGUAGE sql
655 12369 aaronmk
    AS $_$
656
SELECT util.set_comment($1, concat(util.comment($1), $2))
657
$_$;
658
659
660
--
661
-- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
662
--
663
664
COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
665
comment: must start and end with a newline
666
';
667
668
669
--
670 10305 aaronmk
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
671
--
672
673
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
674
    LANGUAGE sql IMMUTABLE
675
    AS $_$
676
SELECT pg_catalog.array_fill($1, ARRAY[$2])
677
$_$;
678
679
680
--
681 10303 aaronmk
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
682
--
683
684
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
685 10354 aaronmk
    LANGUAGE sql IMMUTABLE
686 10303 aaronmk
    AS $_$
687 10321 aaronmk
SELECT util.array_length($1, 1)
688 10303 aaronmk
$_$;
689
690
691
--
692 10304 aaronmk
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
693
--
694
695
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
696 10354 aaronmk
    LANGUAGE sql IMMUTABLE
697 10304 aaronmk
    AS $_$
698 10354 aaronmk
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
699 10304 aaronmk
$_$;
700
701
702
--
703
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
704
--
705
706 12235 aaronmk
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
707
returns 0 instead of NULL for empty arrays
708
';
709 10304 aaronmk
710
711
--
712 13453 aaronmk
-- Name: array_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
713
--
714
715
CREATE FUNCTION array_reverse("array" anyarray) RETURNS anyarray
716
    LANGUAGE sql IMMUTABLE
717
    AS $_$
718 13489 aaronmk
SELECT array(SELECT elem FROM util.in_reverse($1) elem)
719 13453 aaronmk
$_$;
720
721
722
--
723 12662 aaronmk
-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
724 12655 aaronmk
--
725
726 12662 aaronmk
CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
727 12655 aaronmk
    LANGUAGE sql
728
    AS $_$
729 12662 aaronmk
SELECT CASE WHEN util.freq_always_1($1, $2)
730 13097 aaronmk
THEN util.rm_freq($1, $2)
731 12685 aaronmk
ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
732 12662 aaronmk
END
733 12655 aaronmk
$_$;
734
735
736
--
737 13134 aaronmk
-- Name: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
738
--
739
740
CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
741
    LANGUAGE plpgsql IMMUTABLE
742
    AS $$
743
/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
744
return value, causing a type mismatch */
745
BEGIN
746
	-- will then be assignment-cast to return type via INOUT
747
	RETURN value::cstring;
748
END;
749
$$;
750
751
752
--
753
-- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
754
--
755
756
COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
757
allows casting to an arbitrary type without eval()
758
759
usage:
760
SELECT util.cast(''value'', NULL::integer);
761
762
note that there does *not* need to be a cast from text to the output type,
763
because an INOUT cast is used instead
764
(http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)
765
766
ret_type_null: NULL::ret_type
767
';
768
769
770
--
771 8183 aaronmk
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
772 8104 aaronmk
--
773
774
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
775 12446 aaronmk
    LANGUAGE sql STABLE
776 8104 aaronmk
    AS $_$
777
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
778
$_$;
779
780
781
--
782 8183 aaronmk
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
783 8105 aaronmk
--
784
785
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
786
    LANGUAGE plpgsql STRICT
787
    AS $_$
788
BEGIN
789
    -- not yet clustered (ARRAY[] compares NULLs literally)
790 8183 aaronmk
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
791 8105 aaronmk
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
792
    END IF;
793
END;
794
$_$;
795
796
797
--
798 8183 aaronmk
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
799 8105 aaronmk
--
800
801 12235 aaronmk
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
802
idempotent
803
';
804 8105 aaronmk
805
806
--
807 12683 aaronmk
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
808
--
809
810
CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
811
    LANGUAGE sql IMMUTABLE
812
    AS $_$
813
SELECT value
814
FROM unnest($1) value
815
WHERE value IS NOT NULL
816
LIMIT 1
817
$_$;
818
819
820
--
821
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
822
--
823
824
COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
825
uses:
826
* coalescing array elements or rows together
827
* forcing evaluation of all values of a COALESCE()
828
';
829
830
831
--
832 10986 aaronmk
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
833
--
834
835
CREATE FUNCTION col__min(col col_ref) RETURNS integer
836
    LANGUAGE sql STABLE
837
    AS $_$
838
SELECT util.eval2val($$
839
SELECT $$||quote_ident($1.name)||$$
840
FROM $$||$1.table_||$$
841
ORDER BY $$||quote_ident($1.name)||$$ ASC
842
LIMIT 1
843
$$, NULL::integer)
844
$_$;
845
846
847
--
848 10136 aaronmk
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
849
--
850
851
CREATE FUNCTION col_comment(col col_ref) RETURNS text
852
    LANGUAGE plpgsql STABLE STRICT
853
    AS $$
854
DECLARE
855
	comment text;
856
BEGIN
857
	SELECT description
858
	FROM pg_attribute
859
	LEFT JOIN pg_description ON objoid = attrelid
860
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
861
	WHERE attrelid = col.table_ AND attname = col.name
862
	INTO STRICT comment
863
	;
864
	RETURN comment;
865
EXCEPTION
866
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
867
END;
868
$$;
869
870
871
--
872 10130 aaronmk
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
873
--
874
875
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
876
    LANGUAGE plpgsql STABLE STRICT
877
    AS $$
878
DECLARE
879
	default_sql text;
880
BEGIN
881
	SELECT adsrc
882
	FROM pg_attribute
883
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
884
	WHERE attrelid = col.table_ AND attname = col.name
885
	INTO STRICT default_sql
886
	;
887
	RETURN default_sql;
888
EXCEPTION
889
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
890
END;
891
$$;
892
893
894
--
895 10134 aaronmk
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
896
--
897
898
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
899
    LANGUAGE sql STABLE
900
    AS $_$
901
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
902
$_$;
903
904
905
--
906
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
907
--
908
909 12235 aaronmk
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
910
ret_type_null: NULL::ret_type
911
';
912 10134 aaronmk
913
914
--
915 8183 aaronmk
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
916 8180 aaronmk
--
917
918
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
919
    LANGUAGE plpgsql STRICT
920
    AS $$
921
BEGIN
922 8183 aaronmk
    PERFORM util.col_type(col);
923 8180 aaronmk
    RETURN true;
924
EXCEPTION
925
    WHEN undefined_column THEN RETURN false;
926
END;
927
$$;
928
929
930
--
931 8183 aaronmk
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
932 8084 aaronmk
--
933
934
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
935 8097 aaronmk
    LANGUAGE plpgsql STABLE STRICT
936 8084 aaronmk
    AS $$
937
DECLARE
938 8183 aaronmk
    prefix text := util.name(type)||'.';
939 8084 aaronmk
BEGIN
940
    RETURN QUERY
941 8183 aaronmk
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
942
        FROM util.col_names(type) f (name_);
943 8084 aaronmk
END;
944
$$;
945
946
947
--
948 8183 aaronmk
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
949 8151 aaronmk
--
950
951
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
952 12446 aaronmk
    LANGUAGE sql STABLE
953 8151 aaronmk
    AS $_$
954
SELECT attname::text
955
FROM pg_attribute
956 10158 aaronmk
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
957 8151 aaronmk
ORDER BY attnum
958
$_$;
959
960
961
--
962 11667 aaronmk
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
963
--
964
965
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
966
    LANGUAGE plpgsql STABLE STRICT
967
    AS $_$
968
BEGIN
969
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
970
END;
971
$_$;
972
973
974
--
975 8183 aaronmk
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
976 8106 aaronmk
--
977
978
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
979 8169 aaronmk
    LANGUAGE plpgsql STABLE STRICT
980
    AS $$
981
DECLARE
982
    type regtype;
983
BEGIN
984
    SELECT atttypid FROM pg_attribute
985
    WHERE attrelid = col.table_ AND attname = col.name
986
    INTO STRICT type
987
    ;
988
    RETURN type;
989
EXCEPTION
990 8171 aaronmk
    WHEN no_data_found THEN
991 8181 aaronmk
        RAISE undefined_column USING MESSAGE =
992
            concat('undefined column: ', col.name);
993 8169 aaronmk
END;
994
$$;
995 8106 aaronmk
996
997
--
998 12368 aaronmk
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
999
--
1000
1001
CREATE FUNCTION comment(element oid) RETURNS text
1002 12446 aaronmk
    LANGUAGE sql STABLE
1003 12368 aaronmk
    AS $_$
1004
SELECT description FROM pg_description WHERE objoid = $1
1005
$_$;
1006
1007
1008
--
1009 11005 aaronmk
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
1010
--
1011
1012
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
1013
    LANGUAGE sql IMMUTABLE
1014
    AS $_$
1015
SELECT util.esc_name__append($2, $1)
1016
$_$;
1017
1018
1019
--
1020 8183 aaronmk
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
1021 8095 aaronmk
--
1022
1023
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
1024 10388 aaronmk
    LANGUAGE sql IMMUTABLE
1025 8095 aaronmk
    AS $_$
1026
SELECT position($1 in $2) > 0 /*1-based offset*/
1027
$_$;
1028
1029
1030
--
1031 12288 aaronmk
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1032
--
1033
1034
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
1035
    LANGUAGE sql
1036
    AS $_$
1037
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
1038
$_$;
1039
1040
1041
--
1042 12649 aaronmk
-- Name: copy_types_and_data(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1043
--
1044
1045
CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
1046
    LANGUAGE sql
1047
    AS $_$
1048
SELECT util.materialize_view($2, $1)
1049
$_$;
1050
1051
1052
--
1053 8183 aaronmk
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
1054 8094 aaronmk
--
1055
1056
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
1057
    LANGUAGE plpgsql STRICT
1058
    AS $$
1059
BEGIN
1060 12595 aaronmk
	PERFORM util.eval(sql);
1061 8094 aaronmk
EXCEPTION
1062 12676 aaronmk
WHEN   duplicate_table
1063
	OR duplicate_object -- eg. constraint
1064
	OR duplicate_column
1065
	OR duplicate_function
1066
THEN NULL;
1067 12595 aaronmk
WHEN invalid_table_definition THEN
1068
	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
1069
	ELSE RAISE;
1070
	END IF;
1071 8094 aaronmk
END;
1072
$$;
1073
1074
1075
--
1076 8183 aaronmk
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
1077 8094 aaronmk
--
1078
1079 12235 aaronmk
COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
1080
idempotent
1081
';
1082 8094 aaronmk
1083
1084
--
1085 12378 aaronmk
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1086
--
1087
1088
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
1089 12590 aaronmk
    LANGUAGE sql STABLE
1090 12378 aaronmk
    AS $$
1091
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
1092
$$;
1093
1094
1095
--
1096 12668 aaronmk
-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
1097
--
1098
1099
CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
1100
    LANGUAGE sql IMMUTABLE
1101
    AS $_$
1102
SELECT util.raise('NOTICE', $$SELECT $$||$1)
1103
$_$;
1104
1105
1106
--
1107 12433 aaronmk
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1108 12430 aaronmk
--
1109
1110 12433 aaronmk
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
1111 12430 aaronmk
    LANGUAGE sql IMMUTABLE
1112
    AS $_$
1113 13447 aaronmk
SELECT util.debug_print_value('returns: ', $1, $2);
1114 12430 aaronmk
SELECT $1;
1115
$_$;
1116
1117
1118
--
1119 12250 aaronmk
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
1120
--
1121
1122
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
1123
    LANGUAGE sql IMMUTABLE
1124
    AS $_$
1125
/* newline before so the query starts at the beginning of the line.
1126
newline after to visually separate queries from one another. */
1127 12534 aaronmk
SELECT util.raise('NOTICE', $$
1128 12474 aaronmk
$$||util.runnable_sql($1)||$$
1129 12464 aaronmk
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
1130 12250 aaronmk
$_$;
1131
1132
1133
--
1134 13446 aaronmk
-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1135
--
1136
1137
CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
1138
    LANGUAGE sql IMMUTABLE
1139
    AS $_$
1140
SELECT util.raise('NOTICE', concat($1,
1141 13449 aaronmk
(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
1142
$$)
1143 13446 aaronmk
$_$;
1144
1145
1146
--
1147 13448 aaronmk
-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1148
--
1149
1150
CREATE FUNCTION debug_print_var(var text, value anyelement, encode boolean DEFAULT false) RETURNS void
1151
    LANGUAGE sql IMMUTABLE
1152
    AS $_$
1153
/* can't use EXECUTE in the caller because "No substitution of PL/pgSQL
1154
variables is done on the computed command string"
1155
(http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) */
1156
SELECT util.debug_print_value($1||' = ', $2, $3)
1157
$_$;
1158
1159
1160
--
1161 10364 aaronmk
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1162
--
1163
1164
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
1165 12446 aaronmk
    LANGUAGE sql STABLE
1166 10364 aaronmk
    AS $_$
1167
SELECT util.eval2set($$
1168
SELECT col
1169
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
1170
LEFT JOIN $$||$2||$$ ON "to" = col
1171
WHERE "from" IS NULL
1172
$$, NULL::text)
1173
$_$;
1174
1175
1176
--
1177
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1178
--
1179
1180 12235 aaronmk
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
1181
gets table_''s derived columns (all the columns not in the names table)
1182
';
1183 10364 aaronmk
1184
1185
--
1186 12298 aaronmk
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1187 12044 aaronmk
--
1188
1189 12298 aaronmk
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1190 12427 aaronmk
    LANGUAGE sql
1191 12044 aaronmk
    AS $_$
1192 12653 aaronmk
-- create a diff when the # of copies of a row differs between the tables
1193
SELECT util.to_freq($1);
1194
SELECT util.to_freq($2);
1195 12663 aaronmk
SELECT util.auto_rm_freq(ARRAY[$1, $2]);
1196 12653 aaronmk
1197
SELECT * FROM util.diff($1, $2, $3, has_freq := true)
1198 12298 aaronmk
$_$;
1199
1200
1201
--
1202
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1203
--
1204
1205
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1206
usage:
1207
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
1208 12653 aaronmk
1209
col_type_null (*required*): NULL::shared_base_type
1210 12298 aaronmk
';
1211
1212
1213
--
1214 12491 aaronmk
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1215 12298 aaronmk
--
1216
1217 12491 aaronmk
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
1218 12429 aaronmk
    LANGUAGE plpgsql
1219
    SET search_path TO pg_temp
1220 12298 aaronmk
    AS $_$
1221 12429 aaronmk
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
1222
changes of search_path (schema elements are bound at inline time rather than
1223
runtime) */
1224
/* function option search_path is needed to limit the effects of
1225 12492 aaronmk
`SET LOCAL search_path` to the current function */
1226 12429 aaronmk
BEGIN
1227 12491 aaronmk
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
1228 12429 aaronmk
1229 12565 aaronmk
	PERFORM util.mk_keys_func(pg_typeof($3));
1230 12429 aaronmk
	RETURN QUERY
1231 12554 aaronmk
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
1232 12479 aaronmk
$$/* need to explicitly cast each side to the return type because this does not
1233 12284 aaronmk
happen automatically even when an implicit cast is available */
1234 12479 aaronmk
  left_::$$||util.typeof($3)||$$
1235
, right_::$$||util.typeof($3)
1236 12496 aaronmk
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
1237
the *same* base type, *even though* this is optional when using a custom %== */
1238 12553 aaronmk
, util._if($4, $$true/*= CROSS JOIN*/$$,
1239
$$ left_::$$||util.typeof($3)||$$
1240 12496 aaronmk
%== right_::$$||util.typeof($3)||$$
1241
	-- refer to EXPLAIN output for expansion of %==$$
1242 12657 aaronmk
)
1243
,     $$         left_::$$||util.typeof($3)||$$
1244
IS DISTINCT FROM right_::$$||util.typeof($3)
1245
), $3)
1246 12429 aaronmk
	;
1247
END;
1248 12044 aaronmk
$_$;
1249
1250
1251
--
1252 12491 aaronmk
-- 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: -
1253 12280 aaronmk
--
1254
1255 12491 aaronmk
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1256 12280 aaronmk
col_type_null (*required*): NULL::col_type
1257 12299 aaronmk
single_row: whether the tables consist of a single row, which should be
1258
	displayed side-by-side
1259 12282 aaronmk
1260 12498 aaronmk
to match up rows using a subset of the columns, create a custom keys() function
1261
which returns this subset as a record:
1262
-- note that OUT parameters for the returned fields are *not* needed
1263
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
1264
  RETURNS record AS
1265
$BODY$
1266
SELECT ($1.key_field_0, $1.key_field_1)
1267
$BODY$
1268
  LANGUAGE sql IMMUTABLE
1269
  COST 100;
1270
1271
1272 12282 aaronmk
to run EXPLAIN on the FULL JOIN query:
1273
# run this function
1274
# look for a NOTICE containing the expanded query that it ran
1275
# run EXPLAIN on this expanded query
1276 12280 aaronmk
';
1277
1278
1279
--
1280 12653 aaronmk
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1281
--
1282
1283
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1284
    LANGUAGE sql
1285
    AS $_$
1286
SELECT * FROM util.diff($1::text, $2::text, $3,
1287
	single_row := util.has_single_row($1) AND util.has_single_row($2))
1288
$_$;
1289
1290
1291
--
1292
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1293
--
1294
1295
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
1296
helper function used by diff(regclass, regclass)
1297
1298
usage:
1299
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
1300
1301
col_type_null (*required*): NULL::shared_base_type
1302
';
1303
1304
1305
--
1306 8200 aaronmk
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1307
--
1308
1309
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
1310 12446 aaronmk
    LANGUAGE sql
1311 8200 aaronmk
    AS $_$
1312
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
1313
$_$;
1314
1315
1316
--
1317
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
1318
--
1319
1320 12235 aaronmk
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
1321
idempotent
1322
';
1323 8200 aaronmk
1324
1325
--
1326 12292 aaronmk
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
1327 10362 aaronmk
--
1328
1329 12292 aaronmk
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
1330 12446 aaronmk
    LANGUAGE sql
1331 10362 aaronmk
    AS $_$
1332
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
1333 12292 aaronmk
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
1334 10362 aaronmk
$_$;
1335
1336
1337
--
1338 12292 aaronmk
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
1339 10362 aaronmk
--
1340
1341 12292 aaronmk
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
1342 12235 aaronmk
idempotent
1343
';
1344 10362 aaronmk
1345
1346
--
1347 12660 aaronmk
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
1348
--
1349
1350
CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
1351
    LANGUAGE sql
1352
    AS $_$
1353 12686 aaronmk
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
1354
SELECT NULL::void; -- don't fold away functions called in previous query
1355 12660 aaronmk
$_$;
1356
1357
1358
--
1359
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
1360
--
1361
1362
COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
1363
idempotent
1364
';
1365
1366
1367
--
1368 12587 aaronmk
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1369 12331 aaronmk
--
1370
1371 12587 aaronmk
CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
1372 12331 aaronmk
    LANGUAGE sql
1373
    AS $_$
1374 12353 aaronmk
/* use util.qual_name() instead of ::text so that the schema qualifier is always
1375
included in the debug SQL */
1376
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
1377 12331 aaronmk
$_$;
1378
1379
1380
--
1381 12343 aaronmk
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1382
--
1383
1384 12364 aaronmk
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
1385 12343 aaronmk
    LANGUAGE sql
1386
    AS $_$
1387 12347 aaronmk
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
1388 12343 aaronmk
||util._if($3, $$ CASCADE$$, ''::text))
1389
$_$;
1390
1391
1392
--
1393 12364 aaronmk
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
1394 12343 aaronmk
--
1395
1396 12364 aaronmk
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
1397 12343 aaronmk
idempotent
1398
';
1399
1400
1401
--
1402 12413 aaronmk
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1403
--
1404
1405
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
1406
    LANGUAGE sql
1407
    AS $_$
1408 12502 aaronmk
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
1409 12413 aaronmk
$_$;
1410
1411
1412
--
1413
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1414
--
1415
1416
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
1417
    LANGUAGE sql
1418
    AS $_$
1419 12668 aaronmk
SELECT util.debug_print_func_call(util.quote_func_call(
1420
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
1421
util.quote_typed($3)))
1422 12667 aaronmk
;
1423 12413 aaronmk
SELECT util.drop_relation(relation, $3)
1424
FROM util.show_relations_like($1, $2) relation
1425
;
1426
SELECT NULL::void; -- don't fold away functions called in previous query
1427
$_$;
1428
1429
1430
--
1431 12292 aaronmk
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1432 10150 aaronmk
--
1433
1434 12292 aaronmk
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
1435 12446 aaronmk
    LANGUAGE sql
1436 10150 aaronmk
    AS $_$
1437 12347 aaronmk
SELECT util.drop_relation('TABLE', $1, $2)
1438 10150 aaronmk
$_$;
1439
1440
1441
--
1442 12292 aaronmk
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1443 10150 aaronmk
--
1444
1445 12292 aaronmk
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
1446 12235 aaronmk
idempotent
1447
';
1448 10150 aaronmk
1449
1450
--
1451 12292 aaronmk
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1452 12229 aaronmk
--
1453
1454 12292 aaronmk
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
1455 12446 aaronmk
    LANGUAGE sql
1456 12229 aaronmk
    AS $_$
1457 12347 aaronmk
SELECT util.drop_relation('VIEW', $1, $2)
1458 12229 aaronmk
$_$;
1459
1460
1461
--
1462 12292 aaronmk
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
1463 12229 aaronmk
--
1464
1465 12292 aaronmk
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
1466 12235 aaronmk
idempotent
1467
';
1468 12229 aaronmk
1469
1470
--
1471 10322 aaronmk
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
1472
--
1473
1474
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
1475
    LANGUAGE sql IMMUTABLE
1476
    AS $_$
1477
SELECT util.array_fill($1, 0)
1478
$_$;
1479
1480
1481
--
1482
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1483
--
1484
1485 12235 aaronmk
COMMENT ON FUNCTION empty_array(elem_type_null anyelement) IS '
1486
constructs proper empty 1-dimensional array whose dimensions are not NULL ( ''{}''::text[] does not do this)
1487
';
1488 10322 aaronmk
1489
1490
--
1491 8183 aaronmk
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1492 8086 aaronmk
--
1493
1494
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
1495 10388 aaronmk
    LANGUAGE sql IMMUTABLE
1496 8086 aaronmk
    AS $_$
1497 8183 aaronmk
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
1498 8086 aaronmk
$_$;
1499
1500
1501
--
1502 10987 aaronmk
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
1503
--
1504
1505
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
1506
    LANGUAGE sql IMMUTABLE
1507
    AS $_$
1508
SELECT regexp_replace($2, '("?)$', $1||'\1')
1509
$_$;
1510
1511
1512
--
1513 13455 aaronmk
-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
1514
--
1515
1516
CREATE FUNCTION eval(queries text[]) RETURNS void
1517
    LANGUAGE sql
1518
    AS $_$
1519
SELECT util.eval(query) FROM unnest($1) query;
1520
SELECT NULL::void; -- don't fold away functions called in previous query
1521
$_$;
1522
1523
1524
--
1525 12531 aaronmk
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
1526 9824 aaronmk
--
1527
1528 12531 aaronmk
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
1529 12558 aaronmk
    LANGUAGE plpgsql
1530 9824 aaronmk
    AS $$
1531
BEGIN
1532 12531 aaronmk
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1533 12251 aaronmk
	EXECUTE sql;
1534 9824 aaronmk
END;
1535
$$;
1536
1537
1538
--
1539 12181 aaronmk
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1540
--
1541
1542
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
1543
    LANGUAGE plpgsql
1544
    AS $$
1545
BEGIN
1546 12251 aaronmk
	PERFORM util.debug_print_sql(sql);
1547 12181 aaronmk
	RETURN QUERY EXECUTE sql;
1548
END;
1549
$$;
1550
1551
1552
--
1553
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
1554
--
1555
1556 12235 aaronmk
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
1557
col_type_null (*required*): NULL::col_type
1558
';
1559 12181 aaronmk
1560
1561
--
1562 12301 aaronmk
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
1563
--
1564
1565
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
1566
    LANGUAGE plpgsql
1567
    AS $$
1568
BEGIN
1569
	PERFORM util.debug_print_sql(sql);
1570
	RETURN QUERY EXECUTE sql;
1571
END;
1572
$$;
1573
1574
1575
--
1576 12458 aaronmk
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
1577 10363 aaronmk
--
1578
1579 12458 aaronmk
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
1580 10363 aaronmk
    LANGUAGE plpgsql
1581
    AS $$
1582
BEGIN
1583 12458 aaronmk
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
1584 10363 aaronmk
	RETURN QUERY EXECUTE sql;
1585
END;
1586
$$;
1587
1588
1589
--
1590 10129 aaronmk
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1591 10128 aaronmk
--
1592
1593 10129 aaronmk
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1594 12514 aaronmk
    LANGUAGE plpgsql STABLE
1595 10128 aaronmk
    AS $$
1596
DECLARE
1597
	ret_val ret_type_null%TYPE;
1598
BEGIN
1599 12251 aaronmk
	PERFORM util.debug_print_sql(sql);
1600 10128 aaronmk
	EXECUTE sql INTO STRICT ret_val;
1601
	RETURN ret_val;
1602
END;
1603
$$;
1604
1605
1606
--
1607 10129 aaronmk
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1608 10128 aaronmk
--
1609
1610 12235 aaronmk
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
1611
ret_type_null: NULL::ret_type
1612
';
1613 10128 aaronmk
1614
1615
--
1616 10131 aaronmk
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1617
--
1618
1619
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1620
    LANGUAGE sql
1621
    AS $_$
1622 10132 aaronmk
SELECT util.eval2val($$SELECT $$||$1, $2)
1623 10131 aaronmk
$_$;
1624
1625
1626
--
1627
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1628
--
1629
1630 12235 aaronmk
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
1631
ret_type_null: NULL::ret_type
1632
';
1633 10131 aaronmk
1634
1635
--
1636 10133 aaronmk
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
1637
--
1638
1639
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1640
    LANGUAGE sql
1641
    AS $_$
1642
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1643
$_$;
1644
1645
1646
--
1647
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1648
--
1649
1650 12235 aaronmk
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
1651
sql: can be NULL, which will be passed through
1652
ret_type_null: NULL::ret_type
1653
';
1654 10133 aaronmk
1655
1656
--
1657 8183 aaronmk
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1658 8182 aaronmk
--
1659
1660
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1661 12446 aaronmk
    LANGUAGE sql STABLE
1662 8182 aaronmk
    AS $_$
1663
SELECT col_name
1664
FROM unnest($2) s (col_name)
1665 8183 aaronmk
WHERE util.col_exists(($1, col_name))
1666 8182 aaronmk
$_$;
1667
1668
1669
--
1670 11830 aaronmk
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
1671
--
1672
1673
CREATE FUNCTION explain(sql text) RETURNS SETOF text
1674
    LANGUAGE sql
1675
    AS $_$
1676 12459 aaronmk
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
1677 11830 aaronmk
$_$;
1678
1679
1680
--
1681 11833 aaronmk
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
1682
--
1683
1684
CREATE FUNCTION explain2notice(sql text) RETURNS void
1685 12449 aaronmk
    LANGUAGE sql
1686
    AS $_$
1687 12534 aaronmk
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
1688 12449 aaronmk
$_$;
1689 12448 aaronmk
1690
1691
--
1692
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
1693
--
1694
1695
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
1696
    LANGUAGE sql
1697
    AS $_$
1698 12464 aaronmk
-- newline before and after to visually separate it from other debug info
1699 12756 aaronmk
SELECT COALESCE($$
1700 12464 aaronmk
EXPLAIN:
1701 12756 aaronmk
$$||util.fold_explain_msg(util.explain2str($1))||$$
1702
$$, '')
1703 11833 aaronmk
$_$;
1704
1705
1706
--
1707 12452 aaronmk
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
1708
--
1709
1710
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
1711 13403 aaronmk
    LANGUAGE plpgsql
1712
    AS $$
1713
BEGIN
1714
	RETURN util.explain2notice_msg(sql);
1715
EXCEPTION
1716
WHEN syntax_error THEN RETURN NULL; -- non-explainable query
1717
	/* don't use util.is_explainable() because the list provided by Postgres
1718
	(http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691)
1719
	excludes some query types that are in fact EXPLAIN-able */
1720
END;
1721
$$;
1722 12452 aaronmk
1723
1724
--
1725 11832 aaronmk
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
1726
--
1727
1728
CREATE FUNCTION explain2str(sql text) RETURNS text
1729
    LANGUAGE sql
1730
    AS $_$
1731
SELECT util.join_strs(explain, $$
1732
$$) FROM util.explain($1)
1733
$_$;
1734
1735
1736 11835 aaronmk
SET default_tablespace = '';
1737
1738
SET default_with_oids = false;
1739
1740 11832 aaronmk
--
1741 11835 aaronmk
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace:
1742 11831 aaronmk
--
1743
1744 11835 aaronmk
CREATE TABLE explain (
1745
    line text NOT NULL
1746
);
1747
1748
1749
--
1750
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
1751
--
1752
1753
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
1754 11831 aaronmk
    LANGUAGE sql
1755
    AS $_$
1756 11835 aaronmk
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
1757
$$||quote_nullable($1)||$$
1758 11831 aaronmk
)$$)
1759
$_$;
1760
1761
1762
--
1763 11836 aaronmk
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
1764
--
1765
1766 12235 aaronmk
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
1767
usage:
1768 11836 aaronmk
PERFORM util.explain2table($$
1769
query
1770 12235 aaronmk
$$);
1771
';
1772 11836 aaronmk
1773
1774
--
1775 12450 aaronmk
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
1776
--
1777
1778
CREATE FUNCTION first_word(str text) RETURNS text
1779
    LANGUAGE sql IMMUTABLE
1780
    AS $_$
1781 12461 aaronmk
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
1782 12450 aaronmk
$_$;
1783
1784
1785
--
1786 10323 aaronmk
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1787
--
1788
1789
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1790 10355 aaronmk
    LANGUAGE sql IMMUTABLE
1791 10323 aaronmk
    AS $_$
1792 10355 aaronmk
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1793
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1794
) END
1795 10323 aaronmk
$_$;
1796
1797
1798
--
1799
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1800
--
1801
1802 12235 aaronmk
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
1803
ensures that an array will always have proper non-NULL dimensions
1804
';
1805 10323 aaronmk
1806
1807
--
1808 12755 aaronmk
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
1809
--
1810
1811
CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
1812
    LANGUAGE sql IMMUTABLE
1813
    AS $_$
1814
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
1815
$_$;
1816
1817
1818
--
1819 11695 aaronmk
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
1820
--
1821
1822
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
1823
    LANGUAGE plpgsql
1824
    AS $_$
1825
DECLARE
1826
	PG_EXCEPTION_DETAIL text;
1827 13488 aaronmk
	restore_views_info util.restore_views_info;
1828 11695 aaronmk
BEGIN
1829 13488 aaronmk
	restore_views_info = util.save_drop_views(users);
1830 11695 aaronmk
	PERFORM util.eval(cmd);
1831 13487 aaronmk
	PERFORM util.restore_views(restore_views_info);
1832 11695 aaronmk
EXCEPTION
1833
WHEN dependent_objects_still_exist THEN
1834
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
1835
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
1836 13484 aaronmk
	users = array(SELECT * FROM util.regexp_matches_group(
1837
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
1838
		-- will be in forward dependency order
1839 13450 aaronmk
	PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
1840
	PERFORM util.debug_print_var('users', users);
1841 11695 aaronmk
	IF util.is_empty(users) THEN RAISE; END IF;
1842
	PERFORM util.force_recreate(cmd, users);
1843
END;
1844
$_$;
1845
1846
1847
--
1848
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
1849
--
1850
1851 12235 aaronmk
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
1852
idempotent
1853 11695 aaronmk
1854 12235 aaronmk
users: not necessary to provide this because it will be autopopulated
1855
';
1856 11695 aaronmk
1857
1858
--
1859 8321 aaronmk
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1860
--
1861
1862
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1863
    LANGUAGE plpgsql STRICT
1864
    AS $_$
1865
DECLARE
1866
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1867
$$||query;
1868
BEGIN
1869
	EXECUTE mk_view;
1870
EXCEPTION
1871
WHEN invalid_table_definition THEN
1872 8323 aaronmk
	IF SQLERRM = 'cannot drop columns from view'
1873
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1874
	THEN
1875 8321 aaronmk
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1876
		EXECUTE mk_view;
1877
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1878
	END IF;
1879
END;
1880
$_$;
1881
1882
1883
--
1884
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1885
--
1886
1887 12235 aaronmk
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
1888
idempotent
1889
';
1890 8321 aaronmk
1891
1892
--
1893 12654 aaronmk
-- Name: freq_always_1(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1894
--
1895
1896
CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
1897
    LANGUAGE sql STABLE
1898
    AS $_$
1899
SELECT util.eval2val(
1900
$$SELECT NOT EXISTS( -- there is no row that is != 1
1901
	SELECT NULL
1902
	FROM $$||$1||$$
1903
	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
1904
	LIMIT 1
1905
)
1906
$$, NULL::boolean)
1907
$_$;
1908
1909
1910
--
1911 12661 aaronmk
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
1912
--
1913
1914
CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
1915
    LANGUAGE sql STABLE
1916
    AS $_$
1917
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
1918
$_$;
1919
1920
1921
--
1922 11655 aaronmk
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
1923
--
1924
1925
CREATE FUNCTION grants_users() RETURNS SETOF text
1926
    LANGUAGE sql IMMUTABLE
1927
    AS $$
1928
VALUES ('bien_read'), ('public_')
1929
$$;
1930
1931
1932
--
1933 8183 aaronmk
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1934 8085 aaronmk
--
1935
1936
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1937 10388 aaronmk
    LANGUAGE sql IMMUTABLE
1938 8085 aaronmk
    AS $_$
1939
SELECT substring($2 for length($1)) = $1
1940
$_$;
1941
1942
1943
--
1944 12296 aaronmk
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
1945
--
1946
1947
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
1948
    LANGUAGE sql STABLE
1949
    AS $_$
1950
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
1951
$_$;
1952
1953
1954
--
1955 10307 aaronmk
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1956
--
1957
1958
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1959
    LANGUAGE sql IMMUTABLE
1960
    AS $_$
1961 10324 aaronmk
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1962 10307 aaronmk
$_$;
1963
1964
1965
--
1966
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1967
--
1968
1969 12235 aaronmk
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
1970
avoids repeating the same value for each key
1971
';
1972 10307 aaronmk
1973
1974
--
1975 12218 aaronmk
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
1976
--
1977
1978
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
1979
    LANGUAGE sql IMMUTABLE
1980
    AS $_$
1981 12222 aaronmk
SELECT COALESCE($1, $2)
1982 12218 aaronmk
$_$;
1983
1984
1985
--
1986
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
1987
--
1988
1989 12235 aaronmk
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
1990
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
1991
';
1992 12218 aaronmk
1993
1994
--
1995 13452 aaronmk
-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
1996
--
1997
1998
CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
1999
    LANGUAGE sql IMMUTABLE
2000
    AS $_$
2001 13490 aaronmk
SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
2002 13452 aaronmk
$_$;
2003
2004
2005
--
2006 12285 aaronmk
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
2007
--
2008
2009
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
2010
    LANGUAGE sql
2011
    AS $_$
2012
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
2013
$_$;
2014
2015
2016
--
2017 13136 aaronmk
-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2018
--
2019
2020
CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
2021
    LANGUAGE plpgsql IMMUTABLE
2022
    AS $$
2023
BEGIN
2024
	PERFORM util.cast(value, ret_type_null);
2025 13139 aaronmk
	-- must happen *after* cast check, because NULL is not valid for some types
2026
	IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
2027 13136 aaronmk
	RETURN true;
2028
EXCEPTION
2029
WHEN data_exception THEN RETURN false;
2030
END;
2031
$$;
2032
2033
2034
--
2035
-- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
2036
--
2037
2038
COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
2039 13139 aaronmk
passes NULL through. however, if NULL is not valid for the type, false will be
2040
returned instead.
2041
2042 13136 aaronmk
ret_type_null: NULL::ret_type
2043
';
2044
2045
2046
--
2047 10137 aaronmk
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
2048
--
2049
2050
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
2051 12446 aaronmk
    LANGUAGE sql STABLE
2052 10137 aaronmk
    AS $_$
2053 12789 aaronmk
SELECT COALESCE(util.col_comment($1) LIKE '
2054
constant
2055
%', false)
2056 10137 aaronmk
$_$;
2057
2058
2059
--
2060 11659 aaronmk
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2061
--
2062
2063
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
2064
    LANGUAGE sql IMMUTABLE
2065
    AS $_$
2066
SELECT util.array_length($1) = 0
2067
$_$;
2068
2069
2070
--
2071 12457 aaronmk
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
2072
--
2073
2074
CREATE FUNCTION is_explain(sql text) RETURNS boolean
2075
    LANGUAGE sql IMMUTABLE
2076
    AS $_$
2077
SELECT upper(util.first_word($1)) = 'EXPLAIN'
2078
$_$;
2079
2080
2081
--
2082 12451 aaronmk
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
2083
--
2084
2085
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
2086
    LANGUAGE sql IMMUTABLE
2087
    AS $_$
2088
SELECT upper(util.first_word($1)) = ANY(
2089
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
2090
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
2091
)
2092
$_$;
2093
2094
2095
--
2096 10391 aaronmk
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2097
--
2098
2099
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
2100
    LANGUAGE sql IMMUTABLE
2101
    AS $_$
2102
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
2103
$_$;
2104
2105
2106
--
2107 10613 aaronmk
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
2108
--
2109
2110
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
2111
    LANGUAGE sql IMMUTABLE
2112
    AS $_$
2113
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
2114
$_$;
2115
2116
2117
--
2118 12480 aaronmk
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
2119
--
2120
2121
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
2122
    LANGUAGE sql IMMUTABLE
2123
    AS $_$
2124
SELECT upper(util.first_word($1)) = 'SET'
2125
$_$;
2126
2127
2128
--
2129 12330 aaronmk
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
2130
--
2131
2132
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
2133 12332 aaronmk
    LANGUAGE sql STABLE
2134 12330 aaronmk
    AS $_$
2135
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
2136
$_$;
2137
2138
2139
--
2140 12329 aaronmk
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
2141
--
2142
2143
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
2144 12332 aaronmk
    LANGUAGE sql STABLE
2145 12329 aaronmk
    AS $_$
2146
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
2147
$_$;
2148
2149
2150
--
2151 8183 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
2152 4009 aaronmk
--
2153
2154 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
2155 12444 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
2156 4009 aaronmk
    AS $_$
2157 4054 aaronmk
SELECT $1 || $3 || $2
2158 2595 aaronmk
$_$;
2159
2160
2161
--
2162 12444 aaronmk
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
2163
--
2164
2165
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
2166
must be declared STRICT to use the special handling of STRICT aggregating functions
2167
';
2168
2169
2170
--
2171 12436 aaronmk
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
2172
--
2173
2174
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
2175
    LANGUAGE sql IMMUTABLE
2176
    AS $_$
2177
SELECT $1 -- compare on the entire value
2178
$_$;
2179
2180
2181
--
2182 10989 aaronmk
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
2183 10985 aaronmk
--
2184
2185 10989 aaronmk
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
2186 10985 aaronmk
    LANGUAGE sql IMMUTABLE
2187
    AS $_$
2188 10989 aaronmk
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
2189 10985 aaronmk
$_$;
2190
2191
2192
--
2193 13384 aaronmk
-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2194
--
2195
2196
CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
2197
    LANGUAGE plpgsql
2198
    AS $$
2199
DECLARE
2200
	errors_ct integer = 0;
2201
	loop_var loop_type_null%TYPE;
2202
BEGIN
2203
	FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
2204
	LOOP
2205
		BEGIN
2206
			EXECUTE loop_body_sql USING loop_var;
2207
		EXCEPTION
2208
		WHEN OTHERS THEN
2209
			errors_ct = errors_ct+1;
2210
			PERFORM util.raise_error_warning(SQLERRM);
2211
		END;
2212
	END LOOP;
2213
	IF errors_ct > 0 THEN
2214
		-- can't raise exception because this would roll back the transaction
2215
		PERFORM util.raise_error_warning('there were '||errors_ct
2216
			||' errors: see the WARNINGs for details');
2217
	END IF;
2218
END;
2219
$$;
2220
2221
2222
--
2223 12275 aaronmk
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
2224
--
2225
2226
CREATE FUNCTION ltrim_nl(str text) RETURNS text
2227
    LANGUAGE sql IMMUTABLE
2228
    AS $_$
2229
SELECT ltrim($1, $$
2230
$$)
2231
$_$;
2232
2233
2234
--
2235 10110 aaronmk
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
2236
--
2237
2238
CREATE FUNCTION map_filter_insert() RETURNS trigger
2239
    LANGUAGE plpgsql
2240
    AS $$
2241
BEGIN
2242
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
2243
	RETURN new;
2244
END;
2245
$$;
2246
2247
2248
--
2249 8183 aaronmk
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2250 8146 aaronmk
--
2251
2252
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
2253
    LANGUAGE plpgsql STABLE STRICT
2254
    AS $_$
2255
DECLARE
2256
    value text;
2257
BEGIN
2258
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
2259 8149 aaronmk
        INTO value USING key;
2260 8146 aaronmk
    RETURN value;
2261
END;
2262
$_$;
2263
2264
2265
--
2266 10358 aaronmk
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
2267 10325 aaronmk
--
2268
2269 10358 aaronmk
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
2270 10353 aaronmk
    LANGUAGE sql IMMUTABLE
2271 10325 aaronmk
    AS $_$
2272 10374 aaronmk
SELECT util._map(util.nulls_map($1), $2)
2273 10325 aaronmk
$_$;
2274
2275
2276
--
2277 10359 aaronmk
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
2278
--
2279
2280 12235 aaronmk
COMMENT ON FUNCTION map_nulls(nulls text[], value anyelement) IS '
2281
due to dynamic inlining[1], this is just as fast as util._map() which it wraps[2].
2282 10359 aaronmk
2283
[1] inlining of function calls, which is different from constant folding
2284
[2] _map()''s profiling query
2285
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
2286
and map_nulls()''s profiling query
2287
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
2288 10375 aaronmk
both take ~920 ms.
2289 12235 aaronmk
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.
2290
';
2291 10359 aaronmk
2292
2293
--
2294 8183 aaronmk
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
2295 8150 aaronmk
--
2296
2297
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
2298
    LANGUAGE plpgsql STABLE STRICT
2299
    AS $_$
2300
BEGIN
2301
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
2302
END;
2303
$_$;
2304
2305
2306
--
2307 12228 aaronmk
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
2308
--
2309
2310 12262 aaronmk
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
2311 12228 aaronmk
    LANGUAGE sql
2312
    AS $_$
2313 12262 aaronmk
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
2314 12321 aaronmk
$$||util.ltrim_nl($2));
2315
-- make sure the created table has the correct estimated row count
2316
SELECT util.analyze_($1);
2317 12470 aaronmk
2318
SELECT util.append_comment($1, '
2319
contents generated from:
2320 13397 aaronmk
'||util.ltrim_nl(util.runnable_sql($2))||';
2321 12470 aaronmk
');
2322 12228 aaronmk
$_$;
2323
2324
2325
--
2326 12262 aaronmk
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
2327 12228 aaronmk
--
2328
2329 12262 aaronmk
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
2330 12235 aaronmk
idempotent
2331
';
2332 12228 aaronmk
2333
2334
--
2335 12234 aaronmk
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
2336
--
2337
2338 12262 aaronmk
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
2339 12234 aaronmk
    LANGUAGE sql
2340
    AS $_$
2341
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
2342
$_$;
2343
2344
2345
--
2346 12262 aaronmk
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
2347 12234 aaronmk
--
2348
2349 12262 aaronmk
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
2350 12235 aaronmk
idempotent
2351
';
2352 12234 aaronmk
2353
2354
--
2355 8190 aaronmk
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
2356
--
2357
2358
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
2359 12446 aaronmk
    LANGUAGE sql
2360 8190 aaronmk
    AS $_$
2361 10135 aaronmk
SELECT util.create_if_not_exists($$
2362
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
2363 8190 aaronmk
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
2364 10135 aaronmk
||quote_literal($2)||$$;
2365 12235 aaronmk
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
2366
constant
2367
';
2368 10135 aaronmk
$$)
2369 8190 aaronmk
$_$;
2370
2371
2372
--
2373
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
2374
--
2375
2376 12235 aaronmk
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
2377
idempotent
2378
';
2379 8190 aaronmk
2380
2381
--
2382 10296 aaronmk
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
2383 8187 aaronmk
--
2384
2385 10296 aaronmk
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
2386 8187 aaronmk
    LANGUAGE plpgsql STRICT
2387
    AS $_$
2388
DECLARE
2389
    type regtype = util.typeof(expr, col.table_::text::regtype);
2390
    col_name_sql text = quote_ident(col.name);
2391
BEGIN
2392 10296 aaronmk
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
2393
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
2394 8187 aaronmk
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
2395
$$||expr||$$;
2396
$$);
2397
END;
2398
$_$;
2399
2400
2401
--
2402 10296 aaronmk
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
2403 8188 aaronmk
--
2404
2405 12235 aaronmk
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
2406
idempotent
2407
';
2408 8188 aaronmk
2409
2410
--
2411 12554 aaronmk
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
2412 12475 aaronmk
--
2413
2414 12554 aaronmk
CREATE FUNCTION mk_diff_query(left_ text, right_ text, cols text DEFAULT 'left_, right_'::text, join_cond text DEFAULT 'left_ %== right_ -- refer to EXPLAIN output for expansion of %=='::text, filter text DEFAULT 'left_ IS DISTINCT FROM right_'::text) RETURNS text
2415 12475 aaronmk
    LANGUAGE sql IMMUTABLE
2416
    AS $_$
2417
SELECT
2418 12478 aaronmk
$$SELECT
2419 12554 aaronmk
$$||$3||$$
2420 12555 aaronmk
FROM      $$||$1||$$ left_
2421 12554 aaronmk
FULL JOIN $$||$2||$$ right_
2422
ON $$||$4||$$
2423
WHERE $$||$5||$$
2424 12475 aaronmk
ORDER BY left_, right_
2425
$$
2426
$_$;
2427
2428
2429
--
2430 12564 aaronmk
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
2431
--
2432
2433
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
2434 12591 aaronmk
    LANGUAGE sql
2435 12564 aaronmk
    AS $_$
2436 12570 aaronmk
-- keys()
2437 12564 aaronmk
SELECT util.mk_keys_func($1, ARRAY(
2438
SELECT col FROM util.typed_cols($1) col
2439
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
2440 12570 aaronmk
));
2441
2442 12571 aaronmk
-- values_()
2443 12570 aaronmk
SELECT util.mk_keys_func($1, COALESCE(
2444
	NULLIF(ARRAY(
2445
	SELECT col FROM util.typed_cols($1) col
2446
	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
2447
	), ARRAY[]::util.col_cast[])
2448
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
2449 12571 aaronmk
, 'values_');
2450 12564 aaronmk
$_$;
2451
2452
2453
--
2454 12569 aaronmk
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
2455 12561 aaronmk
--
2456
2457 12569 aaronmk
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
2458 12591 aaronmk
    LANGUAGE sql
2459 12561 aaronmk
    AS $_$
2460 12567 aaronmk
SELECT util.create_if_not_exists($$
2461 12577 aaronmk
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
2462
($$||util.mk_typed_cols_list($2)||$$);
2463 12671 aaronmk
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
2464
autogenerated
2465
';
2466 12594 aaronmk
$$);
2467 12577 aaronmk
2468 12594 aaronmk
SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
2469
$_$;
2470
2471
2472
--
2473
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
2474
--
2475
2476
CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
2477
    LANGUAGE sql
2478
    AS $_$
2479
SELECT util.create_if_not_exists($$
2480 12581 aaronmk
CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
2481 12577 aaronmk
||util.qual_name($1)||$$)
2482 12594 aaronmk
  RETURNS $$||util.qual_name($2)||$$ AS
2483 12561 aaronmk
$BODY1$
2484 12577 aaronmk
SELECT ROW($$||
2485 12594 aaronmk
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
2486
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
2487 12561 aaronmk
$BODY1$
2488
  LANGUAGE sql IMMUTABLE
2489
  COST 100;
2490 12594 aaronmk
$$);
2491 12561 aaronmk
$_$;
2492
2493
2494
--
2495 8183 aaronmk
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
2496 8139 aaronmk
--
2497
2498
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
2499 12446 aaronmk
    LANGUAGE sql
2500 8139 aaronmk
    AS $_$
2501 8183 aaronmk
SELECT util.create_if_not_exists($$
2502 8141 aaronmk
CREATE TABLE $$||$1||$$
2503 8139 aaronmk
(
2504 8183 aaronmk
    LIKE util.map INCLUDING ALL
2505 10110 aaronmk
);
2506
2507
CREATE TRIGGER map_filter_insert
2508
  BEFORE INSERT
2509
  ON $$||$1||$$
2510
  FOR EACH ROW
2511
  EXECUTE PROCEDURE util.map_filter_insert();
2512 8141 aaronmk
$$)
2513 8139 aaronmk
$_$;
2514
2515
2516
--
2517 12725 aaronmk
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
2518
--
2519
2520
CREATE FUNCTION mk_not_null(text) RETURNS text
2521
    LANGUAGE sql IMMUTABLE
2522
    AS $_$
2523
SELECT COALESCE($1, '<NULL>')
2524
$_$;
2525
2526
2527
--
2528 12556 aaronmk
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2529
--
2530
2531
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
2532
    LANGUAGE sql IMMUTABLE
2533
    AS $_$
2534 12559 aaronmk
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
2535
util.qual_name((unnest).type), ''), '')
2536 12556 aaronmk
FROM unnest($1)
2537
$_$;
2538
2539
2540
--
2541 12236 aaronmk
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
2542
--
2543
2544
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
2545
    LANGUAGE sql IMMUTABLE
2546
    AS $_$
2547 12486 aaronmk
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
2548 12236 aaronmk
$_$;
2549
2550
2551
--
2552 12486 aaronmk
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
2553
--
2554
2555
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
2556
auto-appends util to the search_path to enable use of util operators
2557
';
2558
2559
2560
--
2561 13474 aaronmk
-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2562
--
2563
2564
CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
2565
    LANGUAGE sql STABLE
2566
    AS $_$
2567 13483 aaronmk
SELECT COALESCE($$COMMENT ON $$||util.relation_type($1)||$$ $$||$1||$$ IS $$
2568 13481 aaronmk
||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
2569 13474 aaronmk
$_$;
2570
2571
2572
--
2573 12467 aaronmk
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
2574
--
2575
2576
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
2577
    LANGUAGE sql IMMUTABLE
2578
    AS $_$
2579
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
2580
$_$;
2581
2582
2583
--
2584 12466 aaronmk
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
2585 12270 aaronmk
--
2586
2587 12466 aaronmk
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
2588 12270 aaronmk
    LANGUAGE sql IMMUTABLE
2589
    AS $_$
2590 12432 aaronmk
/* debug_print_return_value() needed because this function is used with EXECUTE
2591
rather than util.eval() (in order to affect the calling function), so the
2592
search_path would not otherwise be printed */
2593 12487 aaronmk
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
2594
||$$ search_path TO $$||$1
2595 12270 aaronmk
$_$;
2596
2597
2598
--
2599 10113 aaronmk
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
2600
--
2601
2602
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
2603 12446 aaronmk
    LANGUAGE sql
2604 10113 aaronmk
    AS $_$
2605 12240 aaronmk
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
2606 10113 aaronmk
$_$;
2607
2608
2609
--
2610
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
2611
--
2612
2613 12235 aaronmk
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
2614
idempotent
2615
';
2616 10113 aaronmk
2617
2618
--
2619 11011 aaronmk
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2620
--
2621
2622
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
2623
    LANGUAGE plpgsql STRICT
2624
    AS $_$
2625
DECLARE
2626
	view_qual_name text = util.qual_name(view_);
2627
BEGIN
2628
	EXECUTE $$
2629
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2630
  RETURNS SETOF $$||view_||$$ AS
2631
$BODY1$
2632
SELECT * FROM $$||view_qual_name||$$
2633
ORDER BY sort_col
2634
LIMIT $1 OFFSET $2
2635
$BODY1$
2636
  LANGUAGE sql STABLE
2637
  COST 100
2638
  ROWS 1000
2639
$$;
2640
2641
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2642
END;
2643
$_$;
2644
2645
2646
--
2647 8325 aaronmk
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2648
--
2649
2650
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
2651
    LANGUAGE plpgsql STRICT
2652
    AS $_$
2653 10990 aaronmk
DECLARE
2654
	view_qual_name text = util.qual_name(view_);
2655
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
2656 8325 aaronmk
BEGIN
2657
	EXECUTE $$
2658 10990 aaronmk
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
2659
  RETURNS integer AS
2660
$BODY1$
2661
SELECT $$||quote_ident(row_num_col)||$$
2662
FROM $$||view_qual_name||$$
2663
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
2664
LIMIT 1
2665
$BODY1$
2666
  LANGUAGE sql STABLE
2667
  COST 100;
2668
$$;
2669
2670
	EXECUTE $$
2671 8325 aaronmk
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2672
  RETURNS SETOF $$||view_||$$ AS
2673
$BODY1$
2674 10990 aaronmk
SELECT * FROM $$||view_qual_name||$$
2675
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
2676
	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
2677
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
2678 10991 aaronmk
ORDER BY $$||quote_ident(row_num_col)||$$
2679 8325 aaronmk
$BODY1$
2680
  LANGUAGE sql STABLE
2681
  COST 100
2682
  ROWS 1000
2683
$$;
2684 11010 aaronmk
2685
	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
2686
END;
2687
$_$;
2688
2689
2690
--
2691
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
2692
--
2693
2694
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
2695
    LANGUAGE plpgsql STRICT
2696
    AS $_$
2697
DECLARE
2698
	view_qual_name text = util.qual_name(view_);
2699
BEGIN
2700 8326 aaronmk
	EXECUTE $$
2701
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
2702
  RETURNS SETOF $$||view_||$$
2703
  SET enable_sort TO 'off'
2704
  AS
2705
$BODY1$
2706 10990 aaronmk
SELECT * FROM $$||view_qual_name||$$($2, $3)
2707 8326 aaronmk
$BODY1$
2708
  LANGUAGE sql STABLE
2709
  COST 100
2710
  ROWS 1000
2711
;
2712
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
2713
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
2714
If you want to run EXPLAIN and get expanded output, use the regular subset
2715
function instead. (When a config param is set on a function, EXPLAIN produces
2716
just a function scan.)
2717
';
2718
$$;
2719 8325 aaronmk
END;
2720
$_$;
2721
2722
2723
--
2724 11010 aaronmk
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
2725
--
2726
2727 12235 aaronmk
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
2728
creates subset function which turns off enable_sort
2729
';
2730 11010 aaronmk
2731
2732
--
2733 12576 aaronmk
-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
2734
--
2735
2736
CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
2737
    LANGUAGE sql IMMUTABLE
2738
    AS $_$
2739 12579 aaronmk
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
2740 12576 aaronmk
util.qual_name((unnest).type), ', '), '')
2741
FROM unnest($1)
2742
$_$;
2743
2744
2745
--
2746 12242 aaronmk
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
2747
--
2748
2749
CREATE FUNCTION name(table_ regclass) RETURNS text
2750
    LANGUAGE sql STABLE
2751
    AS $_$
2752
SELECT relname::text FROM pg_class WHERE oid = $1
2753
$_$;
2754
2755
2756
--
2757 8183 aaronmk
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
2758 8083 aaronmk
--
2759
2760
CREATE FUNCTION name(type regtype) RETURNS text
2761 12446 aaronmk
    LANGUAGE sql STABLE
2762 8083 aaronmk
    AS $_$
2763
SELECT typname::text FROM pg_type WHERE oid = $1
2764
$_$;
2765
2766
2767
--
2768 12360 aaronmk
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
2769 12355 aaronmk
--
2770
2771 12360 aaronmk
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
2772 12355 aaronmk
    LANGUAGE sql IMMUTABLE
2773
    AS $_$
2774 12360 aaronmk
SELECT octet_length($1) >= util.namedatalen() - $2
2775 12355 aaronmk
$_$;
2776
2777
2778
--
2779 12354 aaronmk
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
2780
--
2781
2782
CREATE FUNCTION namedatalen() RETURNS integer
2783
    LANGUAGE sql IMMUTABLE
2784
    AS $$
2785
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
2786
$$;
2787
2788
2789
--
2790 9958 aaronmk
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
2791
--
2792
2793
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
2794
    LANGUAGE sql IMMUTABLE
2795
    AS $_$
2796 10329 aaronmk
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
2797 9958 aaronmk
$_$;
2798
2799
2800
--
2801 9956 aaronmk
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
2802
--
2803
2804
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
2805 9957 aaronmk
    LANGUAGE sql IMMUTABLE
2806 9956 aaronmk
    AS $_$
2807
SELECT $1 IS NOT NULL
2808
$_$;
2809
2810
2811
--
2812 10373 aaronmk
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
2813
--
2814
2815
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
2816
    LANGUAGE sql IMMUTABLE
2817
    AS $_$
2818
SELECT util.hstore($1, NULL) || '*=>*'
2819
$_$;
2820
2821
2822
--
2823
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
2824
--
2825
2826 12235 aaronmk
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
2827
for use with _map()
2828
';
2829 10373 aaronmk
2830
2831
--
2832 10989 aaronmk
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
2833 10984 aaronmk
--
2834
2835 10989 aaronmk
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
2836 10984 aaronmk
    LANGUAGE sql IMMUTABLE
2837
    AS $_$
2838 10989 aaronmk
SELECT $2 + COALESCE($1, 0)
2839 10984 aaronmk
$_$;
2840
2841
2842
--
2843 12659 aaronmk
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
2844
--
2845
2846
CREATE FUNCTION parent(table_ regclass) RETURNS regclass
2847
    LANGUAGE sql STABLE
2848
    AS $_$
2849
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
2850
$_$;
2851
2852
2853
--
2854 12651 aaronmk
-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2855
--
2856
2857
CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
2858
    LANGUAGE sql
2859
    AS $_$
2860
SELECT util.eval($$INSERT INTO $$||$1||$$
2861
$$||util.ltrim_nl($2));
2862
-- make sure the created table has the correct estimated row count
2863
SELECT util.analyze_($1);
2864
$_$;
2865
2866
2867
--
2868 12575 aaronmk
-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
2869
--
2870
2871
CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
2872
    LANGUAGE sql IMMUTABLE
2873
    AS $_$
2874
SELECT util.qual_name(util.schema($2), $1||util.name($2))
2875
$_$;
2876
2877
2878
--
2879 12494 aaronmk
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
2880
--
2881
2882
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
2883
    LANGUAGE sql
2884
    AS $_$
2885
SELECT util.set_comment($1, concat($2, util.comment($1)))
2886
$_$;
2887
2888
2889
--
2890
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
2891
--
2892
2893
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
2894
comment: must start and end with a newline
2895
';
2896
2897
2898
--
2899 12260 aaronmk
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
2900
--
2901
2902
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
2903
    LANGUAGE sql IMMUTABLE
2904
    AS $_$
2905
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
2906
$_$;
2907
2908
2909
--
2910 10988 aaronmk
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
2911
--
2912
2913
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
2914 12446 aaronmk
    LANGUAGE sql STABLE
2915 12267 aaronmk
    SET search_path TO pg_temp
2916 10988 aaronmk
    AS $_$
2917 12267 aaronmk
SELECT $1::text
2918 10988 aaronmk
$_$;
2919
2920
2921
--
2922 12267 aaronmk
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
2923
--
2924
2925
CREATE FUNCTION qual_name(type regtype) RETURNS text
2926 12446 aaronmk
    LANGUAGE sql STABLE
2927 12267 aaronmk
    SET search_path TO pg_temp
2928
    AS $_$
2929
SELECT $1::text
2930
$_$;
2931
2932
2933
--
2934
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
2935
--
2936
2937
COMMENT ON FUNCTION qual_name(type regtype) IS '
2938
a type''s schema-qualified name
2939
';
2940
2941
2942
--
2943 12268 aaronmk
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
2944
--
2945
2946
CREATE FUNCTION qual_name(type unknown) RETURNS text
2947 12446 aaronmk
    LANGUAGE sql STABLE
2948 12268 aaronmk
    AS $_$
2949
SELECT util.qual_name($1::text::regtype)
2950
$_$;
2951
2952
2953
--
2954 12376 aaronmk
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
2955
--
2956
2957
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
2958
    LANGUAGE sql IMMUTABLE
2959
    AS $_$
2960
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
2961
$_$;
2962
2963
2964
--
2965
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
2966
--
2967
2968
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
2969
    LANGUAGE sql IMMUTABLE
2970
    AS $_$
2971
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
2972
$_$;
2973
2974
2975
--
2976 12371 aaronmk
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
2977
--
2978
2979
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
2980
    LANGUAGE sql IMMUTABLE
2981
    AS $_$
2982 12437 aaronmk
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
2983 12371 aaronmk
$_$;
2984
2985
2986
--
2987 12530 aaronmk
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
2988
--
2989
2990
CREATE FUNCTION raise(type text, msg text) RETURNS void
2991
    LANGUAGE sql IMMUTABLE
2992 12560 aaronmk
    AS $_X$
2993 12530 aaronmk
SELECT util.eval($$
2994
CREATE OR REPLACE FUNCTION pg_temp.__raise()
2995
  RETURNS void AS
2996 12560 aaronmk
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
2997
$__BODY1$
2998 12530 aaronmk
BEGIN
2999
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
3000
END;
3001 12560 aaronmk
$__BODY1$
3002 12530 aaronmk
  LANGUAGE plpgsql IMMUTABLE
3003
  COST 100;
3004 12532 aaronmk
$$, verbose_ := false);
3005 12530 aaronmk
3006 12532 aaronmk
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
3007 12560 aaronmk
$_X$;
3008 12530 aaronmk
3009
3010
--
3011 12533 aaronmk
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
3012
--
3013
3014
COMMENT ON FUNCTION raise(type text, msg text) IS '
3015
type: a log level from
3016
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
3017
or a condition name from
3018
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
3019
';
3020
3021
3022
--
3023 12536 aaronmk
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
3024 12311 aaronmk
--
3025
3026 12536 aaronmk
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
3027 12441 aaronmk
    LANGUAGE sql IMMUTABLE
3028 12311 aaronmk
    AS $_$
3029 12536 aaronmk
SELECT util.raise('WARNING', 'ERROR:  '||$1)
3030 12311 aaronmk
$_$;
3031
3032
3033
--
3034 10116 aaronmk
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
3035
--
3036
3037
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
3038
    LANGUAGE plpgsql IMMUTABLE STRICT
3039
    AS $$
3040
BEGIN
3041
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
3042
END;
3043
$$;
3044
3045
3046
--
3047 11657 aaronmk
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3048
--
3049
3050
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
3051
    LANGUAGE sql IMMUTABLE
3052
    AS $_$
3053
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
3054
$_$;
3055
3056
3057
--
3058 12333 aaronmk
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
3059
--
3060
3061
CREATE FUNCTION regexp_quote(str text) RETURNS text
3062
    LANGUAGE sql IMMUTABLE
3063
    AS $_$
3064
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
3065
$_$;
3066
3067
3068
--
3069 12375 aaronmk
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
3070
--
3071
3072
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
3073
    LANGUAGE sql IMMUTABLE
3074
    AS $_$
3075
SELECT (CASE WHEN right($1, 1) = ')'
3076 12377 aaronmk
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
3077 12375 aaronmk
$_$;
3078
3079
3080
--
3081 12344 aaronmk
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
3082
--
3083
3084
CREATE FUNCTION relation_type(relation regclass) RETURNS text
3085
    LANGUAGE sql STABLE
3086
    AS $_$
3087
SELECT util.relation_type(util.relation_type_char($1))
3088
$_$;
3089
3090
3091
--
3092 12340 aaronmk
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
3093 12339 aaronmk
--
3094
3095 12340 aaronmk
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
3096 12339 aaronmk
    LANGUAGE sql IMMUTABLE
3097
    AS $_$
3098 12593 aaronmk
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
3099 12339 aaronmk
$_$;
3100
3101
3102
--
3103 12588 aaronmk
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
3104
--
3105
3106
CREATE FUNCTION relation_type(type regtype) RETURNS text
3107
    LANGUAGE sql IMMUTABLE
3108
    AS $$
3109
SELECT 'TYPE'::text
3110
$$;
3111
3112
3113
--
3114 12341 aaronmk
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
3115
--
3116
3117
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
3118
    LANGUAGE sql STABLE
3119
    AS $_$
3120
SELECT relkind FROM pg_class WHERE oid = $1
3121
$_$;
3122
3123
3124
--
3125 12293 aaronmk
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
3126
--
3127
3128
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
3129
    LANGUAGE sql
3130
    AS $_$
3131
/* can't have in_table/out_table inherit from *each other*, because inheritance
3132
also causes the rows of the parent table to be included in the child table.
3133
instead, they need to inherit from a common, empty table. */
3134 12382 aaronmk
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
3135
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
3136 13098 aaronmk
SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
3137 12293 aaronmk
SELECT util.inherit($2, $4);
3138
SELECT util.inherit($3, $4);
3139
3140
SELECT util.rematerialize_query($1, $$
3141
SELECT * FROM util.diff(
3142 12419 aaronmk
  $$||util.quote_typed($2)||$$
3143
, $$||util.quote_typed($3)||$$
3144 12293 aaronmk
, NULL::$$||$4||$$)
3145
$$);
3146 12303 aaronmk
3147
/* the table unfortunately cannot be *materialized* in human-readable form,
3148
because this would create column name collisions between the two sides */
3149 12495 aaronmk
SELECT util.prepend_comment($1, '
3150 12303 aaronmk
to view this table in human-readable form (with each side''s tuple column
3151
expanded to its component fields):
3152 12572 aaronmk
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
3153 13092 aaronmk
3154
to display NULL values that are extra or missing:
3155
SELECT * FROM '||$1||';
3156 12303 aaronmk
');
3157 12293 aaronmk
$_$;
3158
3159
3160
--
3161
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
3162
--
3163
3164
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
3165
type_table (*required*): table to create as the shared base type
3166
';
3167
3168
3169
--
3170 12265 aaronmk
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
3171
--
3172
3173
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
3174
    LANGUAGE sql
3175
    AS $_$
3176
SELECT util.drop_table($1);
3177
SELECT util.materialize_query($1, $2);
3178
$_$;
3179
3180
3181
--
3182
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
3183
--
3184
3185
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
3186
idempotent, but repeats action each time
3187
';
3188
3189
3190
--
3191 12247 aaronmk
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
3192
--
3193
3194 12262 aaronmk
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
3195 12247 aaronmk
    LANGUAGE sql
3196
    AS $_$
3197
SELECT util.drop_table($1);
3198
SELECT util.materialize_view($1, $2);
3199
$_$;
3200
3201
3202
--
3203 12262 aaronmk
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
3204 12247 aaronmk
--
3205
3206 12262 aaronmk
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
3207 12247 aaronmk
idempotent, but repeats action each time
3208
';
3209
3210
3211
--
3212 8183 aaronmk
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
3213 8137 aaronmk
--
3214
3215 8148 aaronmk
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
3216 12446 aaronmk
    LANGUAGE sql
3217 8137 aaronmk
    AS $_$
3218 8212 aaronmk
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
3219 8137 aaronmk
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
3220 10309 aaronmk
FROM util.col_names($1::text::regtype) f (name);
3221
SELECT NULL::void; -- don't fold away functions called in previous query
3222 8137 aaronmk
$_$;
3223
3224
3225
--
3226 8183 aaronmk
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
3227 8137 aaronmk
--
3228
3229 12235 aaronmk
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
3230
idempotent
3231
';
3232 8137 aaronmk
3233
3234
--
3235 12349 aaronmk
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3236
--
3237
3238
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
3239
    LANGUAGE sql
3240
    AS $_$
3241 12353 aaronmk
/* use util.qual_name() instead of ::text so that the schema qualifier is always
3242
included in the debug SQL */
3243
SELECT util.rename_relation(util.qual_name($1), $2)
3244 12349 aaronmk
$_$;
3245
3246
3247
--
3248
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
3249
--
3250
3251 12364 aaronmk
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
3252 12349 aaronmk
    LANGUAGE sql
3253
    AS $_$
3254
/* 'ALTER TABLE can be used with views too'
3255
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
3256 12363 aaronmk
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
3257
||quote_ident($2))
3258 12349 aaronmk
$_$;
3259
3260
3261
--
3262 12364 aaronmk
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
3263 12349 aaronmk
--
3264
3265 12364 aaronmk
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
3266 12349 aaronmk
idempotent
3267
';
3268
3269
3270
--
3271 12358 aaronmk
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
3272 12350 aaronmk
--
3273
3274 12358 aaronmk
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
3275 12350 aaronmk
    LANGUAGE sql IMMUTABLE
3276
    AS $_$
3277 12358 aaronmk
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
3278 12350 aaronmk
$_$;
3279
3280
3281
--
3282 12358 aaronmk
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
3283
--
3284
3285
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
3286
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
3287
';
3288
3289
3290
--
3291 10297 aaronmk
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3292
--
3293
3294
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
3295 12446 aaronmk
    LANGUAGE sql
3296 10297 aaronmk
    AS $_$
3297 10596 aaronmk
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
3298
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
3299 10297 aaronmk
SELECT util.set_col_names($1, $2);
3300
$_$;
3301
3302
3303
--
3304
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3305
--
3306
3307 12235 aaronmk
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
3308
idempotent.
3309
alters the names table, so it will need to be repopulated after running this function.
3310
';
3311 10297 aaronmk
3312
3313
--
3314 8183 aaronmk
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
3315 8143 aaronmk
--
3316
3317
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
3318 12446 aaronmk
    LANGUAGE sql
3319 8143 aaronmk
    AS $_$
3320 10152 aaronmk
SELECT util.drop_table($1);
3321 8183 aaronmk
SELECT util.mk_map_table($1);
3322 8143 aaronmk
$_$;
3323
3324
3325
--
3326 13488 aaronmk
-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
3327 13486 aaronmk
--
3328
3329 13488 aaronmk
CREATE FUNCTION restore_views(restore_views_info) RETURNS void
3330 13486 aaronmk
    LANGUAGE sql
3331
    AS $_$
3332
SELECT util.debug_print_var('views', $1);
3333 13491 aaronmk
SELECT util.create_if_not_exists((view_).def)
3334
FROM unnest($1.views) view_; -- in forward dependency order
3335 13486 aaronmk
	/* create_if_not_exists() rather than eval(), because cmd might manually
3336
	re-create a deleted dependent view, causing it to already exist */
3337
SELECT NULL::void; -- don't fold away functions called in previous query
3338
$_$;
3339
3340
3341
--
3342 13096 aaronmk
-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
3343
--
3344
3345
CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
3346
    LANGUAGE sql
3347
    AS $_$
3348
SELECT util.drop_column($1, $2, force := true)
3349
$_$;
3350
3351
3352
--
3353 12356 aaronmk
-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
3354
--
3355
3356
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
3357
    LANGUAGE sql IMMUTABLE
3358
    AS $_$
3359
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
3360
$_$;
3361
3362
3363
--
3364 12473 aaronmk
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
3365
--
3366
3367
CREATE FUNCTION runnable_sql(sql text) RETURNS text
3368
    LANGUAGE sql IMMUTABLE
3369
    AS $_$
3370 12481 aaronmk
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
3371
ELSE util.mk_set_search_path(for_printing := true)||$$;
3372
$$ END)||$1
3373 12473 aaronmk
$_$;
3374
3375
3376
--
3377 11652 aaronmk
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
3378
--
3379
3380
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
3381
    LANGUAGE plpgsql STRICT
3382 13467 aaronmk
    AS $$
3383 11652 aaronmk
DECLARE
3384
	result text = NULL;
3385
BEGIN
3386
	BEGIN
3387 13470 aaronmk
		result = util.show_create_view(view_, replace := false);
3388
			/* replace: no `OR REPLACE` because that causes nonuniform errors
3389
			(eg. invalid_table_definition), instead of the standard
3390
			duplicate_table exception caught by util.create_if_not_exists() */
3391 13467 aaronmk
		PERFORM util.drop_view(view_);
3392 11652 aaronmk
	EXCEPTION
3393
		WHEN undefined_table THEN NULL;
3394
	END;
3395
	RETURN result;
3396
END;
3397 13467 aaronmk
$$;
3398 11652 aaronmk
3399
3400
--
3401 11660 aaronmk
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
3402
--
3403
3404 13488 aaronmk
CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
3405 11660 aaronmk
    LANGUAGE sql
3406
    AS $_$
3407 13488 aaronmk
SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
3408 13491 aaronmk
SELECT (view_, util.save_drop_view(view_))::util.db_item
3409 13485 aaronmk
FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
3410 13488 aaronmk
)))::util.restore_views_info
3411 11660 aaronmk
$_$;
3412
3413
3414
--
3415 12244 aaronmk
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
3416
--
3417
3418
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
3419
    LANGUAGE sql STABLE
3420
    AS $_$
3421
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
3422
$_$;
3423
3424
3425
--
3426 12242 aaronmk
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
3427
--
3428
3429
CREATE FUNCTION schema(table_ regclass) RETURNS text
3430
    LANGUAGE sql STABLE
3431
    AS $_$
3432 12245 aaronmk
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
3433 12242 aaronmk
$_$;
3434
3435
3436
--
3437 10794 aaronmk
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
3438
--
3439
3440
CREATE FUNCTION schema(type regtype) RETURNS text
3441
    LANGUAGE sql STABLE
3442
    AS $_$
3443 12245 aaronmk
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
3444 10794 aaronmk
$_$;
3445
3446
3447
--
3448
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
3449
--
3450
3451
CREATE FUNCTION schema(type_null anyelement) RETURNS text
3452
    LANGUAGE sql STABLE
3453
    AS $_$
3454
SELECT util.schema(pg_typeof($1))
3455
$_$;
3456
3457
3458
--
3459 12134 aaronmk
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
3460
--
3461
3462
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
3463
    LANGUAGE sql STABLE
3464
    AS $_$
3465
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
3466
$_$;
3467
3468
3469
--
3470 12135 aaronmk
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
3471
--
3472
3473 12235 aaronmk
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
3474
a schema bundle is a group of schemas with a common prefix
3475
';
3476 12135 aaronmk
3477
3478
--
3479
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3480
--
3481
3482
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
3483
    LANGUAGE sql
3484
    AS $_$
3485
SELECT util.schema_rename(old_schema,
3486
	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
3487
FROM util.schema_bundle_get_schemas($1) f (old_schema);
3488
SELECT NULL::void; -- don't fold away functions called in previous query
3489
$_$;
3490
3491
3492
--
3493
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3494
--
3495
3496
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
3497
    LANGUAGE plpgsql
3498
    AS $$
3499
BEGIN
3500
	-- don't schema_bundle_rm() the schema_bundle to keep!
3501
	IF replace = with_ THEN RETURN; END IF;
3502
3503
	PERFORM util.schema_bundle_rm(replace);
3504
	PERFORM util.schema_bundle_rename(with_, replace);
3505
END;
3506
$$;
3507
3508
3509
--
3510
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
3511
--
3512
3513
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
3514
    LANGUAGE sql
3515
    AS $_$
3516
SELECT util.schema_rm(schema)
3517
FROM util.schema_bundle_get_schemas($1) f (schema);
3518
SELECT NULL::void; -- don't fold away functions called in previous query
3519
$_$;
3520
3521
3522
--
3523 12238 aaronmk
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
3524 10795 aaronmk
--
3525
3526 12238 aaronmk
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
3527 10795 aaronmk
    LANGUAGE sql STABLE
3528
    AS $_$
3529
SELECT quote_ident(util.schema($1))
3530
$_$;
3531
3532
3533
--
3534 12324 aaronmk
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
3535
--
3536
3537
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
3538 12334 aaronmk
    LANGUAGE sql IMMUTABLE
3539 12324 aaronmk
    AS $_$
3540
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
3541
$_$;
3542
3543
3544
--
3545 12304 aaronmk
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
3546
--
3547
3548
CREATE FUNCTION schema_oid(schema text) RETURNS oid
3549
    LANGUAGE sql STABLE
3550
    AS $_$
3551
SELECT oid FROM pg_namespace WHERE nspname = $1
3552
$_$;
3553
3554
3555
--
3556 12504 aaronmk
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
3557
--
3558
3559
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
3560
    LANGUAGE sql IMMUTABLE
3561
    AS $_$
3562
SELECT util.schema_regexp(schema_anchor := $1)
3563
$_$;
3564
3565
3566
--
3567 12501 aaronmk
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
3568
--
3569
3570
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
3571
    LANGUAGE sql IMMUTABLE
3572
    AS $_$
3573
SELECT util.str_equality_regexp(util.schema($1))
3574
$_$;
3575
3576
3577
--
3578 12132 aaronmk
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
3579
--
3580
3581
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
3582
    LANGUAGE sql
3583
    AS $_$
3584
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
3585
$_$;
3586
3587
3588
--
3589 12133 aaronmk
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
3590
--
3591
3592
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
3593
    LANGUAGE plpgsql
3594
    AS $$
3595
BEGIN
3596
	-- don't schema_rm() the schema to keep!
3597
	IF replace = with_ THEN RETURN; END IF;
3598
3599
	PERFORM util.schema_rm(replace);
3600
	PERFORM util.schema_rename(with_, replace);
3601
END;
3602
$$;
3603
3604
3605
--
3606 12132 aaronmk
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
3607
--
3608
3609
CREATE FUNCTION schema_rm(schema text) RETURNS void
3610
    LANGUAGE sql
3611
    AS $_$
3612
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
3613
$_$;
3614
3615
3616
--
3617 9825 aaronmk
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
3618
--
3619
3620
CREATE FUNCTION search_path_append(schemas text) RETURNS void
3621 12446 aaronmk
    LANGUAGE sql
3622 9825 aaronmk
    AS $_$
3623
SELECT util.eval(
3624
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
3625
$_$;
3626
3627
3628
--
3629 8183 aaronmk
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3630 8153 aaronmk
--
3631
3632
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
3633
    LANGUAGE plpgsql STRICT
3634
    AS $_$
3635
DECLARE
3636 8183 aaronmk
    old text[] = ARRAY(SELECT util.col_names(table_));
3637
    new text[] = ARRAY(SELECT util.map_values(names));
3638 8153 aaronmk
BEGIN
3639
    old = old[1:array_length(new, 1)]; -- truncate to same length
3640 10345 aaronmk
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
3641
||$$ TO $$||quote_ident(value))
3642 10149 aaronmk
    FROM each(hstore(old, new))
3643
    WHERE value != key -- not same name
3644
    ;
3645 8153 aaronmk
END;
3646
$_$;
3647
3648
3649
--
3650 8183 aaronmk
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3651 8153 aaronmk
--
3652
3653 12235 aaronmk
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
3654
idempotent
3655
';
3656 8153 aaronmk
3657
3658
--
3659 10145 aaronmk
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
3660
--
3661
3662
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
3663
    LANGUAGE plpgsql STRICT
3664
    AS $_$
3665
DECLARE
3666
	row_ util.map;
3667
BEGIN
3668 10715 aaronmk
	-- rename any metadata cols rather than re-adding them with new names
3669
	BEGIN
3670
		PERFORM util.set_col_names(table_, names);
3671
	EXCEPTION
3672
		WHEN array_subscript_error THEN -- selective suppress
3673
			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
3674
				-- metadata cols not yet added
3675 12568 aaronmk
			ELSE RAISE;
3676 10715 aaronmk
			END IF;
3677
	END;
3678
3679 10157 aaronmk
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
3680 10145 aaronmk
	LOOP
3681 10147 aaronmk
		PERFORM util.mk_const_col((table_, row_."to"),
3682
			substring(row_."from" from 2));
3683 10145 aaronmk
	END LOOP;
3684
3685
	PERFORM util.set_col_names(table_, names);
3686
END;
3687
$_$;
3688
3689
3690
--
3691
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
3692
--
3693
3694 12235 aaronmk
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
3695
idempotent.
3696
the metadata mappings must be *last* in the names table.
3697
';
3698 10145 aaronmk
3699
3700
--
3701 8183 aaronmk
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
3702 8107 aaronmk
--
3703
3704
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
3705 12733 aaronmk
    LANGUAGE sql
3706 8107 aaronmk
    AS $_$
3707 12734 aaronmk
SELECT util.eval(COALESCE(
3708
$$ALTER TABLE $$||$1||$$
3709 12732 aaronmk
$$||(
3710
	SELECT
3711
	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
3712
	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
3713
, $$)
3714
	FROM
3715
	(
3716
		SELECT
3717
		  quote_ident(col_name) AS col_name_sql
3718 12733 aaronmk
		, util.col_type(($1, col_name)) AS curr_type
3719 12732 aaronmk
		, type AS target_type
3720 12733 aaronmk
		FROM unnest($2)
3721 12732 aaronmk
	) s
3722
	WHERE curr_type != target_type
3723 12734 aaronmk
), ''))
3724 8107 aaronmk
$_$;
3725
3726
3727
--
3728 8183 aaronmk
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
3729 8107 aaronmk
--
3730
3731 12235 aaronmk
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
3732
idempotent
3733
';
3734 8107 aaronmk
3735
3736
--
3737 12302 aaronmk
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3738
--
3739
3740
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
3741 12446 aaronmk
    LANGUAGE sql
3742 12302 aaronmk
    AS $_$
3743 13477 aaronmk
SELECT util.eval(util.mk_set_comment($1, $2))
3744 12302 aaronmk
$_$;
3745
3746
3747
--
3748 12482 aaronmk
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
3749
--
3750
3751
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
3752
    LANGUAGE sql
3753
    AS $_$
3754
SELECT util.eval(util.mk_set_search_path($1, $2))
3755
$_$;
3756
3757
3758
--
3759 13468 aaronmk
-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
3760 11651 aaronmk
--
3761
3762 13468 aaronmk
CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
3763 11651 aaronmk
    LANGUAGE sql STABLE
3764
    AS $_$
3765 13468 aaronmk
SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
3766
||$1||$$ AS
3767 13482 aaronmk
$$||pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
3768 11656 aaronmk
$$||util.show_grants_for($1)
3769 13482 aaronmk
||util.show_set_comment($1)||$$
3770
$$
3771 11651 aaronmk
$_$;
3772
3773
3774
--
3775 11655 aaronmk
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
3776
--
3777
3778
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
3779
    LANGUAGE sql STABLE
3780
    AS $_$
3781 12269 aaronmk
SELECT string_agg(cmd, '')
3782 11655 aaronmk
FROM
3783
(
3784
	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
3785
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
3786
$$ ELSE '' END) AS cmd
3787
	FROM util.grants_users() f (user_)
3788
) s
3789
$_$;
3790
3791
3792
--
3793 12325 aaronmk
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
3794
--
3795
3796 12670 aaronmk
CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['c'::text, 'r'::text, 'v'::text]) RETURNS SETOF regclass
3797 12325 aaronmk
    LANGUAGE sql STABLE
3798
    AS $_$
3799
SELECT oid FROM pg_class
3800
WHERE relkind = ANY($3) AND relname ~ $1
3801
AND util.schema_matches(util.schema(relnamespace), $2)
3802
ORDER BY relname
3803
$_$;
3804
3805
3806
--
3807 13478 aaronmk
-- Name: show_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
3808
--
3809
3810
CREATE FUNCTION show_set_comment(table_ regclass) RETURNS text
3811
    LANGUAGE sql STABLE
3812
    AS $_$
3813 13480 aaronmk
SELECT util.mk_set_comment($1, util.comment($1))
3814 13478 aaronmk
$_$;
3815
3816
3817
--
3818 12592 aaronmk
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3819
--
3820
3821
CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
3822
    LANGUAGE sql STABLE
3823
    AS $_$
3824
SELECT oid
3825
FROM pg_type
3826
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
3827
ORDER BY typname
3828
$_$;
3829
3830
3831
--
3832 12305 aaronmk
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
3833
--
3834
3835 12385 aaronmk
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
3836 12305 aaronmk
    LANGUAGE sql STABLE
3837
    AS $_$
3838 12385 aaronmk
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
3839 12305 aaronmk
$_$;
3840
3841
3842
--
3843 12384 aaronmk
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
3844
--
3845
3846
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
3847
    LANGUAGE sql IMMUTABLE
3848
    AS $_$
3849
SELECT '^'||util.regexp_quote($1)||'$'
3850
$_$;
3851
3852
3853
--
3854 8183 aaronmk
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
3855 8144 aaronmk
--
3856
3857
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
3858 8145 aaronmk
    LANGUAGE plpgsql STABLE STRICT
3859 8144 aaronmk
    AS $_$
3860
DECLARE
3861
    hstore hstore;
3862
BEGIN
3863
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
3864
        table_||$$))$$ INTO STRICT hstore;
3865
    RETURN hstore;
3866
END;
3867
$_$;
3868
3869
3870
--
3871 10184 aaronmk
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3872
--
3873
3874
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
3875 12446 aaronmk
    LANGUAGE sql STABLE
3876 10184 aaronmk
    AS $_$
3877
SELECT COUNT(*) > 0 FROM pg_constraint
3878
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
3879
$_$;
3880
3881
3882
--
3883
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3884
--
3885
3886 12235 aaronmk
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
3887
gets whether a status flag is set by the presence of a table constraint
3888
';
3889 10184 aaronmk
3890
3891
--
3892 10182 aaronmk
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
3893
--
3894
3895
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
3896 12446 aaronmk
    LANGUAGE sql
3897 10182 aaronmk
    AS $_$
3898
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
3899
||quote_ident($2)||$$ CHECK (true)$$)
3900
$_$;
3901
3902
3903
--
3904
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
3905
--
3906
3907 12235 aaronmk
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
3908
stores a status flag by the presence of a table constraint.
3909
idempotent.
3910
';
3911 10182 aaronmk
3912
3913
--
3914 10185 aaronmk
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
3915
--
3916
3917
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
3918 12446 aaronmk
    LANGUAGE sql STABLE
3919 10185 aaronmk
    AS $_$
3920
SELECT util.table_flag__get($1, 'nulls_mapped')
3921
$_$;
3922
3923
3924
--
3925
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3926
--
3927
3928 12235 aaronmk
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
3929
gets whether a table''s NULL-equivalent strings have been replaced with NULL
3930
';
3931 10185 aaronmk
3932
3933
--
3934 10183 aaronmk
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
3935
--
3936
3937
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
3938 12446 aaronmk
    LANGUAGE sql
3939 10183 aaronmk
    AS $_$
3940
SELECT util.table_flag__set($1, 'nulls_mapped')
3941
$_$;
3942
3943
3944
--
3945
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
3946
--
3947
3948 12235 aaronmk
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
3949
sets that a table''s NULL-equivalent strings have been replaced with NULL.
3950
idempotent.
3951
';
3952 10183 aaronmk
3953
3954
--
3955 12652 aaronmk
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
3956
--
3957
3958
CREATE FUNCTION to_freq(table_ regclass) RETURNS void
3959
    LANGUAGE sql
3960
    AS $_$
3961
-- save data before truncating main table
3962
SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
3963
3964
-- repopulate main table w/ copies column
3965
SELECT util.truncate($1);
3966
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
3967
SELECT util.populate_table($1, $$
3968
SELECT (table_).*, copies
3969
FROM (
3970
	SELECT table_, COUNT(*) AS copies
3971
	FROM pg_temp.__copy table_
3972
	GROUP BY table_
3973
) s
3974
$$);
3975
3976
-- delete temp table so it doesn't stay around until end of connection
3977
SELECT util.drop_table('pg_temp.__copy');
3978
$_$;
3979
3980
3981
--
3982 8183 aaronmk
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
3983 8088 aaronmk
--
3984
3985
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
3986
    LANGUAGE plpgsql STRICT
3987
    AS $_$
3988
DECLARE
3989
    row record;
3990
BEGIN
3991 8183 aaronmk
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
3992 8088 aaronmk
    LOOP
3993
        IF row.global_name != row.name THEN
3994
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
3995
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
3996
        END IF;
3997
    END LOOP;
3998
END;
3999
$_$;
4000
4001
4002
--
4003 8183 aaronmk
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4004 8088 aaronmk
--
4005
4006 12235 aaronmk
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
4007
idempotent
4008
';
4009 8088 aaronmk
4010
4011
--
4012 12874 aaronmk
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
4013 10365 aaronmk
--
4014
4015 12874 aaronmk
CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
4016 12446 aaronmk
    LANGUAGE sql
4017 10365 aaronmk
    AS $_$
4018 12874 aaronmk
SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
4019 10365 aaronmk
SELECT NULL::void; -- don't fold away functions called in previous query
4020
$_$;
4021
4022
4023
--
4024 12874 aaronmk
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
4025 10365 aaronmk
--
4026
4027 12874 aaronmk
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass, force boolean) IS '
4028
trims table_ to include only columns in the original data
4029
4030
by default, cascadingly drops dependent columns so that they don''t prevent
4031
trim() from succeeding. note that this requires the dependent columns to then be
4032
manually re-created.
4033
4034
idempotent
4035 12235 aaronmk
';
4036 10365 aaronmk
4037
4038
--
4039 8183 aaronmk
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
4040 8142 aaronmk
--
4041
4042
CREATE FUNCTION truncate(table_ regclass) RETURNS void
4043
    LANGUAGE plpgsql STRICT
4044
    AS $_$
4045
BEGIN
4046
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
4047
END;
4048
$_$;
4049
4050
4051
--
4052 8183 aaronmk
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
4053 8142 aaronmk
--
4054
4055 12235 aaronmk
COMMENT ON FUNCTION truncate(table_ regclass) IS '
4056
idempotent
4057
';
4058 8142 aaronmk
4059
4060
--
4061 12357 aaronmk
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
4062
--
4063
4064
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
4065
    LANGUAGE sql IMMUTABLE
4066
    AS $_$
4067 12361 aaronmk
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
4068 12362 aaronmk
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
4069 12357 aaronmk
$_$;
4070
4071
4072
--
4073 13135 aaronmk
-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
4074
--
4075
4076
CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
4077
    LANGUAGE plpgsql IMMUTABLE
4078
    AS $$
4079
BEGIN
4080
	/* need explicit cast because some types not implicitly-castable, and also
4081
	to make the cast happen inside the try block. (*implicit* casts to the
4082
	return type happen at the end of the function, outside any block.) */
4083
	RETURN util.cast(value, ret_type_null);
4084
EXCEPTION
4085
WHEN data_exception THEN
4086
	PERFORM util.raise('WARNING', SQLERRM);
4087
	RETURN NULL;
4088
END;
4089
$$;
4090
4091
4092
--
4093
-- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
4094
--
4095
4096
COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
4097
ret_type_null: NULL::ret_type
4098
';
4099
4100
4101
--
4102 8199 aaronmk
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
4103
--
4104
4105
CREATE FUNCTION try_create(sql text) RETURNS void
4106
    LANGUAGE plpgsql STRICT
4107
    AS $$
4108
BEGIN
4109 12658 aaronmk
	PERFORM util.eval(sql);
4110 8199 aaronmk
EXCEPTION
4111 12676 aaronmk
WHEN   not_null_violation
4112
		/* trying to add NOT NULL column to parent table, which cascades to
4113
		child table whose values for the new column will be NULL */
4114
	OR wrong_object_type -- trying to alter a view's columns
4115
	OR undefined_column
4116
	OR duplicate_column
4117
THEN NULL;
4118 12684 aaronmk
WHEN datatype_mismatch THEN
4119
	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
4120
	ELSE RAISE; -- rethrow
4121
	END IF;
4122 8199 aaronmk
END;
4123
$$;
4124
4125
4126
--
4127
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
4128
--
4129
4130 12235 aaronmk
COMMENT ON FUNCTION try_create(sql text) IS '
4131
idempotent
4132
';
4133 8199 aaronmk
4134
4135
--
4136 8209 aaronmk
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
4137
--
4138
4139
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
4140 12446 aaronmk
    LANGUAGE sql
4141 8209 aaronmk
    AS $_$
4142
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
4143
$_$;
4144
4145
4146
--
4147
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
4148
--
4149
4150 12235 aaronmk
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
4151
idempotent
4152
';
4153 8209 aaronmk
4154
4155
--
4156 8189 aaronmk
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
4157
--
4158
4159
CREATE FUNCTION type_qual(value anyelement) RETURNS text
4160
    LANGUAGE sql IMMUTABLE
4161
    AS $_$
4162
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
4163
$_$;
4164
4165
4166
--
4167 10161 aaronmk
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
4168
--
4169
4170 12235 aaronmk
COMMENT ON FUNCTION type_qual(value anyelement) IS '
4171
a type''s NOT NULL qualifier
4172
';
4173 10161 aaronmk
4174
4175
--
4176 12562 aaronmk
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
4177
--
4178
4179
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
4180 12590 aaronmk
    LANGUAGE sql STABLE
4181 12562 aaronmk
    AS $_$
4182
SELECT (attname::text, atttypid)::util.col_cast
4183
FROM pg_attribute
4184
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
4185
ORDER BY attnum
4186
$_$;
4187
4188
4189
--
4190 12438 aaronmk
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
4191
--
4192
4193
CREATE FUNCTION typeof(value anyelement) RETURNS text
4194
    LANGUAGE sql IMMUTABLE
4195
    AS $_$
4196
SELECT util.qual_name(pg_typeof($1))
4197
$_$;
4198
4199
4200
--
4201 8185 aaronmk
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
4202
--
4203
4204 10160 aaronmk
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
4205
    LANGUAGE plpgsql STABLE
4206 8185 aaronmk
    AS $_$
4207
DECLARE
4208
    type regtype;
4209
BEGIN
4210 10160 aaronmk
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
4211
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
4212 8185 aaronmk
    RETURN type;
4213
END;
4214
$_$;
4215
4216
4217
--
4218 12490 aaronmk
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
4219 12483 aaronmk
--
4220
4221 12490 aaronmk
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
4222 12483 aaronmk
    LANGUAGE sql
4223
    AS $_$
4224 12488 aaronmk
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
4225 12483 aaronmk
$_$;
4226
4227
4228
--
4229 12490 aaronmk
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
4230 12488 aaronmk
--
4231
4232 12490 aaronmk
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
4233 12488 aaronmk
auto-appends util to the search_path to enable use of util operators
4234
';
4235
4236
4237
--
4238 9959 aaronmk
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
4239
--
4240
4241
CREATE AGGREGATE all_same(anyelement) (
4242
    SFUNC = all_same_transform,
4243
    STYPE = anyarray,
4244
    FINALFUNC = all_same_final
4245
);
4246
4247
4248
--
4249
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
4250
--
4251
4252 12235 aaronmk
COMMENT ON AGGREGATE all_same(anyelement) IS '
4253
includes NULLs in comparison
4254
';
4255 9959 aaronmk
4256
4257
--
4258 8183 aaronmk
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
4259 2595 aaronmk
--
4260
4261
CREATE AGGREGATE join_strs(text, text) (
4262 4052 aaronmk
    SFUNC = join_strs_transform,
4263 4010 aaronmk
    STYPE = text
4264 2595 aaronmk
);
4265
4266
4267 8147 aaronmk
--
4268 12423 aaronmk
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
4269
--
4270
4271
CREATE OPERATOR %== (
4272
    PROCEDURE = "%==",
4273
    LEFTARG = anyelement,
4274
    RIGHTARG = anyelement
4275
);
4276
4277
4278
--
4279
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
4280
--
4281
4282
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
4283
returns whether the map-keys of the compared values are the same
4284
(mnemonic: % is the Perl symbol for a hash map)
4285
4286
should be overridden for types that store both keys and values
4287
4288
used in a FULL JOIN to select which columns to join on
4289
';
4290
4291
4292
--
4293 8183 aaronmk
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
4294 8147 aaronmk
--
4295
4296
CREATE OPERATOR -> (
4297
    PROCEDURE = map_get,
4298
    LEFTARG = regclass,
4299
    RIGHTARG = text
4300
);
4301
4302
4303 10308 aaronmk
--
4304
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
4305
--
4306
4307
CREATE OPERATOR => (
4308
    PROCEDURE = hstore,
4309 10357 aaronmk
    LEFTARG = text[],
4310 10608 aaronmk
    RIGHTARG = text
4311 10308 aaronmk
);
4312
4313
4314
--
4315 10608 aaronmk
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
4316 10308 aaronmk
--
4317
4318 12235 aaronmk
COMMENT ON OPERATOR => (text[], text) IS '
4319
usage: array[''key1'', ...]::text[] => ''value''
4320
';
4321 10308 aaronmk
4322
4323 10391 aaronmk
--
4324 10613 aaronmk
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
4325
--
4326
4327
CREATE OPERATOR ?*>= (
4328
    PROCEDURE = is_populated_more_often_than,
4329
    LEFTARG = anyelement,
4330
    RIGHTARG = anyelement
4331
);
4332
4333
4334
--
4335 10391 aaronmk
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
4336
--
4337
4338
CREATE OPERATOR ?>= (
4339
    PROCEDURE = is_more_complete_than,
4340
    LEFTARG = anyelement,
4341
    RIGHTARG = anyelement
4342
);
4343
4344
4345 11005 aaronmk
--
4346
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
4347
--
4348
4349
CREATE OPERATOR ||% (
4350
    PROCEDURE = concat_esc,
4351
    LEFTARG = text,
4352
    RIGHTARG = text
4353
);
4354
4355
4356
--
4357
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
4358
--
4359
4360 12235 aaronmk
COMMENT ON OPERATOR ||% (text, text) IS '
4361
% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
4362
';
4363 11005 aaronmk
4364
4365 2107 aaronmk
--
4366 8183 aaronmk
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
4367 8140 aaronmk
--
4368
4369
CREATE TABLE map (
4370
    "from" text NOT NULL,
4371 8158 aaronmk
    "to" text,
4372
    filter text,
4373
    notes text
4374 8140 aaronmk
);
4375
4376
4377
--
4378 11834 aaronmk
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
4379
--
4380
4381
4382
4383
--
4384 8183 aaronmk
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
4385 8140 aaronmk
--
4386
4387
4388
4389
--
4390 10342 aaronmk
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
4391 8140 aaronmk
--
4392
4393
ALTER TABLE ONLY map
4394 10342 aaronmk
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
4395 8140 aaronmk
4396
4397
--
4398 10343 aaronmk
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
4399
--
4400
4401
ALTER TABLE ONLY map
4402
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
4403
4404
4405
--
4406 10110 aaronmk
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
4407
--
4408
4409
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
4410
4411
4412
--
4413 2136 aaronmk
-- PostgreSQL database dump complete
4414
--