Project

General

Profile

1 2094 aaronmk
--
2
-- PostgreSQL database dump
3
--
4
5
SET statement_timeout = 0;
6
SET client_encoding = 'UTF8';
7
SET standard_conforming_strings = off;
8
SET check_function_bodies = false;
9
SET client_min_messages = warning;
10
SET escape_string_warning = off;
11
12
--
13
-- Name: functions; Type: SCHEMA; Schema: -; Owner: -
14
--
15
16
CREATE SCHEMA functions;
17
18
19 4982 aaronmk
--
20
-- Name: SCHEMA functions; Type: COMMENT; Schema: -; Owner: -
21
--
22
23
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.';
24
25
26 2107 aaronmk
SET search_path = functions, pg_catalog;
27
28 2094 aaronmk
--
29 2610 aaronmk
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
30
--
31
32
CREATE TYPE datatype AS ENUM (
33
    'str',
34
    'float'
35
);
36
37
38
--
39 4501 aaronmk
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
40 2596 aaronmk
--
41
42 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
43 3422 aaronmk
    LANGUAGE sql IMMUTABLE
44
    AS $_$
45 4501 aaronmk
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
46 3422 aaronmk
$_$;
47
48
49
--
50 4783 aaronmk
-- Name: _cm_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
51
--
52
53
CREATE FUNCTION _cm_to_m(value double precision) RETURNS double precision
54
    LANGUAGE sql IMMUTABLE STRICT
55
    AS $_$
56
SELECT $1/100.
57
$_$;
58
59
60
--
61 4142 aaronmk
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
62
--
63
64
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
65
    LANGUAGE sql IMMUTABLE
66
    AS $_$
67
SELECT $1 = $2
68
$_$;
69
70
71
--
72 4789 aaronmk
-- Name: _fraction_to_percent(double precision); Type: FUNCTION; Schema: functions; Owner: -
73 4760 aaronmk
--
74
75 4789 aaronmk
CREATE FUNCTION _fraction_to_percent(value double precision) RETURNS double precision
76 4760 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
77
    AS $_$
78
SELECT $1*100.
79
$_$;
80
81
82
--
83 5559 aaronmk
-- Name: _ft_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
84
--
85
86
CREATE FUNCTION _ft_to_m(value double precision) RETURNS double precision
87
    LANGUAGE sql IMMUTABLE STRICT
88
    AS $_$
89
SELECT functions._in_to_m($1*12)
90
$_$;
91
92
93
--
94 4764 aaronmk
-- Name: _ha_to_m2(double precision); Type: FUNCTION; Schema: functions; Owner: -
95
--
96
97
CREATE FUNCTION _ha_to_m2(value double precision) RETURNS double precision
98
    LANGUAGE sql IMMUTABLE STRICT
99
    AS $_$
100
SELECT $1*10000.
101
$_$;
102
103
104
--
105 4147 aaronmk
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
106
--
107
108
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
109
    LANGUAGE sql IMMUTABLE
110
    AS $_$
111
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
112
$_$;
113
114
115
--
116
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
117
--
118
119
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
120
    LANGUAGE sql IMMUTABLE
121
    AS $_$
122
SELECT functions._if($1 != '', $2, $3)
123
$_$;
124
125
126
--
127 4863 aaronmk
-- Name: _in_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
128
--
129
130
CREATE FUNCTION _in_to_m(value double precision) RETURNS double precision
131
    LANGUAGE sql IMMUTABLE STRICT
132
    AS $_$
133
SELECT $1*2.54/100.
134
$_$;
135
136
137
--
138 4325 aaronmk
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
139
--
140
141
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
142
    LANGUAGE sql IMMUTABLE
143
    AS $_$
144 5395 aaronmk
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], '; '), '')
145 4325 aaronmk
$_$;
146
147
148
--
149 5009 aaronmk
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
150
--
151
152
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
153
    LANGUAGE sql IMMUTABLE
154
    AS $_$
155 5395 aaronmk
SELECT nullif(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], ' '), '')
156 5009 aaronmk
$_$;
157
158
159
--
160 4683 aaronmk
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
161 3422 aaronmk
--
162
163 4683 aaronmk
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
164 4682 aaronmk
    LANGUAGE sql IMMUTABLE
165
    AS $_$
166
SELECT coalesce($1 || ': ', '') || $2
167
$_$;
168 2596 aaronmk
169
170
--
171 4773 aaronmk
-- Name: _m2_to_ha(double precision); Type: FUNCTION; Schema: functions; Owner: -
172
--
173
174
CREATE FUNCTION _m2_to_ha(value double precision) RETURNS double precision
175
    LANGUAGE sql IMMUTABLE STRICT
176
    AS $_$
