Revision 13868
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 93 | 93 |
|
| 94 | 94 |
|
| 95 | 95 |
-- |
| 96 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 96 |
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 97 | 97 |
-- |
| 98 | 98 |
|
| 99 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger
|
|
| 99 |
CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger
|
|
| 100 | 100 |
LANGUAGE plpgsql |
| 101 | 101 |
AS $$ |
| 102 | 102 |
BEGIN |
| 103 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 104 |
RETURN NULL; |
|
| 105 |
END; |
|
| 106 |
$$; |
|
| 107 |
|
|
| 108 |
|
|
| 109 |
-- |
|
| 110 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 111 |
-- |
|
| 112 |
|
|
| 113 |
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean |
|
| 114 |
LANGUAGE sql IMMUTABLE |
|
| 115 |
AS $_$ |
|
| 116 |
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
|
|
| 117 |
$_$; |
|
| 118 |
|
|
| 119 |
|
|
| 120 |
-- |
|
| 121 |
-- Name: tnrs__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 122 |
-- |
|
| 123 |
|
|
| 124 |
CREATE FUNCTION tnrs__batch_begin() RETURNS trigger |
|
| 125 |
LANGUAGE plpgsql |
|
| 126 |
AS $$ |
|
| 127 |
BEGIN |
|
| 128 | 103 |
PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);
|
| 129 | 104 |
RETURN NULL; |
| 130 | 105 |
END; |
| ... | ... | |
| 132 | 107 |
|
| 133 | 108 |
|
| 134 | 109 |
-- |
| 135 |
-- Name: tnrs__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 110 |
-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 136 | 111 |
-- |
| 137 | 112 |
|
| 138 |
CREATE FUNCTION tnrs__match_num__fill() RETURNS trigger
|
|
| 113 |
CREATE FUNCTION taxon_match__fill() RETURNS trigger
|
|
| 139 | 114 |
LANGUAGE plpgsql |
| 140 | 115 |
AS $$ |
| 141 |
BEGIN |
|
| 142 |
IF new.match_num IS NULL THEN |
|
| 143 |
new.match_num = "TNRS".tnrs__match_num__next(); |
|
| 144 |
END IF; |
|
| 145 |
RETURN new; |
|
| 146 |
END; |
|
| 147 |
$$; |
|
| 148 |
|
|
| 149 |
|
|
| 150 |
-- |
|
| 151 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 152 |
-- |
|
| 153 |
|
|
| 154 |
CREATE FUNCTION tnrs__match_num__next() RETURNS bigint |
|
| 155 |
LANGUAGE sql |
|
| 156 |
AS $$ |
|
| 157 |
SELECT nextval('pg_temp.tnrs__match_num__seq');
|
|
| 158 |
$$; |
|
| 159 |
|
|
| 160 |
|
|
| 161 |
-- |
|
| 162 |
-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 163 |
-- |
|
| 164 |
|
|
| 165 |
CREATE FUNCTION tnrs_populate_fields() RETURNS trigger |
|
| 166 |
LANGUAGE plpgsql |
|
| 167 |
AS $$ |
|
| 168 | 116 |
DECLARE |
| 169 | 117 |
"Specific_epithet_is_plant" boolean := |
| 170 | 118 |
(CASE |
| ... | ... | |
| 221 | 169 |
|
| 222 | 170 |
|
| 223 | 171 |
-- |
| 224 |
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 172 |
-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 225 | 173 |
-- |
| 226 | 174 |
|
| 227 |
COMMENT ON FUNCTION tnrs_populate_fields() IS '
|
|
| 175 |
COMMENT ON FUNCTION taxon_match__fill() IS '
|
|
| 228 | 176 |
IMPORTANT: when changing this function, you must regenerate the derived cols: |
| 229 | 177 |
UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted" |
| 230 | 178 |
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
|
| ... | ... | |
| 234 | 182 |
|
| 235 | 183 |
|
| 236 | 184 |
-- |
| 185 |
-- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 186 |
-- |
|
| 187 |
|
|
| 188 |
CREATE FUNCTION taxon_match__match_num__fill() RETURNS trigger |
|
| 189 |
LANGUAGE plpgsql |
|
| 190 |
AS $$ |
|
| 191 |
BEGIN |
|
| 192 |
IF new.match_num IS NULL THEN |
|
| 193 |
new.match_num = "TNRS".tnrs__match_num__next(); |
|
| 194 |
END IF; |
|
| 195 |
RETURN new; |
|
| 196 |
END; |
|
| 197 |
$$; |
|
| 198 |
|
|
| 199 |
|
|
| 200 |
-- |
|
| 201 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 202 |
-- |
|
| 203 |
|
|
| 204 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger |
|
| 205 |
LANGUAGE plpgsql |
|
| 206 |
AS $$ |
|
| 207 |
BEGIN |
|
| 208 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 209 |
RETURN NULL; |
|
| 210 |
END; |
|
| 211 |
$$; |
|
| 212 |
|
|
| 213 |
|
|
| 214 |
-- |
|
| 215 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 216 |
-- |
|
| 217 |
|
|
| 218 |
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean |
|
| 219 |
LANGUAGE sql IMMUTABLE |
|
| 220 |
AS $_$ |
|
| 221 |
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
|
|
| 222 |
$_$; |
|
| 223 |
|
|
| 224 |
|
|
| 225 |
-- |
|
| 226 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 227 |
-- |
|
| 228 |
|
|
| 229 |
CREATE FUNCTION tnrs__match_num__next() RETURNS bigint |
|
| 230 |
LANGUAGE sql |
|
| 231 |
AS $$ |
|
| 232 |
SELECT nextval('pg_temp.tnrs__match_num__seq');
|
|
| 233 |
$$; |
|
| 234 |
|
|
| 235 |
|
|
| 236 |
-- |
|
| 237 | 237 |
-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: - |
| 238 | 238 |
-- |
| 239 | 239 |
|
| ... | ... | |
| 312 | 312 |
$ make schemas/remake |
| 313 | 313 |
|
| 314 | 314 |
to populate a new column: |
| 315 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER tnrs_populate_fields; --speeds up update
|
|
| 315 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER taxon_match__fill; --speeds up update
|
|
| 316 | 316 |
UPDATE "TNRS".taxon_match SET "col" = value; |
| 317 | 317 |
-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
|
| 318 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER tnrs_populate_fields;
|
|
| 318 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER taxon_match__fill;
|
|
| 319 | 319 |
VACUUM ANALYZE "TNRS".taxon_match --remove previous rows; runtime: 1.5 min ("92633 ms")
|
| 320 | 320 |
|
| 321 | 321 |
to add a constraint: runtime: 3 min ("173620 ms")
|
| ... | ... | |
| 770 | 770 |
|
| 771 | 771 |
|
| 772 | 772 |
-- |
| 773 |
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 773 |
-- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 774 | 774 |
-- |
| 775 | 775 |
|
| 776 | 776 |
ALTER TABLE ONLY taxon_match |
| 777 |
ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);
|
|
| 777 |
ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num);
|
|
| 778 | 778 |
|
| 779 | 779 |
|
| 780 | 780 |
-- |
| ... | ... | |
| 785 | 785 |
|
| 786 | 786 |
|
| 787 | 787 |
-- |
| 788 |
-- Name: tnrs_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 788 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 789 | 789 |
-- |
| 790 | 790 |
|
| 791 |
CREATE INDEX "tnrs_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 791 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 792 | 792 |
|
| 793 | 793 |
|
| 794 | 794 |
-- |
| 795 |
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 795 |
-- Name: taxon_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 796 | 796 |
-- |
| 797 | 797 |
|
| 798 |
CREATE INDEX tnrs__valid_match ON taxon_match USING btree ("Name_submitted") WHERE is_valid_match;
|
|
| 798 |
CREATE INDEX taxon_match__valid_match ON taxon_match USING btree ("Name_submitted") WHERE is_valid_match;
|
|
| 799 | 799 |
|
| 800 | 800 |
|
| 801 | 801 |
-- |
| ... | ... | |
| 806 | 806 |
|
| 807 | 807 |
|
| 808 | 808 |
-- |
| 809 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 809 |
-- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 810 | 810 |
-- |
| 811 | 811 |
|
| 812 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
|
|
| 812 |
CREATE TRIGGER taxon_match__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE taxon_match__batch_begin();
|
|
| 813 | 813 |
|
| 814 | 814 |
|
| 815 | 815 |
-- |
| 816 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 816 |
-- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 817 | 817 |
-- |
| 818 | 818 |
|
| 819 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();
|
|
| 819 |
CREATE TRIGGER taxon_match__fill BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill();
|
|
| 820 | 820 |
|
| 821 | 821 |
|
| 822 | 822 |
-- |
| 823 |
-- Name: tnrs__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 823 |
-- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 824 | 824 |
-- |
| 825 | 825 |
|
| 826 |
CREATE TRIGGER tnrs__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE tnrs__match_num__fill();
|
|
| 826 |
CREATE TRIGGER taxon_match__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__match_num__fill();
|
|
| 827 | 827 |
|
| 828 | 828 |
|
| 829 | 829 |
-- |
| 830 |
-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 830 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 831 | 831 |
-- |
| 832 | 832 |
|
| 833 |
CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();
|
|
| 833 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
|
|
| 834 | 834 |
|
| 835 | 835 |
|
| 836 | 836 |
-- |
| ... | ... | |
| 850 | 850 |
|
| 851 | 851 |
|
| 852 | 852 |
-- |
| 853 |
-- Name: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 853 |
-- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 854 | 854 |
-- |
| 855 | 855 |
|
| 856 | 856 |
ALTER TABLE ONLY taxon_match |
| 857 |
ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 857 |
ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 858 | 858 |
|
| 859 | 859 |
|
| 860 | 860 |
-- |
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15277 | 15277 |
|
| 15278 | 15278 |
|
| 15279 | 15279 |
-- |
| 15280 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15280 |
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15281 | 15281 |
-- |
| 15282 | 15282 |
|
| 15283 | 15283 |
|
| 15284 | 15284 |
|
| 15285 | 15285 |
|
| 15286 | 15286 |
-- |
| 15287 |
-- Name: taxon_name_is_safe(varchar(255)); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15287 |
-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15288 | 15288 |
-- |
| 15289 | 15289 |
|
| 15290 | 15290 |
|
| 15291 | 15291 |
|
| 15292 | 15292 |
|
| 15293 | 15293 |
-- |
| 15294 |
-- Name: tnrs__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15294 |
-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 15295 | 15295 |
-- |
| 15296 | 15296 |
|
| 15297 | 15297 |
|
| 15298 | 15298 |
|
| 15299 | 15299 |
|
| 15300 | 15300 |
-- |
| 15301 |
-- Name: tnrs__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15301 |
-- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15302 | 15302 |
-- |
| 15303 | 15303 |
|
| 15304 | 15304 |
|
| 15305 | 15305 |
|
| 15306 | 15306 |
|
| 15307 | 15307 |
-- |
| 15308 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15308 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15309 | 15309 |
-- |
| 15310 | 15310 |
|
| 15311 | 15311 |
|
| 15312 | 15312 |
|
| 15313 | 15313 |
|
| 15314 | 15314 |
-- |
| 15315 |
-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15315 |
-- Name: taxon_name_is_safe(varchar(255)); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15316 | 15316 |
-- |
| 15317 | 15317 |
|
| 15318 | 15318 |
|
| 15319 | 15319 |
|
| 15320 | 15320 |
|
| 15321 | 15321 |
-- |
| 15322 |
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 15322 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 15323 | 15323 |
-- |
| 15324 | 15324 |
|
| 15325 | 15325 |
|
| ... | ... | |
| 15674 | 15674 |
|
| 15675 | 15675 |
|
| 15676 | 15676 |
-- |
| 15677 |
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15677 |
-- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15678 | 15678 |
-- |
| 15679 | 15679 |
|
| 15680 | 15680 |
ALTER TABLE taxon_match |
| 15681 |
ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);
|
|
| 15681 |
ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num);
|
|
| 15682 | 15682 |
|
| 15683 | 15683 |
|
| 15684 | 15684 |
USE geoscrub; |
| ... | ... | |
| 15709 | 15709 |
|
| 15710 | 15710 |
|
| 15711 | 15711 |
-- |
| 15712 |
-- Name: tnrs_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15712 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15713 | 15713 |
-- |
| 15714 | 15714 |
|
| 15715 |
CREATE INDEX `tnrs_Name_submitted_idx` ON taxon_match (`Name_submitted`);
|
|
| 15715 |
CREATE INDEX `taxon_match_Name_submitted_idx` ON taxon_match (`Name_submitted`);
|
|
| 15716 | 15716 |
|
| 15717 | 15717 |
|
| 15718 | 15718 |
-- |
| 15719 |
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15719 |
-- Name: taxon_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 15720 | 15720 |
-- |
| 15721 | 15721 |
|
| 15722 | 15722 |
|
| ... | ... | |
| 15741 | 15741 |
|
| 15742 | 15742 |
|
| 15743 | 15743 |
-- |
| 15744 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15744 |
-- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15745 | 15745 |
-- |
| 15746 | 15746 |
|
| 15747 | 15747 |
|
| 15748 | 15748 |
|
| 15749 | 15749 |
|
| 15750 | 15750 |
-- |
| 15751 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15751 |
-- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15752 | 15752 |
-- |
| 15753 | 15753 |
|
| 15754 | 15754 |
|
| 15755 | 15755 |
|
| 15756 | 15756 |
|
| 15757 | 15757 |
-- |
| 15758 |
-- Name: tnrs__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15758 |
-- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15759 | 15759 |
-- |
| 15760 | 15760 |
|
| 15761 | 15761 |
|
| 15762 | 15762 |
|
| 15763 | 15763 |
|
| 15764 | 15764 |
-- |
| 15765 |
-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15765 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 15766 | 15766 |
-- |
| 15767 | 15767 |
|
| 15768 | 15768 |
|
| ... | ... | |
| 15785 | 15785 |
|
| 15786 | 15786 |
|
| 15787 | 15787 |
-- |
| 15788 |
-- Name: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 15788 |
-- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 15789 | 15789 |
-- |
| 15790 | 15790 |
|
| 15791 | 15791 |
ALTER TABLE taxon_match |
| 15792 |
ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 15792 |
ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 15793 | 15793 |
|
| 15794 | 15794 |
|
| 15795 | 15795 |
-- |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19042 | 19042 |
|
| 19043 | 19043 |
|
| 19044 | 19044 |
-- |
| 19045 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 19045 |
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 19046 | 19046 |
-- |
| 19047 | 19047 |
|
| 19048 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger
|
|
| 19048 |
CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger
|
|
| 19049 | 19049 |
LANGUAGE plpgsql |
| 19050 | 19050 |
AS $$ |
| 19051 | 19051 |
BEGIN |
| 19052 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 19053 |
RETURN NULL; |
|
| 19054 |
END; |
|
| 19055 |
$$; |
|
| 19056 |
|
|
| 19057 |
|
|
| 19058 |
-- |
|
| 19059 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19060 |
-- |
|
| 19061 |
|
|
| 19062 |
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean |
|
| 19063 |
LANGUAGE sql IMMUTABLE |
|
| 19064 |
AS $_$ |
|
| 19065 |
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
|
|
| 19066 |
$_$; |
|
| 19067 |
|
|
| 19068 |
|
|
| 19069 |
-- |
|
| 19070 |
-- Name: tnrs__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19071 |
-- |
|
| 19072 |
|
|
| 19073 |
CREATE FUNCTION tnrs__batch_begin() RETURNS trigger |
|
| 19074 |
LANGUAGE plpgsql |
|
| 19075 |
AS $$ |
|
| 19076 |
BEGIN |
|
| 19077 | 19052 |
PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);
|
| 19078 | 19053 |
RETURN NULL; |
| 19079 | 19054 |
END; |
| ... | ... | |
| 19081 | 19056 |
|
| 19082 | 19057 |
|
| 19083 | 19058 |
-- |
| 19084 |
-- Name: tnrs__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 19059 |
-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
|
|
| 19085 | 19060 |
-- |
| 19086 | 19061 |
|
| 19087 |
CREATE FUNCTION tnrs__match_num__fill() RETURNS trigger
|
|
| 19062 |
CREATE FUNCTION taxon_match__fill() RETURNS trigger
|
|
| 19088 | 19063 |
LANGUAGE plpgsql |
| 19089 | 19064 |
AS $$ |
| 19090 |
BEGIN |
|
| 19091 |
IF new.match_num IS NULL THEN |
|
| 19092 |
new.match_num = "TNRS".tnrs__match_num__next(); |
|
| 19093 |
END IF; |
|
| 19094 |
RETURN new; |
|
| 19095 |
END; |
|
| 19096 |
$$; |
|
| 19097 |
|
|
| 19098 |
|
|
| 19099 |
-- |
|
| 19100 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19101 |
-- |
|
| 19102 |
|
|
| 19103 |
CREATE FUNCTION tnrs__match_num__next() RETURNS bigint |
|
| 19104 |
LANGUAGE sql |
|
| 19105 |
AS $$ |
|
| 19106 |
SELECT nextval('pg_temp.tnrs__match_num__seq');
|
|
| 19107 |
$$; |
|
| 19108 |
|
|
| 19109 |
|
|
| 19110 |
-- |
|
| 19111 |
-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19112 |
-- |
|
| 19113 |
|
|
| 19114 |
CREATE FUNCTION tnrs_populate_fields() RETURNS trigger |
|
| 19115 |
LANGUAGE plpgsql |
|
| 19116 |
AS $$ |
|
| 19117 | 19065 |
DECLARE |
| 19118 | 19066 |
"Specific_epithet_is_plant" boolean := |
| 19119 | 19067 |
(CASE |
| ... | ... | |
| 19170 | 19118 |
|
| 19171 | 19119 |
|
| 19172 | 19120 |
-- |
| 19173 |
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 19121 |
-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: -
|
|
| 19174 | 19122 |
-- |
| 19175 | 19123 |
|
| 19176 |
COMMENT ON FUNCTION tnrs_populate_fields() IS '
|
|
| 19124 |
COMMENT ON FUNCTION taxon_match__fill() IS '
|
|
| 19177 | 19125 |
IMPORTANT: when changing this function, you must regenerate the derived cols: |
| 19178 | 19126 |
UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted" |
| 19179 | 19127 |
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
|
| ... | ... | |
| 19183 | 19131 |
|
| 19184 | 19132 |
|
| 19185 | 19133 |
-- |
| 19134 |
-- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19135 |
-- |
|
| 19136 |
|
|
| 19137 |
CREATE FUNCTION taxon_match__match_num__fill() RETURNS trigger |
|
| 19138 |
LANGUAGE plpgsql |
|
| 19139 |
AS $$ |
|
| 19140 |
BEGIN |
|
| 19141 |
IF new.match_num IS NULL THEN |
|
| 19142 |
new.match_num = "TNRS".tnrs__match_num__next(); |
|
| 19143 |
END IF; |
|
| 19144 |
RETURN new; |
|
| 19145 |
END; |
|
| 19146 |
$$; |
|
| 19147 |
|
|
| 19148 |
|
|
| 19149 |
-- |
|
| 19150 |
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19151 |
-- |
|
| 19152 |
|
|
| 19153 |
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger |
|
| 19154 |
LANGUAGE plpgsql |
|
| 19155 |
AS $$ |
|
| 19156 |
BEGIN |
|
| 19157 |
INSERT INTO "TNRS".taxon_match_input SELECT new.*; |
|
| 19158 |
RETURN NULL; |
|
| 19159 |
END; |
|
| 19160 |
$$; |
|
| 19161 |
|
|
| 19162 |
|
|
| 19163 |
-- |
|
| 19164 |
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19165 |
-- |
|
| 19166 |
|
|
| 19167 |
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean |
|
| 19168 |
LANGUAGE sql IMMUTABLE |
|
| 19169 |
AS $_$ |
|
| 19170 |
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
|
|
| 19171 |
$_$; |
|
| 19172 |
|
|
| 19173 |
|
|
| 19174 |
-- |
|
| 19175 |
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: - |
|
| 19176 |
-- |
|
| 19177 |
|
|
| 19178 |
CREATE FUNCTION tnrs__match_num__next() RETURNS bigint |
|
| 19179 |
LANGUAGE sql |
|
| 19180 |
AS $$ |
|
| 19181 |
SELECT nextval('pg_temp.tnrs__match_num__seq');
|
|
| 19182 |
$$; |
|
| 19183 |
|
|
| 19184 |
|
|
| 19185 |
-- |
|
| 19186 | 19186 |
-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: - |
| 19187 | 19187 |
-- |
| 19188 | 19188 |
|
| ... | ... | |
| 19303 | 19303 |
$ make schemas/remake |
| 19304 | 19304 |
|
| 19305 | 19305 |
to populate a new column: |
| 19306 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER tnrs_populate_fields; --speeds up update
|
|
| 19306 |
ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER taxon_match__fill; --speeds up update
|
|
| 19307 | 19307 |
UPDATE "TNRS".taxon_match SET "col" = value; |
| 19308 | 19308 |
-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
|
| 19309 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER tnrs_populate_fields;
|
|
| 19309 |
ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER taxon_match__fill;
|
|
| 19310 | 19310 |
VACUUM ANALYZE "TNRS".taxon_match --remove previous rows; runtime: 1.5 min ("92633 ms")
|
| 19311 | 19311 |
|
| 19312 | 19312 |
to add a constraint: runtime: 3 min ("173620 ms")
|
| ... | ... | |
| 19807 | 19807 |
|
| 19808 | 19808 |
|
| 19809 | 19809 |
-- |
| 19810 |
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19810 |
-- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19811 | 19811 |
-- |
| 19812 | 19812 |
|
| 19813 | 19813 |
ALTER TABLE ONLY taxon_match |
| 19814 |
ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);
|
|
| 19814 |
ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num);
|
|
| 19815 | 19815 |
|
| 19816 | 19816 |
|
| 19817 | 19817 |
SET search_path = geoscrub, pg_catalog; |
| ... | ... | |
| 19842 | 19842 |
|
| 19843 | 19843 |
|
| 19844 | 19844 |
-- |
| 19845 |
-- Name: tnrs_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19845 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19846 | 19846 |
-- |
| 19847 | 19847 |
|
| 19848 |
CREATE INDEX "tnrs_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 19848 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 19849 | 19849 |
|
| 19850 | 19850 |
|
| 19851 | 19851 |
-- |
| 19852 |
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19852 |
-- Name: taxon_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
|
|
| 19853 | 19853 |
-- |
| 19854 | 19854 |
|
| 19855 |
CREATE INDEX tnrs__valid_match ON taxon_match USING btree ("Name_submitted") WHERE is_valid_match;
|
|
| 19855 |
CREATE INDEX taxon_match__valid_match ON taxon_match USING btree ("Name_submitted") WHERE is_valid_match;
|
|
| 19856 | 19856 |
|
| 19857 | 19857 |
|
| 19858 | 19858 |
SET search_path = geoscrub, pg_catalog; |
| ... | ... | |
| 19874 | 19874 |
|
| 19875 | 19875 |
|
| 19876 | 19876 |
-- |
| 19877 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19877 |
-- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19878 | 19878 |
-- |
| 19879 | 19879 |
|
| 19880 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
|
|
| 19880 |
CREATE TRIGGER taxon_match__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE taxon_match__batch_begin();
|
|
| 19881 | 19881 |
|
| 19882 | 19882 |
|
| 19883 | 19883 |
-- |
| 19884 |
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19884 |
-- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19885 | 19885 |
-- |
| 19886 | 19886 |
|
| 19887 |
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();
|
|
| 19887 |
CREATE TRIGGER taxon_match__fill BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill();
|
|
| 19888 | 19888 |
|
| 19889 | 19889 |
|
| 19890 | 19890 |
-- |
| 19891 |
-- Name: tnrs__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19891 |
-- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19892 | 19892 |
-- |
| 19893 | 19893 |
|
| 19894 |
CREATE TRIGGER tnrs__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE tnrs__match_num__fill();
|
|
| 19894 |
CREATE TRIGGER taxon_match__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__match_num__fill();
|
|
| 19895 | 19895 |
|
| 19896 | 19896 |
|
| 19897 | 19897 |
-- |
| 19898 |
-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19898 |
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
|
|
| 19899 | 19899 |
-- |
| 19900 | 19900 |
|
| 19901 |
CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();
|
|
| 19901 |
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
|
|
| 19902 | 19902 |
|
| 19903 | 19903 |
|
| 19904 | 19904 |
-- |
| ... | ... | |
| 19918 | 19918 |
|
| 19919 | 19919 |
|
| 19920 | 19920 |
-- |
| 19921 |
-- Name: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 19921 |
-- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
|
|
| 19922 | 19922 |
-- |
| 19923 | 19923 |
|
| 19924 | 19924 |
ALTER TABLE ONLY taxon_match |
| 19925 |
ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 19925 |
ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 19926 | 19926 |
|
| 19927 | 19927 |
|
| 19928 | 19928 |
-- |
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: taxon_match: renamed related items to start with taxon_match__*