Revision 3422
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/functions.sql | ||
|---|---|---|
| 29 | 29 |
|
| 30 | 30 |
|
| 31 | 31 |
-- |
| 32 |
-- Name: _alt(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 32 |
-- Name: __alt(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 33 | 33 |
-- |
| 34 | 34 |
|
| 35 |
CREATE FUNCTION _alt() RETURNS trigger |
|
| 35 |
CREATE FUNCTION __alt() RETURNS trigger
|
|
| 36 | 36 |
LANGUAGE plpgsql IMMUTABLE |
| 37 | 37 |
AS $$ |
| 38 | 38 |
BEGIN |
| ... | ... | |
| 44 | 44 |
|
| 45 | 45 |
|
| 46 | 46 |
-- |
| 47 |
-- Name: _alt(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 47 |
-- Name: __label(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 48 | 48 |
-- |
| 49 | 49 |
|
| 50 |
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
|
|
| 51 |
LANGUAGE sql IMMUTABLE |
|
| 52 |
AS $_$ |
|
| 53 |
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) |
|
| 54 |
$_$; |
|
| 50 |
CREATE FUNCTION __label() RETURNS trigger |
|
| 51 |
LANGUAGE plpgsql IMMUTABLE |
|
| 52 |
AS $$ |
|
| 53 |
BEGIN |
|
| 54 |
new.result := functions._label(new.label, new.value); |
|
| 55 |
RETURN new; |
|
| 56 |
END; |
|
| 57 |
$$; |
|
| 55 | 58 |
|
| 56 | 59 |
|
| 57 | 60 |
-- |
| 58 |
-- Name: _label(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 61 |
-- Name: __merge(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 59 | 62 |
-- |
| 60 | 63 |
|
| 61 |
CREATE FUNCTION _label() RETURNS trigger
|
|
| 64 |
CREATE FUNCTION __merge() RETURNS trigger
|
|
| 62 | 65 |
LANGUAGE plpgsql IMMUTABLE |
| 63 | 66 |
AS $$ |
| 64 | 67 |
BEGIN |
| 65 |
new.result := functions._label(new.label, new.value); |
|
| 68 |
new.result := functions._merge(new."0", new."1", new."2", new."3", new."4", |
|
| 69 |
new."5", new."6", new."7", new."8", new."9"); |
|
| 66 | 70 |
RETURN new; |
| 67 | 71 |
END; |
| 68 | 72 |
$$; |
| 69 | 73 |
|
| 70 | 74 |
|
| 71 | 75 |
-- |
| 72 |
-- Name: _label(text, text); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 76 |
-- Name: __nullIf(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 73 | 77 |
-- |
| 74 | 78 |
|
| 75 |
CREATE FUNCTION _label(label text, value text) RETURNS text
|
|
| 79 |
CREATE FUNCTION "__nullIf"() RETURNS trigger
|
|
| 76 | 80 |
LANGUAGE plpgsql IMMUTABLE |
| 77 | 81 |
AS $$ |
| 78 |
DECLARE |
|
| 79 |
label text NOT NULL := label; -- add NOT NULL |
|
| 80 | 82 |
BEGIN |
| 81 |
RETURN label||': '||value; |
|
| 83 |
new.result := functions."_nullIf"(new.value, new."null", new.type); |
|
| 84 |
RETURN new; |
|
| 82 | 85 |
END; |
| 83 | 86 |
$$; |
| 84 | 87 |
|
| 85 | 88 |
|
| 86 | 89 |
-- |
| 87 |
-- Name: _merge(); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 90 |
-- Name: _alt(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: -
|
|
| 88 | 91 |
-- |
| 89 | 92 |
|
| 90 |
CREATE FUNCTION _merge() RETURNS trigger |
|
| 93 |
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
|
|
| 94 |
LANGUAGE sql IMMUTABLE |
|
| 95 |
AS $_$ |
|
| 96 |
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) |
|
| 97 |
$_$; |
|
| 98 |
|
|
| 99 |
|
|
| 100 |
-- |
|
| 101 |
-- Name: _label(text, text); Type: FUNCTION; Schema: functions; Owner: - |
|
| 102 |
-- |
|
| 103 |
|
|
| 104 |
CREATE FUNCTION _label(label text, value text) RETURNS text |
|
| 91 | 105 |
LANGUAGE plpgsql IMMUTABLE |
| 92 | 106 |
AS $$ |
| 107 |
DECLARE |
|
| 108 |
label text NOT NULL := label; -- add NOT NULL |
|
| 93 | 109 |
BEGIN |
| 94 |
new.result := functions._merge(new."0", new."1", new."2", new."3", new."4", |
|
| 95 |
new."5", new."6", new."7", new."8", new."9"); |
|
| 96 |
RETURN new; |
|
| 110 |
RETURN label||': '||value; |
|
| 97 | 111 |
END; |
| 98 | 112 |
$$; |
| 99 | 113 |
|
| ... | ... | |
| 137 | 151 |
|
| 138 | 152 |
|
| 139 | 153 |
-- |
| 140 |
-- Name: _nullIf(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 141 |
-- |
|
| 142 |
|
|
| 143 |
CREATE FUNCTION "_nullIf"() RETURNS trigger |
|
| 144 |
LANGUAGE plpgsql IMMUTABLE |
|
| 145 |
AS $$ |
|
| 146 |
BEGIN |
|
| 147 |
new.result := functions."_nullIf"(new.value, new."null", new.type); |
|
| 148 |
RETURN new; |
|
| 149 |
END; |
|
| 150 |
$$; |
|
| 151 |
|
|
| 152 |
|
|
| 153 |
-- |
|
| 154 | 154 |
-- Name: _nullIf(text, text, datatype); Type: FUNCTION; Schema: functions; Owner: - |
| 155 | 155 |
-- |
| 156 | 156 |
|
| ... | ... | |
| 304 | 304 |
-- Name: _alt; Type: TRIGGER; Schema: functions; Owner: - |
| 305 | 305 |
-- |
| 306 | 306 |
|
| 307 |
CREATE TRIGGER _alt BEFORE INSERT OR UPDATE ON _alt FOR EACH ROW EXECUTE PROCEDURE functions._alt();
|
|
| 307 |
CREATE TRIGGER _alt BEFORE INSERT OR UPDATE ON _alt FOR EACH ROW EXECUTE PROCEDURE __alt();
|
|
| 308 | 308 |
|
| 309 | 309 |
|
| 310 | 310 |
-- |
| 311 | 311 |
-- Name: _label; Type: TRIGGER; Schema: functions; Owner: - |
| 312 | 312 |
-- |
| 313 | 313 |
|
| 314 |
CREATE TRIGGER _label BEFORE INSERT OR UPDATE ON _label FOR EACH ROW EXECUTE PROCEDURE _label(); |
|
| 314 |
CREATE TRIGGER _label BEFORE INSERT OR UPDATE ON _label FOR EACH ROW EXECUTE PROCEDURE __label();
|
|
| 315 | 315 |
|
| 316 | 316 |
|
| 317 | 317 |
-- |
| 318 | 318 |
-- Name: _merge; Type: TRIGGER; Schema: functions; Owner: - |
| 319 | 319 |
-- |
| 320 | 320 |
|
| 321 |
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE functions._merge();
|
|
| 321 |
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE __merge();
|
|
| 322 | 322 |
|
| 323 | 323 |
|
| 324 | 324 |
-- |
| 325 | 325 |
-- Name: _nullIf; Type: TRIGGER; Schema: functions; Owner: - |
| 326 | 326 |
-- |
| 327 | 327 |
|
| 328 |
CREATE TRIGGER "_nullIf" BEFORE INSERT OR UPDATE ON "_nullIf" FOR EACH ROW EXECUTE PROCEDURE "_nullIf"(); |
|
| 328 |
CREATE TRIGGER "_nullIf" BEFORE INSERT OR UPDATE ON "_nullIf" FOR EACH ROW EXECUTE PROCEDURE "__nullIf"();
|
|
| 329 | 329 |
|
| 330 | 330 |
|
| 331 | 331 |
-- |
| schemas/py_functions.sql | ||
|---|---|---|
| 21 | 21 |
SET search_path = py_functions, pg_catalog; |
| 22 | 22 |
|
| 23 | 23 |
-- |
| 24 |
-- Name: __dateRangeEnd(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 25 |
-- |
|
| 26 |
|
|
| 27 |
CREATE FUNCTION "__dateRangeEnd"() RETURNS trigger |
|
| 28 |
LANGUAGE plpgsql IMMUTABLE |
|
| 29 |
AS $$ |
|
| 30 |
BEGIN |
|
| 31 |
new.result := py_functions."_dateRangeEnd"(new.value); |
|
| 32 |
RETURN new; |
|
| 33 |
END; |
|
| 34 |
$$; |
|
| 35 |
|
|
| 36 |
|
|
| 37 |
-- |
|
| 38 |
-- Name: __dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 39 |
-- |
|
| 40 |
|
|
| 41 |
CREATE FUNCTION "__dateRangeStart"() RETURNS trigger |
|
| 42 |
LANGUAGE plpgsql IMMUTABLE |
|
| 43 |
AS $$ |
|
| 44 |
BEGIN |
|
| 45 |
new.result := py_functions."_dateRangeStart"(new.value); |
|
| 46 |
RETURN new; |
|
| 47 |
END; |
|
| 48 |
$$; |
|
| 49 |
|
|
| 50 |
|
|
| 51 |
-- |
|
| 52 |
-- Name: __namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 53 |
-- |
|
| 54 |
|
|
| 55 |
CREATE FUNCTION "__namePart"() RETURNS trigger |
|
| 56 |
LANGUAGE plpgsql IMMUTABLE |
|
| 57 |
AS $$ |
|
| 58 |
BEGIN |
|
| 59 |
new.result := py_functions."_namePart"(new.first, new.middle, new.last); |
|
| 60 |
RETURN new; |
|
| 61 |
END; |
|
| 62 |
$$; |
|
| 63 |
|
|
| 64 |
|
|
| 65 |
-- |
|
| 24 | 66 |
-- Name: _date(text, text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien |
| 25 | 67 |
-- |
| 26 | 68 |
|
| ... | ... | |
| 101 | 143 |
|
| 102 | 144 |
|
| 103 | 145 |
-- |
| 104 |
-- Name: _dateRangeEnd(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 105 |
-- |
|
| 106 |
|
|
| 107 |
CREATE FUNCTION "_dateRangeEnd"() RETURNS trigger |
|
| 108 |
LANGUAGE plpgsql IMMUTABLE |
|
| 109 |
AS $$ |
|
| 110 |
BEGIN |
|
| 111 |
new.result := py_functions."_dateRangeEnd"(new.value); |
|
| 112 |
RETURN new; |
|
| 113 |
END; |
|
| 114 |
$$; |
|
| 115 |
|
|
| 116 |
|
|
| 117 |
-- |
|
| 118 | 146 |
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien |
| 119 | 147 |
-- |
| 120 | 148 |
|
| ... | ... | |
| 126 | 154 |
|
| 127 | 155 |
|
| 128 | 156 |
-- |
| 129 |
-- Name: _dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 130 |
-- |
|
| 131 |
|
|
| 132 |
CREATE FUNCTION "_dateRangeStart"() RETURNS trigger |
|
| 133 |
LANGUAGE plpgsql IMMUTABLE |
|
| 134 |
AS $$ |
|
| 135 |
BEGIN |
|
| 136 |
new.result := py_functions."_dateRangeStart"(new.value); |
|
| 137 |
RETURN new; |
|
| 138 |
END; |
|
| 139 |
$$; |
|
| 140 |
|
|
| 141 |
|
|
| 142 |
-- |
|
| 143 | 157 |
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien |
| 144 | 158 |
-- |
| 145 | 159 |
|
| ... | ... | |
| 151 | 165 |
|
| 152 | 166 |
|
| 153 | 167 |
-- |
| 154 |
-- Name: _namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 155 |
-- |
|
| 156 |
|
|
| 157 |
CREATE FUNCTION "_namePart"() RETURNS trigger |
|
| 158 |
LANGUAGE plpgsql IMMUTABLE |
|
| 159 |
AS $$ |
|
| 160 |
BEGIN |
|
| 161 |
new.result := py_functions."_namePart"(new.first, new.middle, new.last); |
|
| 162 |
RETURN new; |
|
| 163 |
END; |
|
| 164 |
$$; |
|
| 165 |
|
|
| 166 |
|
|
| 167 |
-- |
|
| 168 | 168 |
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien |
| 169 | 169 |
-- |
| 170 | 170 |
|
| ... | ... | |
| 305 | 305 |
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien |
| 306 | 306 |
-- |
| 307 | 307 |
|
| 308 |
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeEnd"(); |
|
| 308 |
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeEnd"();
|
|
| 309 | 309 |
|
| 310 | 310 |
|
| 311 | 311 |
-- |
| 312 | 312 |
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien |
| 313 | 313 |
-- |
| 314 | 314 |
|
| 315 |
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"(); |
|
| 315 |
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeStart"();
|
|
| 316 | 316 |
|
| 317 | 317 |
|
| 318 | 318 |
-- |
| 319 | 319 |
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien |
| 320 | 320 |
-- |
| 321 | 321 |
|
| 322 |
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE py_functions."_namePart"();
|
|
| 322 |
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "__namePart"();
|
|
| 323 | 323 |
|
| 324 | 324 |
|
| 325 | 325 |
-- |
Also available in: Unified diff
schemas/functions.sql, py_functions.sql: Renamed trigger functions to avoid collisions with plain SQL functions of the same name but different signatures, so that the plain SQL functions can be uniquely identified by their name without also requiring their signature