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: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
292
--
293

    
294
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
295
    LANGUAGE plpgsql IMMUTABLE STRICT
296
    AS $$
297
DECLARE
298
    result value%TYPE := util._map(map, value::text)::unknown;
299
BEGIN
300
    RETURN result;
301
END;
302
$$;
303

    
304

    
305
--
306
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
307
--
308

    
309
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
310
    LANGUAGE sql IMMUTABLE
311
    AS $_$
312
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
313
$_$;
314

    
315

    
316
--
317
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
318
--
319

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

    
354

    
355
--
356
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
357
--
358

    
359
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
360
    LANGUAGE sql IMMUTABLE
361
    AS $_$
362
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
363
$_$;
364

    
365

    
366
--
367
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
368
--
369

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

    
404

    
405
--
406
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
407
--
408

    
409
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
410
    LANGUAGE sql IMMUTABLE
411
    AS $_$
412
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
413
$_$;
414

    
415

    
416
--
417
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
418
--
419

    
420
CREATE FUNCTION _not(value boolean) RETURNS boolean
421
    LANGUAGE sql IMMUTABLE STRICT
422
    AS $_$
423
SELECT NOT $1
424
$_$;
425

    
426

    
427
--
428
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
429
--
430

    
431
CREATE FUNCTION _now() RETURNS timestamp with time zone
432
    LANGUAGE sql STABLE
433
    AS $$
434
SELECT now()
435
$$;
436

    
437

    
438
--
439
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
440
--
441

    
442
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
443
    LANGUAGE plpgsql IMMUTABLE
444
    AS $$
445
DECLARE
446
    type util.datatype NOT NULL := type; -- add NOT NULL
447
BEGIN
448
    IF type = 'str' THEN RETURN nullif(value::text, "null");
449
    -- Invalid value is ignored, but invalid null value generates error
450
    ELSIF type = 'float' THEN
451
        DECLARE
452
            -- Outside the try block so that invalid null value generates error
453
            "null" double precision := "null"::double precision;
454
        BEGIN
455
            RETURN nullif(value::double precision, "null");
456
        EXCEPTION
457
            WHEN data_exception THEN RETURN value; -- ignore invalid value
458
        END;
459
    END IF;
460
END;
461
$$;
462

    
463

    
464
--
465
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
466
--
467

    
468
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
469
    LANGUAGE sql IMMUTABLE
470
    AS $_$
471
SELECT util."_nullIf"($1, $2, $3::util.datatype)
472
$_$;
473

    
474

    
475
--
476
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
477
--
478

    
479
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
480
    LANGUAGE sql IMMUTABLE
481
    AS $_$
482
SELECT bool_or(value)
483
FROM
484
(VALUES
485
      ($1)
486
    , ($2)
487
    , ($3)
488
    , ($4)
489
    , ($5)
490
)
491
AS v (value)
492
$_$;
493

    
494

    
495
--
496
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
497
--
498

    
499
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.';
500

    
501

    
502
--
503
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
504
--
505

    
506
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
507
    LANGUAGE sql IMMUTABLE
508
    AS $_$
509
SELECT $2 - $1
510
$_$;
511

    
512

    
513
--
514
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
515
--
516

    
517
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
518
    LANGUAGE sql IMMUTABLE STRICT
519
    AS $_$
520
SELECT regexp_split_to_table($1, $2)
521
$_$;
522

    
523

    
524
--
525
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
526
--
527

    
528
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
529
    LANGUAGE sql IMMUTABLE
530
    AS $_$
531
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
532
$_$;
533

    
534

    
535
--
536
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
537
--
538

    
539
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
540
    LANGUAGE plpgsql IMMUTABLE
541
    AS $$
542
DECLARE
543
	value_cmp         state%TYPE = ARRAY[value];
544
	state             state%TYPE = COALESCE(state, value_cmp);
545
	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
546
BEGIN
547
	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
548
END;
549
$$;
550

    
551

    
552
--
553
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
554
--
555

    
556
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
557
    LANGUAGE sql IMMUTABLE
558
    AS $_$
559
SELECT pg_catalog.array_fill($1, ARRAY[$2])
560
$_$;
561

    
562

    
563
--
564
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
565
--
566

    
567
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
568
    LANGUAGE sql IMMUTABLE
