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: _label(text, text); Type: FUNCTION; Schema: functions; Owner: -
77
--
78

    
79
CREATE FUNCTION _label(label text, value text) RETURNS text
80
    LANGUAGE plpgsql IMMUTABLE
81
    AS $$
82
DECLARE
83
    label text NOT NULL := label; -- add NOT NULL
84
BEGIN
85
    RETURN label||': '||value;
86
END;
87
$$;
88

    
89

    
90
--
91
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
92
--
93

    
94
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
95
    LANGUAGE sql IMMUTABLE
96
    AS $_$
97
SELECT functions.join_strs(value, '; ')
98
FROM
99
(
100
    SELECT *
101
    FROM
102
    (
103
        SELECT
104
        DISTINCT ON (value)
105
        *
106
        FROM
107
        (VALUES
108
              (1, $1)
109
            , (2, $2)
110
            , (3, $3)
111
            , (4, $4)
112
            , (5, $5)
113
            , (6, $6)
114
            , (7, $7)
115
            , (8, $8)
116
            , (9, $9)
117
            , (10, $10)
118
        )
119
        AS v (sort_order, value)
120
        WHERE value IS NOT NULL
121
    )
122
    AS v
123
    ORDER BY sort_order
124
)
125
AS v
126
$_$;
127

    
128

    
129
--
130
-- Name: _nullIf(text, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
131
--
132

    
133
CREATE FUNCTION "_nullIf"(value text, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS text
134
    LANGUAGE plpgsql IMMUTABLE
135
    AS $$
136
DECLARE
137
    "null" text NOT NULL := "null"; -- add NOT NULL
138
    type functions.datatype NOT NULL := type; -- add NOT NULL
139
BEGIN
140
    IF type = 'str' THEN RETURN nullif(value, "null"); -- no cast needed
141
    -- Invalid value is ignored, but invalid null value generates error
142
    ELSIF type = 'float' THEN
143
        DECLARE
144
            -- Outside the try block so that invalid null value generates error
145
            "null" double precision := "null"::double precision;
146
        BEGIN
147
            RETURN nullif(value::double precision, "null");
148
        EXCEPTION
149
            WHEN data_exception THEN RETURN value; -- ignore invalid value
150
        END;
151
    END IF;
152
END;
153
$$;
154

    
155

    
156
--
157
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; Owner: -
158
--
159

    
160
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
161
    LANGUAGE sql IMMUTABLE STRICT
162
    AS $_$
163
SELECT $1 || $3 || $2
164
$_$;
165

    
166

    
167
--
168
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
169
--
170

    
171
CREATE AGGREGATE join_strs(text, text) (
172
    SFUNC = join_strs_transform,
173
    STYPE = text
174
);
175

    
176

    
177
--
178
-- PostgreSQL database dump complete
179
--
180

    
(4-4/20)