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
    map hstore := '*' => '!' || map; -- set default value for '*'
150
    match text := COALESCE(map -> value, map -> '*');
151
BEGIN
152
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
153
    ELSIF match = '*' THEN RETURN value;
154
    ELSE RETURN match;
155
    END IF;
156
END;
157
$$;
158

    
159

    
160
--
161
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
162
--
163

    
164
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
165
    LANGUAGE sql IMMUTABLE
166
    AS $_$
167
SELECT max(value)
168
FROM
169
(VALUES
170
      ($1)
171
    , ($2)
172
    , ($3)
173
    , ($4)
174
    , ($5)
175
    , ($6)
176
    , ($7)
177
    , ($8)
178
    , ($9)
179
    , ($10)
180
)
181
AS v (value)
182
$_$;
183

    
184

    
185
--
186
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
187
--
188

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

    
223

    
224
--
225
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
226
--
227

    
228
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
229
    LANGUAGE sql IMMUTABLE
230
    AS $_$
231
SELECT min(value)
232
FROM
233
(VALUES
234
      ($1)
235
    , ($2)
236
    , ($3)
237
    , ($4)
238
    , ($5)
239
    , ($6)
240
    , ($7)
241
    , ($8)
242
    , ($9)
243
    , ($10)
244
)
245
AS v (value)
246
$_$;
247

    
248

    
249
--
250
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
251
--
252

    
253
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
254
    LANGUAGE plpgsql IMMUTABLE
255
    AS $$
256
DECLARE
257
    "null" text NOT NULL := "null"; -- add NOT NULL
258
    type functions.datatype NOT NULL := type; -- add NOT NULL
259
BEGIN
260
    IF type = 'str' THEN RETURN nullif(value::text, "null");
261
    -- Invalid value is ignored, but invalid null value generates error
262
    ELSIF type = 'float' THEN
263
        DECLARE
264
            -- Outside the try block so that invalid null value generates error
265
            "null" double precision := "null"::double precision;
266
        BEGIN
267
            RETURN nullif(value::double precision, "null");
268
        EXCEPTION
269
            WHEN data_exception THEN RETURN value; -- ignore invalid value
270
        END;
271
    END IF;
272
END;
273
$$;
274

    
275

    
276
--
277
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
278
--
279

    
280
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
281
    LANGUAGE sql IMMUTABLE
282
    AS $_$
283
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
284
$_$;
285

    
286

    
287
--
288
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
289
--
290

    
291
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
292
    LANGUAGE sql IMMUTABLE STRICT
293
    AS $_$
294
SELECT $1 || $3 || $2
295
$_$;
296

    
297

    
298
--
299
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
300
--
301

    
302
CREATE AGGREGATE join_strs(text, text) (
303
    SFUNC = join_strs_transform,
304
    STYPE = text
305
);
306

    
307

    
308
--
309
-- PostgreSQL database dump complete
310
--
311

    
(4-4/20)