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: functions; Type: SCHEMA; Schema: -; Owner: -
13
--
14

    
15
CREATE SCHEMA functions;
16

    
17

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

    
22
COMMENT ON SCHEMA functions 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 = functions, pg_catalog;
26

    
27
--
28
-- Name: compass_dir; Type: TYPE; Schema: functions; Owner: -
29
--
30

    
31
CREATE TYPE compass_dir AS ENUM (
32
    'N',
33
    'E',
34
    'S',
35
    'W'
36
);
37

    
38

    
39
--
40
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
41
--
42

    
43
CREATE TYPE datatype AS ENUM (
44
    'str',
45
    'float'
46
);
47

    
48

    
49
--
50
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
51
--
52

    
53
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
54
    LANGUAGE sql IMMUTABLE
55
    AS $_$
56
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
57
$_$;
58

    
59

    
60
--
61
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
62
--
63

    
64
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
65
    LANGUAGE sql IMMUTABLE
66
    AS $_$
67
SELECT bool_and(value)
68
FROM
69
(VALUES
70
      ($1)
71
    , ($2)
72
    , ($3)
73
    , ($4)
74
    , ($5)
75
)
76
AS v (value)
77
$_$;
78

    
79

    
80
--
81
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
82
--
83

    
84
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.';
85

    
86

    
87
--
88
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
89
--
90

    
91
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
92
    LANGUAGE sql IMMUTABLE
93
    AS $_$
94
SELECT avg(value)
95
FROM
96
(VALUES
97
      ($1)
98
    , ($2)
99
    , ($3)
100
    , ($4)
101
    , ($5)
102
)
103
AS v (value)
104
$_$;
105

    
106

    
107
--
108
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: functions; Owner: -
109
--
110

    
111
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
112
    LANGUAGE sql IMMUTABLE STRICT
113
    AS $_$
114
SELECT (g[1]||'1')::integer*functions._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::functions.compass_dir)
115
FROM 
116
(
117
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
118
    UNION ALL
119
    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
120
    FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
121
)
122
matches (g)
123
$_$;
124

    
125

    
126
--
127
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: functions; Owner: -
128
--
129

    
130
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
131
    LANGUAGE sql IMMUTABLE
132
    AS $_$
133
SELECT sum(value)*COALESCE(functions._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
134
FROM
135
(VALUES
136
      ($1)
137
    , ($2/60)
138
    , ($3/60/60)
139
)
140
AS v (value)
141
$_$;
142

    
143

    
144
--
145
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
146
--
147

    
148
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
149
    LANGUAGE sql IMMUTABLE
150
    AS $_$
151
SELECT $1 = $2
152
$_$;
153

    
154

    
155
--
156
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: functions; Owner: -
157
--
158

    
159
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
160
    LANGUAGE sql IMMUTABLE
161
    AS $_$
162
-- Fix dates after threshold date
163
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
164
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
165
$_$;
166

    
167

    
168
--
169
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
170
--
171

    
172
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
173
    LANGUAGE sql IMMUTABLE
174
    AS $_$
175
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
176
$_$;
177

    
178

    
179
--
180
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
181
--
182

    
183
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
184
    LANGUAGE sql IMMUTABLE
185
    AS $_$
186
SELECT functions._if($1 != '', $2, $3)
187
$_$;
188

    
189

    
190
--
191
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
192
--
193

    
194
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
195
    LANGUAGE sql IMMUTABLE
196
    AS $_$
197
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], '; '), '')
198
$_$;
199

    
200

    
201
--
202
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
203
--
204

    
205
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
206
    LANGUAGE sql IMMUTABLE
207
    AS $_$
208
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], ' '), '')
209
$_$;
210

    
211

    
212
--
213
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
214
--
215

    
216
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
217
    LANGUAGE sql IMMUTABLE
218
    AS $_$
219
SELECT coalesce($1 || ': ', '') || $2
220
$_$;
221

    
222

    
223
--
224
-- Name: _map(hstore, text); Type: FUNCTION; Schema: functions; Owner: -
225
--
226

    
227
CREATE FUNCTION _map(map hstore, value text) RETURNS text
228
    LANGUAGE plpgsql IMMUTABLE
229
    AS $$
230
DECLARE
231
    match text := map -> value;
232
BEGIN
233
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
234
        match := map -> '*'; -- use default entry
235
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
236
        END IF;
237
    END IF;
238
    
239
    -- Interpret result
240
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
241
    ELSIF match = '*' THEN RETURN value;
242
    ELSE RETURN match;
243
    END IF;
