Project

General

Profile

1 2094 aaronmk
--
2
-- PostgreSQL database dump
3
--
4
5
SET statement_timeout = 0;
6
SET client_encoding = 'UTF8';
7 6213 aaronmk
SET standard_conforming_strings = on;
8 2094 aaronmk
SET check_function_bodies = false;
9
SET client_min_messages = warning;
10
11
--
12 8183 aaronmk
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
13 2094 aaronmk
--
14
15 8183 aaronmk
CREATE SCHEMA util;
16 2094 aaronmk
17
18 4982 aaronmk
--
19 8183 aaronmk
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
20 4982 aaronmk
--
21
22 8183 aaronmk
COMMENT ON SCHEMA util IS 'IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.';
23 4982 aaronmk
24
25 8183 aaronmk
SET search_path = util, pg_catalog;
26 2107 aaronmk
27 2094 aaronmk
--
28 8183 aaronmk
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
29 8107 aaronmk
--
30
31
CREATE TYPE col_cast AS (
32
	col_name text,
33
	type regtype
34
);
35
36
37
--
38 8183 aaronmk
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
39 8106 aaronmk
--
40
41
CREATE TYPE col_ref AS (
42
	table_ regclass,
43
	name text
44
);
45
46
47
--
48 8183 aaronmk
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
49 7673 aaronmk
--
50
51
CREATE TYPE compass_dir AS ENUM (
52
    'N',
53
    'E',
54
    'S',
55
    'W'
56
);
57
58
59
--
60 8183 aaronmk
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
61 2610 aaronmk
--
62
63
CREATE TYPE datatype AS ENUM (
64
    'str',
65
    'float'
66
);
67
68
69
--
70 8183 aaronmk
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
71 2596 aaronmk
--
72
73 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
74 3422 aaronmk
    LANGUAGE sql IMMUTABLE
75
    AS $_$
76 4501 aaronmk
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
77 3422 aaronmk
$_$;
78
79
80
--
81 8183 aaronmk
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
82 5937 aaronmk
--
83
84 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
85 5937 aaronmk
    LANGUAGE sql IMMUTABLE
86
    AS $_$
87
SELECT bool_and(value)
88
FROM
89
(VALUES
90
      ($1)
91
    , ($2)
92 5956 aaronmk
    , ($3)
93
    , ($4)
94
    , ($5)
95 5937 aaronmk
)
96
AS v (value)
97
$_$;
98
99
100
--
101 8183 aaronmk
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
102 5937 aaronmk
--
103
104 6437 aaronmk
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_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.';
105 5937 aaronmk
106
107
--
108 8183 aaronmk
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
109 7704 aaronmk
--
110
111
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
112
    LANGUAGE sql IMMUTABLE
113
    AS $_$
114
SELECT avg(value)
115
FROM
116
(VALUES
117
      ($1)
118
    , ($2)
119
    , ($3)
120
    , ($4)
121
    , ($5)
122
)
123
AS v (value)
124
$_$;
125
126
127
--
128 8183 aaronmk
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
129 7679 aaronmk
--
130
131
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
132
    LANGUAGE sql IMMUTABLE STRICT
133
    AS $_$
134 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)
135 7698 aaronmk
FROM
136
(
137
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
138
    UNION ALL
139 7702 aaronmk
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
140
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
141 7698 aaronmk
)
142
matches (g)
143 7679 aaronmk
$_$;
144
145
146
--
147 8183 aaronmk
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
148 7672 aaronmk
--
149
150 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
151 7672 aaronmk
    LANGUAGE sql IMMUTABLE
152
    AS $_$
153 8183 aaronmk
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
154 7672 aaronmk
FROM
155
(VALUES
156 7677 aaronmk
      ($1)
157 7672 aaronmk
    , ($2/60)
158
    , ($3/60/60)
159
)
160
AS v (value)
161
$_$;
162
163
164
--
165 8183 aaronmk
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
166 7723 aaronmk
--
167
168
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
169
    LANGUAGE sql IMMUTABLE
170
    AS $_$
171 8183 aaronmk
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
172 7723 aaronmk
$_$;
173
174
175
--
176 8183 aaronmk
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
177 4142 aaronmk
--
178
179
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
180
    LANGUAGE sql IMMUTABLE
181
    AS $_$
182
SELECT $1 = $2
183
$_$;
184
185
186
--
187 8183 aaronmk
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
188 7396 aaronmk
--
189
190
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
191
    LANGUAGE sql IMMUTABLE
192
    AS $_$
193
-- Fix dates after threshold date
194
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
195
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
196
$_$;
197
198
199
--
200 8183 aaronmk
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
201 4147 aaronmk
--
202
203
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
204
    LANGUAGE sql IMMUTABLE
205
    AS $_$
206
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
207
$_$;
208
209
210
--
211 8183 aaronmk
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
212 4147 aaronmk
--
213
214
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
215
    LANGUAGE sql IMMUTABLE
216
    AS $_$
