Project

General

Profile

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

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

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

    
15
CREATE SCHEMA util;
16

    
17

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

    
22
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

    
24

    
25
SET search_path = util, pg_catalog;
26

    
27
--
28
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
29
--
30

    
31
CREATE TYPE col_cast AS (
32
	col_name text,
33
	type regtype
34
);
35

    
36

    
37
--
38
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
39
--
40

    
41
CREATE TYPE col_ref AS (
42
	table_ regclass,
43
	name text
44
);
45

    
46

    
47
--
48
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
49
--
50

    
51
CREATE TYPE compass_dir AS ENUM (
52
    'N',
53
    'E',
54
    'S',
55
    'W'
56
);
57

    
58

    
59
--
60
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
61
--
62

    
63
CREATE TYPE datatype AS ENUM (
64
    'str',
65
    'float'
66
);
67

    
68

    
69
--
70
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
71
--
72

    
73
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
    LANGUAGE sql IMMUTABLE
75
    AS $_$
76
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
77
$_$;
78

    
79

    
80
--
81
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
82
--
83

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

    
99

    
100
--
101
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
102
--
103

    
104
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

    
106

    
107
--
108
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
109
--
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
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
129
--
130

    
131
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
132
    LANGUAGE sql IMMUTABLE STRICT
133
    AS $_$
134
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
FROM 
136
(
137
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
138
    UNION ALL
139
    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
)
142
matches (g)
143
$_$;
144

    
145

    
146
--
147
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
148
--
149

    
150
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
    LANGUAGE sql IMMUTABLE
152
    AS $_$
153
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
154
FROM
155
(VALUES
156
      ($1)
157
    , ($2/60)
158
    , ($3/60/60)
159
)
160
AS v (value)
161
$_$;
162

    
163

    
164
--
165
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
166
--
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
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
172
$_$;
173

    
174

    
175
--
176
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
177
--
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
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
188
--
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
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
201
--
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
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
212
--
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
SELECT util._if($1 != '', $2, $3)
218
$_$;
219

    
220

    
221
--
222
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
223
--
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
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
229
$_$;
230

    
231

    
232
--
233
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
234
--
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
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
240
$_$;
241

    
242

    
243
--
244
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
245
--
246

    
247
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
248
    LANGUAGE sql IMMUTABLE
249
    AS $_$
250
SELECT coalesce($1 || ': ', '') || $2
251
$_$;
252

    
253

    
254
--
255
-- 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
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
267
--
268

    
269
CREATE FUNCTION _map(map hstore, value text) RETURNS text
270
    LANGUAGE plpgsql IMMUTABLE STRICT
271
    AS $$
272
DECLARE
273
    match text := map -> value;
274
BEGIN
275
    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
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
283
    ELSIF match = '*' THEN RETURN value;
284
    ELSE RETURN match;
285
    END IF;
286
END;
287
$$;
288

    
289

    
290
--
291
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
292
--
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
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
298
$_$;
299

    
300

    
301
--
302
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
303
--
304

    
305
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
    LANGUAGE sql IMMUTABLE
307
    AS $_$
