Revision 2530
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 82 | 82 |
|
| 83 | 83 |
|
| 84 | 84 |
-- |
| 85 |
-- Name: _toGrowthform(); Type: FUNCTION; Schema: public; Owner: - |
|
| 86 |
-- |
|
| 87 |
|
|
| 88 |
|
|
| 89 |
|
|
| 90 |
|
|
| 91 |
-- |
|
| 92 |
-- Name: _toPlacerank(); Type: FUNCTION; Schema: public; Owner: - |
|
| 93 |
-- |
|
| 94 |
|
|
| 95 |
|
|
| 96 |
|
|
| 97 |
|
|
| 98 |
-- |
|
| 99 |
-- Name: _toTaxonrank(); Type: FUNCTION; Schema: public; Owner: - |
|
| 100 |
-- |
|
| 101 |
|
|
| 102 |
|
|
| 103 |
|
|
| 104 |
|
|
| 105 |
-- |
|
| 106 | 85 |
-- Name: aggregateoccurrence_plantobs_count_1(); Type: FUNCTION; Schema: public; Owner: - |
| 107 | 86 |
-- |
| 108 | 87 |
|
| ... | ... | |
| 156 | 135 |
|
| 157 | 136 |
|
| 158 | 137 |
-- |
| 159 |
-- Name: _toGrowthform; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 160 |
-- |
|
| 161 |
|
|
| 162 |
CREATE TABLE `_toGrowthform` ( |
|
| 163 |
result text, |
|
| 164 |
not_null int(1) DEFAULT true NOT NULL, |
|
| 165 |
value text |
|
| 166 |
); |
|
| 167 |
|
|
| 168 |
|
|
| 169 |
-- |
|
| 170 |
-- Name: _toPlacerank; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 171 |
-- |
|
| 172 |
|
|
| 173 |
CREATE TABLE `_toPlacerank` ( |
|
| 174 |
result text, |
|
| 175 |
not_null int(1) DEFAULT true NOT NULL, |
|
| 176 |
value text |
|
| 177 |
); |
|
| 178 |
|
|
| 179 |
|
|
| 180 |
-- |
|
| 181 |
-- Name: _toTaxonrank; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 182 |
-- |
|
| 183 |
|
|
| 184 |
CREATE TABLE `_toTaxonrank` ( |
|
| 185 |
result text, |
|
| 186 |
not_null int(1) DEFAULT true NOT NULL, |
|
| 187 |
value text |
|
| 188 |
); |
|
| 189 |
|
|
| 190 |
|
|
| 191 |
|
|
| 192 |
|
|
| 193 |
-- |
|
| 194 | 138 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 195 | 139 |
-- |
| 196 | 140 |
|
| ... | ... | |
| 3716 | 3660 |
|
| 3717 | 3661 |
|
| 3718 | 3662 |
-- |
| 3719 |
-- Name: _toGrowthform_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3720 |
-- |
|
| 3721 |
|
|
| 3722 |
|
|
| 3723 |
|
|
| 3724 |
|
|
| 3725 |
-- |
|
| 3726 |
-- Name: _toPlacerank_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3727 |
-- |
|
| 3728 |
|
|
| 3729 |
|
|
| 3730 |
|
|
| 3731 |
|
|
| 3732 |
-- |
|
| 3733 |
-- Name: _toTaxonrank_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3734 |
-- |
|
| 3735 |
|
|
| 3736 |
|
|
| 3737 |
|
|
| 3738 |
|
|
| 3739 |
-- |
|
| 3740 | 3663 |
-- Name: address_organization_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3741 | 3664 |
-- |
| 3742 | 3665 |
|
| ... | ... | |
| 4850 | 4773 |
|
| 4851 | 4774 |
|
| 4852 | 4775 |
-- |
| 4853 |
-- Name: _toGrowthform; Type: TRIGGER; Schema: public; Owner: - |
|
| 4854 |
-- |
|
| 4855 |
|
|
| 4856 |
|
|
| 4857 |
|
|
| 4858 |
|
|
| 4859 |
-- |
|
| 4860 |
-- Name: _toPlacerank; Type: TRIGGER; Schema: public; Owner: - |
|
| 4861 |
-- |
|
| 4862 |
|
|
| 4863 |
|
|
| 4864 |
|
|
| 4865 |
|
|
| 4866 |
-- |
|
| 4867 |
-- Name: _toTaxonrank; Type: TRIGGER; Schema: public; Owner: - |
|
| 4868 |
-- |
|
| 4869 |
|
|
| 4870 |
|
|
| 4871 |
|
|
| 4872 |
|
|
| 4873 |
-- |
|
| 4874 | 4776 |
-- Name: aggregateoccurrence_plantobs_count_1; Type: TRIGGER; Schema: public; Owner: - |
| 4875 | 4777 |
-- |
| 4876 | 4778 |
|
| schemas/functions.sql | ||
|---|---|---|
| 19 | 19 |
SET search_path = functions, pg_catalog; |
| 20 | 20 |
|
| 21 | 21 |
-- |
| 22 |
-- Name: _toBool(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 23 |
-- |
|
| 24 |
|
|
| 25 |
CREATE FUNCTION "_toBool"() RETURNS trigger |
|
| 26 |
LANGUAGE plpgsql IMMUTABLE |
|
| 27 |
AS $$ |
|
| 28 |
BEGIN |
|
| 29 |
BEGIN |
|
| 30 |
new.result := CAST(new.value AS boolean); |
|
| 31 |
EXCEPTION |
|
| 32 |
WHEN data_exception THEN |
|
| 33 |
new.result := NULL; |
|
| 34 |
RAISE WARNING '%', SQLERRM; |
|
| 35 |
END; |
|
| 36 |
RETURN new; |
|
| 37 |
END; |
|
| 38 |
$$; |
|
| 39 |
|
|
| 40 |
|
|
| 41 |
-- |
|
| 42 |
-- Name: _toDouble(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 43 |
-- |
|
| 44 |
|
|
| 45 |
CREATE FUNCTION "_toDouble"() RETURNS trigger |
|
| 46 |
LANGUAGE plpgsql IMMUTABLE |
|
| 47 |
AS $$ |
|
| 48 |
BEGIN |
|
| 49 |
BEGIN |
|
| 50 |
new.result := CAST(new.value AS double precision); |
|
| 51 |
EXCEPTION |
|
| 52 |
WHEN data_exception THEN |
|
| 53 |
new.result := NULL; |
|
| 54 |
RAISE WARNING '%', SQLERRM; |
|
| 55 |
END; |
|
| 56 |
RETURN new; |
|
| 57 |
END; |
|
| 58 |
$$; |
|
| 59 |
|
|
| 60 |
|
|
| 61 |
-- |
|
| 62 |
-- Name: _toTimestamp(); Type: FUNCTION; Schema: functions; Owner: - |
|
| 63 |
-- |
|
| 64 |
|
|
| 65 |
CREATE FUNCTION "_toTimestamp"() RETURNS trigger |
|
| 66 |
LANGUAGE plpgsql IMMUTABLE |
|
| 67 |
AS $$ |
|
| 68 |
BEGIN |
|
| 69 |
BEGIN |
|
| 70 |
new.result := CAST(new.value AS timestamp with time zone); |
|
| 71 |
EXCEPTION |
|
| 72 |
WHEN data_exception THEN |
|
| 73 |
new.result := NULL; |
|
| 74 |
RAISE WARNING '%', SQLERRM; |
|
| 75 |
END; |
|
| 76 |
RETURN new; |
|
| 77 |
END; |
|
| 78 |
$$; |
|
| 79 |
|
|
| 80 |
|
|
| 81 |
-- |
|
| 82 | 22 |
-- Name: boolean(text); Type: FUNCTION; Schema: functions; Owner: - |
| 83 | 23 |
-- |
| 84 | 24 |
|
| ... | ... | |
| 138 | 78 |
$$; |
| 139 | 79 |
|
| 140 | 80 |
|
| 141 |
SET default_tablespace = ''; |
|
| 142 |
|
|
| 143 |
SET default_with_oids = false; |
|
| 144 |
|
|
| 145 | 81 |
-- |
| 146 |
-- Name: _toBool; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 147 |
-- |
|
| 148 |
|
|
| 149 |
CREATE TABLE "_toBool" ( |
|
| 150 |
result boolean, |
|
| 151 |
not_null boolean DEFAULT true NOT NULL, |
|
| 152 |
value text |
|
| 153 |
); |
|
| 154 |
|
|
| 155 |
|
|
| 156 |
-- |
|
| 157 |
-- Name: _toDouble; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 158 |
-- |
|
| 159 |
|
|
| 160 |
CREATE TABLE "_toDouble" ( |
|
| 161 |
result double precision, |
|
| 162 |
not_null boolean DEFAULT true NOT NULL, |
|
| 163 |
value text |
|
| 164 |
); |
|
| 165 |
|
|
| 166 |
|
|
| 167 |
-- |
|
| 168 |
-- Name: _toTimestamp; Type: TABLE; Schema: functions; Owner: -; Tablespace: |
|
| 169 |
-- |
|
| 170 |
|
|
| 171 |
CREATE TABLE "_toTimestamp" ( |
|
| 172 |
result timestamp with time zone, |
|
| 173 |
not_null boolean DEFAULT true NOT NULL, |
|
| 174 |
value text |
|
| 175 |
); |
|
| 176 |
|
|
| 177 |
|
|
| 178 |
-- |
|
| 179 |
-- Name: _toBool_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 180 |
-- |
|
| 181 |
|
|
| 182 |
CREATE UNIQUE INDEX "_toBool_unique" ON "_toBool" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 183 |
|
|
| 184 |
|
|
| 185 |
-- |
|
| 186 |
-- Name: _toDouble_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 187 |
-- |
|
| 188 |
|
|
| 189 |
CREATE UNIQUE INDEX "_toDouble_unique" ON "_toDouble" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 190 |
|
|
| 191 |
|
|
| 192 |
-- |
|
| 193 |
-- Name: _toTimestamp_unique; Type: INDEX; Schema: functions; Owner: -; Tablespace: |
|
| 194 |
-- |
|
| 195 |
|
|
| 196 |
CREATE UNIQUE INDEX "_toTimestamp_unique" ON "_toTimestamp" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 197 |
|
|
| 198 |
|
|
| 199 |
-- |
|
| 200 |
-- Name: _toBool; Type: TRIGGER; Schema: functions; Owner: - |
|
| 201 |
-- |
|
| 202 |
|
|
| 203 |
CREATE TRIGGER "_toBool" BEFORE INSERT OR UPDATE ON "_toBool" FOR EACH ROW EXECUTE PROCEDURE "_toBool"(); |
|
| 204 |
|
|
| 205 |
|
|
| 206 |
-- |
|
| 207 |
-- Name: _toDouble; Type: TRIGGER; Schema: functions; Owner: - |
|
| 208 |
-- |
|
| 209 |
|
|
| 210 |
CREATE TRIGGER "_toDouble" BEFORE INSERT OR UPDATE ON "_toDouble" FOR EACH ROW EXECUTE PROCEDURE "_toDouble"(); |
|
| 211 |
|
|
| 212 |
|
|
| 213 |
-- |
|
| 214 |
-- Name: _toTimestamp; Type: TRIGGER; Schema: functions; Owner: - |
|
| 215 |
-- |
|
| 216 |
|
|
| 217 |
CREATE TRIGGER "_toTimestamp" BEFORE INSERT OR UPDATE ON "_toTimestamp" FOR EACH ROW EXECUTE PROCEDURE "_toTimestamp"(); |
|
| 218 |
|
|
| 219 |
|
|
| 220 |
-- |
|
| 221 | 82 |
-- PostgreSQL database dump complete |
| 222 | 83 |
-- |
| 223 | 84 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 178 | 178 |
|
| 179 | 179 |
|
| 180 | 180 |
-- |
| 181 |
-- Name: _toGrowthform(); Type: FUNCTION; Schema: public; Owner: - |
|
| 182 |
-- |
|
| 183 |
|
|
| 184 |
CREATE FUNCTION "_toGrowthform"() RETURNS trigger |
|
| 185 |
LANGUAGE plpgsql IMMUTABLE |
|
| 186 |
AS $$ |
|
| 187 |
BEGIN |
|
| 188 |
BEGIN |
|
| 189 |
new.result := CAST(new.value AS growthform); |
|
| 190 |
EXCEPTION |
|
| 191 |
WHEN data_exception THEN |
|
| 192 |
new.result := NULL; |
|
| 193 |
RAISE WARNING '%', SQLERRM; |
|
| 194 |
END; |
|
| 195 |
RETURN new; |
|
| 196 |
END; |
|
| 197 |
$$; |
|
| 198 |
|
|
| 199 |
|
|
| 200 |
-- |
|
| 201 |
-- Name: _toPlacerank(); Type: FUNCTION; Schema: public; Owner: - |
|
| 202 |
-- |
|
| 203 |
|
|
| 204 |
CREATE FUNCTION "_toPlacerank"() RETURNS trigger |
|
| 205 |
LANGUAGE plpgsql IMMUTABLE |
|
| 206 |
AS $$ |
|
| 207 |
BEGIN |
|
| 208 |
BEGIN |
|
| 209 |
new.result := CAST(new.value AS placerank); |
|
| 210 |
EXCEPTION |
|
| 211 |
WHEN data_exception THEN |
|
| 212 |
new.result := NULL; |
|
| 213 |
RAISE WARNING '%', SQLERRM; |
|
| 214 |
END; |
|
| 215 |
RETURN new; |
|
| 216 |
END; |
|
| 217 |
$$; |
|
| 218 |
|
|
| 219 |
|
|
| 220 |
-- |
|
| 221 |
-- Name: _toTaxonrank(); Type: FUNCTION; Schema: public; Owner: - |
|
| 222 |
-- |
|
| 223 |
|
|
| 224 |
CREATE FUNCTION "_toTaxonrank"() RETURNS trigger |
|
| 225 |
LANGUAGE plpgsql IMMUTABLE |
|
| 226 |
AS $$ |
|
| 227 |
BEGIN |
|
| 228 |
BEGIN |
|
| 229 |
new.result := CAST(new.value AS taxonrank); |
|
| 230 |
EXCEPTION |
|
| 231 |
WHEN data_exception THEN |
|
| 232 |
new.result := NULL; |
|
| 233 |
RAISE WARNING '%', SQLERRM; |
|
| 234 |
END; |
|
| 235 |
RETURN new; |
|
| 236 |
END; |
|
| 237 |
$$; |
|
| 238 |
|
|
| 239 |
|
|
| 240 |
-- |
|
| 241 | 181 |
-- Name: aggregateoccurrence_plantobs_count_1(); Type: FUNCTION; Schema: public; Owner: - |
| 242 | 182 |
-- |
| 243 | 183 |
|
| ... | ... | |
| 426 | 366 |
|
| 427 | 367 |
SET default_tablespace = ''; |
| 428 | 368 |
|
| 429 |
SET default_with_oids = false; |
|
| 430 |
|
|
| 431 |
-- |
|
| 432 |
-- Name: _toGrowthform; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 433 |
-- |
|
| 434 |
|
|
| 435 |
CREATE TABLE "_toGrowthform" ( |
|
| 436 |
result growthform, |
|
| 437 |
not_null boolean DEFAULT true NOT NULL, |
|
| 438 |
value text |
|
| 439 |
); |
|
| 440 |
|
|
| 441 |
|
|
| 442 |
-- |
|
| 443 |
-- Name: _toPlacerank; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 444 |
-- |
|
| 445 |
|
|
| 446 |
CREATE TABLE "_toPlacerank" ( |
|
| 447 |
result placerank, |
|
| 448 |
not_null boolean DEFAULT true NOT NULL, |
|
| 449 |
value text |
|
| 450 |
); |
|
| 451 |
|
|
| 452 |
|
|
| 453 |
-- |
|
| 454 |
-- Name: _toTaxonrank; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 455 |
-- |
|
| 456 |
|
|
| 457 |
CREATE TABLE "_toTaxonrank" ( |
|
| 458 |
result taxonrank, |
|
| 459 |
not_null boolean DEFAULT true NOT NULL, |
|
| 460 |
value text |
|
| 461 |
); |
|
| 462 |
|
|
| 463 |
|
|
| 464 | 369 |
SET default_with_oids = true; |
| 465 | 370 |
|
| 466 | 371 |
-- |
| ... | ... | |
| 4321 | 4226 |
|
| 4322 | 4227 |
|
| 4323 | 4228 |
-- |
| 4324 |
-- Name: _toGrowthform_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4325 |
-- |
|
| 4326 |
|
|
| 4327 |
CREATE UNIQUE INDEX "_toGrowthform_unique" ON "_toGrowthform" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 4328 |
|
|
| 4329 |
|
|
| 4330 |
-- |
|
| 4331 |
-- Name: _toPlacerank_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4332 |
-- |
|
| 4333 |
|
|
| 4334 |
CREATE UNIQUE INDEX "_toPlacerank_unique" ON "_toPlacerank" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 4335 |
|
|
| 4336 |
|
|
| 4337 |
-- |
|
| 4338 |
-- Name: _toTaxonrank_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4339 |
-- |
|
| 4340 |
|
|
| 4341 |
CREATE UNIQUE INDEX "_toTaxonrank_unique" ON "_toTaxonrank" USING btree ((COALESCE(value, '\\N'::text))); |
|
| 4342 |
|
|
| 4343 |
|
|
| 4344 |
-- |
|
| 4345 | 4229 |
-- Name: address_organization_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4346 | 4230 |
-- |
| 4347 | 4231 |
|
| ... | ... | |
| 5455 | 5339 |
|
| 5456 | 5340 |
|
| 5457 | 5341 |
-- |
| 5458 |
-- Name: _toGrowthform; Type: TRIGGER; Schema: public; Owner: - |
|
| 5459 |
-- |
|
| 5460 |
|
|
| 5461 |
CREATE TRIGGER "_toGrowthform" BEFORE INSERT OR UPDATE ON "_toGrowthform" FOR EACH ROW EXECUTE PROCEDURE "_toGrowthform"(); |
|
| 5462 |
|
|
| 5463 |
|
|
| 5464 |
-- |
|
| 5465 |
-- Name: _toPlacerank; Type: TRIGGER; Schema: public; Owner: - |
|
| 5466 |
-- |
|
| 5467 |
|
|
| 5468 |
CREATE TRIGGER "_toPlacerank" BEFORE INSERT OR UPDATE ON "_toPlacerank" FOR EACH ROW EXECUTE PROCEDURE "_toPlacerank"(); |
|
| 5469 |
|
|
| 5470 |
|
|
| 5471 |
-- |
|
| 5472 |
-- Name: _toTaxonrank; Type: TRIGGER; Schema: public; Owner: - |
|
| 5473 |
-- |
|
| 5474 |
|
|
| 5475 |
CREATE TRIGGER "_toTaxonrank" BEFORE INSERT OR UPDATE ON "_toTaxonrank" FOR EACH ROW EXECUTE PROCEDURE "_toTaxonrank"(); |
|
| 5476 |
|
|
| 5477 |
|
|
| 5478 |
-- |
|
| 5479 | 5342 |
-- Name: aggregateoccurrence_plantobs_count_1; Type: TRIGGER; Schema: public; Owner: - |
| 5480 | 5343 |
-- |
| 5481 | 5344 |
|
Also available in: Unified diff
schemas/vegbien.sql, functions.sql: Removed _to* relational functions because type casting for those types is now automatic