217 8183 aaronmk
SELECT util._if($1 != '', $2, $3)
218 4147 aaronmk
$_$;
219
220
221
--
222 8183 aaronmk
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
223 4325 aaronmk
--
224
225
CREATE FUNCTION _join("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
226
    LANGUAGE sql IMMUTABLE
227
    AS $_$
228 7848 aaronmk
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
229 4325 aaronmk
$_$;
230
231
232
--
233 8183 aaronmk
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
234 5009 aaronmk
--
235
236
CREATE FUNCTION _join_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
237
    LANGUAGE sql IMMUTABLE
238
    AS $_$
239 7848 aaronmk
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
240 5009 aaronmk
$_$;
241
242
243
--
244 8183 aaronmk
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
245 3422 aaronmk
--
246
247 4683 aaronmk
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
248 4682 aaronmk
    LANGUAGE sql IMMUTABLE
249
    AS $_$
250
SELECT coalesce($1 || ': ', '') || $2
251
$_$;
252 2596 aaronmk
253
254
--
255 8825 aaronmk
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
256
--
257
258
CREATE FUNCTION _lowercase(value text) RETURNS text
259
    LANGUAGE sql IMMUTABLE STRICT
260
    AS $_$
261
SELECT lower($1)
262
$_$;
263
264
265
--
266 8183 aaronmk
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
267 6222 aaronmk
--
268
269
CREATE FUNCTION _map(map hstore, value text) RETURNS text
270 7820 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
271 6222 aaronmk
    AS $$
272
DECLARE
273 6271 aaronmk
    match text := map -> value;
274 6222 aaronmk
BEGIN
275 6271 aaronmk
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
276
        match := map -> '*'; -- use default entry
277
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
278
        END IF;
279
    END IF;
280
281
    -- Interpret result
282 6243 aaronmk
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
283
    ELSIF match = '*' THEN RETURN value;
284
    ELSE RETURN match;
285 6222 aaronmk
    END IF;
286
END;
287
$$;
288
289
290
--
291 8183 aaronmk
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
292 5408 aaronmk
--
293
294
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
295
    LANGUAGE sql IMMUTABLE
296
    AS $_$
297 7289 aaronmk
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
298 5408 aaronmk
$_$;
299
300
301
--
302 8183 aaronmk
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
303 2940 aaronmk
--
304
305 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
306 2940 aaronmk
    LANGUAGE sql IMMUTABLE
307
    AS $_$
308 8183 aaronmk
SELECT util.join_strs(value, '; ')
309 2940 aaronmk
FROM
310
(
311
    SELECT *
312
    FROM
313
    (
314
        SELECT
315
        DISTINCT ON (value)
316
        *
317
        FROM
318
        (VALUES
319 4012 aaronmk
              (1, $1)
320
            , (2, $2)
321
            , (3, $3)
322
            , (4, $4)
323
            , (5, $5)
324
            , (6, $6)
325
            , (7, $7)
326
            , (8, $8)
327
            , (9, $9)
328
            , (10, $10)
329 2940 aaronmk
        )
330
        AS v (sort_order, value)
331 4011 aaronmk
        WHERE value IS NOT NULL
332 2940 aaronmk
    )
333
    AS v
334
    ORDER BY sort_order
335
)
336
AS v
337
$_$;
338
339
340
--
341 8183 aaronmk
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
342 7140 aaronmk
--
343
344
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
345
    LANGUAGE sql IMMUTABLE
346
    AS $_$
347
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
348
$_$;
349
350
351
--
352 8183 aaronmk
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
353 6354 aaronmk
--
354
355
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
356
    LANGUAGE sql IMMUTABLE
357
    AS $_$
358 8183 aaronmk
SELECT util.join_strs(value, ' ')
359 6354 aaronmk
FROM
360
(
361
    SELECT *
362
    FROM
363
    (
364
        SELECT
365
        DISTINCT ON (value)
366
        *
367
        FROM
368
        (VALUES
369
              (1, $1)
370
            , (2, $2)
371
            , (3, $3)
372
            , (4, $4)
373
            , (5, $5)
374
            , (6, $6)
375
            , (7, $7)
376
            , (8, $8)
377
            , (9, $9)
378
            , (10, $10)
379
        )
380
        AS v (sort_order, value)
381
        WHERE value IS NOT NULL
382
    )
383
    AS v
384
    ORDER BY sort_order
385
)
386
AS v
387
$_$;
388
389
390
--
391 8183 aaronmk
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
392 5408 aaronmk
--
393
394
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
395
    LANGUAGE sql IMMUTABLE
396
    AS $_$
397 7289 aaronmk
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
398 5408 aaronmk
$_$;
399
400
401
--
402 8183 aaronmk
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
403 6316 aaronmk
--
404
405
CREATE FUNCTION _not(value boolean) RETURNS boolean
406
    LANGUAGE sql IMMUTABLE STRICT
407
    AS $_$
408
SELECT NOT $1
409
$_$;
410
411
412
--
413 8183 aaronmk
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
414 7104 aaronmk
--
415
416
CREATE FUNCTION _now() RETURNS timestamp with time zone
417
    LANGUAGE sql STABLE
418
    AS $$
419
SELECT now()
420
$$;
421
422
423
--
424 8183 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
425 2949 aaronmk
--
426
427 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
428 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
429
    AS $$
430
DECLARE
431 8183 aaronmk
    type util.datatype NOT NULL := type; -- add NOT NULL
432 2949 aaronmk
BEGIN
433 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
434 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
435 2949 aaronmk
    ELSIF type = 'float' THEN
436 2722 aaronmk
        DECLARE
437
            -- Outside the try block so that invalid null value generates error
438 2949 aaronmk
            "null" double precision := "null"::double precision;
439 2722 aaronmk
        BEGIN
440 2949 aaronmk
            RETURN nullif(value::double precision, "null");
441 2722 aaronmk
        EXCEPTION
442 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
443 2722 aaronmk
        END;
444 2610 aaronmk
    END IF;
445
END;
446
$$;
447
448
449
--
450 8183 aaronmk
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
451 4479 aaronmk
--
452
453
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
454
    LANGUAGE sql IMMUTABLE
455
    AS $_$
456 8183 aaronmk
SELECT util."_nullIf"($1, $2, $3::util.datatype)
457 4479 aaronmk
$_$;
458
459
460
--
461 8183 aaronmk
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
462 6355 aaronmk
--
463
464
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
465
    LANGUAGE sql IMMUTABLE
466
    AS $_$
467
SELECT bool_or(value)
468
FROM
469
(VALUES
470
      ($1)
471
    , ($2)
472
    , ($3)
473
    , ($4)
474
    , ($5)
475
)
476
AS v (value)
477
$_$;
478
479
480
--
481 8183 aaronmk
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
482 6437 aaronmk
--
483
484
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '_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.';
485
486
487
--
488 8183 aaronmk
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
489 7706 aaronmk
--
490
491
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
492
    LANGUAGE sql IMMUTABLE
493
    AS $_$
494
SELECT $2 - $1
495
$_$;
496
497
498
--
499 8183 aaronmk
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
500 6793 aaronmk
--
501
502
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
503
    LANGUAGE sql IMMUTABLE STRICT
504
    AS $_$
505
SELECT regexp_split_to_table($1, $2)
506
$_$;
507
508
509
--
510 9959 aaronmk
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
511
--
512
513
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
514
    LANGUAGE sql IMMUTABLE
515
    AS $_$
516
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
517
$_$;
518
519
520
--
521
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
522
--
523
524
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
525
    LANGUAGE plpgsql IMMUTABLE
526
    AS $$
527
DECLARE
528
	value_cmp         state%TYPE = ARRAY[value];
529
	state             state%TYPE = COALESCE(state, value_cmp);
530
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
531
BEGIN
532
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
533
END;
534
$$;
535
536
537
--
538 8183 aaronmk
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
539 8104 aaronmk
--
540
541
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
542
    LANGUAGE sql STABLE STRICT
543
    AS $_$
544
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
545
$_$;
546
547
548
--
549 8183 aaronmk
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
550 8105 aaronmk
--
551
552
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
553
    LANGUAGE plpgsql STRICT
554
    AS $_$
555
BEGIN
556
    -- not yet clustered (ARRAY[] compares NULLs literally)
557 8183 aaronmk
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
558 8105 aaronmk
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
559
    END IF;
560
END;
561
$_$;
562
563
564
--
565 8183 aaronmk
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
566 8105 aaronmk
--
567
568
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
569
570
571
--
572 10136 aaronmk
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
573
--
574
575
CREATE FUNCTION col_comment(col col_ref) RETURNS text
576
    LANGUAGE plpgsql STABLE STRICT
577
    AS $$
578
DECLARE
579
	comment text;
580
BEGIN
581
	SELECT description
582
	FROM pg_attribute
583
	LEFT JOIN pg_description ON objoid = attrelid
584
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
585
	WHERE attrelid = col.table_ AND attname = col.name
586
	INTO STRICT comment
587
	;
588
	RETURN comment;
589
EXCEPTION
590
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
591
END;
592
$$;
593
594
595
--
596 10130 aaronmk
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
597
--
598
599
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
600
    LANGUAGE plpgsql STABLE STRICT
601
    AS $$
602
DECLARE
603
	default_sql text;
604
BEGIN
605
	SELECT adsrc
606
	FROM pg_attribute
607
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
608
	WHERE attrelid = col.table_ AND attname = col.name
609
	INTO STRICT default_sql
610
	;
611
	RETURN default_sql;
612
EXCEPTION
613
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
614
END;
615
$$;
616
617
618
--
619 10134 aaronmk
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
620
--
621
622
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
623
    LANGUAGE sql STABLE
624
    AS $_$
625
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
626
$_$;
627
628
629
--
630
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
631
--
632
633
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
634
635
636
--
637 8183 aaronmk
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
638 8180 aaronmk
--
639
640
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
641
    LANGUAGE plpgsql STRICT
642
    AS $$
643
BEGIN
644 8183 aaronmk
    PERFORM util.col_type(col);
645 8180 aaronmk
    RETURN true;
646
EXCEPTION
647
    WHEN undefined_column THEN RETURN false;
648
END;
649
$$;
650
651
652
--
653 8183 aaronmk
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
654 8084 aaronmk
--
655
656
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
657 8097 aaronmk
    LANGUAGE plpgsql STABLE STRICT
658 8084 aaronmk
    AS $$
659
DECLARE
660 8183 aaronmk
    prefix text := util.name(type)||'.';
661 8084 aaronmk
BEGIN
662
    RETURN QUERY
663 8183 aaronmk
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
664
        FROM util.col_names(type) f (name_);
665 8084 aaronmk
END;
666
$$;
667
668
669
--
670 8183 aaronmk
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
671 8082 aaronmk
--
672
673
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
674 8097 aaronmk
    LANGUAGE plpgsql STABLE STRICT
675 8082 aaronmk
    AS $_$
676
BEGIN
677
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
678
END;
679
$_$;
680
681
682
--
683 8183 aaronmk
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
684 8151 aaronmk
--
685
686
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
687
    LANGUAGE sql STABLE STRICT
688
    AS $_$
689
SELECT attname::text
690
FROM pg_attribute
691 10158 aaronmk
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
692 8151 aaronmk
ORDER BY attnum
693
$_$;
694
695
696
--
697 8183 aaronmk
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
698 8106 aaronmk
--
699
700
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
701 8169 aaronmk
    LANGUAGE plpgsql STABLE STRICT
702
    AS $$
703
DECLARE
704
    type regtype;
705
BEGIN
706
    SELECT atttypid FROM pg_attribute
707
    WHERE attrelid = col.table_ AND attname = col.name
708
    INTO STRICT type
709
    ;
710
    RETURN type;
711
EXCEPTION
712 8171 aaronmk
    WHEN no_data_found THEN
713 8181 aaronmk
        RAISE undefined_column USING MESSAGE =
714
            concat('undefined column: ', col.name);
715 8169 aaronmk
END;
716
$$;
717 8106 aaronmk
718
719
--
720 8183 aaronmk
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
721 8095 aaronmk
--
722
723
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
724
    LANGUAGE sql IMMUTABLE STRICT
725
    AS $_$
726
SELECT position($1 in $2) > 0 /*1-based offset*/
727
$_$;
728
729
730
--
731 8183 aaronmk
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
732 8094 aaronmk
--
733
734
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
735
    LANGUAGE plpgsql STRICT
736
    AS $$
737
BEGIN
738 10146 aaronmk
    PERFORM util.eval(sql);
739 8094 aaronmk
EXCEPTION
740 8103 aaronmk
    WHEN duplicate_table  THEN NULL;
741 10181 aaronmk
    WHEN duplicate_object THEN NULL; -- e.g. constraint
742 8103 aaronmk
    WHEN duplicate_column THEN NULL;
743 8094 aaronmk
END;
744
$$;
745
746
747
--
748 8183 aaronmk
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
749 8094 aaronmk
--
750
751
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
752
753
754
--
755 8200 aaronmk
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
756
--
757
758
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
759
    LANGUAGE sql STRICT
760
    AS $_$
761
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
762
$_$;
763
764
765
--
766
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
767
--
768
769
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
770
771
772
--
773 10150 aaronmk
-- Name: drop_table(text); Type: FUNCTION; Schema: util; Owner: -
774
--
775
776
CREATE FUNCTION drop_table(table_ text) RETURNS void
777
    LANGUAGE sql STRICT
778
    AS $_$
779
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
780
$_$;
781
782
783
--
784
-- Name: FUNCTION drop_table(table_ text); Type: COMMENT; Schema: util; Owner: -
785
--
786
787
COMMENT ON FUNCTION drop_table(table_ text) IS 'idempotent';
788
789
790
--
791 8183 aaronmk
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
792 8086 aaronmk
--
793
794
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
795 8138 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
796 8086 aaronmk
    AS $_$
797 8183 aaronmk
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
798 8086 aaronmk
$_$;
799
800
801
--
802 9824 aaronmk
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
803
--
804
805
CREATE FUNCTION eval(sql text) RETURNS void
806
    LANGUAGE plpgsql STRICT
807
    AS $$
808
BEGIN
809
    RAISE NOTICE '%', sql;
810
    EXECUTE sql;
811
END;
812
$$;
813
814
815
--
816 10129 aaronmk
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
817 10128 aaronmk
--
818
819 10129 aaronmk
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
820 10128 aaronmk
    LANGUAGE plpgsql
821
    AS $$
822
DECLARE
823
	ret_val ret_type_null%TYPE;
824
BEGIN
825
	RAISE NOTICE '%', sql;
826
	EXECUTE sql INTO STRICT ret_val;
827
	RETURN ret_val;
828
END;
829
$$;
830
831
832
--
833 10129 aaronmk
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
834 10128 aaronmk
--
835
836 10129 aaronmk
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
837 10128 aaronmk
838
839
--
840 10131 aaronmk
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
841
--
842
843
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
844
    LANGUAGE sql
845
    AS $_$
846 10132 aaronmk
SELECT util.eval2val($$SELECT $$||$1, $2)
847 10131 aaronmk
$_$;
848
849
850
--
851
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
852
--
853
854
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
855
856
857
--
858 10133 aaronmk
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
859
--
860
861
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
862
    LANGUAGE sql
863
    AS $_$
864
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
865
$_$;
866
867
868
--
869
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
870
--
871
872
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS 'sql: can be NULL, which will be passed through
873
ret_type_null: NULL::ret_type';
874
875
876
--
877 8183 aaronmk
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
878 8182 aaronmk
--
879
880
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
881
    LANGUAGE sql STABLE STRICT
882
    AS $_$
883
SELECT col_name
884
FROM unnest($2) s (col_name)
885 8183 aaronmk
WHERE util.col_exists(($1, col_name))
886 8182 aaronmk
$_$;
887
888
889
--
890 8321 aaronmk
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
891
--
892
893
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
894
    LANGUAGE plpgsql STRICT
895
    AS $_$
896
DECLARE
897
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
898
$$||query;
899
BEGIN
900
	EXECUTE mk_view;
901
EXCEPTION
902
WHEN invalid_table_definition THEN
903 8323 aaronmk
	IF SQLERRM = 'cannot drop columns from view'
904
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
905
	THEN
906 8321 aaronmk
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
907
		EXECUTE mk_view;
908
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
909
	END IF;
910
END;
911
$_$;
912
913
914
--
915
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
916
--
917
918
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
919
920
921
--
922 8183 aaronmk
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
923 8085 aaronmk
--
924
925
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
926
    LANGUAGE sql IMMUTABLE STRICT
927
    AS $_$
928
SELECT substring($2 for length($1)) = $1
929
$_$;
930
931
932
--
933 10137 aaronmk
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
934
--
935
936
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
937
    LANGUAGE sql STABLE STRICT
938
    AS $_$
939
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
940
$_$;
941
942
943
--
944 8183 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
945 4009 aaronmk
--
946
947 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
948 4054 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
949 4009 aaronmk
    AS $_$
950 4054 aaronmk
SELECT $1 || $3 || $2
951 2595 aaronmk
$_$;
952
953
954
--
955 10110 aaronmk
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
956
--
957
958
CREATE FUNCTION map_filter_insert() RETURNS trigger
959
    LANGUAGE plpgsql
960
    AS $$
961
BEGIN
962
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
963
	RETURN new;
964
END;
965
$$;
966
967
968
--
969 8183 aaronmk
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
970 8146 aaronmk
--
971
972
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
973
    LANGUAGE plpgsql STABLE STRICT
974
    AS $_$
975
DECLARE
976
    value text;
977
BEGIN
978
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
979 8149 aaronmk
        INTO value USING key;
980 8146 aaronmk
    RETURN value;
981
END;
982
$_$;
983
984
985
--
986 8183 aaronmk
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
987 8150 aaronmk
--
988
989
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
990
    LANGUAGE plpgsql STABLE STRICT
991
    AS $_$
992
BEGIN
993
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
994
END;
995
$_$;
996
997
998
--
999 8190 aaronmk
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1000
--
1001
1002
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1003
    LANGUAGE sql STRICT
1004
    AS $_$
1005 10135 aaronmk
SELECT util.create_if_not_exists($$
1006
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1007 8190 aaronmk
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1008 10135 aaronmk
||quote_literal($2)||$$;
1009
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS 'constant';
1010
$$)
1011 8190 aaronmk
$_$;
1012
1013
1014
--
1015
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1016
--
1017
1018
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
1019
1020
1021
--
1022 8187 aaronmk
-- Name: mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1023
--
1024
1025
CREATE FUNCTION mk_derived_col(col col_ref, expr text) RETURNS void
1026
    LANGUAGE plpgsql STRICT
1027
    AS $_$
1028
DECLARE
1029
    type regtype = util.typeof(expr, col.table_::text::regtype);
1030
    col_name_sql text = quote_ident(col.name);
1031
BEGIN
1032
    PERFORM util.create_if_not_exists($$
1033
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;
1034
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1035
$$||expr||$$;
1036
$$);
1037
END;
1038
$_$;
1039
1040
1041
--
1042 8188 aaronmk
-- Name: FUNCTION mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1043
--
1044
1045
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text) IS 'idempotent';
1046
1047
1048
--
1049 8183 aaronmk
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1050 8139 aaronmk
--
1051
1052
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1053 8141 aaronmk
    LANGUAGE sql STRICT
