Revision 14327
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 242 | 242 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
|
| 243 | 243 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| 244 | 244 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 245 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 245 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 246 | 246 |
ELSE "*Accepted_name_species" |
| 247 | 247 |
END FROM (SELECT new.*) new); |
| 248 | 248 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
| ... | ... | |
| 468 | 468 |
CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
|
| 469 | 469 |
CASE |
| 470 | 470 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 471 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 471 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 472 | 472 |
ELSE "*Accepted_name_species" |
| 473 | 473 |
END))), |
| 474 | 474 |
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))))),
|
| ... | ... | |
| 754 | 754 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
| 755 | 755 |
= CASE |
| 756 | 756 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 757 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 757 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 758 | 758 |
ELSE "*Accepted_name_species" |
| 759 | 759 |
END |
| 760 | 760 |
|
| ... | ... | |
| 763 | 763 |
to modify expr: |
| 764 | 764 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 765 | 765 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 766 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 766 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 767 | 767 |
ELSE "*Accepted_name_species" |
| 768 | 768 |
END$$)::util.derived_col_def); |
| 769 | 769 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15491 | 15491 |
END)))) |
| 15492 | 15492 |
CASE |
| 15493 | 15493 |
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`)
|
| 15494 |
WHEN (`*Accepted_name_rank` = CAST('genus' AS varchar(255))) THEN concat_ws(CAST(' ' AS varchar(255)), `*Accepted_name`, `[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`)
|
|
| 15494 |
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`)
|
|
| 15495 | 15495 |
ELSE `*Accepted_name_species` |
| 15496 | 15496 |
END))) |
| 15497 | 15497 |
CASE |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19304 | 19304 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
|
| 19305 | 19305 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
| 19306 | 19306 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19307 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19307 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19308 | 19308 |
ELSE "*Accepted_name_species" |
| 19309 | 19309 |
END FROM (SELECT new.*) new); |
| 19310 | 19310 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
| ... | ... | |
| 19572 | 19572 |
CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
|
| 19573 | 19573 |
CASE |
| 19574 | 19574 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19575 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19575 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19576 | 19576 |
ELSE "*Accepted_name_species" |
| 19577 | 19577 |
END))), |
| 19578 | 19578 |
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))))),
|
| ... | ... | |
| 19858 | 19858 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
| 19859 | 19859 |
= CASE |
| 19860 | 19860 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19861 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19861 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19862 | 19862 |
ELSE "*Accepted_name_species" |
| 19863 | 19863 |
END |
| 19864 | 19864 |
|
| ... | ... | |
| 19867 | 19867 |
to modify expr: |
| 19868 | 19868 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 19869 | 19869 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
| 19870 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19870 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19871 | 19871 |
ELSE "*Accepted_name_species" |
| 19872 | 19872 |
END$$)::util.derived_col_def); |
| 19873 | 19873 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
Also available in: Unified diff
bugfix: inputs/.TNRS/schema.sql: "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org": need to use "[accepted_]genus__@DwC__@vegpath.org" rather than "*Accepted_name" for this for rank = genus