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 = 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
SET search_path = functions, pg_catalog;
20

    
21
--
22
-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
23
--
24

    
25
CREATE TYPE datatype AS ENUM (
26
    'str',
27
    'float'
28
);
29

    
30

    
31
--
32
-- Name: _alt(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: -
33
--
34

    
35
CREATE FUNCTION _alt("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
36
    LANGUAGE sql IMMUTABLE
37
    AS $_$
38
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
39
$_$;
40

    
41

    
42
--
43
-- Name: _label(text, text); Type: FUNCTION; Schema: functions; Owner: -
44
--
45

    
46
CREATE FUNCTION _label(label text, value text) RETURNS text
47
    LANGUAGE plpgsql IMMUTABLE
48
    AS $$
49
DECLARE
50
    label text NOT NULL := label; -- add NOT NULL
51
BEGIN
52
    RETURN label||': '||value;
53
END;
54
$$;
55

    
56

    
57
--
58
-- Name: _merge(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: -
59
--
60

    
61
CREATE FUNCTION _merge("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
62
    LANGUAGE sql IMMUTABLE
63
    AS $_$
64
SELECT functions.join_strs('; ', value)
65
FROM
66
(
67
    SELECT *
68
    FROM
69
    (
70
        SELECT
71
        DISTINCT ON (value)
72
        *
73
        FROM
74
        (VALUES
75
              (1, $1)
76
            , (2, $2)
77
            , (3, $3)
78
            , (4, $4)
79
            , (5, $5)
80
            , (6, $6)
81
            , (7, $7)
82
            , (8, $8)
83
            , (9, $9)
84
            , (10, $10)
85
        )
86
        AS v (sort_order, value)
87
        WHERE value IS NOT NULL
88
    )
89
    AS v
90
    ORDER BY sort_order
91
)
92
AS v
93
$_$;
94

    
95

    
96
--
97
-- Name: _nullIf(text, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
98
--
99

    
100
CREATE FUNCTION "_nullIf"(value text, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS text
101
    LANGUAGE plpgsql IMMUTABLE
102
    AS $$
103
DECLARE
104
    "null" text NOT NULL := "null"; -- add NOT NULL
105
    type functions.datatype NOT NULL := type; -- add NOT NULL
106
BEGIN
107
    IF type = 'str' THEN RETURN nullif(value, "null"); -- no cast needed
108
    -- Invalid value is ignored, but invalid null value generates error
109
    ELSIF type = 'float' THEN
110
        DECLARE
111
            -- Outside the try block so that invalid null value generates error
112
            "null" double precision := "null"::double precision;
113
        BEGIN
114
            RETURN nullif(value::double precision, "null");
115
        EXCEPTION
116
            WHEN data_exception THEN RETURN value; -- ignore invalid value
117
        END;
118
    END IF;
119
END;
120
$$;
121

    
122

    
123
--
124
-- Name: join_strs_transform_fold_empty(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
125
--
126

    
127
CREATE FUNCTION join_strs_transform_fold_empty(state text, delim text, value text) RETURNS text
128
    LANGUAGE sql IMMUTABLE
129
    AS $_$
130
SELECT functions.join_strs_transform_preserve_empty($1, $2, NULLIF($3, ''))
131
$_$;
132

    
133

    
134
--
135
-- Name: join_strs_transform_preserve_empty(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
136
--
137

    
138
CREATE FUNCTION join_strs_transform_preserve_empty(state text, delim text, value text) RETURNS text
139
    LANGUAGE sql IMMUTABLE
140
    AS $_$
141
SELECT (CASE
142
    WHEN $1 IS NOT NULL AND $3 IS NOT NULL
143
    THEN $1 || $2 || $3
144
    ELSE COALESCE($1, $3)
145
    END)
146
$_$;
147

    
148

    
149
--
150
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
151
--
152

    
153
CREATE AGGREGATE join_strs(text, text) (
154
    SFUNC = join_strs_transform_preserve_empty,
155
    STYPE = text
156
);
157

    
158

    
159
--
160
-- PostgreSQL database dump complete
161
--
162

    
(4-4/20)