1054 8139 aaronmk
    AS $_$
1055 8183 aaronmk
SELECT util.create_if_not_exists($$
1056 8141 aaronmk
CREATE TABLE $$||$1||$$
1057 8139 aaronmk
(
1058 8183 aaronmk
    LIKE util.map INCLUDING ALL
1059 10110 aaronmk
);
1060
1061
CREATE TRIGGER map_filter_insert
1062
  BEFORE INSERT
1063
  ON $$||$1||$$
1064
  FOR EACH ROW
1065
  EXECUTE PROCEDURE util.map_filter_insert();
1066 8141 aaronmk
$$)
1067 8139 aaronmk
$_$;
1068
1069
1070
--
1071 10113 aaronmk
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1072
--
1073
1074
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1075
    LANGUAGE sql STRICT
1076
    AS $_$
1077
SELECT util.mk_const_col(($1, 'source'), util.table_schema($1))
1078
$_$;
1079
1080
1081
--
1082
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1083
--
1084
1085
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS 'idempotent';
1086
1087
1088
--
1089 8325 aaronmk
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1090
--
1091
1092
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1093
    LANGUAGE plpgsql STRICT
1094
    AS $_$
1095
BEGIN
1096
	EXECUTE $$
1097
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1098
  RETURNS SETOF $$||view_||$$ AS