569
    AS $_$
570
SELECT util.array_length($1, 1)
571
$_$;
572

    
573

    
574
--
575
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
576
--
577

    
578
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
579
    LANGUAGE sql IMMUTABLE
580
    AS $_$
581
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
582
$_$;
583

    
584

    
585
--
586
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
587
--
588

    
589
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS 'returns 0 instead of NULL for empty arrays';
590

    
591

    
592
--
593
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
594
--
595

    
596
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
597
    LANGUAGE sql STABLE STRICT
598
    AS $_$
599
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
600
$_$;
601

    
602

    
603
--
604
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
605
--
606

    
607
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
608
    LANGUAGE plpgsql STRICT
609
    AS $_$
610
BEGIN
611
    -- not yet clustered (ARRAY[] compares NULLs literally)
612
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
613
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
614
    END IF;
615
END;
616
$_$;
617

    
618

    
619
--
620
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
621
--
622

    
623
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
624

    
625

    
626
--
627
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
628
--
629

    
630
CREATE FUNCTION col_comment(col col_ref) RETURNS text
631
    LANGUAGE plpgsql STABLE STRICT
632
    AS $$
633
DECLARE
634
	comment text;
635
BEGIN
636
	SELECT description
637
	FROM pg_attribute
638
	LEFT JOIN pg_description ON objoid = attrelid
639
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
640
	WHERE attrelid = col.table_ AND attname = col.name
641
	INTO STRICT comment
642
	;
643
	RETURN comment;
644
EXCEPTION
645
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
646
END;
647
$$;
648

    
649

    
650
--
651
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
652
--
653

    
654
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
655
    LANGUAGE plpgsql STABLE STRICT
656
    AS $$
657
DECLARE
658
	default_sql text;
659
BEGIN
660
	SELECT adsrc
661
	FROM pg_attribute
662
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
663
	WHERE attrelid = col.table_ AND attname = col.name
664
	INTO STRICT default_sql
665
	;
666
	RETURN default_sql;
667
EXCEPTION
668
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
669
END;
670
$$;
671

    
672

    
673
--
674
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
675
--
676

    
677
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
678
    LANGUAGE sql STABLE
679
    AS $_$
680
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
681
$_$;
682

    
683

    
684
--
685
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
686
--
687

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

    
690

    
691
--
692
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
693
--
694

    
695
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
696
    LANGUAGE plpgsql STRICT
697
    AS $$
698
BEGIN
699
    PERFORM util.col_type(col);
700
    RETURN true;
701
EXCEPTION
702
    WHEN undefined_column THEN RETURN false;
703
END;
704
$$;
705

    
706

    
707
--
708
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
709
--
710

    
711
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
712
    LANGUAGE plpgsql STABLE STRICT
713
    AS $$
714
DECLARE
715
    prefix text := util.name(type)||'.';
716
BEGIN
717
    RETURN QUERY
718
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
719
        FROM util.col_names(type) f (name_);
720
END;
721
$$;
722

    
723

    
724
--
725
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
726
--
727

    
728
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
729
    LANGUAGE plpgsql STABLE STRICT
730
    AS $_$
731
BEGIN
732
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
733
END;
734
$_$;
735

    
736

    
737
--
738
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
739
--
740

    
741
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
742
    LANGUAGE sql STABLE STRICT
743
    AS $_$
744
SELECT attname::text
745
FROM pg_attribute
746
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
747
ORDER BY attnum
748
$_$;
749

    
750

    
751
--
752
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
753
--
754

    
755
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
756
    LANGUAGE plpgsql STABLE STRICT
757
    AS $$
758
DECLARE
759
    type regtype;
760
BEGIN
761
    SELECT atttypid FROM pg_attribute
762
    WHERE attrelid = col.table_ AND attname = col.name
763
    INTO STRICT type
764
    ;
765
    RETURN type;
766
EXCEPTION
767
    WHEN no_data_found THEN
768
        RAISE undefined_column USING MESSAGE =
769
            concat('undefined column: ', col.name);