308
SELECT util.join_strs(value, '; ')
309
FROM
310
(
311
    SELECT *
312
    FROM
313
    (
314
        SELECT
315
        DISTINCT ON (value)
316
        *
317
        FROM
318
        (VALUES
319
              (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
        )
330
        AS v (sort_order, value)
331
        WHERE value IS NOT NULL
332
    )
333
    AS v
334
    ORDER BY sort_order
335
)
336
AS v
337
$_$;
338

    
339

    
340
--
341
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
342
--
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
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
353
--
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
SELECT util.join_strs(value, ' ')
359
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
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
392
--
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
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
398
$_$;
399

    
400

    
401
--
402
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
403
--
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
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
414
--
415

    
416
CREATE FUNCTION _now() RETURNS timestamp with time zone
417
    LANGUAGE sql STABLE
418
    AS $$
419
SELECT now()
420
$$;
421

    
422

    
423
--
424
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
425
--
426

    
427
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
428
    LANGUAGE plpgsql IMMUTABLE
429
    AS $$
430
DECLARE
431
    type util.datatype NOT NULL := type; -- add NOT NULL
432
BEGIN
433
    IF type = 'str' THEN RETURN nullif(value::text, "null");
434
    -- Invalid value is ignored, but invalid null value generates error
435
    ELSIF type = 'float' THEN
436
        DECLARE
437
            -- Outside the try block so that invalid null value generates error
438
            "null" double precision := "null"::double precision;
439
        BEGIN
440
            RETURN nullif(value::double precision, "null");
441
        EXCEPTION
442
            WHEN data_exception THEN RETURN value; -- ignore invalid value
443
        END;
444
    END IF;
445
END;
446
$$;
447

    
448

    
449
--
450
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
451
--
452

    
453
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
454
    LANGUAGE sql IMMUTABLE
455
    AS $_$
456
SELECT util."_nullIf"($1, $2, $3::util.datatype)
457
$_$;
458

    
459

    
460
--
461
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
462
--
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
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
482
--
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
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
489
--
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
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
500
--
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
-- 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
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
539
--
540

    
541
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
542
    LANGUAGE sql IMMUTABLE STRICT
543
    AS $_$
544
SELECT array_length($1, 1)
545
$_$;
546

    
547

    
548
--
549
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
550
--
551

    
552
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
553
    LANGUAGE sql STABLE STRICT
554
    AS $_$
555
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
556
$_$;
557

    
558

    
559
--
560
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
561
--
562

    
563
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
564
    LANGUAGE plpgsql STRICT
565
    AS $_$
566
BEGIN
567
    -- not yet clustered (ARRAY[] compares NULLs literally)
568
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
569
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
570
    END IF;
571
END;
572
$_$;
573

    
574

    
575
--
576
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
577
--
578

    
579
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
580

    
581

    
582
--
583
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
584
--
585

    
586
CREATE FUNCTION col_comment(col col_ref) RETURNS text
587
    LANGUAGE plpgsql STABLE STRICT
588
    AS $$
589
DECLARE
590
	comment text;
591
BEGIN
592
	SELECT description
593
	FROM pg_attribute
594
	LEFT JOIN pg_description ON objoid = attrelid
595
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
596
	WHERE attrelid = col.table_ AND attname = col.name
597
	INTO STRICT comment
598
	;
599
	RETURN comment;
600
EXCEPTION
601
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
602
END;
603
$$;
604

    
605

    
606
--
607
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
608
--
609

    
610
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
611
    LANGUAGE plpgsql STABLE STRICT
612
    AS $$
613
DECLARE
614
	default_sql text;
615
BEGIN
616
	SELECT adsrc
617
	FROM pg_attribute
618
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
619
	WHERE attrelid = col.table_ AND attname = col.name
620
	INTO STRICT default_sql
621
	;
622
	RETURN default_sql;
623
EXCEPTION
624
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
625
END;
626
$$;
627

    
628

    
629
--
630
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
631
--
632

    
633
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
634
    LANGUAGE sql STABLE
635
    AS $_$
636
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
637
$_$;
638

    
639

    
640
--
641
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
642
--
643

    
644
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
645

    
646

    
647
--
648
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
649
--
650

    
651
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
652
    LANGUAGE plpgsql STRICT
653
    AS $$
654
BEGIN
655
    PERFORM util.col_type(col);
656
    RETURN true;
657
EXCEPTION
658
    WHEN undefined_column THEN RETURN false;
659
END;
660
$$;
661

    
662

    
663
--
664
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
665
--
666

    
667
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
668
    LANGUAGE plpgsql STABLE STRICT
669
    AS $$
670
DECLARE
671
    prefix text := util.name(type)||'.';
672
BEGIN
673
    RETURN QUERY
674
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
675
        FROM util.col_names(type) f (name_);
676
END;
677
$$;
678

    
679

    
680
--
681
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
682
--
683

    
684
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
685
    LANGUAGE plpgsql STABLE STRICT
686
    AS $_$
687
BEGIN
688
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
689
END;
690
$_$;
691

    
692

    
693
--
694
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
695
--
696

    
697
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
698
    LANGUAGE sql STABLE STRICT
699
    AS $_$
700
SELECT attname::text
701
FROM pg_attribute
702
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
703
ORDER BY attnum
704
$_$;
705

    
706

    
707
--
708
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
709
--
710

    
711
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
712
    LANGUAGE plpgsql STABLE STRICT
713
    AS $$
714
DECLARE
715
    type regtype;
716
BEGIN
717
    SELECT atttypid FROM pg_attribute
718
    WHERE attrelid = col.table_ AND attname = col.name
719
    INTO STRICT type
720
    ;
721
    RETURN type;
722
EXCEPTION
723
    WHEN no_data_found THEN
724
        RAISE undefined_column USING MESSAGE =
725
            concat('undefined column: ', col.name);
726
END;
727
$$;
728

    
729

    
730
--
731
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
732
--
733

    
734
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
735
    LANGUAGE sql IMMUTABLE STRICT
736
    AS $_$
737
SELECT position($1 in $2) > 0 /*1-based offset*/
738
$_$;
739

    
740

    
741
--
742
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
743
--
744

    
745
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
746
    LANGUAGE plpgsql STRICT
747
    AS $$
748
BEGIN
749
    PERFORM util.eval(sql);
750
EXCEPTION
751
    WHEN duplicate_table  THEN NULL;
752
    WHEN duplicate_object THEN NULL; -- e.g. constraint
753
    WHEN duplicate_column THEN NULL;
754
    WHEN invalid_table_definition THEN
755
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
756
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
757
        END IF;
758
END;
759
$$;
760

    
761

    
762
--
763
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
764
--
765

    
766
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
767

    
768

    
769
--
770
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
771
--
772

    
773
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
774
    LANGUAGE sql STRICT
775
    AS $_$
776
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
777
$_$;
778

    
779

    
780
--
781
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
782
--
783

    
784
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
785

    
786

    
787
--
788
-- Name: drop_table(text); Type: FUNCTION; Schema: util; Owner: -
789
--
790

    
791
CREATE FUNCTION drop_table(table_ text) RETURNS void
792
    LANGUAGE sql STRICT
793
    AS $_$
794
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
795
$_$;
796

    
797

    
798
--
799
-- Name: FUNCTION drop_table(table_ text); Type: COMMENT; Schema: util; Owner: -
800
--
801

    
802
COMMENT ON FUNCTION drop_table(table_ text) IS 'idempotent';
803

    
804

    
805
--
806
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
807
--
808

    
809
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
810
    LANGUAGE sql IMMUTABLE STRICT
811
    AS $_$
812
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
813
$_$;
814

    
815

    
816
--
817
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
818
--
819

    
820
CREATE FUNCTION eval(sql text) RETURNS void
821
    LANGUAGE plpgsql STRICT
822
    AS $$
823
BEGIN
824
    RAISE NOTICE '%', sql;
825
    EXECUTE sql;
826
END;
827
$$;
828

    
829

    
830
--
831
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
832
--
833

    
834
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
835
    LANGUAGE plpgsql
836
    AS $$
837
DECLARE
838
	ret_val ret_type_null%TYPE;
839
BEGIN
840
	RAISE NOTICE '%', sql;
841
	EXECUTE sql INTO STRICT ret_val;
842
	RETURN ret_val;
843
END;
844
$$;
845

    
846

    
847
--
848
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
849
--
850

    
851
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
852

    
853

    
854
--
855
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
856
--
857

    
858
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
859
    LANGUAGE sql
860
    AS $_$
861
SELECT util.eval2val($$SELECT $$||$1, $2)
862
$_$;
863

    
864

    
865
--
866
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
867
--
868

    
869
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
870

    
871

    
872
--
873
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
874
--
875

    
876
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
877
    LANGUAGE sql
878
    AS $_$
879
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
880
$_$;
881

    
882

    
883
--
884
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
885
--
886

    
887
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS 'sql: can be NULL, which will be passed through
888
ret_type_null: NULL::ret_type';
889

    
890

    
891
--
892
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
893
--
894

    
895
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
896
    LANGUAGE sql STABLE STRICT
897
    AS $_$
898
SELECT col_name
899
FROM unnest($2) s (col_name)
900
WHERE util.col_exists(($1, col_name))
901
$_$;
902

    
903

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

    
908
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
909
    LANGUAGE plpgsql STRICT
910
    AS $_$
911
DECLARE
912
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
913
$$||query;
914
BEGIN
915
	EXECUTE mk_view;
916
EXCEPTION
917
WHEN invalid_table_definition THEN
918
	IF SQLERRM = 'cannot drop columns from view'
919
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
920
	THEN
921
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
922
		EXECUTE mk_view;
923
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
924
	END IF;
925
END;
926
$_$;
927

    
928

    
929
--
930
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
931
--
932

    
933
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
934

    
935

    
936
--
937
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
938
--
939

    
940
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
941
    LANGUAGE sql IMMUTABLE STRICT
942
    AS $_$
943
SELECT substring($2 for length($1)) = $1
944
$_$;
945

    
946

    
947
--
948
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
949
--
950

    
951
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
952
    LANGUAGE sql STABLE STRICT
953
    AS $_$
954
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
955
$_$;
956

    
957

    
958
--
959
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
960
--
961

    
962
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
963
    LANGUAGE sql IMMUTABLE STRICT
964
    AS $_$
965
SELECT $1 || $3 || $2
966
$_$;
967

    
968

    
969
--
970
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
971
--
972

    
973
CREATE FUNCTION map_filter_insert() RETURNS trigger
974
    LANGUAGE plpgsql
975
    AS $$
976
BEGIN
977
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
978
	RETURN new;
979
END;
980
$$;
981

    
982

    
983
--
984
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
985
--
986

    
987
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
988
    LANGUAGE plpgsql STABLE STRICT
989
    AS $_$
990
DECLARE
991
    value text;
992
BEGIN
993
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
994
        INTO value USING key;
995
    RETURN value;
996
END;
997
$_$;
998

    
999

    
1000
--
1001
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1002
--
1003

    
1004
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1005
    LANGUAGE plpgsql STABLE STRICT
1006
    AS $_$
1007
BEGIN
1008
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1009
END;
1010
$_$;
1011

    
1012

    
1013
--
1014
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
1015
--
1016

    
1017
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1018
    LANGUAGE sql STRICT
1019
    AS $_$
1020
SELECT util.create_if_not_exists($$
1021
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1022
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1023
||quote_literal($2)||$$;
1024
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS 'constant';
1025
$$)
1026
$_$;
1027

    
1028

    
1029
--
1030
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1031
--
1032

    
1033
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
1034

    
1035

    
1036
--
1037
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1038
--
1039

    
1040
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1041
    LANGUAGE plpgsql STRICT
1042
    AS $_$
1043
DECLARE
1044
    type regtype = util.typeof(expr, col.table_::text::regtype);
1045
    col_name_sql text = quote_ident(col.name);
1046
BEGIN
1047
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1048
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1049
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1050
$$||expr||$$;
1051
$$);
1052
END;
1053
$_$;
1054

    
1055

    
1056
--
1057
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1058
--
1059

    
1060
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS 'idempotent';
1061

    
1062

    
1063
--
1064
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1065
--
1066

    
1067
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1068
    LANGUAGE sql STRICT