1099
$BODY1$
1100
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
1101 8331 aaronmk
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
1102 8325 aaronmk
$BODY1$
1103
  LANGUAGE sql STABLE
1104
  COST 100
1105
  ROWS 1000
1106
$$;
1107 8326 aaronmk
-- Also create subset function which turns off enable_sort
1108
	EXECUTE $$
1109
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1110
  RETURNS SETOF $$||view_||$$
1111
  SET enable_sort TO 'off'
1112
  AS
1113
$BODY1$
1114 8328 aaronmk
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
1115 8326 aaronmk
$BODY1$
1116
  LANGUAGE sql STABLE
1117
  COST 100
1118
  ROWS 1000
1119
;
1120
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1121
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1122
If you want to run EXPLAIN and get expanded output, use the regular subset
1123
function instead. (When a config param is set on a function, EXPLAIN produces
1124
just a function scan.)
1125
';
1126
$$;
1127 8325 aaronmk
END;
1128
$_$;
1129
1130
1131
--
1132 8183 aaronmk
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1133 8083 aaronmk
--
1134
1135
CREATE FUNCTION name(type regtype) RETURNS text
1136 8097 aaronmk
    LANGUAGE sql STABLE STRICT
1137 8083 aaronmk
    AS $_$
1138
SELECT typname::text FROM pg_type WHERE oid = $1
1139
$_$;
1140
1141
1142
--
1143 9958 aaronmk
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1144
--
1145
1146
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
1147
    LANGUAGE sql IMMUTABLE
