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 'NULL values are ignored rather than propagated.';
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: _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
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
257
--
258

    
259
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
260
    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
    IF type = 'str' THEN RETURN nullif(value::text, "null");
267
    -- Invalid value is ignored, but invalid null value generates error
268
    ELSIF type = 'float' THEN
269
        DECLARE
270
            -- Outside the try block so that invalid null value generates error
271
            "null" double precision := "null"::double precision;
272
        BEGIN
273
            RETURN nullif(value::double precision, "null");
274
        EXCEPTION
275
            WHEN data_exception THEN RETURN value; -- ignore invalid value
276
        END;
277
    END IF;
278
END;
279
$$;
280

    
281

    
282
--
283
-- 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
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
295
--
296

    
297
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
298
    LANGUAGE sql IMMUTABLE STRICT
299
    AS $_$
300
SELECT $1 || $3 || $2
301
$_$;
302

    
303

    
304
--
305
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
306
--
307

    
308
CREATE AGGREGATE join_strs(text, text) (
309
    SFUNC = join_strs_transform,
310
    STYPE = text
311
);
312

    
313

    
314
--
315
-- PostgreSQL database dump complete
316
--
317

    
(4-4/22)