1069
    AS $_$
1070
SELECT util.create_if_not_exists($$
1071
CREATE TABLE $$||$1||$$
1072
(
1073
    LIKE util.map INCLUDING ALL
1074
);
1075

    
1076
CREATE TRIGGER map_filter_insert
1077
  BEFORE INSERT
1078
  ON $$||$1||$$
1079
  FOR EACH ROW
1080
  EXECUTE PROCEDURE util.map_filter_insert();
1081
$$)
1082
$_$;
1083

    
1084

    
1085
--
1086
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1087
--
1088

    
1089
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1090
    LANGUAGE sql STRICT
1091
    AS $_$
1092
SELECT util.mk_const_col(($1, 'source'), util.table_schema($1))
1093
$_$;
1094

    
1095

    
1096
--
1097
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1098
--
1099

    
1100
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS 'idempotent';
1101

    
1102

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

    
1107
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1108
    LANGUAGE plpgsql STRICT
1109
    AS $_$
1110
BEGIN
1111
	EXECUTE $$
1112
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1113
  RETURNS SETOF $$||view_||$$ AS
1114
$BODY1$
1115
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
1116
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
1117
$BODY1$
1118
  LANGUAGE sql STABLE
1119
  COST 100
1120
  ROWS 1000
1121
$$;
1122
-- Also create subset function which turns off enable_sort
1123
	EXECUTE $$