1148
    AS $_$
1149
SELECT $1 IS NOT NULL AND array_length($1, 1)/*ARRAY[]->NULL*/ IS NOT NULL
1150
$_$;
1151
1152
1153
--
1154 9956 aaronmk
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
1155
--
1156
1157
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
1158 9957 aaronmk
    LANGUAGE sql IMMUTABLE
1159 9956 aaronmk
    AS $_$
1160
SELECT $1 IS NOT NULL
1161
$_$;
1162
1163
1164
--
1165 10116 aaronmk
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
1166
--
1167
1168
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
1169
    LANGUAGE plpgsql IMMUTABLE STRICT
1170
    AS $$
1171
BEGIN
1172
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
1173
END;
1174
$$;
1175
1176
1177
--
1178 8183 aaronmk
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1179 8137 aaronmk
--
1180
1181 8148 aaronmk
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
1182 8137 aaronmk
    LANGUAGE sql STRICT
1183
    AS $_$
1184 8212 aaronmk
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1185 8137 aaronmk
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
1186 8183 aaronmk
FROM util.col_names($1::text::regtype) f (name)
1187 8137 aaronmk
$_$;
1188
1189
1190
--
1191 8183 aaronmk
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
1192 8137 aaronmk
--
1193
1194 8148 aaronmk
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
1195 8137 aaronmk
1196
1197
--
1198 8183 aaronmk
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1199 8143 aaronmk
--
1200
1201
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
1202
    LANGUAGE sql STRICT