244
END;
245
$$;
246

    
247

    
248
--
249
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
250
--
251

    
252
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
253
    LANGUAGE sql IMMUTABLE
254
    AS $_$
255
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
256
$_$;
257

    
258

    
259
--
260
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
261
--
262

    
263
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
264
    LANGUAGE sql IMMUTABLE
265
    AS $_$
266
SELECT functions.join_strs(value, '; ')
267
FROM
268
(
269
    SELECT *
270
    FROM
271
    (
272
        SELECT
273
        DISTINCT ON (value)
274
        *
275
        FROM
276
        (VALUES
277
              (1, $1)
278
            , (2, $2)
279
            , (3, $3)
280
            , (4, $4)
281
            , (5, $5)
282
            , (6, $6)
283
            , (7, $7)
284
            , (8, $8)
285
            , (9, $9)
286
            , (10, $10)
287
        )
288
        AS v (sort_order, value)
289
        WHERE value IS NOT NULL
290
    )
291
    AS v
292
    ORDER BY sort_order
293
)
294
AS v
295
$_$;
296

    
297

    
298
--
299
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: functions; Owner: -
300
--
301

    
302
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
303
    LANGUAGE sql IMMUTABLE
304
    AS $_$
305
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
306
$_$;
307

    
308

    
309
--
310
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
311
--
312

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

    
347

    
348
--
349
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
350
--
351

    
352
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
353
    LANGUAGE sql IMMUTABLE
354
    AS $_$
355
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
356
$_$;
357

    
358

    
359
--
360
-- Name: _not(boolean); Type: FUNCTION; Schema: functions; Owner: -
361
--
362

    
363
CREATE FUNCTION _not(value boolean) RETURNS boolean
364
    LANGUAGE sql IMMUTABLE STRICT
365
    AS $_$
366
SELECT NOT $1
367
$_$;
368

    
369

    
370
--
371
-- Name: _now(); Type: FUNCTION; Schema: functions; Owner: -
372
--
373

    
374
CREATE FUNCTION _now() RETURNS timestamp with time zone
375
    LANGUAGE sql STABLE
376
    AS $$
377
SELECT now()
378
$$;
379

    
380

    
381
--
382
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
383
--
384

    
385
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
386
    LANGUAGE plpgsql IMMUTABLE
387
    AS $$
388
DECLARE
389
    type functions.datatype NOT NULL := type; -- add NOT NULL
390
BEGIN
391
    IF type = 'str' THEN RETURN nullif(value::text, "null");
392
    -- Invalid value is ignored, but invalid null value generates error
393
    ELSIF type = 'float' THEN
394
        DECLARE
395
            -- Outside the try block so that invalid null value generates error
396
            "null" double precision := "null"::double precision;
397
        BEGIN
398
            RETURN nullif(value::double precision, "null");
399
        EXCEPTION
400
            WHEN data_exception THEN RETURN value; -- ignore invalid value
401
        END;
402
    END IF;
403
END;
404
$$;
405

    
406

    
407
--
408
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
409
--
410

    
411
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
412
    LANGUAGE sql IMMUTABLE
413
    AS $_$
414
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
415
$_$;
416

    
417

    
418
--
419
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
420
--
421

    
422
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
423
    LANGUAGE sql IMMUTABLE
424
    AS $_$
425
SELECT bool_or(value)
426
FROM
427
(VALUES
428
      ($1)
429
    , ($2)
430
    , ($3)
431
    , ($4)
432
    , ($5)
433
)
434
AS v (value)
435
$_$;
436

    
437

    
438
--
439
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
440
--
441

    
442
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.';
443

    
444

    
445
--
446
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
447
--
448

    
449
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
450
    LANGUAGE sql IMMUTABLE
451
    AS $_$
452
SELECT $2 - $1
453
$_$;
454

    
455

    
456
--
457
-- Name: _split(text, text); Type: FUNCTION; Schema: functions; Owner: -
458
--
459

    
460
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
461
    LANGUAGE sql IMMUTABLE STRICT
462
    AS $_$
463
SELECT regexp_split_to_table($1, $2)
464
$_$;
465

    
466

    
467
--
468
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
469
--
470

    
471
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
472
    LANGUAGE sql IMMUTABLE STRICT
473
    AS $_$
474
SELECT $1 || $3 || $2
475
$_$;
476

    
477

    
478
--
479
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
480
--
481

    
482
CREATE AGGREGATE join_strs(text, text) (
483
    SFUNC = join_strs_transform,
484
    STYPE = text
485
);
486

    
487

    
488
--
489
-- PostgreSQL database dump complete
490
--
491

    
(4-4/25)