1124
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1125
  RETURNS SETOF $$||view_||$$
1126
  SET enable_sort TO 'off'
1127
  AS
1128
$BODY1$
1129
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
1130
$BODY1$
1131
  LANGUAGE sql STABLE
1132
  COST 100
1133
  ROWS 1000
1134
;
1135
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1136
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1137
If you want to run EXPLAIN and get expanded output, use the regular subset
1138
function instead. (When a config param is set on a function, EXPLAIN produces
1139
just a function scan.)
1140
';
1141
$$;
1142
END;
1143
$_$;
1144

    
1145

    
1146
--
1147
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1148
--
1149

    
1150
CREATE FUNCTION name(type regtype) RETURNS text
1151
    LANGUAGE sql STABLE STRICT
1152
    AS $_$
1153
SELECT typname::text FROM pg_type WHERE oid = $1
1154
$_$;
1155

    
1156

    
1157
--
1158
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1159
--
1160

    
1161
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
1162
    LANGUAGE sql IMMUTABLE
1163
    AS $_$
1164
SELECT $1 IS NOT NULL AND array_length($1, 1)/*ARRAY[]->NULL*/ IS NOT NULL
1165
$_$;
1166

    
1167

    
1168
--
1169
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
1170
--
1171

    
1172
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
1173
    LANGUAGE sql IMMUTABLE
