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 5956 aaronmk
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS 'NULL values are ignored rather than propagated.';
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 5408 aaronmk
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
232
--
233
234
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
235
    LANGUAGE sql IMMUTABLE
236
    AS $_$
237
SELECT min(value)
238
FROM
239
(VALUES
240
      ($1)
241
    , ($2)
242
    , ($3)
243
    , ($4)
244
    , ($5)
245
    , ($6)
246
    , ($7)
247
    , ($8)
248
    , ($9)
249
    , ($10)
250
)
251
AS v (value)
252
$_$;
253
254
255
--
256 4475 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
257 2949 aaronmk
--
258
259 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
260 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
261
    AS $$
262
DECLARE
263
    "null" text NOT NULL := "null"; -- add NOT NULL
264
    type functions.datatype NOT NULL := type; -- add NOT NULL
265
BEGIN
266 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
267 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
268 2949 aaronmk
    ELSIF type = 'float' THEN
269 2722 aaronmk
        DECLARE
270
            -- Outside the try block so that invalid null value generates error
271 2949 aaronmk
            "null" double precision := "null"::double precision;
272 2722 aaronmk
        BEGIN
273 2949 aaronmk
            RETURN nullif(value::double precision, "null");
274 2722 aaronmk
        EXCEPTION
275 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
276 2722 aaronmk
        END;
277 2610 aaronmk
    END IF;
278
END;
279
$$;
280
281
282
--
283 4479 aaronmk
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
284
--
285
286
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
287
    LANGUAGE sql IMMUTABLE
288
    AS $_$
289
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
290
$_$;
291
292
293
--
294 4052 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
295 4009 aaronmk
--
296
297 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
298 4054 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
299 4009 aaronmk
    AS $_$
300 4054 aaronmk
SELECT $1 || $3 || $2
301 2595 aaronmk
$_$;
302
303
304
--
305
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
306
--
307
308
CREATE AGGREGATE join_strs(text, text) (
309 4052 aaronmk
    SFUNC = join_strs_transform,
310 4010 aaronmk
    STYPE = text
311 2595 aaronmk
);
312
313
314 2107 aaronmk
--
315 2136 aaronmk
-- PostgreSQL database dump complete
316
--