1203
    AS $_$
1204 10152 aaronmk
SELECT util.drop_table($1);
1205 8183 aaronmk
SELECT util.mk_map_table($1);
1206 8143 aaronmk
$_$;
1207
1208
1209
--
1210 9825 aaronmk
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
1211
--
1212
1213
CREATE FUNCTION search_path_append(schemas text) RETURNS void
1214
    LANGUAGE sql STRICT
1215
    AS $_$
1216
SELECT util.eval(
1217
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
1218
$_$;
1219
1220
1221
--
1222 8183 aaronmk
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1223 8153 aaronmk
--
1224
1225
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
1226
    LANGUAGE plpgsql STRICT
1227
    AS $_$
1228
DECLARE
1229 8183 aaronmk
    old text[] = ARRAY(SELECT util.col_names(table_));
1230
    new text[] = ARRAY(SELECT util.map_values(names));
1231 8153 aaronmk
BEGIN
1232
    old = old[1:array_length(new, 1)]; -- truncate to same length
1233 8212 aaronmk
    PERFORM util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1234 8153 aaronmk
        ||quote_ident(key)||$$ TO $$||quote_ident(value))
1235 10149 aaronmk
    FROM each(hstore(old, new))
1236
    WHERE value != key -- not same name
1237
    ;
1238 8153 aaronmk
END;
1239
$_$;
1240
1241
1242
--
1243 8183 aaronmk
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1244 8153 aaronmk
--
1245
1246
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
1247
1248
1249
--
1250 10145 aaronmk
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1251
--
1252
1253
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
1254
    LANGUAGE plpgsql STRICT
