Revision 14378
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 266 | 266 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org" |
| 267 | 267 |
END FROM (SELECT new.*) new); |
| 268 | 268 |
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);
|
| 269 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
|
| 269 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[]) FROM (SELECT new.*) new);
|
|
| 270 | 270 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 271 | 271 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| 272 | 272 |
new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
|
| ... | ... | |
| 553 | 553 |
END))), |
| 554 | 554 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 555 | 555 |
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)))),
|
| 556 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
|
| 556 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[])))),
|
|
| 557 | 557 |
CONSTRAINT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" CHECK ((NOT ("__accepted_{genus,specific_epithet,infra_{rank,epithet}}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name", ' '::text)))),
|
| 558 | 558 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| 559 | 559 |
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))))
|
| ... | ... | |
| 859 | 859 |
-- |
| 860 | 860 |
|
| 861 | 861 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 862 |
= regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
| 862 |
= NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], ''{}''::text[])
|
|
| 863 | 863 |
|
| 864 | 864 |
derived column |
| 865 | 865 |
|
| 866 | 866 |
to modify expr: |
| 867 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
|
| 867 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], ''{}''::text[])$$)::util.derived_col_def);
|
|
| 868 | 868 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 869 | 869 |
|
| 870 | 870 |
to rename: |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19885 | 19885 |
ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org" |
| 19886 | 19886 |
END FROM (SELECT new.*) new); |
| 19887 | 19887 |
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);
|
| 19888 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
|
| 19888 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[]) FROM (SELECT new.*) new);
|
|
| 19889 | 19889 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| 19890 | 19890 |
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
| 19891 | 19891 |
new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
|
| ... | ... | |
| 20214 | 20214 |
END))), |
| 20215 | 20215 |
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
| 20216 | 20216 |
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)))),
|
| 20217 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
|
| 20217 |
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[])))),
|
|
| 20218 | 20218 |
CONSTRAINT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" CHECK ((NOT ("__accepted_{genus,specific_epithet,infra_{rank,epithet}}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name", ' '::text)))),
|
| 20219 | 20219 |
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
| 20220 | 20220 |
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))))
|
| ... | ... | |
| 20520 | 20520 |
-- |
| 20521 | 20521 |
|
| 20522 | 20522 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 20523 |
= regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
| 20523 |
= NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], ''{}''::text[])
|
|
| 20524 | 20524 |
|
| 20525 | 20525 |
derived column |
| 20526 | 20526 |
|
| 20527 | 20527 |
to modify expr: |
| 20528 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
|
| 20528 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], ''{}''::text[])$$)::util.derived_col_def);
|
|
| 20529 | 20529 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20530 | 20530 |
|
| 20531 | 20531 |
to rename: |
Also available in: Unified diff
inputs/.TNRS/schema.sql: _accepted_infraspecific{rank,epithet}: use array slice of new _accepted{genus,specific_epithet,infra_{rank,epithet}}, which is simpler than using remove_prefix() in __accepted_infraspecific_label