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: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
511
--
512

    
513
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
514
    LANGUAGE sql STABLE STRICT
515
    AS $_$
516
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
517
$_$;
518

    
519

    
520
--
521
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
522
--
523

    
524
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
525
    LANGUAGE plpgsql STRICT
526
    AS $_$
527
BEGIN
528
    -- not yet clustered (ARRAY[] compares NULLs literally)
529
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
530
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
531
    END IF;
532
END;
533
$_$;
534

    
535

    
536
--
537
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
538
--
539

    
540
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
541

    
542

    
543
--
544
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
545
--
546

    
547
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
548
    LANGUAGE plpgsql STRICT
549
    AS $$
550
BEGIN
551
    PERFORM util.col_type(col);
552
    RETURN true;
553
EXCEPTION
554
    WHEN undefined_column THEN RETURN false;
555
END;
556
$$;
557

    
558

    
559
--
560
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
561
--
562

    
563
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
564
    LANGUAGE plpgsql STABLE STRICT
565
    AS $$
566
DECLARE
567
    prefix text := util.name(type)||'.';
568
BEGIN
569
    RETURN QUERY
570
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
571
        FROM util.col_names(type) f (name_);
572
END;
573
$$;
574

    
575

    
576
--
577
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
578
--
579

    
580
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
581
    LANGUAGE plpgsql STABLE STRICT
582
    AS $_$
583
BEGIN
584
    RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
585
END;
586
$_$;
587

    
588

    
589
--
590
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
591
--
592

    
593
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
594
    LANGUAGE sql STABLE STRICT
595
    AS $_$
596
SELECT attname::text
597
FROM pg_attribute
598
WHERE attrelid = $1 AND attnum >= 1
599
ORDER BY attnum
600
$_$;
601

    
602

    
603
--
604
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
605
--
606

    
607
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
608
    LANGUAGE plpgsql STABLE STRICT
609
    AS $$
610
DECLARE
611
    type regtype;
612
BEGIN
613
    SELECT atttypid FROM pg_attribute
614
    WHERE attrelid = col.table_ AND attname = col.name
615
    INTO STRICT type
616
    ;
617
    RETURN type;
618
EXCEPTION
619
    WHEN no_data_found THEN
620
        RAISE undefined_column USING MESSAGE =
621
            concat('undefined column: ', col.name);
622
END;
623
$$;
624

    
625

    
626
--
627
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
628
--
629

    
630
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
631
    LANGUAGE sql IMMUTABLE STRICT
632
    AS $_$
633
SELECT position($1 in $2) > 0 /*1-based offset*/
634
$_$;
635

    
636

    
637
--
638
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
639
--
640

    
641
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
642
    LANGUAGE plpgsql STRICT
643
    AS $$
644
BEGIN
645
    EXECUTE sql;
646
EXCEPTION
647
    WHEN duplicate_table  THEN NULL;
648
    WHEN duplicate_column THEN NULL;
649
END;
650
$$;
651

    
652

    
653
--
654
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
655
--
656

    
657
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
658

    
659

    
660
--
661
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
662
--
663

    
664
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
665
    LANGUAGE sql STRICT
666
    AS $_$
667
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
668
$_$;
669

    
670

    
671
--
672
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
673
--
674

    
675
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS 'idempotent';
676

    
677

    
678
--
679
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
680
--
681

    
682
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
683
    LANGUAGE sql IMMUTABLE STRICT
684
    AS $_$
685
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
686
$_$;
687

    
688

    
689
--
690
-- Name: eval(text); Type: FUNCTION; Schema: util; Owner: -
691
--
692

    
693
CREATE FUNCTION eval(sql text) RETURNS void
694
    LANGUAGE plpgsql STRICT
695
    AS $$
696
BEGIN
697
    RAISE NOTICE '%', sql;
698
    EXECUTE sql;