1255
    AS $_$
1256
DECLARE
1257
	row_ util.map;
1258
BEGIN
1259 10157 aaronmk
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
1260 10145 aaronmk
	LOOP
1261 10147 aaronmk
		PERFORM util.mk_const_col((table_, row_."to"),
1262
			substring(row_."from" from 2));
1263 10145 aaronmk
	END LOOP;
1264
1265
	PERFORM util.set_col_names(table_, names);
1266
END;
1267
$_$;
1268
1269
1270
--
1271
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1272
--
1273
1274
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS 'idempotent.
1275 10157 aaronmk
the metadata mappings must be *last* in the names table.';
1276 10145 aaronmk
1277
1278
--
1279 8183 aaronmk
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
1280 8107 aaronmk
--
1281
1282
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
1283
    LANGUAGE plpgsql STRICT
1284
    AS $_$
1285
DECLARE
1286
    sql text = $$ALTER TABLE $$||table_||$$
1287
$$||NULLIF(array_to_string(ARRAY(
1288
    SELECT
1289
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
1290
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1291
    FROM
1292
    (
1293
        SELECT
1294
          quote_ident(col_name) AS col_name_sql
1295 8183 aaronmk
        , util.col_type((table_, col_name)) AS curr_type
1296 8107 aaronmk
        , type AS target_type
1297
        FROM unnest(col_casts)
1298
    ) s
1299
    WHERE curr_type != target_type
1300
), '
1301
, '), '');
1302
BEGIN
1303
    RAISE NOTICE '%', sql;
1304
    EXECUTE COALESCE(sql, '');
1305
END;
1306
$_$;
1307
1308
1309
--
1310 8183 aaronmk
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1311 8107 aaronmk
--
1312
1313
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
1314
1315
1316
--
1317 8183 aaronmk
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1318 8144 aaronmk
--
1319
1320
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1321 8145 aaronmk
    LANGUAGE plpgsql STABLE STRICT
1322 8144 aaronmk
    AS $_$
1323
DECLARE
1324
    hstore hstore;
1325
BEGIN
1326
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1327
        table_||$$))$$ INTO STRICT hstore;
1328
    RETURN hstore;
1329
END;
1330
$_$;
1331
1332
1333
--
1334 10184 aaronmk
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1335
--
1336
1337
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
1338
    LANGUAGE sql STABLE STRICT
1339
    AS $_$
1340
SELECT COUNT(*) > 0 FROM pg_constraint
1341
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
1342
$_$;
1343
1344
1345
--
1346
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1347
--
1348
1349
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS 'gets whether a status flag is set by the presence of a table constraint';
1350
1351
1352
--
1353 10182 aaronmk
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1354
--
1355
1356
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
1357
    LANGUAGE sql STRICT
1358
    AS $_$
1359
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
1360
||quote_ident($2)||$$ CHECK (true)$$)
1361
$_$;
1362
1363
1364
--
1365
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1366
--
1367
1368
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS 'stores a status flag by the presence of a table constraint.
1369
idempotent.';
1370
1371
1372
--
1373 10185 aaronmk
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
1374
--
1375
1376
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
1377
    LANGUAGE sql STABLE STRICT
1378
    AS $_$
1379
SELECT util.table_flag__get($1, 'nulls_mapped')
1380
$_$;
1381
1382
1383
--
1384
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1385
--
1386
1387
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS 'gets whether a table''s NULL-equivalent strings have been replaced with NULL';
1388
1389
1390
--
1391 10183 aaronmk
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
1392
--
1393
1394
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
1395
    LANGUAGE sql STRICT
1396
    AS $_$
1397
SELECT util.table_flag__set($1, 'nulls_mapped')
1398
$_$;
1399
1400
1401
--
1402
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1403
--
1404
1405
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS 'sets that a table''s NULL-equivalent strings have been replaced with NULL.
1406
idempotent.';
1407
1408
1409
--
1410 10112 aaronmk
-- Name: table_schema(regclass); Type: FUNCTION; Schema: util; Owner: -
1411
--
1412
1413
CREATE FUNCTION table_schema(table_ regclass) RETURNS text
1414
    LANGUAGE sql STABLE STRICT
1415
    AS $_$
1416
SELECT nspname::text FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = $1)
1417
$_$;
1418
1419
1420
--
1421 8183 aaronmk
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1422 8088 aaronmk
--
1423
1424
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1425
    LANGUAGE plpgsql STRICT
1426
    AS $_$
1427
DECLARE
1428
    row record;
