Revision 13868
Added by Aaron Marcuse-Kubitza over 10 years ago
| 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 |
-- |
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: taxon_match: renamed related items to start with taxon_match__*