699
END;
700
$$;
701

    
702

    
703
--
704
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
705
--
706

    
707
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
708
    LANGUAGE sql STABLE STRICT
709
    AS $_$
710
SELECT col_name
711
FROM unnest($2) s (col_name)
712
WHERE util.col_exists(($1, col_name))
713
$_$;
714

    
715

    
716
--
717
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
718
--
719

    
720
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
721
    LANGUAGE plpgsql STRICT
722
    AS $_$
723
DECLARE
724
	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
725
$$||query;
726
BEGIN
727
	EXECUTE mk_view;
728
EXCEPTION
729
WHEN invalid_table_definition THEN
730
	IF SQLERRM = 'cannot drop columns from view'
731
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
732
	THEN
733
		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
734
		EXECUTE mk_view;
735
	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
736
	END IF;
737
END;
738
$_$;
739

    
740

    
741
--
742
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
743
--
744

    
745
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS 'idempotent';
746

    
747

    
748
--
749
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
750
--
751

    
752
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
753
    LANGUAGE sql IMMUTABLE STRICT
754
    AS $_$
755
SELECT substring($2 for length($1)) = $1
756
$_$;
757

    
758

    
759
--
760
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
761
--
762

    
763
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
764
    LANGUAGE sql IMMUTABLE STRICT
765
    AS $_$
766
SELECT $1 || $3 || $2
767
$_$;
768

    
769

    
770
--
771
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
772
--
773

    
774
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
775
    LANGUAGE plpgsql STABLE STRICT
776
    AS $_$
777
DECLARE
778
    value text;
779
BEGIN
780
    EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
781
        INTO value USING key;
782
    RETURN value;
783
END;
784
$_$;
785

    
786

    
787
--
788
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
789
--
790

    
791
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
792
    LANGUAGE plpgsql STABLE STRICT
793
    AS $_$
794
BEGIN
795
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
796
END;
797
$_$;
798

    
799

    
800
--
801
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
802
--
803

    
804
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
805
    LANGUAGE sql STRICT
806
    AS $_$
807
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
808
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
809
||quote_literal($2))
810
$_$;
811

    
812

    
813
--
814
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
815
--
816

    
817
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS 'idempotent';
818

    
819

    
820
--
821
-- Name: mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
822
--
823

    
824
CREATE FUNCTION mk_derived_col(col col_ref, expr text) RETURNS void
825
    LANGUAGE plpgsql STRICT
826
    AS $_$
827
DECLARE
828
    type regtype = util.typeof(expr, col.table_::text::regtype);
829
    col_name_sql text = quote_ident(col.name);
830
BEGIN
831
    PERFORM util.create_if_not_exists($$
832
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;
833
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
834
$$||expr||$$;
835
$$);
836
END;
837
$_$;
838

    
839

    
840
--
841
-- Name: FUNCTION mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
842
--
843

    
844
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text) IS 'idempotent';
845

    
846

    
847
--
848
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
849
--
850

    
851
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
852
    LANGUAGE sql STRICT
853
    AS $_$
854
SELECT util.create_if_not_exists($$
855
CREATE TABLE $$||$1||$$
856
(
857
    LIKE util.map INCLUDING ALL
858
)
859
$$)
860
$_$;
861

    
862

    
863
--
864
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
865
--
866

    
867
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
868
    LANGUAGE plpgsql STRICT
869
    AS $_$
870
BEGIN
871
	EXECUTE $$
872
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
873
  RETURNS SETOF $$||view_||$$ AS
874
$BODY1$
875
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
876
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0)+1 AND COALESCE(COALESCE($2, 0)+1 + $1 - 1, 2147483647)
877
$BODY1$
878
  LANGUAGE sql STABLE
879
  COST 100
880
  ROWS 1000
881
$$;
882
-- Also create subset function which turns off enable_sort
883
	EXECUTE $$
884
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
885
  RETURNS SETOF $$||view_||$$
886
  SET enable_sort TO 'off'
