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 8183 aaronmk
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
256 6222 aaronmk
--
257
258
CREATE FUNCTION _map(map hstore, value text) RETURNS text
259 7820 aaronmk
    LANGUAGE plpgsql IMMUTABLE STRICT
260 6222 aaronmk
    AS $$
261
DECLARE
262 6271 aaronmk
    match text := map -> value;
263 6222 aaronmk
BEGIN
264 6271 aaronmk
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
265
        match := map -> '*'; -- use default entry
266
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
267
        END IF;
268
    END IF;
269
270
    -- Interpret result
271 6243 aaronmk
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
272
    ELSIF match = '*' THEN RETURN value;
273
    ELSE RETURN match;
274 6222 aaronmk
    END IF;
275
END;
276
$$;
277
278
279
--
280 8183 aaronmk
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
281 5408 aaronmk
--
282
283
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
284
    LANGUAGE sql IMMUTABLE
285
    AS $_$
286 7289 aaronmk
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
287 5408 aaronmk
$_$;
288
289
290
--
291 8183 aaronmk
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
292 2940 aaronmk
--
293
294 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
295 2940 aaronmk
    LANGUAGE sql IMMUTABLE
296
    AS $_$
297 8183 aaronmk
SELECT util.join_strs(value, '; ')
298 2940 aaronmk
FROM
299
(
300
    SELECT *
301
    FROM
302
    (
303
        SELECT
304
        DISTINCT ON (value)
305
        *
306
        FROM
307
        (VALUES
308 4012 aaronmk
              (1, $1)
309
            , (2, $2)
310
            , (3, $3)
311
            , (4, $4)
312
            , (5, $5)
313
            , (6, $6)
314
            , (7, $7)
315
            , (8, $8)
316
            , (9, $9)
317
            , (10, $10)
318 2940 aaronmk
        )
319
        AS v (sort_order, value)
320 4011 aaronmk
        WHERE value IS NOT NULL
321 2940 aaronmk
    )
322
    AS v
323
    ORDER BY sort_order
324
)
325
AS v
326
$_$;
327
328
329
--
330 8183 aaronmk
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
331 7140 aaronmk
--
332
333
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
334
    LANGUAGE sql IMMUTABLE
335
    AS $_$
336
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
337
$_$;
338
339
340
--
341 8183 aaronmk
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
342 6354 aaronmk
--
343
344
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
345
    LANGUAGE sql IMMUTABLE
346
    AS $_$
347 8183 aaronmk
SELECT util.join_strs(value, ' ')
348 6354 aaronmk
FROM
349
(
350
    SELECT *
351
    FROM
352
    (
353
        SELECT
354
        DISTINCT ON (value)
355
        *
356
        FROM
357
        (VALUES
358
              (1, $1)
359
            , (2, $2)
360
            , (3, $3)
361
            , (4, $4)
362
            , (5, $5)
363
            , (6, $6)
364
            , (7, $7)
365
            , (8, $8)
366
            , (9, $9)
367
            , (10, $10)
368
        )
369
        AS v (sort_order, value)
370
        WHERE value IS NOT NULL
371
    )
372
    AS v
373
    ORDER BY sort_order
374
)
375
AS v
376
$_$;
377
378
379
--
380 8183 aaronmk
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
381 5408 aaronmk
--
382
383
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
384
    LANGUAGE sql IMMUTABLE
385
    AS $_$
386 7289 aaronmk
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
387 5408 aaronmk
$_$;
388
389
390
--
391 8183 aaronmk
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
392 6316 aaronmk
--
393
394
CREATE FUNCTION _not(value boolean) RETURNS boolean
395
    LANGUAGE sql IMMUTABLE STRICT
396
    AS $_$
397
SELECT NOT $1
398
$_$;
399
400
401
--
402 8183 aaronmk
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
403 7104 aaronmk
--
404
405
CREATE FUNCTION _now() RETURNS timestamp with time zone
406
    LANGUAGE sql STABLE
407
    AS $$
408
SELECT now()
409
$$;
410
411
412
--
413 8183 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
414 2949 aaronmk
--
415
416 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
417 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
418
    AS $$
419
DECLARE
420 8183 aaronmk
    type util.datatype NOT NULL := type; -- add NOT NULL
421 2949 aaronmk
BEGIN
422 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
423 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
424 2949 aaronmk
    ELSIF type = 'float' THEN
425 2722 aaronmk
        DECLARE
426
            -- Outside the try block so that invalid null value generates error
427 2949 aaronmk
            "null" double precision := "null"::double precision;
428 2722 aaronmk
        BEGIN
