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(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
33
--
34

    
35
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) RETURNS anyelement
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: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
44
--
45

    
46
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
47
    LANGUAGE sql IMMUTABLE
48
    AS $_$
49
SELECT $1 = $2
50
$_$;
51

    
52

    
53
--
54
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
55
--
56

    
57
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
58
    LANGUAGE sql IMMUTABLE
59
    AS $_$
60
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
61
$_$;
62

    
63

    
64
--
65
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
66
--
67

    
68
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
69
    LANGUAGE sql IMMUTABLE
70
    AS $_$
71
SELECT functions._if($1 != '', $2, $3)
72
$_$;
73

    
74

    
75
--
76
-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
77
--
78

    
79
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
80
    LANGUAGE sql IMMUTABLE
81
    AS $_$
82
SELECT array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], '; ')
83
$_$;
84

    
85

    
86
--
87
-- Name: _label(text, text); Type: FUNCTION; Schema: functions; Owner: -
88
--
89

    
90
CREATE FUNCTION _label(label text, value text) RETURNS text
91
    LANGUAGE plpgsql IMMUTABLE
92
    AS $$
93
DECLARE
94
    label text NOT NULL := label; -- add NOT NULL
95
BEGIN
96
    RETURN label||': '||value;
97
END;
98
$$;
99

    
100

    
101
--
102
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
103
--
104

    
105
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
106
    LANGUAGE sql IMMUTABLE
107
    AS $_$
108
SELECT functions.join_strs(value, '; ')
109
FROM
110
(
111
    SELECT *
112
    FROM
113
    (
114
        SELECT
115
        DISTINCT ON (value)
116
        *
117
        FROM
118
        (VALUES
119
              (1, $1)
120
            , (2, $2)
121
            , (3, $3)
122
            , (4, $4)
123
            , (5, $5)
124
            , (6, $6)
125
            , (7, $7)
126
            , (8, $8)
127
            , (9, $9)
128
            , (10, $10)
129
        )
130
        AS v (sort_order, value)
131
        WHERE value IS NOT NULL
132
    )
133
    AS v
134
    ORDER BY sort_order
135
)
136
AS v
137
$_$;
138

    
139

    
140
--
141
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
142
--
143

    
144
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
145
    LANGUAGE plpgsql IMMUTABLE
146
    AS $$
147
DECLARE
148
    "null" text NOT NULL := "null"; -- add NOT NULL
149
    type functions.datatype NOT NULL := type; -- add NOT NULL
150
BEGIN
151
    IF type = 'str' THEN RETURN nullif(value::text, "null");
152
    -- Invalid value is ignored, but invalid null value generates error
153
    ELSIF type = 'float' THEN
154
        DECLARE
155
            -- Outside the try block so that invalid null value generates error
156
            "null" double precision := "null"::double precision;
157
        BEGIN
158
            RETURN nullif(value::double precision, "null");
159
        EXCEPTION
160
            WHEN data_exception THEN RETURN value; -- ignore invalid value
161
        END;
162
    END IF;
163
END;
164
$$;
165

    
166

    
167
--
168
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: functions; Owner: -
169
--
170

    
171
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
172
    LANGUAGE sql IMMUTABLE
173
    AS $_$
174
SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
175
$_$;
176

    
177

    
178
--
179
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
180
--
181

    
182
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
183
    LANGUAGE sql IMMUTABLE STRICT
184
    AS $_$
185
SELECT $1 || $3 || $2
186
$_$;
187

    
188

    
189
--
190
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
191
--
192

    
193
CREATE AGGREGATE join_strs(text, text) (
194
    SFUNC = join_strs_transform,
195
    STYPE = text
196
);
197

    
198

    
199
--
200
-- PostgreSQL database dump complete
201
--
202

    
(4-4/20)