770
END;
771
$$;
772

    
773

    
774
--
775
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
776
--
777

    
778
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
779
    LANGUAGE sql IMMUTABLE STRICT
780
    AS $_$
781
SELECT position($1 in $2) > 0 /*1-based offset*/
782
$_$;
783

    
784

    
785
--
786
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
787
--
788

    
789
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
790
    LANGUAGE plpgsql STRICT
791
    AS $$
792
BEGIN
793
    PERFORM util.eval(sql);
794
EXCEPTION
795
    WHEN duplicate_table  THEN NULL;
796
    WHEN duplicate_object THEN NULL; -- e.g. constraint
797
    WHEN duplicate_column THEN NULL;
798
    WHEN invalid_table_definition THEN
799
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
800
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
801
        END IF;
802
END;
803
$$;
804

    
805

    
806
--
807
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
808
--
809

    
810
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
811

    
812

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

    
817
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
818
    LANGUAGE sql STABLE STRICT
819
    AS $_$
820
SELECT util.eval2set($$
821
SELECT col
822
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
823
LEFT JOIN $$||$2||$$ ON "to" = col
824
WHERE "from" IS NULL
825
$$, NULL::text)
826
$_$;
827

    
828

    
829
--
830
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
831
--
832

    
833
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS 'gets table_''s derived columns (all the columns not in the names table)';
834

    
835

    
836
--
837
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
838
--
839

    
840
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
841
    LANGUAGE sql STRICT
842
    AS $_$
843
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
844
$_$;
845

    
846

    
847
--
848
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
849
--
850

    
851
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
852

    
853

    
854
--
855
-- Name: drop_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
856
--
857

    
858
CREATE FUNCTION drop_column(col col_ref) RETURNS void
859
    LANGUAGE sql STRICT
860
    AS $_$
861
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
862
quote_ident($1.name))
863
$_$;
864

    
865

    
866
--
867
-- Name: FUNCTION drop_column(col col_ref); Type: COMMENT; Schema: util; Owner: -
868
--
869

    
870
COMMENT ON FUNCTION drop_column(col col_ref) IS 'idempotent';
871

    
872

    
873
--
874
-- Name: drop_table(text); Type: FUNCTION; Schema: util; Owner: -
875
--
876

    
877
CREATE FUNCTION drop_table(table_ text) RETURNS void
878
    LANGUAGE sql STRICT
879
    AS $_$
880
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
881
$_$;
882

    
883

    
884
--
885
-- Name: FUNCTION drop_table(table_ text); Type: COMMENT; Schema: util; Owner: -
886
--
887

    
888
COMMENT ON FUNCTION drop_table(table_ text) IS 'idempotent';
889

    
890

    
891
--
892
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
893
--
894

    
895
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
896
    LANGUAGE sql IMMUTABLE
897
    AS $_$
898
SELECT util.array_fill($1, 0)
899
$_$;
900

    
901

    
902
--
903
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
904
--
905

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

    
908

    
909
--
910
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
911
--
912

    
913
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
914
    LANGUAGE sql IMMUTABLE STRICT
915
    AS $_$
916
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
917
$_$;
918

    
919

    
920
--
921
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
922
--
923

    
924
CREATE FUNCTION eval(sql text) RETURNS void
925
    LANGUAGE plpgsql STRICT
926
    AS $$
927
BEGIN
928
    RAISE NOTICE '%', sql;
929
    EXECUTE sql;
930
END;
931
$$;
932

    
933

    
934
--
935
-- Name: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
936
--
937

    
938
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS SETOF anyelement
939
    LANGUAGE plpgsql
940
    AS $$
941
BEGIN
942
	RAISE NOTICE '%', sql;
943
	RETURN QUERY EXECUTE sql;
