Project

General

Profile

1 2094 aaronmk
--
2
-- PostgreSQL database dump
3
--
4
5
SET statement_timeout = 0;
6
SET client_encoding = 'UTF8';
7 6213 aaronmk
SET standard_conforming_strings = on;
8 2094 aaronmk
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 4982 aaronmk
--
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 2107 aaronmk
SET search_path = functions, pg_catalog;
26
27 2094 aaronmk
--
28 2610 aaronmk
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
29
--
30
31
CREATE TYPE datatype AS ENUM (
32
    'str',
33
    'float'
34
);
35
36
37
--
38 4501 aaronmk
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
39 2596 aaronmk
--
40
41 4501 aaronmk
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 3422 aaronmk
    LANGUAGE sql IMMUTABLE
43
    AS $_$
44 4501 aaronmk
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
45 3422 aaronmk
$_$;
46
47
48
--
49 5956 aaronmk
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
50 5937 aaronmk
--
51
52 5956 aaronmk
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 5937 aaronmk
    LANGUAGE sql IMMUTABLE
54
    AS $_$
55
SELECT bool_and(value)
56
FROM
57
(VALUES
58
      ($1)
59
    , ($2)
60 5956 aaronmk
    , ($3)
61
    , ($4)
62
    , ($5)
63 5937 aaronmk
)
64
AS v (value)
65
$_$;
66
67
68
--
69 5956 aaronmk
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
70 5937 aaronmk
--
71
72 6437 aaronmk
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 5937 aaronmk
74
75
--
76 4142 aaronmk
-- 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 4147 aaronmk
-- 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 4325 aaronmk
-- 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 5395 aaronmk
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], '; '), '')
116 4325 aaronmk
$_$;
117
118
119
--
120 5009 aaronmk
-- 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 5395 aaronmk
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], ' '), '')
127 5009 aaronmk
$_$;
128
129
130
--
131 4683 aaronmk
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
132 3422 aaronmk
--
133
134 4683 aaronmk
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
135 4682 aaronmk
    LANGUAGE sql IMMUTABLE
136
    AS $_$
137
SELECT coalesce($1 || ': ', '') || $2
138
$_$;
139 2596 aaronmk
140
141
--
142 6222 aaronmk
-- 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 6271 aaronmk
    match text := map -> value;
150 6222 aaronmk
BEGIN
151 6271 aaronmk
    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 6243 aaronmk
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
159
    ELSIF match = '*' THEN RETURN value;
160
    ELSE RETURN match;
161 6222 aaronmk
    END IF;
162
END;
163
$$;
164
165
166
--
167 5408 aaronmk
-- 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 4150 aaronmk
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
193 2940 aaronmk
--
194
195 4150 aaronmk
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 2940 aaronmk
    LANGUAGE sql IMMUTABLE
197
    AS $_$
198 4053 aaronmk
SELECT functions.join_strs(value, '; ')
199 2940 aaronmk
FROM
200
(
201
    SELECT *
202
    FROM
203
    (
204
        SELECT
205
        DISTINCT ON (value)
206
        *
207
        FROM
208
        (VALUES
209 4012 aaronmk
              (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 2940 aaronmk
        )
220
        AS v (sort_order, value)
221 4011 aaronmk
        WHERE value IS NOT NULL
222 2940 aaronmk
    )
223
    AS v
224
    ORDER BY sort_order
225
)
226
AS v
227
$_$;
228
229
230
--
231 6354 aaronmk
-- 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 5408 aaronmk
-- 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 6316 aaronmk
-- 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 4475 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
307 2949 aaronmk
--
308
309 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
310 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
311
    AS $$
312
DECLARE
313
    type functions.datatype NOT NULL := type; -- add NOT NULL
314
BEGIN
315 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
316 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
317 2949 aaronmk
    ELSIF type = 'float' THEN
318 2722 aaronmk
        DECLARE
319
            -- Outside the try block so that invalid null value generates error
320 2949 aaronmk
            "null" double precision := "null"::double precision;
321 2722 aaronmk
        BEGIN
322 2949 aaronmk
            RETURN nullif(value::double precision, "null");
323 2722 aaronmk
        EXCEPTION
324 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
325 2722 aaronmk
        END;
326 2610 aaronmk
    END IF;
327
END;
328
$$;
329
330
331
--
332 4479 aaronmk
-- 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 6355 aaronmk
-- 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 6437 aaronmk
-- 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 4052 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
371 4009 aaronmk
--
372
373 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
374 4054 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
375 4009 aaronmk
    AS $_$
376 4054 aaronmk
SELECT $1 || $3 || $2
377 2595 aaronmk
$_$;
378
379
380
--
381
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
382
--
383
384
CREATE AGGREGATE join_strs(text, text) (
385 4052 aaronmk
    SFUNC = join_strs_transform,
386 4010 aaronmk
    STYPE = text
387 2595 aaronmk
);
388
389
390 2107 aaronmk
--
391 2136 aaronmk
-- PostgreSQL database dump complete
392
--