Revision 14311
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 19258 | 19258 |
AS $$ |
| 19259 | 19259 |
BEGIN |
| 19260 | 19260 |
-- clear derived cols so old values won't be used in calculations |
| 19261 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL; |
|
| 19261 | 19262 |
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 19262 | 19263 |
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; |
| 19263 | 19264 |
new.matched_has_accepted = NULL; |
| 19264 | 19265 |
new."__accepted_{genus,specific_epithet}" = NULL;
|
| 19265 | 19266 |
new."[accepted_]genus__@DwC__@vegpath.org" = NULL; |
| 19266 | 19267 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL; |
| 19268 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; |
|
| 19267 | 19269 |
new.__accepted_infraspecific_label = NULL; |
| 19268 | 19270 |
new."__accepted_infraspecific_{rank,epithet}" = NULL;
|
| 19269 | 19271 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL; |
| ... | ... | |
| 19279 | 19281 |
new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 19280 | 19282 |
new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 19281 | 19283 |
new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; |
| 19282 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL; |
|
| 19283 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; |
|
| 19284 | 19284 |
|
| 19285 | 19285 |
-- populate derived cols |
| 19286 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); |
|
| 19286 | 19287 |
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
|
| 19287 | 19288 |
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
|
| 19288 | 19289 |
new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new); |
| 19289 | 19290 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
| 19290 | 19291 |
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[1] FROM (SELECT new.*) new);
|
| 19291 | 19292 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
|
| 19293 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
|
| 19294 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19295 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19296 |
ELSE "*Accepted_name_species" |
|
| 19297 |
END FROM (SELECT new.*) new); |
|
| 19292 | 19298 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
| 19293 | 19299 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 19294 | 19300 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| ... | ... | |
| 19334 | 19340 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
| 19335 | 19341 |
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" |
| 19336 | 19342 |
END FROM (SELECT new.*) new); |
| 19337 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); |
|
| 19338 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
|
| 19339 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19340 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19341 |
ELSE "*Accepted_name_species" |
|
| 19342 |
END FROM (SELECT new.*) new); |
|
| 19343 | 19343 |
|
| 19344 | 19344 |
RETURN new; |
| 19345 | 19345 |
END; |
| ... | ... | |
| 19513 | 19513 |
"*Accepted_name_lsid" text, |
| 19514 | 19514 |
is_valid_match boolean NOT NULL, |
| 19515 | 19515 |
scrubbed_unique_taxon_name text, |
| 19516 |
"[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text, |
|
| 19516 | 19517 |
"[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text, |
| 19517 | 19518 |
"[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text, |
| 19518 | 19519 |
matched_has_accepted boolean, |
| 19519 | 19520 |
"__accepted_{genus,specific_epithet}" text[],
|
| 19520 | 19521 |
"[accepted_]genus__@DwC__@vegpath.org" text, |
| 19521 | 19522 |
"[accepted_]specificEpithet__@DwC__@vegpath.org" text, |
| 19523 |
"[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text, |
|
| 19522 | 19524 |
__accepted_infraspecific_label text, |
| 19523 | 19525 |
"__accepted_infraspecific_{rank,epithet}" text[],
|
| 19524 | 19526 |
"[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text, |
| ... | ... | |
| 19534 | 19536 |
"[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text, |
| 19535 | 19537 |
"[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text, |
| 19536 | 19538 |
"[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text, |
| 19537 |
"[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text, |
|
| 19538 |
"[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text, |
|
| 19539 | 19539 |
CONSTRAINT "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[1]))),
|
| 19540 | 19540 |
CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[1]))),
|
| 19541 | 19541 |
CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
|
| ... | ... | |
| 19637 | 19637 |
|
| 19638 | 19638 |
|
| 19639 | 19639 |
-- |
| 19640 |
-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 19641 |
-- |
|
| 19642 |
|
|
| 19643 |
COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS ' |
|
| 19644 |
= "*Unmatched_terms" |
|
| 19645 |
|
|
| 19646 |
derived column |
|
| 19647 |
|
|
| 19648 |
to modify expr: |
|
| 19649 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def);
|
|
| 19650 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 19651 |
|
|
| 19652 |
to rename: |
|
| 19653 |
# rename column |
|
| 19654 |
# rename CHECK constraint |
|
| 19655 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19656 |
'; |
|
| 19657 |
|
|
| 19658 |
|
|
| 19659 |
-- |
|
| 19640 | 19660 |
-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
| 19641 | 19661 |
-- |
| 19642 | 19662 |
|
| ... | ... | |
| 19757 | 19777 |
|
| 19758 | 19778 |
|
| 19759 | 19779 |
-- |
| 19780 |
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 19781 |
-- |
|
| 19782 |
|
|
| 19783 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
|
| 19784 |
= CASE |
|
| 19785 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19786 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19787 |
ELSE "*Accepted_name_species" |
|
| 19788 |
END |
|
| 19789 |
|
|
| 19790 |
derived column |
|
| 19791 |
|
|
| 19792 |
to modify expr: |
|
| 19793 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
|
| 19794 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19795 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19796 |
ELSE "*Accepted_name_species" |
|
| 19797 |
END$$)::util.derived_col_def); |
|
| 19798 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 19799 |
|
|
| 19800 |
to rename: |
|
| 19801 |
# rename column |
|
| 19802 |
# rename CHECK constraint |
|
| 19803 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19804 |
'; |
|
| 19805 |
|
|
| 19806 |
|
|
| 19807 |
-- |
|
| 19760 | 19808 |
-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: - |
| 19761 | 19809 |
-- |
| 19762 | 19810 |
|
| ... | ... | |
| 20117 | 20165 |
|
| 20118 | 20166 |
|
| 20119 | 20167 |
-- |
| 20120 |
-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20121 |
-- |
|
| 20122 |
|
|
| 20123 |
COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS ' |
|
| 20124 |
= "*Unmatched_terms" |
|
| 20125 |
|
|
| 20126 |
derived column |
|
| 20127 |
|
|
| 20128 |
to modify expr: |
|
| 20129 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def);
|
|
| 20130 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 20131 |
|
|
| 20132 |
to rename: |
|
| 20133 |
# rename column |
|
| 20134 |
# rename CHECK constraint |
|
| 20135 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 20136 |
'; |
|
| 20137 |
|
|
| 20138 |
|
|
| 20139 |
-- |
|
| 20140 |
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 20141 |
-- |
|
| 20142 |
|
|
| 20143 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
|
| 20144 |
= CASE |
|
| 20145 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20146 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20147 |
ELSE "*Accepted_name_species" |
|
| 20148 |
END |
|
| 20149 |
|
|
| 20150 |
derived column |
|
| 20151 |
|
|
| 20152 |
to modify expr: |
|
| 20153 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
|
| 20154 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20155 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20156 |
ELSE "*Accepted_name_species" |
|
| 20157 |
END$$)::util.derived_col_def); |
|
| 20158 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 20159 |
|
|
| 20160 |
to rename: |
|
| 20161 |
# rename column |
|
| 20162 |
# rename CHECK constraint |
|
| 20163 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 20164 |
'; |
|
| 20165 |
|
|
| 20166 |
|
|
| 20167 |
-- |
|
| 20168 | 20168 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
| 20169 | 20169 |
-- |
| 20170 | 20170 |
|
Also available in: Unified diff
inputs/.TNRS/schema.sql: reordered derived columns in dependency order