429 2949 aaronmk
            RETURN nullif(value::double precision, "null");
430 2722 aaronmk
        EXCEPTION
431 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
432 2722 aaronmk
        END;
433 2610 aaronmk
    END IF;
434
END;
435
$$;
436
437
438
--
439 8183 aaronmk
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
440 4479 aaronmk
--
441
442
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
443
    LANGUAGE sql IMMUTABLE
444
    AS $_$
445 8183 aaronmk
SELECT util."_nullIf"($1, $2, $3::util.datatype)
446 4479 aaronmk
$_$;
447
448
449
--
450 8183 aaronmk
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
451 6355 aaronmk
--
452
453
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
454
    LANGUAGE sql IMMUTABLE
455
    AS $_$
456
SELECT bool_or(value)
457
FROM
458
(VALUES
459
      ($1)
460
    , ($2)
461
    , ($3)
462
    , ($4)
463
    , ($5)
464
)
465
AS v (value)
466
$_$;
467
468
469
--
470 8183 aaronmk
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
471 6437 aaronmk
--
472
473
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.';
474
475
476
--
477 8183 aaronmk
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
478 7706 aaronmk
--
479
480
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
481
    LANGUAGE sql IMMUTABLE
482
    AS $_$
483
SELECT $2 - $1
484
$_$;
485
486
487
--
488 8183 aaronmk
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
489 6793 aaronmk
--
490
491
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
492
    LANGUAGE sql IMMUTABLE STRICT
493
    AS $_$
494
SELECT regexp_split_to_table($1, $2)
495
$_$;
496
497
498
--
499 8183 aaronmk
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
500 8104 aaronmk
--
501
502
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
503
    LANGUAGE sql STABLE STRICT
504
    AS $_$
505
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
506
$_$;
507
508
509
--
510 8183 aaronmk
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
511 8105 aaronmk
--
512
513
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
514
    LANGUAGE plpgsql STRICT
515
    AS $_$
516
BEGIN
517
    -- not yet clustered (ARRAY[] compares NULLs literally)
518 8183 aaronmk
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
519 8105 aaronmk
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
520
    END IF;
521
END;
522
$_$;
523
524
525
--
526 8183 aaronmk
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
527 8105 aaronmk
--
528
529
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
530
531
532
--
533 8183 aaronmk
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
534 8180 aaronmk
--
535
536
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
537
    LANGUAGE plpgsql STRICT
538
    AS $$
539
BEGIN
540 8183 aaronmk
    PERFORM util.col_type(col);
541 8180 aaronmk
    RETURN true;
542
EXCEPTION
543
    WHEN undefined_column THEN RETURN false;
544
END;
545
$$;
546
547
548
--
549 8183 aaronmk
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
550 8084 aaronmk
--
551
552
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
553 8097 aaronmk
    LANGUAGE plpgsql STABLE STRICT
554 8084 aaronmk
    AS $$
555
DECLARE
556 8183 aaronmk
    prefix text := util.name(type)||'.';
557 8084 aaronmk
BEGIN
558
    RETURN QUERY
559 8183 aaronmk
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
560
        FROM util.col_names(type) f (name_);
561 8084 aaronmk
END;
562
$$;
563
564
565
--
566 8183 aaronmk
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
567 8082 aaronmk
--
568
569
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
570 8097 aaronmk
    LANGUAGE plpgsql STABLE STRICT
571 8082 aaronmk
    AS $_$
572
BEGIN
573
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
574
END;
575
$_$;
576
577
578
--
579 8183 aaronmk
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
580 8151 aaronmk
--
581
582
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
583
    LANGUAGE sql STABLE STRICT
584
    AS $_$
585
SELECT attname::text
586
FROM pg_attribute
587
WHERE attrelid = $1 AND attnum >= 1
588
ORDER BY attnum
589
$_$;
590
591
592
--
593 8183 aaronmk
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
594 8106 aaronmk
--
595
596
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
597 8169 aaronmk
    LANGUAGE plpgsql STABLE STRICT
598
    AS $$
599
DECLARE
600
    type regtype;
601
BEGIN
602
    SELECT atttypid FROM pg_attribute
603
    WHERE attrelid = col.table_ AND attname = col.name
604
    INTO STRICT type
605
    ;
606
    RETURN type;
607
EXCEPTION
608 8171 aaronmk
    WHEN no_data_found THEN
609 8181 aaronmk
        RAISE undefined_column USING MESSAGE =
610
            concat('undefined column: ', col.name);
611 8169 aaronmk
END;
612
$$;
613 8106 aaronmk
614
615
--
616 8183 aaronmk
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
617 8095 aaronmk
--
618
619
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
620
    LANGUAGE sql IMMUTABLE STRICT
