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
              (0, $1)
76
            , (1, $2)
77
            , (2, $3)
78
            , (3, $4)
79
            , (4, $5)
80
            , (5, $6)
81
            , (6, $7)
82
            , (7, $8)
83
            , (8, $9)
84
            , (9, $10)
85
        )
86
        AS v (sort_order, value)
87
    )
88
    AS v
89
    ORDER BY sort_order
90
)
91
AS v
92
$_$;
93

    
94

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

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

    
121

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

    
126
CREATE FUNCTION join_strs_(state text, delim text, value text) RETURNS text
127
    LANGUAGE sql IMMUTABLE
128
    AS $_$
129
SELECT $1 || (CASE
130
WHEN $1 = '' OR $3 IS NULL OR $3 = ''
131
THEN ''
132
ELSE $2
133
END) || coalesce($3, '');
134
$_$;
135

    
136

    
137
--
138
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
139
--
140

    
141
CREATE AGGREGATE join_strs(text, text) (
142
    SFUNC = join_strs_,
143
    STYPE = text,
144
    INITCOND = ''
145
);
146

    
147

    
148
--
149
-- PostgreSQL database dump complete
150
--
151

    
(4-4/20)