944
END;
945
$$;
946

    
947

    
948
--
949
-- Name: FUNCTION eval2set(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
950
--
951

    
952
COMMENT ON FUNCTION eval2set(sql text, ret_type_null anyelement) IS 'ret_type_null: NULL::ret_type';
953

    
954

    
955
--
956
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
957
--
958

    
959
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
960
    LANGUAGE plpgsql
961
    AS $$
962
DECLARE
963
	ret_val ret_type_null%TYPE;
964
BEGIN
965
	RAISE NOTICE '%', sql;
966
	EXECUTE sql INTO STRICT ret_val;
967
	RETURN ret_val;
968
END;
969
$$;
970

    
971

    
972
--
973
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
974
--
975

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

    
978

    
979
--
980
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
981
--
982

    
983
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
984
    LANGUAGE sql
985
    AS $_$
986
SELECT util.eval2val($$SELECT $$||$1, $2)
987
$_$;
988

    
989

    
990
--
991
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
992
--
993

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

    
996

    
997
--
998
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
999
--
1000

    
1001
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
1002
    LANGUAGE sql
1003
    AS $_$
1004
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
1005
$_$;
1006

    
1007

    
1008
--
1009
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
1010
--
1011

    
1012
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS 'sql: can be NULL, which will be passed through
1013
ret_type_null: NULL::ret_type';
1014

    
1015

    
1016
--
1017
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
1018
--
1019

    
1020
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
1021
    LANGUAGE sql STABLE STRICT
1022
    AS $_$
1023
SELECT col_name
1024
FROM unnest($2) s (col_name)
1025
WHERE util.col_exists(($1, col_name))
1026
$_$;
1027

    
1028

    
1029
--
1030
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
1031
--
1032

    
1033
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
1034
    LANGUAGE sql IMMUTABLE
1035
    AS $_$
1036
-- STRICT handles NULLs, so that the array will always be a value
1037
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
1038
	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
1039
) END
1040
$_$;
1041

    
1042

    
1043
--
1044
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
1045
--
1046

    
1047
COMMENT ON FUNCTION fix_array("array" anyarray) IS 'ensures that an array will always have proper non-NULL dimensions';
1048

    
1049

    
1050
--
1051
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
1052
--
1053

    
1054
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
1055
    LANGUAGE plpgsql STRICT
1056
    AS $_$
1057
DECLARE
1058
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
1059
$$||query;
1060
BEGIN
1061
	EXECUTE mk_view;
1062
EXCEPTION
1063
WHEN invalid_table_definition THEN
1064
	IF SQLERRM = 'cannot drop columns from view'
1065
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
1066
	THEN
1067
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
1068
		EXECUTE mk_view;
1069
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
1070
	END IF;
1071
END;
1072
$_$;
1073

    
1074

    
1075
--
1076
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
1077
--
1078

    
1079
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
1080

    
1081

    
1082
--
1083
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
1084
--
1085

    
1086
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
1087
    LANGUAGE sql IMMUTABLE STRICT
1088
    AS $_$
1089
SELECT substring($2 for length($1)) = $1
1090
$_$;
1091

    
1092

    
1093
--
1094
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
1095
--
1096

    
1097
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
1098
    LANGUAGE sql IMMUTABLE
1099
    AS $_$
