Revision 14367
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 257 | 257 |
ELSE NULL::text |
| 258 | 258 |
END) FROM (SELECT new.*) new); |
| 259 | 259 |
new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
| 260 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
|
| 260 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text) FROM (SELECT new.*) new);
|
|
| 261 | 261 |
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet}"[1],
|
| 262 | 262 |
CASE |
| 263 | 263 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
|
| ... | ... | |
| 267 | 267 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| 268 | 268 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 269 | 269 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 270 |
ELSE "*Accepted_name_species"
|
|
| 270 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 271 | 271 |
END FROM (SELECT new.*) new); |
| 272 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 272 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 273 | 273 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 274 | 274 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 275 | 275 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| ... | ... | |
| 292 | 292 |
ELSE "*Specific_epithet_matched" |
| 293 | 293 |
END FROM (SELECT new.*) new); |
| 294 | 294 |
new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE |
| 295 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 295 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 296 | 296 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 297 | 297 |
END FROM (SELECT new.*) new); |
| 298 | 298 |
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| ... | ... | |
| 496 | 496 |
CASE |
| 497 | 497 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 498 | 498 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 499 |
ELSE "*Accepted_name_species"
|
|
| 499 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 500 | 500 |
END))), |
| 501 | 501 |
CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),
|
| 502 | 502 |
CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
|
| ... | ... | |
| 546 | 546 |
END))), |
| 547 | 547 |
CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
| 548 | 548 |
CASE |
| 549 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 549 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 550 | 550 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 551 | 551 |
END))), |
| 552 | 552 |
CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
|
| ... | ... | |
| 560 | 560 |
ELSE "*Name_matched_rank" |
| 561 | 561 |
END))), |
| 562 | 562 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 563 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 563 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 564 | 564 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
| 565 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
|
| 565 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text)))),
|
|
| 566 | 566 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| 567 | 567 |
CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))
|
| 568 | 568 |
); |
| ... | ... | |
| 759 | 759 |
-- |
| 760 | 760 |
|
| 761 | 761 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
| 762 |
= regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
|
| 762 |
= regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)
|
|
| 763 | 763 |
|
| 764 | 764 |
derived column |
| 765 | 765 |
|
| 766 | 766 |
to modify expr: |
| 767 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
|
| 767 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)$$)::util.derived_col_def);
|
|
| 768 | 768 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 769 | 769 |
|
| 770 | 770 |
to rename: |
| ... | ... | |
| 830 | 830 |
= CASE |
| 831 | 831 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 832 | 832 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 833 |
ELSE "*Accepted_name_species"
|
|
| 833 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 834 | 834 |
END |
| 835 | 835 |
|
| 836 | 836 |
derived column |
| ... | ... | |
| 839 | 839 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 840 | 840 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 841 | 841 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 842 |
ELSE "*Accepted_name_species"
|
|
| 842 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 843 | 843 |
END$$)::util.derived_col_def); |
| 844 | 844 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 845 | 845 |
|
| ... | ... | |
| 855 | 855 |
-- |
| 856 | 856 |
|
| 857 | 857 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 858 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 858 |
= ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 859 | 859 |
|
| 860 | 860 |
derived column |
| 861 | 861 |
|
| 862 | 862 |
to modify expr: |
| 863 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 863 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 864 | 864 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 865 | 865 |
|
| 866 | 866 |
to rename: |
| ... | ... | |
| 1080 | 1080 |
|
| 1081 | 1081 |
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
| 1082 | 1082 |
= CASE |
| 1083 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 1083 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 1084 | 1084 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 1085 | 1085 |
END |
| 1086 | 1086 |
|
| ... | ... | |
| 1088 | 1088 |
|
| 1089 | 1089 |
to modify expr: |
| 1090 | 1090 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
| 1091 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 1091 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 1092 | 1092 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 1093 | 1093 |
END$$)::util.derived_col_def); |
| 1094 | 1094 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15995 | 15995 |
CASE |
| 15996 | 15996 |
WHEN (`*Accepted_name_rank` = CAST('family' AS varchar(255))) THEN concat_ws(CAST(' ' AS varchar(255)), `Accepted_family__@TNRS__@vegpath.org`, `[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`)
|
| 15997 | 15997 |
WHEN (`*Accepted_name_rank` = CAST('genus' AS varchar(255))) THEN concat_ws(CAST(' ' AS varchar(255)), `[accepted_]genus__@DwC__@vegpath.org`, `[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`)
|
| 15998 |
ELSE `*Accepted_name_species`
|
|
| 15998 |
ELSE `Accepted_species[_binomial]__@TNRS__@vegpath.org`
|
|
| 15999 | 15999 |
END))) |
| 16000 | 16000 |
CASE |
| 16001 | 16001 |
WHEN matched_has_accepted THEN `[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org` |
| ... | ... | |
| 16031 | 16031 |
ELSE `[matched_]scientificName[_with_author]__@DwC__@vegpath.org` |
| 16032 | 16032 |
END))) |
| 16033 | 16033 |
CASE |
| 16034 |
WHEN matched_has_accepted THEN `*Accepted_name_species`
|
|
| 16034 |
WHEN matched_has_accepted THEN `Accepted_species[_binomial]__@TNRS__@vegpath.org`
|
|
| 16035 | 16035 |
ELSE `[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org` |
| 16036 | 16036 |
END))) |
| 16037 | 16037 |
CASE |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19876 | 19876 |
ELSE NULL::text |
| 19877 | 19877 |
END) FROM (SELECT new.*) new); |
| 19878 | 19878 |
new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
| 19879 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
|
| 19879 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text) FROM (SELECT new.*) new);
|
|
| 19880 | 19880 |
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet}"[1],
|
| 19881 | 19881 |
CASE |
| 19882 | 19882 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
|
| ... | ... | |
| 19886 | 19886 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| 19887 | 19887 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19888 | 19888 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19889 |
ELSE "*Accepted_name_species"
|
|
| 19889 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 19890 | 19890 |
END FROM (SELECT new.*) new); |
| 19891 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 19891 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 19892 | 19892 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 19893 | 19893 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 19894 | 19894 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| ... | ... | |
| 19911 | 19911 |
ELSE "*Specific_epithet_matched" |
| 19912 | 19912 |
END FROM (SELECT new.*) new); |
| 19913 | 19913 |
new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE |
| 19914 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 19914 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 19915 | 19915 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 19916 | 19916 |
END FROM (SELECT new.*) new); |
| 19917 | 19917 |
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| ... | ... | |
| 20157 | 20157 |
CASE |
| 20158 | 20158 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20159 | 20159 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20160 |
ELSE "*Accepted_name_species"
|
|
| 20160 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20161 | 20161 |
END))), |
| 20162 | 20162 |
CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),
|
| 20163 | 20163 |
CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
|
| ... | ... | |
| 20207 | 20207 |
END))), |
| 20208 | 20208 |
CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
| 20209 | 20209 |
CASE |
| 20210 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 20210 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20211 | 20211 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 20212 | 20212 |
END))), |
| 20213 | 20213 |
CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
|
| ... | ... | |
| 20221 | 20221 |
ELSE "*Name_matched_rank" |
| 20222 | 20222 |
END))), |
| 20223 | 20223 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 20224 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 20224 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
|
| 20225 | 20225 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
| 20226 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
|
| 20226 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", ' '::text)))),
|
|
| 20227 | 20227 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| 20228 | 20228 |
CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))
|
| 20229 | 20229 |
); |
| ... | ... | |
| 20420 | 20420 |
-- |
| 20421 | 20421 |
|
| 20422 | 20422 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
| 20423 |
= regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
|
| 20423 |
= regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)
|
|
| 20424 | 20424 |
|
| 20425 | 20425 |
derived column |
| 20426 | 20426 |
|
| 20427 | 20427 |
to modify expr: |
| 20428 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
|
| 20428 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("Accepted_species[_binomial]__@TNRS__@vegpath.org", '' ''::text)$$)::util.derived_col_def);
|
|
| 20429 | 20429 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20430 | 20430 |
|
| 20431 | 20431 |
to rename: |
| ... | ... | |
| 20491 | 20491 |
= CASE |
| 20492 | 20492 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20493 | 20493 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20494 |
ELSE "*Accepted_name_species"
|
|
| 20494 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20495 | 20495 |
END |
| 20496 | 20496 |
|
| 20497 | 20497 |
derived column |
| ... | ... | |
| 20500 | 20500 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 20501 | 20501 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20502 | 20502 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 20503 |
ELSE "*Accepted_name_species"
|
|
| 20503 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20504 | 20504 |
END$$)::util.derived_col_def); |
| 20505 | 20505 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20506 | 20506 |
|
| ... | ... | |
| 20516 | 20516 |
-- |
| 20517 | 20517 |
|
| 20518 | 20518 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 20519 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 20519 |
= ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 20520 | 20520 |
|
| 20521 | 20521 |
derived column |
| 20522 | 20522 |
|
| 20523 | 20523 |
to modify expr: |
| 20524 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 20524 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 20525 | 20525 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20526 | 20526 |
|
| 20527 | 20527 |
to rename: |
| ... | ... | |
| 20741 | 20741 |
|
| 20742 | 20742 |
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
| 20743 | 20743 |
= CASE |
| 20744 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 20744 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20745 | 20745 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 20746 | 20746 |
END |
| 20747 | 20747 |
|
| ... | ... | |
| 20749 | 20749 |
|
| 20750 | 20750 |
to modify expr: |
| 20751 | 20751 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
| 20752 |
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
|
| 20752 |
WHEN matched_has_accepted THEN "Accepted_species[_binomial]__@TNRS__@vegpath.org"
|
|
| 20753 | 20753 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
| 20754 | 20754 |
END$$)::util.derived_col_def); |
| 20755 | 20755 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
Also available in: Unified diff
bugfix: inputs/.TNRS/schema.sql: taxon_match: use "Accepted_species[_binomial]__@TNRS__@vegpath.org" instead of "*Accepted_name_species". this fixes a bug in __accepted_infraspecific_label where Accepted_name_species with trailing whitespace could not be prefix-removed from names that contained just a species binomial.