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: col_cast; Type: TYPE; Schema: functions; Owner: -
|
29
|
--
|
30
|
|
31
|
CREATE TYPE col_cast AS (
|
32
|
col_name text,
|
33
|
type regtype
|
34
|
);
|
35
|
|
36
|
|
37
|
--
|
38
|
-- Name: col_ref; Type: TYPE; Schema: functions; Owner: -
|
39
|
--
|
40
|
|
41
|
CREATE TYPE col_ref AS (
|
42
|
table_ regclass,
|
43
|
name text
|
44
|
);
|
45
|
|
46
|
|
47
|
--
|
48
|
-- Name: compass_dir; Type: TYPE; Schema: functions; Owner: -
|
49
|
--
|
50
|
|
51
|
CREATE TYPE compass_dir AS ENUM (
|
52
|
'N',
|
53
|
'E',
|
54
|
'S',
|
55
|
'W'
|
56
|
);
|
57
|
|
58
|
|
59
|
--
|
60
|
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
|
61
|
--
|
62
|
|
63
|
CREATE TYPE datatype AS ENUM (
|
64
|
'str',
|
65
|
'float'
|
66
|
);
|
67
|
|
68
|
|
69
|
--
|
70
|
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
71
|
--
|
72
|
|
73
|
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
|
74
|
LANGUAGE sql IMMUTABLE
|
75
|
AS $_$
|
76
|
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
|
77
|
$_$;
|
78
|
|
79
|
|
80
|
--
|
81
|
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
|
82
|
--
|
83
|
|
84
|
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
|
85
|
LANGUAGE sql IMMUTABLE
|
86
|
AS $_$
|
87
|
SELECT bool_and(value)
|
88
|
FROM
|
89
|
(VALUES
|
90
|
($1)
|
91
|
, ($2)
|
92
|
, ($3)
|
93
|
, ($4)
|
94
|
, ($5)
|
95
|
)
|
96
|
AS v (value)
|
97
|
$_$;
|
98
|
|
99
|
|
100
|
--
|
101
|
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
|
102
|
--
|
103
|
|
104
|
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.';
|
105
|
|
106
|
|
107
|
--
|
108
|
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
|
109
|
--
|
110
|
|
111
|
CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
|
112
|
LANGUAGE sql IMMUTABLE
|
113
|
AS $_$
|
114
|
SELECT avg(value)
|
115
|
FROM
|
116
|
(VALUES
|
117
|
($1)
|
118
|
, ($2)
|
119
|
, ($3)
|
120
|
, ($4)
|
121
|
, ($5)
|
122
|
)
|
123
|
AS v (value)
|
124
|
$_$;
|
125
|
|
126
|
|
127
|
--
|
128
|
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: functions; Owner: -
|
129
|
--
|
130
|
|
131
|
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
|
132
|
LANGUAGE sql IMMUTABLE STRICT
|
133
|
AS $_$
|
134
|
SELECT (g[1]||'1')::integer*functions._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::functions.compass_dir)
|
135
|
FROM
|
136
|
(
|
137
|
SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
|
138
|
UNION ALL
|
139
|
SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
|
140
|
FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
|
141
|
)
|
142
|
matches (g)
|
143
|
$_$;
|
144
|
|
145
|
|
146
|
--
|
147
|
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: functions; Owner: -
|
148
|
--
|
149
|
|
150
|
CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
|
151
|
LANGUAGE sql IMMUTABLE
|
152
|
AS $_$
|
153
|
SELECT sum(value)*COALESCE(functions._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
|
154
|
FROM
|
155
|
(VALUES
|
156
|
($1)
|
157
|
, ($2/60)
|
158
|
, ($3/60/60)
|
159
|
)
|
160
|
AS v (value)
|
161
|
$_$;
|
162
|
|
163
|
|
164
|
--
|
165
|
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: functions; Owner: -
|
166
|
--
|
167
|
|
168
|
CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
|
169
|
LANGUAGE sql IMMUTABLE
|
170
|
AS $_$
|
171
|
SELECT functions._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::functions.compass_dir)
|
172
|
$_$;
|
173
|
|
174
|
|
175
|
--
|
176
|
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
177
|
--
|
178
|
|
179
|
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
|
180
|
LANGUAGE sql IMMUTABLE
|
181
|
AS $_$
|
182
|
SELECT $1 = $2
|
183
|
$_$;
|
184
|
|
185
|
|
186
|
--
|
187
|
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: functions; Owner: -
|
188
|
--
|
189
|
|
190
|
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
|
191
|
LANGUAGE sql IMMUTABLE
|
192
|
AS $_$
|
193
|
-- Fix dates after threshold date
|
194
|
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
|
195
|
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
|
196
|
$_$;
|
197
|
|
198
|
|
199
|
--
|
200
|
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
201
|
--
|
202
|
|
203
|
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
204
|
LANGUAGE sql IMMUTABLE
|
205
|
AS $_$
|
206
|
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
|
207
|
$_$;
|
208
|
|
209
|
|
210
|
--
|
211
|
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
212
|
--
|
213
|
|
214
|
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
215
|
LANGUAGE sql IMMUTABLE
|
216
|
AS $_$
|
217
|
SELECT functions._if($1 != '', $2, $3)
|
218
|
$_$;
|
219
|
|
220
|
|
221
|
--
|
222
|
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
223
|
--
|
224
|
|
225
|
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
|
226
|
LANGUAGE sql IMMUTABLE
|
227
|
AS $_$
|
228
|
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
|
229
|
$_$;
|
230
|
|
231
|
|
232
|
--
|
233
|
-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
234
|
--
|
235
|
|
236
|
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
|
237
|
LANGUAGE sql IMMUTABLE
|
238
|
AS $_$
|
239
|
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
|
240
|
$_$;
|
241
|
|
242
|
|
243
|
--
|
244
|
-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
245
|
--
|
246
|
|
247
|
CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
|
248
|
LANGUAGE sql IMMUTABLE
|
249
|
AS $_$
|
250
|
SELECT coalesce($1 || ': ', '') || $2
|
251
|
$_$;
|
252
|
|
253
|
|
254
|
--
|
255
|
-- Name: _map(hstore, text); Type: FUNCTION; Schema: functions; Owner: -
|
256
|
--
|
257
|
|
258
|
CREATE FUNCTION _map(map hstore, value text) RETURNS text
|
259
|
LANGUAGE plpgsql IMMUTABLE STRICT
|
260
|
AS $$
|
261
|
DECLARE
|
262
|
match text := map -> value;
|
263
|
BEGIN
|
264
|
IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
|
265
|
match := map -> '*'; -- use default entry
|
266
|
IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
|
267
|
END IF;
|
268
|
END IF;
|
269
|
|
270
|
-- Interpret result
|
271
|
IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
|
272
|
ELSIF match = '*' THEN RETURN value;
|
273
|
ELSE RETURN match;
|
274
|
END IF;
|
275
|
END;
|
276
|
$$;
|
277
|
|
278
|
|
279
|
--
|
280
|
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
281
|
--
|
282
|
|
283
|
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
|
284
|
LANGUAGE sql IMMUTABLE
|
285
|
AS $_$
|
286
|
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
|
287
|
$_$;
|
288
|
|
289
|
|
290
|
--
|
291
|
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
292
|
--
|
293
|
|
294
|
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
|
295
|
LANGUAGE sql IMMUTABLE
|
296
|
AS $_$
|
297
|
SELECT functions.join_strs(value, '; ')
|
298
|
FROM
|
299
|
(
|
300
|
SELECT *
|
301
|
FROM
|
302
|
(
|
303
|
SELECT
|
304
|
DISTINCT ON (value)
|
305
|
*
|
306
|
FROM
|
307
|
(VALUES
|
308
|
(1, $1)
|
309
|
, (2, $2)
|
310
|
, (3, $3)
|
311
|
, (4, $4)
|
312
|
, (5, $5)
|
313
|
, (6, $6)
|
314
|
, (7, $7)
|
315
|
, (8, $8)
|
316
|
, (9, $9)
|
317
|
, (10, $10)
|
318
|
)
|
319
|
AS v (sort_order, value)
|
320
|
WHERE value IS NOT NULL
|
321
|
)
|
322
|
AS v
|
323
|
ORDER BY sort_order
|
324
|
)
|
325
|
AS v
|
326
|
$_$;
|
327
|
|
328
|
|
329
|
--
|
330
|
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
331
|
--
|
332
|
|
333
|
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
|
334
|
LANGUAGE sql IMMUTABLE
|
335
|
AS $_$
|
336
|
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
|
337
|
$_$;
|
338
|
|
339
|
|
340
|
--
|
341
|
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
342
|
--
|
343
|
|
344
|
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
|
345
|
LANGUAGE sql IMMUTABLE
|
346
|
AS $_$
|
347
|
SELECT functions.join_strs(value, ' ')
|
348
|
FROM
|
349
|
(
|
350
|
SELECT *
|
351
|
FROM
|
352
|
(
|
353
|
SELECT
|
354
|
DISTINCT ON (value)
|
355
|
*
|
356
|
FROM
|
357
|
(VALUES
|
358
|
(1, $1)
|
359
|
, (2, $2)
|
360
|
, (3, $3)
|
361
|
, (4, $4)
|
362
|
, (5, $5)
|
363
|
, (6, $6)
|
364
|
, (7, $7)
|
365
|
, (8, $8)
|
366
|
, (9, $9)
|
367
|
, (10, $10)
|
368
|
)
|
369
|
AS v (sort_order, value)
|
370
|
WHERE value IS NOT NULL
|
371
|
)
|
372
|
AS v
|
373
|
ORDER BY sort_order
|
374
|
)
|
375
|
AS v
|
376
|
$_$;
|
377
|
|
378
|
|
379
|
--
|
380
|
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
|
381
|
--
|
382
|
|
383
|
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
|
384
|
LANGUAGE sql IMMUTABLE
|
385
|
AS $_$
|
386
|
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
|
387
|
$_$;
|
388
|
|
389
|
|
390
|
--
|
391
|
-- Name: _not(boolean); Type: FUNCTION; Schema: functions; Owner: -
|
392
|
--
|
393
|
|
394
|
CREATE FUNCTION _not(value boolean) RETURNS boolean
|
395
|
LANGUAGE sql IMMUTABLE STRICT
|
396
|
AS $_$
|
397
|
SELECT NOT $1
|
398
|
$_$;
|
399
|
|
400
|
|
401
|
--
|
402
|
-- Name: _now(); Type: FUNCTION; Schema: functions; Owner: -
|
403
|
--
|
404
|
|
405
|
CREATE FUNCTION _now() RETURNS timestamp with time zone
|
406
|
LANGUAGE sql STABLE
|
407
|
AS $$
|
408
|
SELECT now()
|
409
|
$$;
|
410
|
|
411
|
|
412
|
--
|
413
|
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
|
414
|
--
|
415
|
|
416
|
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
|
417
|
LANGUAGE plpgsql IMMUTABLE
|
418
|
AS $$
|
419
|
DECLARE
|
420
|
type functions.datatype NOT NULL := type; -- add NOT NULL
|
421
|
BEGIN
|
422
|
IF type = 'str' THEN RETURN nullif(value::text, "null");
|
423
|
-- Invalid value is ignored, but invalid null value generates error
|
424
|
ELSIF type = 'float' THEN
|
425
|
DECLARE
|
426
|
-- Outside the try block so that invalid null value generates error
|
427
|
"null" double precision := "null"::double precision;
|
428
|
BEGIN
|
429
|
RETURN nullif(value::double precision, "null");
|
430
|
EXCEPTION
|
431
|
WHEN data_exception THEN RETURN value; -- ignore invalid value
|
432
|
END;
|
433
|
END IF;
|
434
|
END;
|
435
|
$$;
|
436
|
|
437
|
|
438
|
--
|
439
|
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
|
440
|
--
|
441
|
|
442
|
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
|
443
|
LANGUAGE sql IMMUTABLE
|
444
|
AS $_$
|
445
|
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
|
446
|
$_$;
|
447
|
|
448
|
|
449
|
--
|
450
|
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: functions; Owner: -
|
451
|
--
|
452
|
|
453
|
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
|
454
|
LANGUAGE sql IMMUTABLE
|
455
|
AS $_$
|
456
|
SELECT bool_or(value)
|
457
|
FROM
|
458
|
(VALUES
|
459
|
($1)
|
460
|
, ($2)
|
461
|
, ($3)
|
462
|
, ($4)
|
463
|
, ($5)
|
464
|
)
|
465
|
AS v (value)
|
466
|
$_$;
|
467
|
|
468
|
|
469
|
--
|
470
|
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: functions; Owner: -
|
471
|
--
|
472
|
|
473
|
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.';
|
474
|
|
475
|
|
476
|
--
|
477
|
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: functions; Owner: -
|
478
|
--
|
479
|
|
480
|
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
|
481
|
LANGUAGE sql IMMUTABLE
|
482
|
AS $_$
|
483
|
SELECT $2 - $1
|
484
|
$_$;
|
485
|
|
486
|
|
487
|
--
|
488
|
-- Name: _split(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
489
|
--
|
490
|
|
491
|
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
|
492
|
LANGUAGE sql IMMUTABLE STRICT
|
493
|
AS $_$
|
494
|
SELECT regexp_split_to_table($1, $2)
|
495
|
$_$;
|
496
|
|
497
|
|
498
|
--
|
499
|
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: functions; Owner: -
|
500
|
--
|
501
|
|
502
|
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
|
503
|
LANGUAGE sql STABLE STRICT
|
504
|
AS $_$
|
505
|
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
|
506
|
$_$;
|
507
|
|
508
|
|
509
|
--
|
510
|
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: functions; Owner: -
|
511
|
--
|
512
|
|
513
|
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
|
514
|
LANGUAGE plpgsql STRICT
|
515
|
AS $_$
|
516
|
BEGIN
|
517
|
-- not yet clustered (ARRAY[] compares NULLs literally)
|
518
|
IF ARRAY[functions.cluster_index(table_)] != ARRAY[index] THEN
|
519
|
EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
|
520
|
END IF;
|
521
|
END;
|
522
|
$_$;
|
523
|
|
524
|
|
525
|
--
|
526
|
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: functions; Owner: -
|
527
|
--
|
528
|
|
529
|
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
|
530
|
|
531
|
|
532
|
--
|
533
|
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: functions; Owner: -
|
534
|
--
|
535
|
|
536
|
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
|
537
|
LANGUAGE plpgsql STABLE STRICT
|
538
|
AS $$
|
539
|
DECLARE
|
540
|
prefix text := functions.name(type)||'.';
|
541
|
BEGIN
|
542
|
RETURN QUERY
|
543
|
SELECT name_, (CASE WHEN functions.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
|
544
|
FROM functions.col_names(type) f (name_);
|
545
|
END;
|
546
|
$$;
|
547
|
|
548
|
|
549
|
--
|
550
|
-- Name: col_names(regtype); Type: FUNCTION; Schema: functions; Owner: -
|
551
|
--
|
552
|
|
553
|
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
|
554
|
LANGUAGE plpgsql STABLE STRICT
|
555
|
AS $_$
|
556
|
BEGIN
|
557
|
RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
|
558
|
END;
|
559
|
$_$;
|
560
|
|
561
|
|
562
|
--
|
563
|
-- Name: col_type(col_ref); Type: FUNCTION; Schema: functions; Owner: -
|
564
|
--
|
565
|
|
566
|
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
|
567
|
LANGUAGE sql STABLE STRICT
|
568
|
AS $_$
|
569
|
SELECT atttypid FROM pg_attribute
|
570
|
WHERE attrelid = $1.table_ AND attname = $1.name
|
571
|
$_$;
|
572
|
|
573
|
|
574
|
--
|
575
|
-- Name: contains(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
576
|
--
|
577
|
|
578
|
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
|
579
|
LANGUAGE sql IMMUTABLE STRICT
|
580
|
AS $_$
|
581
|
SELECT position($1 in $2) > 0 /*1-based offset*/
|
582
|
$_$;
|
583
|
|
584
|
|
585
|
--
|
586
|
-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: functions; Owner: -
|
587
|
--
|
588
|
|
589
|
CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
|
590
|
LANGUAGE plpgsql STRICT
|
591
|
AS $$
|
592
|
BEGIN
|
593
|
EXECUTE sql;
|
594
|
EXCEPTION
|
595
|
WHEN duplicate_table THEN NULL;
|
596
|
WHEN duplicate_column THEN NULL;
|
597
|
END;
|
598
|
$$;
|
599
|
|
600
|
|
601
|
--
|
602
|
-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: functions; Owner: -
|
603
|
--
|
604
|
|
605
|
COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
|
606
|
|
607
|
|
608
|
--
|
609
|
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
610
|
--
|
611
|
|
612
|
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
|
613
|
LANGUAGE sql IMMUTABLE STRICT
|
614
|
AS $_$
|
615
|
SELECT (CASE WHEN functions.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
|
616
|
$_$;
|
617
|
|
618
|
|
619
|
--
|
620
|
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
621
|
--
|
622
|
|
623
|
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
|
624
|
LANGUAGE sql IMMUTABLE STRICT
|
625
|
AS $_$
|
626
|
SELECT substring($2 for length($1)) = $1
|
627
|
$_$;
|
628
|
|
629
|
|
630
|
--
|
631
|
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
|
632
|
--
|
633
|
|
634
|
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
|
635
|
LANGUAGE sql IMMUTABLE STRICT
|
636
|
AS $_$
|
637
|
SELECT $1 || $3 || $2
|
638
|
$_$;
|
639
|
|
640
|
|
641
|
--
|
642
|
-- Name: name(regtype); Type: FUNCTION; Schema: functions; Owner: -
|
643
|
--
|
644
|
|
645
|
CREATE FUNCTION name(type regtype) RETURNS text
|
646
|
LANGUAGE sql STABLE STRICT
|
647
|
AS $_$
|
648
|
SELECT typname::text FROM pg_type WHERE oid = $1
|
649
|
$_$;
|
650
|
|
651
|
|
652
|
--
|
653
|
-- Name: rename_cols(regclass, hstore); Type: FUNCTION; Schema: functions; Owner: -
|
654
|
--
|
655
|
|
656
|
CREATE FUNCTION rename_cols(table_ regclass, renames hstore) RETURNS void
|
657
|
LANGUAGE sql STRICT
|
658
|
AS $_$
|
659
|
SELECT functions.rename_if_exists($$ALTER TABLE $$||$1||$$ RENAME $$
|
660
|
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
|
661
|
FROM functions.col_names($1::text::regtype) f (name)
|
662
|
$_$;
|
663
|
|
664
|
|
665
|
--
|
666
|
-- Name: FUNCTION rename_cols(table_ regclass, renames hstore); Type: COMMENT; Schema: functions; Owner: -
|
667
|
--
|
668
|
|
669
|
COMMENT ON FUNCTION rename_cols(table_ regclass, renames hstore) IS 'idempotent';
|
670
|
|
671
|
|
672
|
--
|
673
|
-- Name: rename_if_exists(text); Type: FUNCTION; Schema: functions; Owner: -
|
674
|
--
|
675
|
|
676
|
CREATE FUNCTION rename_if_exists(sql text) RETURNS void
|
677
|
LANGUAGE plpgsql STRICT
|
678
|
AS $$
|
679
|
BEGIN
|
680
|
EXECUTE sql;
|
681
|
EXCEPTION
|
682
|
WHEN undefined_column THEN NULL;
|
683
|
END;
|
684
|
$$;
|
685
|
|
686
|
|
687
|
--
|
688
|
-- Name: FUNCTION rename_if_exists(sql text); Type: COMMENT; Schema: functions; Owner: -
|
689
|
--
|
690
|
|
691
|
COMMENT ON FUNCTION rename_if_exists(sql text) IS 'idempotent';
|
692
|
|
693
|
|
694
|
--
|
695
|
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: functions; Owner: -
|
696
|
--
|
697
|
|
698
|
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
|
699
|
LANGUAGE plpgsql STRICT
|
700
|
AS $_$
|
701
|
DECLARE
|
702
|
sql text = $$ALTER TABLE $$||table_||$$
|
703
|
$$||NULLIF(array_to_string(ARRAY(
|
704
|
SELECT
|
705
|
$$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
|
706
|
||$$ USING $$||col_name_sql||$$::$$||target_type
|
707
|
FROM
|
708
|
(
|
709
|
SELECT
|
710
|
quote_ident(col_name) AS col_name_sql
|
711
|
, functions.col_type((table_, col_name)) AS curr_type
|
712
|
, type AS target_type
|
713
|
FROM unnest(col_casts)
|
714
|
) s
|
715
|
WHERE curr_type != target_type
|
716
|
), '
|
717
|
, '), '');
|
718
|
BEGIN
|
719
|
RAISE NOTICE '%', sql;
|
720
|
EXECUTE COALESCE(sql, '');
|
721
|
END;
|
722
|
$_$;
|
723
|
|
724
|
|
725
|
--
|
726
|
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: functions; Owner: -
|
727
|
--
|
728
|
|
729
|
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
|
730
|
|
731
|
|
732
|
--
|
733
|
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: functions; Owner: -
|
734
|
--
|
735
|
|
736
|
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
|
737
|
LANGUAGE plpgsql STRICT
|
738
|
AS $_$
|
739
|
DECLARE
|
740
|
row record;
|
741
|
BEGIN
|
742
|
FOR row IN SELECT * FROM functions.col_global_names(table_::text::regtype)
|
743
|
LOOP
|
744
|
IF row.global_name != row.name THEN
|
745
|
EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
|
746
|
||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
|
747
|
END IF;
|
748
|
END LOOP;
|
749
|
END;
|
750
|
$_$;
|
751
|
|
752
|
|
753
|
--
|
754
|
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: functions; Owner: -
|
755
|
--
|
756
|
|
757
|
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
|
758
|
|
759
|
|
760
|
--
|
761
|
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
|
762
|
--
|
763
|
|
764
|
CREATE AGGREGATE join_strs(text, text) (
|
765
|
SFUNC = join_strs_transform,
|
766
|
STYPE = text
|
767
|
);
|
768
|
|
769
|
|
770
|
--
|
771
|
-- PostgreSQL database dump complete
|
772
|
--
|
773
|
|