1174
    AS $_$
1175
SELECT $1 IS NOT NULL
1176
$_$;
1177

    
1178

    
1179
--
1180
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
1181
--
1182

    
1183
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
1184
    LANGUAGE plpgsql IMMUTABLE STRICT
1185
    AS $$
1186
BEGIN
1187
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
1188
END;
1189
$$;
1190

    
1191

    
1192
--
1193
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1194
--
1195

    
1196
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
1197
    LANGUAGE sql STRICT
1198
    AS $_$
1199
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1200
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
1201
FROM util.col_names($1::text::regtype) f (name)
1202
$_$;
1203

    
1204

    
1205
--
1206
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
1207
--
1208

    
1209
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
1210

    
1211

    
1212
--
1213
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1214
--
1215

    
1216
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
1217
    LANGUAGE sql STRICT
1218
    AS $_$
1219
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
1220
SELECT util.set_col_names($1, $2);
1221
$_$;
1222

    
1223

    
1224
--
1225
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1226
--
1227

    
1228
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS 'idempotent.
1229
alters the names table, so it will need to be repopulated after running this function.';
1230

    
1231

    
1232
--
1233
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1234
--
1235

    
1236
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
1237
    LANGUAGE sql STRICT
1238
    AS $_$
1239
SELECT util.drop_table($1);
1240
SELECT util.mk_map_table($1);
1241
$_$;
1242

    
1243

    
1244
--
1245
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
1246
--
1247

    
1248
CREATE FUNCTION search_path_append(schemas text) RETURNS void
1249
    LANGUAGE sql STRICT
1250
    AS $_$
1251
SELECT util.eval(
1252
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
1253
$_$;
1254

    
1255

    
1256
--
1257
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1258
--
1259

    
1260
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
1261
    LANGUAGE plpgsql STRICT
1262
    AS $_$
1263
DECLARE
1264
    old text[] = ARRAY(SELECT util.col_names(table_));
1265
    new text[] = ARRAY(SELECT util.map_values(names));
1266
BEGIN
1267
    old = old[1:array_length(new, 1)]; -- truncate to same length
1268
    PERFORM util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1269
        ||quote_ident(key)||$$ TO $$||quote_ident(value))
1270
    FROM each(hstore(old, new))
1271
    WHERE value != key -- not same name
1272
    ;
1273
END;
1274
$_$;
1275

    
1276

    
1277
--
1278
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1279
--
1280

    
1281
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
1282

    
1283

    
1284
--
1285
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1286
--
1287

    
1288
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
1289
    LANGUAGE plpgsql STRICT
1290
    AS $_$