1100
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
1101
$_$;
1102

    
1103

    
1104
--
1105
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
1106
--
1107

    
1108
COMMENT ON FUNCTION hstore(keys text[], value text) IS 'avoids repeating the same value for each key';
1109

    
1110

    
1111
--
1112
-- Name: hstore(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1113
--
1114

    
1115
CREATE FUNCTION hstore(keys text[], value anyelement) RETURNS hstore
1116
    LANGUAGE sql IMMUTABLE
1117
    AS $_$
1118
SELECT util.hstore($1, $2::text)
1119
$_$;
1120

    
1121

    
1122
--
1123
-- Name: FUNCTION hstore(keys text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1124
--
1125

    
1126
COMMENT ON FUNCTION hstore(keys text[], value anyelement) IS 'avoids repeating the same value for each key';
1127

    
1128

    
1129
--
1130
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
1131
--
1132

    
1133
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
1134
    LANGUAGE sql STABLE STRICT
1135
    AS $_$
1136
SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
1137
$_$;
1138

    
1139

    
1140
--
1141
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
1142
--
1143

    
1144
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
1145
    LANGUAGE sql IMMUTABLE STRICT
1146
    AS $_$
1147
SELECT $1 || $3 || $2
1148
$_$;
1149

    
1150

    
1151
--
1152
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
1153
--
1154

    
1155
CREATE FUNCTION map_filter_insert() RETURNS trigger
1156
    LANGUAGE plpgsql
1157
    AS $$
1158
BEGIN
1159
	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
1160
	RETURN new;
1161
END;
1162
$$;
1163

    
1164

    
1165
--
1166
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1167
--
1168

    
1169
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
1170
    LANGUAGE plpgsql STABLE STRICT
1171
    AS $_$
1172
DECLARE
1173
    value text;
1174
BEGIN
1175
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
1176
        INTO value USING key;
1177
    RETURN value;
1178
END;
1179
$_$;
1180

    
1181

    
1182
--
1183
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
1184
--
1185

    
1186
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
1187
    LANGUAGE sql IMMUTABLE
1188
    AS $_$
1189
SELECT util._map(util.hstore($1, NULL) || '*=>*', $2)
1190
$_$;
1191

    
1192

    
1193
--
1194
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
1195
--
1196

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

    
1199
[1] inlining of function calls, which is different from constant folding
1200
[2] _map()''s profiling query
1201
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
1202
and map_nulls()''s profiling query
1203
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
1204
both take ~920 ms';
1205

    
1206

    
1207
--
1208
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
1209
--
1210

    
1211
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
1212
    LANGUAGE plpgsql STABLE STRICT
1213
    AS $_$
1214
BEGIN
1215
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
1216
END;
1217
$_$;
1218

    
1219

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

    
1224
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
1225
    LANGUAGE sql STRICT
1226
    AS $_$
1227
SELECT util.create_if_not_exists($$
1228
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
1229
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
1230
||quote_literal($2)||$$;
1231
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS 'constant';
1232
$$)
1233
$_$;
1234

    
1235

    
1236
--
1237
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
1238
--
1239

    
1240
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
1241

    
1242

    
1243
--
1244
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
1245
--
1246

    
1247
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
1248
    LANGUAGE plpgsql STRICT
1249
    AS $_$
1250
DECLARE
1251
    type regtype = util.typeof(expr, col.table_::text::regtype);
1252
    col_name_sql text = quote_ident(col.name);
1253
BEGIN
1254
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
1255
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
1256
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
1257
$$||expr||$$;
1258
$$);
1259
END;
1260
$_$;
1261

    
1262

    
1263
--
1264
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
1265
--
1266

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

    
1269

    
1270
--
1271
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1272
--
1273

    
1274
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
1275
    LANGUAGE sql STRICT
1276
    AS $_$
1277
SELECT util.create_if_not_exists($$
1278
CREATE TABLE $$||$1||$$
1279
(
1280
    LIKE util.map INCLUDING ALL
1281
);
1282

    
1283
CREATE TRIGGER map_filter_insert
1284
  BEFORE INSERT
1285
  ON $$||$1||$$
1286
  FOR EACH ROW
1287
  EXECUTE PROCEDURE util.map_filter_insert();
1288
$$)
1289
$_$;
1290

    
1291

    
1292
--
1293
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
1294
--
1295

    
1296
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
1297
    LANGUAGE sql STRICT
1298
    AS $_$
1299
SELECT util.mk_const_col(($1, 'source'), util.table_schema($1))
1300
$_$;
1301

    
1302

    
1303
--
1304
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1305
--
1306

    
1307
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS 'idempotent';
1308

    
1309

    
1310
--
1311
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1312
--
1313

    
1314
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
1315
    LANGUAGE plpgsql STRICT
1316
    AS $_$
1317
BEGIN
1318
	EXECUTE $$
1319
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1320
  RETURNS SETOF $$||view_||$$ AS
1321
$BODY1$
1322
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
1323
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
1324
$BODY1$
1325
  LANGUAGE sql STABLE
1326
  COST 100
1327
  ROWS 1000
1328
$$;
1329
-- Also create subset function which turns off enable_sort
1330
	EXECUTE $$
1331
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
1332
  RETURNS SETOF $$||view_||$$
1333
  SET enable_sort TO 'off'
1334
  AS
1335
$BODY1$
1336
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
1337
$BODY1$
1338
  LANGUAGE sql STABLE
1339
  COST 100
1340
  ROWS 1000
1341
;
1342
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
1343
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
1344
If you want to run EXPLAIN and get expanded output, use the regular subset
1345
function instead. (When a config param is set on a function, EXPLAIN produces
1346
just a function scan.)
1347
';
1348
$$;
1349
END;
1350
$_$;
1351

    
1352

    
1353
--
1354
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
1355
--
1356

    
1357
CREATE FUNCTION name(type regtype) RETURNS text
1358
    LANGUAGE sql STABLE STRICT
1359
    AS $_$
1360
SELECT typname::text FROM pg_type WHERE oid = $1
1361
$_$;
1362

    
1363

    
1364
--
1365
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
1366
--
1367

    
1368
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
1369
    LANGUAGE sql IMMUTABLE
1370
    AS $_$
1371
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
1372
$_$;
1373

    
1374

    
1375
--
1376
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
1377
--
1378

    
1379
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
1380
    LANGUAGE sql IMMUTABLE
1381
    AS $_$
1382
SELECT $1 IS NOT NULL
1383
$_$;
1384

    
1385

    
1386
--
1387
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
1388
--
1389

    
1390
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
1391
    LANGUAGE plpgsql IMMUTABLE STRICT
1392
    AS $$
1393
BEGIN
1394
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
1395
END;
1396
$$;
1397

    
1398

    
1399
--
1400
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
1401
--
1402

    
1403
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
1404
    LANGUAGE sql STRICT
1405
    AS $_$
1406
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
1407
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
1408
FROM util.col_names($1::text::regtype) f (name);
1409
SELECT NULL::void; -- don't fold away functions called in previous query
1410
$_$;
1411

    
1412

    
1413
--
1414
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
1415
--
1416

    
1417
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
1418

    
1419

    
1420
--
1421
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1422
--
1423

    
1424
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
1425
    LANGUAGE sql STRICT
1426
    AS $_$
1427
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
1428
SELECT util.set_col_names($1, $2);
1429
$_$;
1430

    
1431

    
1432
--
1433
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1434
--
1435

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

    
1439

    
1440
--
1441
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
1442
--
1443

    
1444
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
1445
    LANGUAGE sql STRICT
1446
    AS $_$
1447
SELECT util.drop_table($1);
1448
SELECT util.mk_map_table($1);
1449
$_$;
1450

    
1451

    
1452
--
1453
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
1454
--
1455

    
1456
CREATE FUNCTION search_path_append(schemas text) RETURNS void
1457
    LANGUAGE sql STRICT
1458
    AS $_$
1459
SELECT util.eval(
1460
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
1461
$_$;
1462

    
1463

    
1464
--
1465
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1466
--
1467

    
1468
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
1469
    LANGUAGE plpgsql STRICT
1470
    AS $_$
1471
DECLARE
1472
    old text[] = ARRAY(SELECT util.col_names(table_));
1473
    new text[] = ARRAY(SELECT util.map_values(names));
1474
BEGIN
1475
    old = old[1:array_length(new, 1)]; -- truncate to same length
1476
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
1477
||$$ TO $$||quote_ident(value))
1478
    FROM each(hstore(old, new))
1479
    WHERE value != key -- not same name
1480
    ;
1481
END;
1482
$_$;
1483

    
1484

    
1485
--
1486
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1487
--
1488

    
1489
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
1490

    
1491

    
1492
--
1493
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1494
--
1495

    
1496
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
1497
    LANGUAGE plpgsql STRICT
1498
    AS $_$
1499
DECLARE
1500
	row_ util.map;
1501
BEGIN
1502
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
1503
	LOOP
1504
		PERFORM util.mk_const_col((table_, row_."to"),
1505
			substring(row_."from" from 2));
1506
	END LOOP;
1507
	
1508
	PERFORM util.set_col_names(table_, names);
1509
END;
1510
$_$;
1511

    
1512

    
1513
--
1514
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1515
--
1516

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

    
1520

    
1521
--
1522
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
1523
--
1524

    
1525
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
1526
    LANGUAGE plpgsql STRICT
1527
    AS $_$
1528
DECLARE
1529
    sql text = $$ALTER TABLE $$||table_||$$
1530
$$||NULLIF(array_to_string(ARRAY(
1531
    SELECT
1532
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
1533
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1534
    FROM
1535
    (
1536
        SELECT
1537
          quote_ident(col_name) AS col_name_sql
1538
        , util.col_type((table_, col_name)) AS curr_type
1539
        , type AS target_type
1540
        FROM unnest(col_casts)
1541
    ) s
1542
    WHERE curr_type != target_type
1543
), '
1544
, '), '');
1545
BEGIN
1546
    RAISE NOTICE '%', sql;
1547
    EXECUTE COALESCE(sql, '');
1548
END;
1549
$_$;
1550

    
1551

    
1552
--
1553
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1554
--
1555

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

    
1558

    
1559
--
1560
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1561
--
1562

    
1563
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1564
    LANGUAGE plpgsql STABLE STRICT
1565
    AS $_$
1566
DECLARE
1567
    hstore hstore;
1568
BEGIN
1569
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1570
        table_||$$))$$ INTO STRICT hstore;
1571
    RETURN hstore;
1572
END;
1573
$_$;
1574

    
1575

    
1576
--
1577
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1578
--
1579

    
1580
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
1581
    LANGUAGE sql STABLE STRICT
1582
    AS $_$
1583
SELECT COUNT(*) > 0 FROM pg_constraint
1584
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
1585
$_$;
1586

    
1587

    
1588
--
1589
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1590
--
1591

    
1592
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';
1593

    
1594

    
1595
--
1596
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
1597
--
1598

    
1599
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
1600
    LANGUAGE sql STRICT
1601
    AS $_$
1602
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
1603
||quote_ident($2)||$$ CHECK (true)$$)
1604
$_$;
1605

    
1606

    
1607
--
1608
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
1609
--
1610

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

    
1614

    
1615
--
1616
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
1617
--
1618

    
1619
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
1620
    LANGUAGE sql STABLE STRICT