1429
BEGIN
1430 8183 aaronmk
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1431 8088 aaronmk
    LOOP
1432
        IF row.global_name != row.name THEN
1433
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1434
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1435
        END IF;
1436
    END LOOP;
1437
END;
1438
$_$;
1439
1440
1441
--
1442 8183 aaronmk
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1443 8088 aaronmk
--
1444
1445
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1446
1447
1448
--
1449 8183 aaronmk
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
1450 8142 aaronmk
--
1451
1452
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1453
    LANGUAGE plpgsql STRICT
1454
    AS $_$
1455
BEGIN
1456
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1457
END;
1458
$_$;
1459
1460
1461
--
1462 8183 aaronmk
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1463 8142 aaronmk
--
1464
1465
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1466
1467
1468
--
1469 8199 aaronmk
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1470
--
1471
1472
CREATE FUNCTION try_create(sql text) RETURNS void
1473
    LANGUAGE plpgsql STRICT
1474
    AS $$
1475
BEGIN
1476 10146 aaronmk
    PERFORM util.eval(sql);
1477 8199 aaronmk
EXCEPTION
1478 10071 aaronmk
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
1479 8199 aaronmk
    WHEN undefined_column THEN NULL;
1480
    WHEN duplicate_column THEN NULL;
1481
END;
1482
$$;
1483
1484
1485
--
1486
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1487
--
1488
1489
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1490
1491
1492
--
1493 8209 aaronmk
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1494
--
1495
1496
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1497
    LANGUAGE sql STRICT
1498
    AS $_$
1499
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1500
$_$;
1501
1502
1503
--
1504
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1505
--
1506
1507
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1508
1509
1510
--
1511 8189 aaronmk
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1512
--
1513
1514
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1515
    LANGUAGE sql IMMUTABLE
1516
    AS $_$
1517
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1518
$_$;
1519
1520
1521
--
1522 10161 aaronmk
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
1523
--
1524
1525
COMMENT ON FUNCTION type_qual(value anyelement) IS 'a type''s NOT NULL qualifier';
1526
1527
1528
--
1529 8324 aaronmk
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1530
--
1531
1532
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1533
    LANGUAGE sql STABLE STRICT
1534
    SET search_path TO pg_temp
1535
    AS $_$
1536
SELECT $1::text
1537
$_$;
1538
1539
1540
--
1541 10161 aaronmk
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
1542
--
1543
1544
COMMENT ON FUNCTION type_qual_name(type regtype) IS 'a type''s schema-qualified name';
1545
1546
1547
--
1548 8185 aaronmk
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1549
--
1550
1551 10160 aaronmk
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
1552
    LANGUAGE plpgsql STABLE
1553 8185 aaronmk
    AS $_$
1554
DECLARE
1555
    type regtype;
1556
BEGIN
1557 10160 aaronmk
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
1558
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
1559 8185 aaronmk
    RETURN type;
1560
END;
1561
$_$;
1562
1563
1564
--
1565 9959 aaronmk
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
1566
--
1567
1568
CREATE AGGREGATE all_same(anyelement) (
1569
    SFUNC = all_same_transform,
1570
    STYPE = anyarray,
1571
    FINALFUNC = all_same_final
1572
);
1573
1574
1575
--
1576
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
1577
--
1578
1579
COMMENT ON AGGREGATE all_same(anyelement) IS 'includes NULLs in comparison';
1580
1581
1582
--
1583 8183 aaronmk
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1584 2595 aaronmk
--
1585
1586
CREATE AGGREGATE join_strs(text, text) (
1587 4052 aaronmk
    SFUNC = join_strs_transform,
1588 4010 aaronmk
    STYPE = text
1589 2595 aaronmk
);
1590
1591
1592 8147 aaronmk
--
1593 8183 aaronmk
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1594 8147 aaronmk
--
1595
1596
CREATE OPERATOR -> (
1597
    PROCEDURE = map_get,
1598
    LEFTARG = regclass,
1599
    RIGHTARG = text
1600
);
1601
1602
1603 8140 aaronmk
SET default_tablespace = '';
1604
1605
SET default_with_oids = false;
1606
1607 2107 aaronmk
--
1608 8183 aaronmk
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
1609 8140 aaronmk
--
1610
1611
CREATE TABLE map (
1612
    "from" text NOT NULL,
1613 8158 aaronmk
    "to" text,
1614
    filter text,
1615
    notes text
1616 8140 aaronmk
);
1617
1618
1619
--
1620 8183 aaronmk
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
1621 8140 aaronmk
--
1622
1623
1624
1625
--
1626 8183 aaronmk
-- Name: map_pkey; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
1627 8140 aaronmk
--
1628
1629
ALTER TABLE ONLY map
1630
    ADD CONSTRAINT map_pkey PRIMARY KEY ("from");
1631
1632
1633
--
1634 10110 aaronmk
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
1635
--
1636
1637
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
1638
1639
1640
--
1641 2136 aaronmk
-- PostgreSQL database dump complete
1642
--