1291
DECLARE
1292
	row_ util.map;
1293
BEGIN
1294
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
1295
	LOOP
1296
		PERFORM util.mk_const_col((table_, row_."to"),
1297
			substring(row_."from" from 2));
1298
	END LOOP;
1299
	
1300
	PERFORM util.set_col_names(table_, names);
1301
END;
1302
$_$;
1303

    
1304

    
1305
--
1306
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1307
--
1308

    
1309
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS 'idempotent.
1310
the metadata mappings must be *last* in the names table.';
1311

    
1312

    
1313
--
1314
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
1315
--
1316

    
1317
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
1318
    LANGUAGE plpgsql STRICT
1319
    AS $_$
1320
DECLARE
1321
    sql text = $$ALTER TABLE $$||table_||$$
1322
$$||NULLIF(array_to_string(ARRAY(
1323
    SELECT
1324
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
1325
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1326
    FROM
1327
    (
1328
        SELECT
1329
          quote_ident(col_name) AS col_name_sql
1330
        , util.col_type((table_, col_name)) AS curr_type
1331
        , type AS target_type
1332
        FROM unnest(col_casts)
1333
    ) s
1334
    WHERE curr_type != target_type
1335
), '
1336
, '), '');
1337
BEGIN
1338
    RAISE NOTICE '%', sql;
1339
    EXECUTE COALESCE(sql, '');
1340
END;
1341
$_$;
1342

    
1343

    
1344
--
1345
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1346
--
1347

    
1348
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
1349

    
1350

    
1351
--
1352
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1353
--
1354

    
1355
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1356
    LANGUAGE plpgsql STABLE STRICT
1357
    AS $_$
1358
DECLARE
1359
    hstore hstore;
1360
BEGIN
1361
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1362
        table_||$$))$$ INTO STRICT hstore;
1363
    RETURN hstore;
1364
END;
1365
$_$;
1366

    
1367

    
1368
--
1369
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1370
--
1371

    
1372
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
1373
    LANGUAGE sql STABLE STRICT
1374
    AS $_$
1375
SELECT COUNT(*) > 0 FROM pg_constraint
1376
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
1377
$_$;
1378

    
1379

    
1380
--
1381
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1382
--
1383

    
1384
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';
1385

    
1386

    
1387
--
1388
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1389
--
1390

    
1391
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
1392
    LANGUAGE sql STRICT
1393
    AS $_$
1394
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
1395
||quote_ident($2)||$$ CHECK (true)$$)
1396
$_$;
1397

    
1398

    
1399
--
1400
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1401
--
1402

    
1403
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS 'stores a status flag by the presence of a table constraint.
1404
idempotent.';
1405

    
1406

    
1407
--
1408
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
1409
--
1410

    
1411
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
1412
    LANGUAGE sql STABLE STRICT
1413
    AS $_$
1414
SELECT util.table_flag__get($1, 'nulls_mapped')
1415
$_$;
1416

    
1417

    
1418
--
1419
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1420
--
1421

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

    
1424

    
1425
--
1426
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
1427
--
1428

    
1429
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
1430
    LANGUAGE sql STRICT
1431
    AS $_$
1432
SELECT util.table_flag__set($1, 'nulls_mapped')
1433
$_$;
1434

    
1435

    
1436
--
1437
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1438
--
1439

    
1440
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS 'sets that a table''s NULL-equivalent strings have been replaced with NULL.
1441
idempotent.';
1442

    
1443

    
1444
--
1445
-- Name: table_schema(regclass); Type: FUNCTION; Schema: util; Owner: -
1446
--
1447

    
1448
CREATE FUNCTION table_schema(table_ regclass) RETURNS text
1449
    LANGUAGE sql STABLE STRICT
1450
    AS $_$
1451
SELECT nspname::text FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = $1)
1452
$_$;
1453

    
1454

    
1455
--
1456
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1457
--
1458

    
1459
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1460
    LANGUAGE plpgsql STRICT
1461
    AS $_$
1462
DECLARE
1463
    row record;
1464
BEGIN
1465
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1466
    LOOP
1467
        IF row.global_name != row.name THEN
1468
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1469
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1470
        END IF;
1471
    END LOOP;
