Revision 3695
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: - |
|
| 33 |
-- |
|
| 34 |
|
|
| 35 |
CREATE FUNCTION __alt() RETURNS trigger |
|
| 36 |
LANGUAGE plpgsql IMMUTABLE |
|
| 37 |
AS $$ |
|
| 38 |
BEGIN |
|
| 39 |
new.result := functions._alt(new."0", new."1", new."2", new."3", new."4", |
|
| 40 |
new."5", new."6", new."7", new."8", new."9"); |
|
| 41 |
RETURN new; |
|
| 42 |
END; |
|
| 43 |
$$; |
|
| 44 |
|
|
| 45 |
|
|
| 46 |
-- |
|
| 47 |
-- Name: __label(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 48 |
-- |
|
| 49 |
|
|
| 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 |
$$; |
|
| 58 |
|
|
| 59 |
|
|
| 60 |
-- |
|
| 61 |
-- Name: __merge(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 62 |
-- |
|
| 63 |
|
|
| 64 |
CREATE FUNCTION __merge() RETURNS trigger |
|
| 65 |
LANGUAGE plpgsql IMMUTABLE |
|
| 66 |
AS $$ |
|
| 67 |
BEGIN |
|
| 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"); |
|
| 70 |
RETURN new; |
|
| 71 |
END; |
|
| 72 |
$$; |
|
| 73 |
|
|
| 74 |
|
|
| 75 |
-- |
|
| 76 |
-- Name: __nullIf(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 77 |
-- |
|
| 78 |
|
|
| 79 |
CREATE FUNCTION "__nullIf"() RETURNS trigger |
|
| 80 |
LANGUAGE plpgsql IMMUTABLE |
|
| 81 |
AS $$ |
|
| 82 |
BEGIN |
|
| 83 |
new.result := functions."_nullIf"(new.value, new."null", new.type); |
|
| 84 |
RETURN new; |
|
| 85 |
END; |
|
| 86 |
$$; |
|
| 87 |
|
|
| 88 |
|
|
| 89 |
-- |
|
| 90 | 32 |
-- Name: _alt(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: - |
| 91 | 33 |
-- |
| 92 | 34 |
|
| ... | ... | |
| 203 | 145 |
); |
| 204 | 146 |
|
| 205 | 147 |
|
| 206 |
SET default_tablespace = ''; |
|
| 207 |
|
|
| 208 |
SET default_with_oids = false; |
|
| 209 |
|
|
| 210 | 148 |
-- |
| 211 |
-- Name: _alt; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 212 |
-- |
|
| 213 |
|
|
| 214 |
CREATE TABLE _alt ( |
|
| 215 |
result text, |
|
| 216 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 217 |
"0" text, |
|
| 218 |
"1" text, |
|
| 219 |
"2" text, |
|
| 220 |
"3" text, |
|
| 221 |
"4" text, |
|
| 222 |
"5" text, |
|
| 223 |
"6" text, |
|
| 224 |
"7" text, |
|
| 225 |
"8" text, |
|
| 226 |
"9" text |
|
| 227 |
); |
|
| 228 |
|
|
| 229 |
|
|
| 230 |
-- |
|
| 231 |
-- Name: _label; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 232 |
-- |
|
| 233 |
|
|
| 234 |
CREATE TABLE _label ( |
|
| 235 |
result text, |
|
| 236 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 237 |
label text NOT NULL, |
|
| 238 |
value text |
|
| 239 |
); |
|
| 240 |
|
|
| 241 |
|
|
| 242 |
-- |
|
| 243 |
-- Name: _merge; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 244 |
-- |
|
| 245 |
|
|
| 246 |
CREATE TABLE _merge ( |
|
| 247 |
result text, |
|
| 248 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 249 |
"0" text, |
|
| 250 |
"1" text, |
|
| 251 |
"2" text, |
|
| 252 |
"3" text, |
|
| 253 |
"4" text, |
|
| 254 |
"5" text, |
|
| 255 |
"6" text, |
|
| 256 |
"7" text, |
|
| 257 |
"8" text, |
|
| 258 |
"9" text |
|
| 259 |
); |
|
| 260 |
|
|
| 261 |
|
|
| 262 |
-- |
|
| 263 |
-- Name: _nullIf; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 264 |
-- |
|
| 265 |
|
|
| 266 |
CREATE TABLE "_nullIf" ( |
|
| 267 |
result text, |
|
| 268 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 269 |
"null" text NOT NULL, |
|
| 270 |
type datatype DEFAULT 'str'::datatype NOT NULL, |
|
| 271 |
value text |
|
| 272 |
); |
|
| 273 |
|
|
| 274 |
|
|
| 275 |
-- |
|
| 276 |
-- Name: _alt_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 277 |
-- |
|
| 278 |
|
|
| 279 |
CREATE UNIQUE INDEX _alt_unique ON _alt USING btree ((COALESCE("0", '\\N'::text)), (COALESCE("1", '\\N'::text)), (COALESCE("2", '\\N'::text)), (COALESCE("3", '\\N'::text)), (COALESCE("4", '\\N'::text)), (COALESCE("5", '\\N'::text)), (COALESCE("6", '\\N'::text)), (COALESCE("7", '\\N'::text)), (COALESCE("8", '\\N'::text)), (COALESCE("9", '\\N'::text)));
|
|
| 280 |
|
|
| 281 |
|
|
| 282 |
-- |
|
| 283 |
-- Name: _label_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 284 |
-- |
|
| 285 |
|
|
| 286 |
CREATE UNIQUE INDEX _label_unique ON _label USING btree ((COALESCE(label, '\\N'::text)), (COALESCE(value, '\\N'::text))); |
|
| 287 |
|
|
| 288 |
|
|
| 289 |
-- |
|
| 290 |
-- Name: _merge_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 291 |
-- |
|
| 292 |
|
|
| 293 |
CREATE UNIQUE INDEX _merge_unique ON _merge USING btree ((COALESCE("0", '\\N'::text)), (COALESCE("1", '\\N'::text)), (COALESCE("2", '\\N'::text)), (COALESCE("3", '\\N'::text)), (COALESCE("4", '\\N'::text)), (COALESCE("5", '\\N'::text)), (COALESCE("6", '\\N'::text)), (COALESCE("7", '\\N'::text)), (COALESCE("8", '\\N'::text)), (COALESCE("9", '\\N'::text)));
|
|
| 294 |
|
|
| 295 |
|
|
| 296 |
-- |
|
| 297 |
-- Name: _nullIf_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 298 |
-- |
|
| 299 |
|
|
| 300 |
CREATE UNIQUE INDEX "_nullIf_unique" ON "_nullIf" USING btree ("null", type, (COALESCE(value, '\\N'::text)));
|
|
| 301 |
|
|
| 302 |
|
|
| 303 |
-- |
|
| 304 |
-- Name: _alt; Type: TRIGGER; Schema: functions; Owner: - |
|
| 305 |
-- |
|
| 306 |
|
|
| 307 |
CREATE TRIGGER _alt BEFORE INSERT OR UPDATE ON _alt FOR EACH ROW EXECUTE PROCEDURE __alt(); |
|
| 308 |
|
|
| 309 |
|
|
| 310 |
-- |
|
| 311 |
-- Name: _label; Type: TRIGGER; Schema: functions; Owner: - |
|
| 312 |
-- |
|
| 313 |
|
|
| 314 |
CREATE TRIGGER _label BEFORE INSERT OR UPDATE ON _label FOR EACH ROW EXECUTE PROCEDURE __label(); |
|
| 315 |
|
|
| 316 |
|
|
| 317 |
-- |
|
| 318 |
-- Name: _merge; Type: TRIGGER; Schema: functions; Owner: - |
|
| 319 |
-- |
|
| 320 |
|
|
| 321 |
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE __merge(); |
|
| 322 |
|
|
| 323 |
|
|
| 324 |
-- |
|
| 325 |
-- Name: _nullIf; Type: TRIGGER; Schema: functions; Owner: - |
|
| 326 |
-- |
|
| 327 |
|
|
| 328 |
CREATE TRIGGER "_nullIf" BEFORE INSERT OR UPDATE ON "_nullIf" FOR EACH ROW EXECUTE PROCEDURE "__nullIf"(); |
|
| 329 |
|
|
| 330 |
|
|
| 331 |
-- |
|
| 332 | 149 |
-- PostgreSQL database dump complete |
| 333 | 150 |
-- |
| 334 | 151 |
|
| 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 |
ALTER FUNCTION py_functions."__dateRangeEnd"() OWNER TO bien; |
|
| 38 |
|
|
| 39 |
-- |
|
| 40 |
-- Name: __dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 41 |
-- |
|
| 42 |
|
|
| 43 |
CREATE FUNCTION "__dateRangeStart"() RETURNS trigger |
|
| 44 |
LANGUAGE plpgsql IMMUTABLE |
|
| 45 |
AS $$ |
|
| 46 |
BEGIN |
|
| 47 |
new.result := py_functions."_dateRangeStart"(new.value); |
|
| 48 |
RETURN new; |
|
| 49 |
END; |
|
| 50 |
$$; |
|
| 51 |
|
|
| 52 |
|
|
| 53 |
ALTER FUNCTION py_functions."__dateRangeStart"() OWNER TO bien; |
|
| 54 |
|
|
| 55 |
-- |
|
| 56 |
-- Name: __namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien |
|
| 57 |
-- |
|
| 58 |
|
|
| 59 |
CREATE FUNCTION "__namePart"() RETURNS trigger |
|
| 60 |
LANGUAGE plpgsql IMMUTABLE |
|
| 61 |
AS $$ |
|
| 62 |
BEGIN |
|
| 63 |
new.result := py_functions."_namePart"(new.first, new.middle, new.last); |
|
| 64 |
RETURN new; |
|
| 65 |
END; |
|
| 66 |
$$; |
|
| 67 |
|
|
| 68 |
|
|
| 69 |
ALTER FUNCTION py_functions."__namePart"() OWNER TO bien; |
|
| 70 |
|
|
| 71 |
-- |
|
| 72 | 24 |
-- Name: _date(text, text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien |
| 73 | 25 |
-- |
| 74 | 26 |
|
| ... | ... | |
| 251 | 203 |
|
| 252 | 204 |
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien; |
| 253 | 205 |
|
| 254 |
SET default_tablespace = ''; |
|
| 255 |
|
|
| 256 |
SET default_with_oids = false; |
|
| 257 |
|
|
| 258 | 206 |
-- |
| 259 |
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: |
|
| 260 |
-- |
|
| 261 |
|
|
| 262 |
CREATE TABLE "_dateRangeEnd" ( |
|
| 263 |
result text, |
|
| 264 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 265 |
value text |
|
| 266 |
); |
|
| 267 |
|
|
| 268 |
|
|
| 269 |
ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien; |
|
| 270 |
|
|
| 271 |
-- |
|
| 272 |
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: |
|
| 273 |
-- |
|
| 274 |
|
|
| 275 |
CREATE TABLE "_dateRangeStart" ( |
|
| 276 |
result text, |
|
| 277 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 278 |
value text |
|
| 279 |
); |
|
| 280 |
|
|
| 281 |
|
|
| 282 |
ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien; |
|
| 283 |
|
|
| 284 |
-- |
|
| 285 |
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: |
|
| 286 |
-- |
|
| 287 |
|
|
| 288 |
CREATE TABLE "_namePart" ( |
|
| 289 |
result text, |
|
| 290 |
not_null_col boolean DEFAULT true NOT NULL, |
|
| 291 |
first text, |
|
| 292 |
middle text, |
|
| 293 |
last text |
|
| 294 |
); |
|
| 295 |
|
|
| 296 |
|
|
| 297 |
ALTER TABLE py_functions."_namePart" OWNER TO bien; |
|
| 298 |
|
|
| 299 |
-- |
|
| 300 |
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: |
|
| 301 |
-- |
|
| 302 |
|
|
| 303 |
CREATE UNIQUE INDEX "_dateRangeEnd_unique" ON "_dateRangeEnd" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 304 |
|
|
| 305 |
|
|
| 306 |
-- |
|
| 307 |
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: |
|
| 308 |
-- |
|
| 309 |
|
|
| 310 |
CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 311 |
|
|
| 312 |
|
|
| 313 |
-- |
|
| 314 |
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: |
|
| 315 |
-- |
|
| 316 |
|
|
| 317 |
CREATE UNIQUE INDEX "_namePart_unique" ON "_namePart" USING btree ((COALESCE(first, '\\N'::text)), (COALESCE(middle, '\\N'::text)), (COALESCE(last, '\\N'::text))); |
|
| 318 |
|
|
| 319 |
|
|
| 320 |
-- |
|
| 321 |
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien |
|
| 322 |
-- |
|
| 323 |
|
|
| 324 |
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeEnd"(); |
|
| 325 |
|
|
| 326 |
|
|
| 327 |
-- |
|
| 328 |
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien |
|
| 329 |
-- |
|
| 330 |
|
|
| 331 |
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeStart"(); |
|
| 332 |
|
|
| 333 |
|
|
| 334 |
-- |
|
| 335 |
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien |
|
| 336 |
-- |
|
| 337 |
|
|
| 338 |
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "__namePart"(); |
|
| 339 |
|
|
| 340 |
|
|
| 341 |
-- |
|
| 342 | 207 |
-- PostgreSQL database dump complete |
| 343 | 208 |
-- |
| 344 | 209 |
|
Also available in: Unified diff
schemas/functions.sql, py_functions.sql: Removed no longer needed relational functions, since sql_io.put_table() supports regular SQL functions