887
  AS
888
$BODY1$
889
SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
890
$BODY1$
891
  LANGUAGE sql STABLE
892
  COST 100
893
  ROWS 1000
894
;
895
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
896
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
897
If you want to run EXPLAIN and get expanded output, use the regular subset
898
function instead. (When a config param is set on a function, EXPLAIN produces
899
just a function scan.)
900
';
901
$$;
902
END;
903
$_$;
904

    
905

    
906
--
907
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
908
--
909

    
910
CREATE FUNCTION name(type regtype) RETURNS text
911
    LANGUAGE sql STABLE STRICT
912
    AS $_$
913
SELECT typname::text FROM pg_type WHERE oid = $1
914
$_$;
915

    
916

    
917
--
918
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
919
--
920

    
921
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
922
    LANGUAGE sql STRICT
923
    AS $_$
924
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
925
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
926
FROM util.col_names($1::text::regtype) f (name)
927
$_$;
928

    
929

    
930
--
931
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
932
--
933

    
934
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
935

    
936

    
937
--
938
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
939
--
940

    
941
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
942
    LANGUAGE sql STRICT
943
    AS $_$
944
SELECT util.mk_map_table($1);
945
SELECT util.truncate($1);
946
$_$;
947

    
948

    
949
--
950
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
951
--
952

    
953
CREATE FUNCTION search_path_append(schemas text) RETURNS void
954
    LANGUAGE sql STRICT
955
    AS $_$
956
SELECT util.eval(
957
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
958
$_$;
959

    
960

    
961
--
962
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
963
--
964

    
965
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
966
    LANGUAGE plpgsql STRICT
967
    AS $_$
968
DECLARE
969
    old text[] = ARRAY(SELECT util.col_names(table_));
970
    new text[] = ARRAY(SELECT util.map_values(names));
971
BEGIN
972
    old = old[1:array_length(new, 1)]; -- truncate to same length
973
    PERFORM util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
974
        ||quote_ident(key)||$$ TO $$||quote_ident(value))
975
    FROM each(hstore(old, new));
976
END;
977
$_$;
978

    
979

    
980
--
981
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
982
--
983

    
984
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
985

    
986

    
987
--
988
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
989
--
990

    
991
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
992
    LANGUAGE plpgsql STRICT
993
    AS $_$
994
DECLARE
995
    sql text = $$ALTER TABLE $$||table_||$$
996
$$||NULLIF(array_to_string(ARRAY(
997
    SELECT
998
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
999
    ||$$ USING $$||col_name_sql||$$::$$||target_type
1000
    FROM
1001
    (
1002
        SELECT
1003
          quote_ident(col_name) AS col_name_sql
1004
        , util.col_type((table_, col_name)) AS curr_type
1005
        , type AS target_type
1006
        FROM unnest(col_casts)
1007
    ) s
1008
    WHERE curr_type != target_type
1009
), '
1010
, '), '');
1011
BEGIN
1012
    RAISE NOTICE '%', sql;
1013
    EXECUTE COALESCE(sql, '');
1014
END;
1015
$_$;
1016

    
1017

    
1018
--
1019
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
1020
--
1021

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

    
1024

    
1025
--
1026
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
1027
--
1028

    
1029
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
1030
    LANGUAGE plpgsql STABLE STRICT
1031
    AS $_$
1032
DECLARE
1033
    hstore hstore;
1034
BEGIN
1035
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
1036
        table_||$$))$$ INTO STRICT hstore;
1037
    RETURN hstore;
1038
END;
1039
$_$;
1040

    
1041

    
1042
--
1043
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
1044
--
1045

    
1046
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
1047
    LANGUAGE plpgsql STRICT
1048
    AS $_$
1049
DECLARE
1050
    row record;
1051
BEGIN
1052
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
1053
    LOOP
1054
        IF row.global_name != row.name THEN
1055
            EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
1056
                ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
1057
        END IF;