177
SELECT $1/10000.
178
$_$;
179
180
181
--
182 5840 aaronmk
-- Name: _m_to_cm(double precision); Type: FUNCTION; Schema: functions; Owner: -
183
--
184
185
CREATE FUNCTION _m_to_cm(value double precision) RETURNS double precision
186
    LANGUAGE sql IMMUTABLE STRICT
187
    AS $_$
188
SELECT $1*100.
189
$_$;
190
191
192
--
193 5408 aaronmk
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
194
--
195
196
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
197
    LANGUAGE sql IMMUTABLE
198
    AS $_$
199
SELECT max(value)
200
FROM
201
(VALUES
202
      ($1)
203
    , ($2)
204
    , ($3)
205
    , ($4)
206
    , ($5)
207
    , ($6)
208
    , ($7)
209
    , ($8)
210
    , ($9)
211
    , ($10)
212
)
213
AS v (value)
214
$_$;
215
216
217
--
218 4150 aaronmk
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
219 2940 aaronmk
--
220
221 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
222 2940 aaronmk
    LANGUAGE sql IMMUTABLE
223
    AS $_$
224 4053 aaronmk
SELECT functions.join_strs(value, '; ')
225 2940 aaronmk
FROM
226
(
227
    SELECT *
228
    FROM
229
    (
230
        SELECT
231
        DISTINCT ON (value)
232
        *
233
        FROM
234
        (VALUES
235 4012 aaronmk
              (1, $1)
236
            , (2, $2)
237
            , (3, $3)
238
            , (4, $4)
239
            , (5, $5)
240
            , (6, $6)
241
            , (7, $7)
242
            , (8, $8)
243
            , (9, $9)
244
            , (10, $10)
245 2940 aaronmk
        )
246
        AS v (sort_order, value)
247 4011 aaronmk
        WHERE value IS NOT NULL
248 2940 aaronmk
    )
249
    AS v
250
    ORDER BY sort_order
251
)
252
AS v
253
$_$;
254
255
256
--
257 5408 aaronmk
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
258
--
259
260
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
261
    LANGUAGE sql IMMUTABLE
262
    AS $_$
263
SELECT min(value)
264
FROM
265
(VALUES
266
      ($1)
267
    , ($2)
268
    , ($3)
269
    , ($4)
270
    , ($5)
271
    , ($6)
272
    , ($7)
273
    , ($8)
274
    , ($9)
275
    , ($10)
276
)
277
AS v (value)
278
$_$;
279
280
281
--
282 4475 aaronmk
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
283 2949 aaronmk
--
284
285 4475 aaronmk
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
286 2949 aaronmk
    LANGUAGE plpgsql IMMUTABLE
287
    AS $$
288
DECLARE
289
    "null" text NOT NULL := "null"; -- add NOT NULL
290
    type functions.datatype NOT NULL := type; -- add NOT NULL
291
BEGIN
292 4475 aaronmk
    IF type = 'str' THEN RETURN nullif(value::text, "null");
293 2722 aaronmk
    -- Invalid value is ignored, but invalid null value generates error
294 2949 aaronmk
    ELSIF type = 'float' THEN
295 2722 aaronmk
        DECLARE
296
            -- Outside the try block so that invalid null value generates error
297 2949 aaronmk
            "null" double precision := "null"::double precision;
298 2722 aaronmk
        BEGIN
299 2949 aaronmk
            RETURN nullif(value::double precision, "null");
300 2722 aaronmk
        EXCEPTION
301 2949 aaronmk
            WHEN data_exception THEN RETURN value; -- ignore invalid value
302 2722 aaronmk
        END;
303 2610 aaronmk
    END IF;
304
END;
305
$$;
306
307
308
--
309 4479 aaronmk
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
310
--
311
312
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
313
    LANGUAGE sql IMMUTABLE
314
    AS $_$
315
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
316
$_$;
317
318
319
--
320 4789 aaronmk
-- Name: _percent_to_fraction(double precision); Type: FUNCTION; Schema: functions; Owner: -
321 4760 aaronmk
--
322
323 4789 aaronmk
CREATE FUNCTION _percent_to_fraction(value double precision) RETURNS double precision
324 4760 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
325
    AS $_$
326
SELECT $1/100.
327
$_$;
328
329
330
--
331 4052 aaronmk
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
332 4009 aaronmk
--
333
334 4053 aaronmk
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
335 4054 aaronmk
    LANGUAGE sql IMMUTABLE STRICT
336 4009 aaronmk
    AS $_$
337 4054 aaronmk
SELECT $1 || $3 || $2
338 2595 aaronmk
$_$;
339
340
341
--
342
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
343
--
344
345
CREATE AGGREGATE join_strs(text, text) (
346 4052 aaronmk
    SFUNC = join_strs_transform,
347 4010 aaronmk
    STYPE = text
348 2595 aaronmk
);
349
350
351 2107 aaronmk
--
352 2136 aaronmk
-- PostgreSQL database dump complete
353
--