Revision 13868
Added by Aaron Marcuse-Kubitza over 10 years ago
| 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__*