1058
    END LOOP;
1059
END;
1060
$_$;
1061

    
1062

    
1063
--
1064
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1065
--
1066

    
1067
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
1068

    
1069

    
1070
--
1071
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
1072
--
1073

    
1074
CREATE FUNCTION truncate(table_ regclass) RETURNS void
1075
    LANGUAGE plpgsql STRICT
1076
    AS $_$
1077
BEGIN
1078
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
1079
END;
1080
$_$;
1081

    
1082

    
1083
--
1084
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
1085
--
1086

    
1087
COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
1088

    
1089

    
1090
--
1091
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
1092
--
1093

    
1094
CREATE FUNCTION try_create(sql text) RETURNS void
1095
    LANGUAGE plpgsql STRICT
1096
    AS $$
1097
BEGIN
1098
    EXECUTE sql;
1099
EXCEPTION
1100
    WHEN undefined_column THEN NULL;
1101
    WHEN duplicate_column THEN NULL;
1102
END;
1103
$$;
1104

    
1105

    
1106
--
1107
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
1108
--
1109

    
1110
COMMENT ON FUNCTION try_create(sql text) IS 'idempotent';
1111

    
1112

    
1113
--
1114
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
1115
--
1116

    
1117
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
1118
    LANGUAGE sql STRICT
1119
    AS $_$
1120
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
1121
$_$;
1122

    
1123

    
1124
--
1125
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
1126
--
1127

    
1128
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS 'idempotent';
1129

    
1130

    
1131
--
1132
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
1133
--
1134

    
1135
CREATE FUNCTION type_qual(value anyelement) RETURNS text
1136
    LANGUAGE sql IMMUTABLE
1137
    AS $_$
1138
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
1139
$_$;
1140

    
1141

    
1142
--
1143
-- Name: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
1144
--
1145

    
1146
CREATE FUNCTION type_qual_name(type regtype) RETURNS text
1147
    LANGUAGE sql STABLE STRICT
1148
    SET search_path TO pg_temp
1149
    AS $_$
1150
SELECT $1::text
1151
$_$;
1152

    
1153

    
1154
--
1155
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
1156
--
1157

    
1158
CREATE FUNCTION typeof(expr text, table_ regtype) RETURNS regtype
1159
    LANGUAGE plpgsql STABLE STRICT
1160
    AS $_$
1161
DECLARE
1162
    type regtype;
1163
BEGIN
1164
    EXECUTE $$SELECT pg_typeof($$||expr||$$) FROM (SELECT (NULL::$$||table_||
1165
    $$).*) _s$$ INTO STRICT type;
1166
    RETURN type;
1167
END;
1168
$_$;
1169

    
1170

    
1171
--
1172
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
1173
--
1174

    
1175
CREATE AGGREGATE join_strs(text, text) (
1176
    SFUNC = join_strs_transform,
1177
    STYPE = text
1178
);
1179

    
1180

    
1181
--
1182
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
1183
--
1184

    
1185
CREATE OPERATOR -> (
1186
    PROCEDURE = map_get,
1187
    LEFTARG = regclass,
1188
    RIGHTARG = text
1189
);
1190

    
1191

    
1192
SET default_tablespace = '';
1193

    
1194
SET default_with_oids = false;
1195

    
1196
--
1197
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
1198
--
1199

    
1200
CREATE TABLE map (
1201
    "from" text NOT NULL,
1202
    "to" text,
1203
    filter text,
1204
    notes text
1205
);
1206

    
1207

    
1208
--
1209
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
1210
--
1211

    
1212

    
1213

    
1214
--
1215
-- Name: map_pkey; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
1216
--
1217

    
1218
ALTER TABLE ONLY map
1219
    ADD CONSTRAINT map_pkey PRIMARY KEY ("from");
1220

    
1221

    
1222
--
1223
-- PostgreSQL database dump complete
1224
--
1225

    
(16-16/26)