621
    AS $_$
622
SELECT position($1 in $2) > 0 /*1-based offset*/
623
$_$;
624
625
626
--
627 8183 aaronmk
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
628 8094 aaronmk
--
629
630
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
631
    LANGUAGE plpgsql STRICT
632
    AS $$
633
BEGIN
634
    EXECUTE sql;
635
EXCEPTION
636 8103 aaronmk
    WHEN duplicate_table  THEN NULL;
637
    WHEN duplicate_column THEN NULL;
638 8094 aaronmk
END;
639
$$;
640
641
642
--
643 8183 aaronmk
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
644 8094 aaronmk
--
645
646
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
647
648
649
--
650 8200 aaronmk
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
651
--
652
653
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
654
    LANGUAGE sql STRICT
655
    AS $_$
656
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
657
$_$;
658
659
660
--
661
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
662
--
663
664
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
665
666
667
--
668 8183 aaronmk
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
669 8086 aaronmk
--
670
671
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
672 8138 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
673 8086 aaronmk
    AS $_$
674 8183 aaronmk
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
675 8086 aaronmk
$_$;
676
677
678
--
679 8183 aaronmk
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
680 8182 aaronmk
--
681
682
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
683
    LANGUAGE sql STABLE STRICT
684
    AS $_$
685
SELECT col_name
686
FROM unnest($2) s (col_name)
687 8183 aaronmk
WHERE util.col_exists(($1, col_name))
688 8182 aaronmk
$_$;
689
690
691
--
692 8321 aaronmk
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
693
--
694
695
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
696
    LANGUAGE plpgsql STRICT
697
    AS $_$
698
DECLARE
699
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
700
$$||query;
701
BEGIN
702
	EXECUTE mk_view;
703
EXCEPTION
704
WHEN invalid_table_definition THEN
705 8323 aaronmk
	IF SQLERRM = 'cannot drop columns from view'
706
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
707
	THEN
708 8321 aaronmk
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
709
		EXECUTE mk_view;
710
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
711
	END IF;
712
END;
713
$_$;
714
715
716
--
717
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
718
--
719
720
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
721
722
723
--
724 8183 aaronmk
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
725 8085 aaronmk
--
726
727
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
728
    LANGUAGE sql IMMUTABLE STRICT
729
    AS $_$
730
SELECT substring($2 for length($1)) = $1
731
$_$;
732
733
734
--
735 8183 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
736 4009 aaronmk
--
737
738 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
739 4054 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
740 4009 aaronmk
    AS $_$
741 4054 aaronmk
SELECT $1 || $3 || $2
742 2595 aaronmk
$_$;
743
744
745
--
746 8183 aaronmk
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
747 8146 aaronmk
--
748
749
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
750
    LANGUAGE plpgsql STABLE STRICT
751
    AS $_$
752
DECLARE
753
    value text;
754
BEGIN
755
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
756 8149 aaronmk
        INTO value USING key;
757 8146 aaronmk
    RETURN value;
758
END;
759
$_$;
760
761
762
--
763 8183 aaronmk
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
764 8150 aaronmk
--
765
766
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
767
    LANGUAGE plpgsql STABLE STRICT
768
    AS $_$
769
BEGIN
770
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
771
END;
772
$_$;
773
774
775
--
776 8190 aaronmk
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
777
--
778
779
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
780
    LANGUAGE sql STRICT
781
    AS $_$
782
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
783
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
784
||quote_literal($2))
785
$_$;
786
787
788
--
789
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
790
--
791
792
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
793
794
795
--
796 8187 aaronmk
-- Name: mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
797
--
798
799
CREATE FUNCTION mk_derived_col(col col_ref, expr text) RETURNS void
800
    LANGUAGE plpgsql STRICT
801
    AS $_$
802
DECLARE
803
    type regtype = util.typeof(expr, col.table_::text::regtype);
804
    col_name_sql text = quote_ident(col.name);
805
BEGIN
806
    PERFORM util.create_if_not_exists($$
807
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;
808
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
809
$$||expr||$$;
810
$$);
811
END;
812
$_$;
813
814
815
--
816 8188 aaronmk
-- Name: FUNCTION mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
817
--
818
819
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text) IS 'idempotent';
820
821
822
--
823 8183 aaronmk
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
824 8139 aaronmk
--
825
826
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
827 8141 aaronmk
    LANGUAGE sql STRICT
828 8139 aaronmk
    AS $_$