1472
END;
1473
$_$;
1474

    
1475

    
1476
--
1477
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1478
--
1479

    
1480
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1481

    
1482

    
1483
--
1484
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
1485
--
1486

    
1487
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1488
    LANGUAGE plpgsql STRICT
1489
    AS $_$
1490
BEGIN
1491
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1492
END;
1493
$_$;
1494

    
1495

    
1496
--
1497
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1498
--
1499

    
1500
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1501

    
1502

    
1503
--
1504
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1505
--
1506

    
1507
CREATE FUNCTION try_create(sql text) RETURNS void
1508
    LANGUAGE plpgsql STRICT
1509
    AS $$
1510
BEGIN
1511
    PERFORM util.eval(sql);
1512
EXCEPTION
1513
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
1514
    WHEN undefined_column THEN NULL;
1515
    WHEN duplicate_column THEN NULL;
1516
END;
1517
$$;
1518

    
1519

    
1520
--
1521
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1522
--
1523

    
1524
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1525

    
1526

    
1527
--
1528
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1529
--
1530

    
1531
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1532
    LANGUAGE sql STRICT
1533
    AS $_$
1534
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1535
$_$;
1536

    
1537

    
1538
--
1539
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1540
--
1541

    
1542
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1543

    
1544

    
1545
--
1546
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1547
--
1548

    
1549
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1550
    LANGUAGE sql IMMUTABLE
1551
    AS $_$
1552
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1553
$_$;
1554

    
1555

    
1556
--
1557
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
1558
--
1559

    
1560
COMMENT ON FUNCTION type_qual(value anyelement) IS 'a type''s NOT NULL qualifier';
1561

    
1562

    
1563
--
1564
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1565
--
1566

    
1567
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1568
    LANGUAGE sql STABLE STRICT
1569
    SET search_path TO pg_temp
1570
    AS $_$
1571
SELECT $1::text
1572
$_$;
1573

    
1574

    
1575
--
1576
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
1577
--
1578

    
1579
COMMENT ON FUNCTION type_qual_name(type regtype) IS 'a type''s schema-qualified name';
1580

    
1581

    
1582
--
1583
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1584
--
1585

    
1586
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
1587
    LANGUAGE plpgsql STABLE
1588
    AS $_$
1589
DECLARE
1590
    type regtype;
1591
BEGIN
1592
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
1593
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
1594
    RETURN type;
1595
END;
1596
$_$;
1597

    
1598

    
1599
--
1600
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
1601
--
1602

    
1603
CREATE AGGREGATE all_same(anyelement) (
1604
    SFUNC = all_same_transform,
1605
    STYPE = anyarray,
1606
    FINALFUNC = all_same_final
1607
);
1608

    
1609

    
1610
--
1611
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
1612
--
1613

    
1614
COMMENT ON AGGREGATE all_same(anyelement) IS 'includes NULLs in comparison';
1615

    
1616

    
1617
--
1618
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1619
--
1620

    
1621
CREATE AGGREGATE join_strs(text, text) (
1622
    SFUNC = join_strs_transform,
1623
    STYPE = text
1624
);
1625

    
1626

    
1627
--
1628
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1629
--
1630

    
1631
CREATE OPERATOR -> (
1632
    PROCEDURE = map_get,
1633
    LEFTARG = regclass,
1634
    RIGHTARG = text
1635
);
1636

    
1637

    
1638
SET default_tablespace = '';
1639

    
1640
SET default_with_oids = false;
1641

    
1642
--
1643
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1644
--
1645

    
1646
CREATE TABLE map (
1647
    "from" text NOT NULL,
1648
    "to" text,
1649
    filter text,
1650
    notes text
1651
);
1652

    
1653

    
1654
--
1655
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
1656
--
1657

    
1658

    
1659

    
1660
--
1661
-- Name: map_pkey; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
1662
--
1663

    
1664
ALTER TABLE ONLY map
1665
    ADD CONSTRAINT map_pkey PRIMARY KEY ("from");
1666

    
1667

    
1668
--
1669
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
1670
--
1671

    
1672
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
1673

    
1674

    
1675
--
1676
-- PostgreSQL database dump complete
1677
--
1678

    
(17-17/27)