1621
    AS $_$
1622
SELECT util.table_flag__get($1, 'nulls_mapped')
1623
$_$;
1624

    
1625

    
1626
--
1627
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1628
--
1629

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

    
1632

    
1633
--
1634
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
1635
--
1636

    
1637
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
1638
    LANGUAGE sql STRICT
1639
    AS $_$
1640
SELECT util.table_flag__set($1, 'nulls_mapped')
1641
$_$;
1642

    
1643

    
1644
--
1645
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1646
--
1647

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

    
1651

    
1652
--
1653
-- Name: table_schema(regclass); Type: FUNCTION; Schema: util; Owner: -
1654
--
1655

    
1656
CREATE FUNCTION table_schema(table_ regclass) RETURNS text
1657
    LANGUAGE sql STABLE STRICT
1658
    AS $_$
1659
SELECT nspname::text FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = $1)
1660
$_$;
1661

    
1662

    
1663
--
1664
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1665
--
1666

    
1667
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1668
    LANGUAGE plpgsql STRICT
1669
    AS $_$
1670
DECLARE
1671
    row record;
1672
BEGIN
1673
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1674
    LOOP
1675
        IF row.global_name != row.name THEN
1676
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1677
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1678
        END IF;
1679
    END LOOP;
1680
END;
1681
$_$;
1682

    
1683

    
1684
--
1685
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1686
--
1687

    
1688
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1689

    
1690

    
1691
--
1692
-- Name: trim(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
1693
--
1694

    
1695
CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
1696
    LANGUAGE sql STRICT
1697
    AS $_$
1698
SELECT util.drop_column(($1, col)) FROM util.derived_cols($1, $2) f (col);
1699
SELECT NULL::void; -- don't fold away functions called in previous query
1700
$_$;
1701

    
1702

    
1703
--
1704
-- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
1705
--
1706

    
1707
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS 'trims table_ to include only original columns, as defined by the names table.
1708
idempotent.';
1709

    
1710

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

    
1715
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1716
    LANGUAGE plpgsql STRICT
1717
    AS $_$
1718
BEGIN
1719
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1720
END;
1721
$_$;
1722

    
1723

    
1724
--
1725
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1726
--
1727

    
1728
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1729

    
1730

    
1731
--
1732
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1733
--
1734

    
1735
CREATE FUNCTION try_create(sql text) RETURNS void
1736
    LANGUAGE plpgsql STRICT
1737
    AS $$
1738
BEGIN
1739
    PERFORM util.eval(sql);
1740
EXCEPTION
1741
    WHEN wrong_object_type THEN NULL; -- trying to alter a view's columns
1742
    WHEN undefined_column THEN NULL;
1743
    WHEN duplicate_column THEN NULL;
1744
END;
1745
$$;
1746

    
1747

    
1748
--
1749
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1750
--
1751

    
1752
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1753

    
1754

    
1755
--
1756
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1757
--
1758

    
1759
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1760
    LANGUAGE sql STRICT
1761
    AS $_$
1762
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1763
$_$;
1764

    
1765

    
1766
--
1767
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1768
--
1769

    
1770
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1771

    
1772

    
1773
--
1774
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1775
--
1776

    
1777
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1778
    LANGUAGE sql IMMUTABLE
1779
    AS $_$
1780
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1781
$_$;
1782

    
1783

    
1784
--
1785
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
1786
--
1787

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

    
1790

    
1791
--
1792
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1793
--
1794

    
1795
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1796
    LANGUAGE sql STABLE STRICT
1797
    SET search_path TO pg_temp
1798
    AS $_$
1799
SELECT $1::text
1800
$_$;
1801

    
1802

    
1803
--
1804
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
1805
--
1806

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

    
1809

    
1810
--
1811
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1812
--
1813

    
1814
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
1815
    LANGUAGE plpgsql STABLE
1816
    AS $_$
1817
DECLARE
1818
    type regtype;
1819
BEGIN
1820
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
1821
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
1822
    RETURN type;
1823
END;
1824
$_$;
1825

    
1826

    
1827
--
1828
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
1829
--
1830

    
1831
CREATE AGGREGATE all_same(anyelement) (
1832
    SFUNC = all_same_transform,
1833
    STYPE = anyarray,
1834
    FINALFUNC = all_same_final
1835
);
1836

    
1837

    
1838
--
1839
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
1840
--
1841

    
1842
COMMENT ON AGGREGATE all_same(anyelement) IS 'includes NULLs in comparison';
1843

    
1844

    
1845
--
1846
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1847
--
1848

    
1849
CREATE AGGREGATE join_strs(text, text) (
1850
    SFUNC = join_strs_transform,
1851
    STYPE = text
1852
);
1853

    
1854

    
1855
--
1856
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1857
--
1858

    
1859
CREATE OPERATOR -> (
1860
    PROCEDURE = map_get,
1861
    LEFTARG = regclass,
1862
    RIGHTARG = text
1863
);
1864

    
1865

    
1866
--
1867
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
1868
--
1869

    
1870
CREATE OPERATOR => (
1871
    PROCEDURE = hstore,
1872
    LEFTARG = text[],
1873
    RIGHTARG = anyelement
1874
);
1875

    
1876

    
1877
--
1878
-- Name: OPERATOR => (text[], anyelement); Type: COMMENT; Schema: util; Owner: -
1879
--
1880

    
1881
COMMENT ON OPERATOR => (text[], anyelement) IS 'usage: array[''key1'', ...]::text[] => ''value''';
1882

    
1883

    
1884
SET default_tablespace = '';
1885

    
1886
SET default_with_oids = false;
1887

    
1888
--
1889
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1890
--
1891

    
1892
CREATE TABLE map (
1893
    "from" text NOT NULL,
1894
    "to" text,
1895
    filter text,
1896
    notes text
1897
);
1898

    
1899

    
1900
--
1901
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
1902
--
1903

    
1904

    
1905

    
1906
--
1907
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
1908
--
1909

    
1910
ALTER TABLE ONLY map
1911
    ADD CONSTRAINT map__unique__from UNIQUE ("from");
1912

    
1913

    
1914
--
1915
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
1916
--
1917

    
1918
ALTER TABLE ONLY map
1919
    ADD CONSTRAINT map__unique__to UNIQUE ("to");
1920

    
1921

    
1922
--
1923
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
1924
--
1925

    
1926
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
1927

    
1928

    
1929
--
1930
-- PostgreSQL database dump complete
1931
--
1932

    
(17-17/27)