829 8183 aaronmk
SELECT util.create_if_not_exists($$
830 8141 aaronmk
CREATE TABLE $$||$1||$$
831 8139 aaronmk
(
832 8183 aaronmk
    LIKE util.map INCLUDING ALL
833 8140 aaronmk
)
834 8141 aaronmk
$$)
835 8139 aaronmk
$_$;
836
837
838
--
839 8325 aaronmk
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
840
--
841
842
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
843
    LANGUAGE plpgsql STRICT
844
    AS $_$
845
BEGIN
846
	EXECUTE $$
847
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
848
  RETURNS SETOF $$||view_||$$ AS
849
$BODY1$
850
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
851 8331 aaronmk
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
852 8325 aaronmk
$BODY1$
853
  LANGUAGE sql STABLE
854
  COST 100
855
  ROWS 1000
856
$$;
857 8326 aaronmk
-- Also create subset function which turns off enable_sort
858
	EXECUTE $$
859
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
860
  RETURNS SETOF $$||view_||$$
861
  SET enable_sort TO 'off'
862
  AS
863
$BODY1$
864 8328 aaronmk
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
865 8326 aaronmk
$BODY1$
866
  LANGUAGE sql STABLE
867
  COST 100
868
  ROWS 1000
869
;
870
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
871
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
872
If you want to run EXPLAIN and get expanded output, use the regular subset
873
function instead. (When a config param is set on a function, EXPLAIN produces
874
just a function scan.)
875
';
876
$$;
877 8325 aaronmk
END;
878
$_$;
879
880
881
--
882 8183 aaronmk
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
883 8083 aaronmk
--
884
885
CREATE FUNCTION name(type regtype) RETURNS text
886 8097 aaronmk
    LANGUAGE sql STABLE STRICT
887 8083 aaronmk
    AS $_$
888
SELECT typname::text FROM pg_type WHERE oid = $1
889
$_$;
890
891
892
--
893 8183 aaronmk
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
894 8137 aaronmk
--
895
896 8148 aaronmk
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
897 8137 aaronmk
    LANGUAGE sql STRICT
898
    AS $_$
899 8212 aaronmk
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
900 8137 aaronmk
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
901 8183 aaronmk
FROM util.col_names($1::text::regtype) f (name)
902 8137 aaronmk
$_$;
903
904
905
--
906 8183 aaronmk
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
907 8137 aaronmk
--
908
909 8148 aaronmk
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
910 8137 aaronmk
911
912
--
913 8183 aaronmk
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
914 8143 aaronmk
--
915
916
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
917
    LANGUAGE sql STRICT
918
    AS $_$
919 8183 aaronmk
SELECT util.mk_map_table($1);
920
SELECT util.truncate($1);
921 8143 aaronmk
$_$;
922
923
924
--
925 8183 aaronmk
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
926 8153 aaronmk
--
927
928
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
929
    LANGUAGE plpgsql STRICT
930
    AS $_$
931
DECLARE
932 8183 aaronmk
    old text[] = ARRAY(SELECT util.col_names(table_));
933
    new text[] = ARRAY(SELECT util.map_values(names));
934 8153 aaronmk
BEGIN
935
    old = old[1:array_length(new, 1)]; -- truncate to same length
936 8212 aaronmk
    PERFORM util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
937 8153 aaronmk
        ||quote_ident(key)||$$ TO $$||quote_ident(value))
938
    FROM each(hstore(old, new));
939
END;
940
$_$;
941
942
943
--
944 8183 aaronmk
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
945 8153 aaronmk
--
946
947
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
948
949
950
--
951 8183 aaronmk
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
952 8107 aaronmk
--
953
954
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
955
    LANGUAGE plpgsql STRICT
956
    AS $_$
957
DECLARE
958
    sql text = $$ALTER TABLE $$||table_||$$
959
$$||NULLIF(array_to_string(ARRAY(
960
    SELECT
961
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
962
    ||$$ USING $$||col_name_sql||$$::$$||target_type
963
    FROM
964
    (
965
        SELECT
966
          quote_ident(col_name) AS col_name_sql
967 8183 aaronmk
        , util.col_type((table_, col_name)) AS curr_type
968 8107 aaronmk
        , type AS target_type
969
        FROM unnest(col_casts)
970
    ) s
971
    WHERE curr_type != target_type
972
), '
973
, '), '');
974
BEGIN
975
    RAISE NOTICE '%', sql;
976
    EXECUTE COALESCE(sql, '');
977
END;
978
$_$;
979
980
981
--
982 8183 aaronmk
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
983 8107 aaronmk
--
984
985
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
986
987
988
--
989 8183 aaronmk
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
990 8144 aaronmk
--
991
992
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
993 8145 aaronmk
    LANGUAGE plpgsql STABLE STRICT
994 8144 aaronmk
    AS $_$
995
DECLARE
996
    hstore hstore;
997
BEGIN
998
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
999
        table_||$$))$$ INTO STRICT hstore;
