Revision 14357
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 267 | 267 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 268 | 268 |
ELSE "*Accepted_name_species" |
| 269 | 269 |
END FROM (SELECT new.*) new); |
| 270 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 270 |
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);
|
|
| 271 | 271 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 272 | 272 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 273 | 273 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| ... | ... | |
| 556 | 556 |
ELSE "*Name_matched_rank" |
| 557 | 557 |
END))), |
| 558 | 558 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 559 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),
|
|
| 559 |
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)))),
|
|
| 560 | 560 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
| 561 | 561 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
| 562 | 562 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| ... | ... | |
| 828 | 828 |
-- |
| 829 | 829 |
|
| 830 | 830 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 831 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
| 831 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 832 | 832 |
|
| 833 | 833 |
derived column |
| 834 | 834 |
|
| 835 | 835 |
to modify expr: |
| 836 |
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), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 836 |
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);
|
|
| 837 | 837 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 838 | 838 |
|
| 839 | 839 |
to rename: |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19886 | 19886 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19887 | 19887 |
ELSE "*Accepted_name_species" |
| 19888 | 19888 |
END FROM (SELECT new.*) new); |
| 19889 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
|
| 19889 |
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);
|
|
| 19890 | 19890 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 19891 | 19891 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 19892 | 19892 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| ... | ... | |
| 20217 | 20217 |
ELSE "*Name_matched_rank" |
| 20218 | 20218 |
END))), |
| 20219 | 20219 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 20220 |
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),
|
|
| 20220 |
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)))),
|
|
| 20221 | 20221 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
| 20222 | 20222 |
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
| 20223 | 20223 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| ... | ... | |
| 20489 | 20489 |
-- |
| 20490 | 20490 |
|
| 20491 | 20491 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 20492 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
| 20492 |
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
|
| 20493 | 20493 |
|
| 20494 | 20494 |
derived column |
| 20495 | 20495 |
|
| 20496 | 20496 |
to modify expr: |
| 20497 |
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), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
|
| 20497 |
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);
|
|
| 20498 | 20498 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20499 | 20499 |
|
| 20500 | 20500 |
to rename: |
Also available in: Unified diff
bugfix: inputs/.TNRS/schema.sql: taxon_match.__accepted_infraspecific_label: need to use case-insensitive matching of the removed prefix because TNRS lowercases part of the Accepted_name