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: datatype; Type: TYPE; Schema: functions; Owner: -
29
--
30

    
31
CREATE TYPE datatype AS ENUM (
32
    'str',
33
    'float'
34
);
35

    
36

    
37
--
38
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
39
--
40

    
41
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
42
    LANGUAGE sql IMMUTABLE
43
    AS $_$
44
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
45
$_$;
46

    
47

    
48
--
49
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
50
--
51

    
52
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
53
    LANGUAGE sql IMMUTABLE
54
    AS $_$
55
SELECT bool_and(value)
56
FROM
57
(VALUES
58
      ($1)
59
    , ($2)
60
    , ($3)
61
    , ($4)
62
    , ($5)
63
)
64
AS v (value)
65
$_$;
66

    
67

    
68
--
69
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
70
--
71

    
72
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.';
73

    
74

    
75
--
76
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
77
--
78

    
79
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
80
    LANGUAGE sql IMMUTABLE
81
    AS $_$
82
SELECT $1 = $2
83
$_$;
84

    
85

    
86
--
87
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
88
--
89

    
90
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
91
    LANGUAGE sql IMMUTABLE
92
    AS $_$
93
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
94
$_$;
95

    
96

    
97
--
98
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
99
--
100

    
101
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
102
    LANGUAGE sql IMMUTABLE
103
    AS $_$
104
SELECT functions._if($1 != '', $2, $3)
105
$_$;
106

    
107

    
108
--
109
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
110
--
111

    
112
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
113
    LANGUAGE sql IMMUTABLE
114
    AS $_$
115
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], '; '), '')
116
$_$;
117

    
118

    
119
--
120
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
121
--
122

    
123
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
124
    LANGUAGE sql IMMUTABLE
125
    AS $_$
126
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], ' '), '')
127
$_$;
128

    
129

    
130
--
131
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
132
--
133

    
134
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
135
    LANGUAGE sql IMMUTABLE
136
    AS $_$
137
SELECT coalesce($1 || ': ', '') || $2
138
$_$;
139

    
140

    
141
--
142
-- Name: _map(hstore, text); Type: FUNCTION; Schema: functions; Owner: -
143
--
144

    
145
CREATE FUNCTION _map(map hstore, value text) RETURNS text
146
    LANGUAGE plpgsql IMMUTABLE
147
    AS $$
148
DECLARE
149
    match text := map -> value;
150
BEGIN
151
    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
152
        match := map -> '*'; -- use default entry
153
        IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
154
        END IF;
155
    END IF;
156
    
157
    -- Interpret result
158
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
159
    ELSIF match = '*' THEN RETURN value;
160
    ELSE RETURN match;
161
    END IF;
162
END;
163
$$;
164

    
165

    
166
--
167
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
168
--
169

    
170
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
171
    LANGUAGE sql IMMUTABLE
172
    AS $_$
173
SELECT max(value)
174
FROM
175
(VALUES
176
      ($1)
177
    , ($2)
178
    , ($3)
179
    , ($4)
180
    , ($5)
181
    , ($6)
182
    , ($7)
183
    , ($8)
184
    , ($9)
185
    , ($10)
186
)
187
AS v (value)
188
$_$;
189

    
190

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

    
195
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
196
    LANGUAGE sql IMMUTABLE
197
    AS $_$
198
SELECT functions.join_strs(value, '; ')
199
FROM
200
(
201
    SELECT *
202
    FROM
203
    (
204
        SELECT
205
        DISTINCT ON (value)
206
        *
207
        FROM
208
        (VALUES
209
              (1, $1)
210
            , (2, $2)
211
            , (3, $3)
212
            , (4, $4)
213
            , (5, $5)
214
            , (6, $6)
215
            , (7, $7)
216
            , (8, $8)
217
            , (9, $9)
218
            , (10, $10)
219
        )
220
        AS v (sort_order, value)
221
        WHERE value IS NOT NULL
222
    )
223
    AS v
224
    ORDER BY sort_order
225
)
226
AS v
227
$_$;
228

    
229

    
230
--
231
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
232
--
233

    
234
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
235
    LANGUAGE sql IMMUTABLE
236
    AS $_$
237
SELECT functions.join_strs(value, ' ')
238
FROM
239
(
240
    SELECT *
241
    FROM
242
    (
243
        SELECT
244
        DISTINCT ON (value)
245
        *
246
        FROM
247
        (VALUES
248
              (1, $1)
249
            , (2, $2)
250
            , (3, $3)
251
            , (4, $4)
252
            , (5, $5)
253
            , (6, $6)
254
            , (7, $7)
255
            , (8, $8)
256
            , (9, $9)
257
            , (10, $10)
258
        )
259
        AS v (sort_order, value)
260
        WHERE value IS NOT NULL
261
    )
262
    AS v
263
    ORDER BY sort_order
264
)
265
AS v
266
$_$;
267

    
268

    
269
--
270
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
271
--
272

    
273
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
274
    LANGUAGE sql IMMUTABLE
275
    AS $_$
276
SELECT min(value)
277
FROM
278
(VALUES
279
      ($1)
280
    , ($2)
281
    , ($3)
282
    , ($4)
283
    , ($5)
284
    , ($6)
285
    , ($7)
286
    , ($8)
287
    , ($9)
288
    , ($10)
289
)
290
AS v (value)
291
$_$;
292

    
293

    
294
--
295
-- Name: _not(boolean); Type: FUNCTION; Schema: functions; Owner: -
296
--
297

    
298
CREATE FUNCTION _not(value boolean) RETURNS boolean
299
    LANGUAGE sql IMMUTABLE STRICT
300
    AS $_$
301
SELECT NOT $1
302
$_$;
303

    
304

    
305
--
306
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
307
--
308

    
309
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
310
    LANGUAGE plpgsql IMMUTABLE
311
    AS $$
312
DECLARE
313
    type functions.datatype NOT NULL := type; -- add NOT NULL
314
BEGIN
315
    IF type = 'str' THEN RETURN nullif(value::text, "null");
316
    -- Invalid value is ignored, but invalid null value generates error
317
    ELSIF type = 'float' THEN
318
        DECLARE
319
            -- Outside the try block so that invalid null value generates error
320
            "null" double precision := "null"::double precision;
321
        BEGIN
322
            RETURN nullif(value::double precision, "null");
323
        EXCEPTION
324
            WHEN data_exception THEN RETURN value; -- ignore invalid value
325
        END;
326
    END IF;
327
END;
328
$$;
329

    
330

    
331
--
332
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
333
--
334

    
335
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
336
    LANGUAGE sql IMMUTABLE
337
    AS $_$
338
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
339
$_$;
340

    
341

    
342
--
343
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
344
--
345

    
346
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
347
    LANGUAGE sql IMMUTABLE
348
    AS $_$
349
SELECT bool_or(value)
350
FROM
351
(VALUES
352
      ($1)
353
    , ($2)
354
    , ($3)
355
    , ($4)
356
    , ($5)
357
)
358
AS v (value)
359
$_$;
360

    
361

    
362
--
363
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
364
--
365

    
366
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.';
367

    
368

    
369
--
370
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
371
--
372

    
373
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
374
    LANGUAGE sql IMMUTABLE STRICT
375
    AS $_$
376
SELECT $1 || $3 || $2
377
$_$;
378

    
379

    
380
--
381
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
382
--
383

    
384
CREATE AGGREGATE join_strs(text, text) (
385
    SFUNC = join_strs_transform,
386
    STYPE = text
387
);
388

    
389

    
390
--
391
-- PostgreSQL database dump complete
392
--
393

    
(4-4/25)