1000
    RETURN hstore;
1001
END;
1002
$_$;
1003
1004
1005
--
1006 8183 aaronmk
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1007 8088 aaronmk
--
1008
1009
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1010
    LANGUAGE plpgsql STRICT
1011
    AS $_$
1012
DECLARE
1013
    row record;
1014
BEGIN
1015 8183 aaronmk
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1016 8088 aaronmk
    LOOP
1017
        IF row.global_name != row.name THEN
1018
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1019
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1020
        END IF;
1021
    END LOOP;
1022
END;
1023
$_$;
1024
1025
1026
--
1027 8183 aaronmk
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1028 8088 aaronmk
--
1029
1030
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1031
1032
1033
--
1034 8183 aaronmk
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
1035 8142 aaronmk
--
1036
1037
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1038
    LANGUAGE plpgsql STRICT
1039
    AS $_$
1040
BEGIN
1041
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1042
END;
1043
$_$;
1044
1045
1046
--
1047 8183 aaronmk
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1048 8142 aaronmk
--
1049
1050
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1051
1052
1053
--
1054 8199 aaronmk
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1055
--
1056
1057
CREATE FUNCTION try_create(sql text) RETURNS void
1058
    LANGUAGE plpgsql STRICT
1059
    AS $$
1060
BEGIN
1061
    EXECUTE sql;
1062
EXCEPTION
1063
    WHEN undefined_column THEN NULL;
1064
    WHEN duplicate_column THEN NULL;
1065
END;
1066
$$;
1067
1068
1069
--
1070
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1071
--
1072
1073
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1074
1075
1076
--
1077 8209 aaronmk
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1078
--
1079
1080
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1081
    LANGUAGE sql STRICT
1082
    AS $_$
1083
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1084
$_$;
1085
1086
1087
--
1088
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1089
--
1090
1091
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1092
1093
1094
--
1095 8189 aaronmk
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1096
--
1097
1098
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1099
    LANGUAGE sql IMMUTABLE
1100
    AS $_$
1101
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1102
$_$;
1103
1104
1105
--
1106 8324 aaronmk
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1107
--
1108
1109
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1110
    LANGUAGE sql STABLE STRICT
1111
    SET search_path TO pg_temp
1112
    AS $_$
1113
SELECT $1::text
1114
$_$;
1115
1116
1117
--
1118 8185 aaronmk
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1119
--
1120
1121
CREATE FUNCTION typeof(expr text, table_ regtype) RETURNS regtype
1122
    LANGUAGE plpgsql STABLE STRICT
1123
    AS $_$
1124
DECLARE
1125
    type regtype;
1126
BEGIN
1127
    EXECUTE $$SELECT pg_typeof($$||expr||$$) FROM (SELECT (NULL::$$||table_||
1128
    $$).*) _s$$ INTO STRICT type;
1129
    RETURN type;
1130
END;
1131
$_$;
1132
1133
1134
--
1135 8183 aaronmk
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1136 2595 aaronmk
--
1137
1138
CREATE AGGREGATE join_strs(text, text) (
1139 4052 aaronmk
    SFUNC = join_strs_transform,
1140 4010 aaronmk
    STYPE = text
1141 2595 aaronmk
);
1142
1143
1144 8147 aaronmk
--
1145 8183 aaronmk
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1146 8147 aaronmk
--
1147
1148
CREATE OPERATOR -> (
1149
    PROCEDURE = map_get,
1150
    LEFTARG = regclass,
1151
    RIGHTARG = text
1152
);
1153
1154
1155 8140 aaronmk
SET default_tablespace = '';
1156
1157
SET default_with_oids = false;
1158
1159 2107 aaronmk
--
1160 8183 aaronmk
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
1161 8140 aaronmk
--
1162
1163
CREATE TABLE map (
1164
    "from" text NOT NULL,
1165 8158 aaronmk
    "to" text,
1166
    filter text,
1167
    notes text
1168 8140 aaronmk
);
1169
1170
1171
--
1172 8183 aaronmk
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
1173 8140 aaronmk
--
1174
1175
1176
1177
--
1178 8183 aaronmk
-- Name: map_pkey; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
1179 8140 aaronmk
--
1180
1181
ALTER TABLE ONLY map
1182
    ADD CONSTRAINT map_pkey PRIMARY KEY ("from");
1183
1184
1185
--
1186 2136 aaronmk
-- PostgreSQL database dump complete
1187
--