Revision 14311
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 196 | 196 |
AS $$ |
| 197 | 197 |
BEGIN |
| 198 | 198 |
-- clear derived cols so old values won't be used in calculations |
| 199 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL; |
|
| 199 | 200 |
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 200 | 201 |
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; |
| 201 | 202 |
new.matched_has_accepted = NULL; |
| 202 | 203 |
new."__accepted_{genus,specific_epithet}" = NULL;
|
| 203 | 204 |
new."[accepted_]genus__@DwC__@vegpath.org" = NULL; |
| 204 | 205 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL; |
| 206 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; |
|
| 205 | 207 |
new.__accepted_infraspecific_label = NULL; |
| 206 | 208 |
new."__accepted_infraspecific_{rank,epithet}" = NULL;
|
| 207 | 209 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL; |
| ... | ... | |
| 217 | 219 |
new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 218 | 220 |
new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 219 | 221 |
new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; |
| 220 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL; |
|
| 221 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; |
|
| 222 | 222 |
|
| 223 | 223 |
-- populate derived cols |
| 224 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); |
|
| 224 | 225 |
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
|
| 225 | 226 |
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
|
| 226 | 227 |
new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new); |
| 227 | 228 |
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
| 228 | 229 |
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[1] FROM (SELECT new.*) new);
|
| 229 | 230 |
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
|
| 231 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
|
| 232 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 233 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 234 |
ELSE "*Accepted_name_species" |
|
| 235 |
END FROM (SELECT new.*) new); |
|
| 230 | 236 |
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
|
| 231 | 237 |
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
| 232 | 238 |
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
| ... | ... | |
| 272 | 278 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
| 273 | 279 |
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" |
| 274 | 280 |
END FROM (SELECT new.*) new); |
| 275 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); |
|
| 276 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
|
| 277 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 278 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 279 |
ELSE "*Accepted_name_species" |
|
| 280 |
END FROM (SELECT new.*) new); |
|
| 281 | 281 |
|
| 282 | 282 |
RETURN new; |
| 283 | 283 |
END; |
| ... | ... | |
| 533 | 533 |
|
| 534 | 534 |
|
| 535 | 535 |
-- |
| 536 |
-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 537 |
-- |
|
| 538 |
|
|
| 539 |
COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS ' |
|
| 540 |
= "*Unmatched_terms" |
|
| 541 |
|
|
| 542 |
derived column |
|
| 543 |
|
|
| 544 |
to modify expr: |
|
| 545 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def);
|
|
| 546 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 547 |
|
|
| 548 |
to rename: |
|
| 549 |
# rename column |
|
| 550 |
# rename CHECK constraint |
|
| 551 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 552 |
'; |
|
| 553 |
|
|
| 554 |
|
|
| 555 |
-- |
|
| 536 | 556 |
-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
| 537 | 557 |
-- |
| 538 | 558 |
|
| ... | ... | |
| 653 | 673 |
|
| 654 | 674 |
|
| 655 | 675 |
-- |
| 676 |
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 677 |
-- |
|
| 678 |
|
|
| 679 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
|
| 680 |
= CASE |
|
| 681 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 682 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 683 |
ELSE "*Accepted_name_species" |
|
| 684 |
END |
|
| 685 |
|
|
| 686 |
derived column |
|
| 687 |
|
|
| 688 |
to modify expr: |
|
| 689 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
|
| 690 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 691 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 692 |
ELSE "*Accepted_name_species" |
|
| 693 |
END$$)::util.derived_col_def); |
|
| 694 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 695 |
|
|
| 696 |
to rename: |
|
| 697 |
# rename column |
|
| 698 |
# rename CHECK constraint |
|
| 699 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 700 |
'; |
|
| 701 |
|
|
| 702 |
|
|
| 703 |
-- |
|
| 656 | 704 |
-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: - |
| 657 | 705 |
-- |
| 658 | 706 |
|
| ... | ... | |
| 1013 | 1061 |
|
| 1014 | 1062 |
|
| 1015 | 1063 |
-- |
| 1016 |
-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1017 |
-- |
|
| 1018 |
|
|
| 1019 |
COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS ' |
|
| 1020 |
= "*Unmatched_terms" |
|
| 1021 |
|
|
| 1022 |
derived column |
|
| 1023 |
|
|
| 1024 |
to modify expr: |
|
| 1025 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def);
|
|
| 1026 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 1027 |
|
|
| 1028 |
to rename: |
|
| 1029 |
# rename column |
|
| 1030 |
# rename CHECK constraint |
|
| 1031 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 1032 |
'; |
|
| 1033 |
|
|
| 1034 |
|
|
| 1035 |
-- |
|
| 1036 |
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1037 |
-- |
|
| 1038 |
|
|
| 1039 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
|
| 1040 |
= CASE |
|
| 1041 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1042 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1043 |
ELSE "*Accepted_name_species" |
|
| 1044 |
END |
|
| 1045 |
|
|
| 1046 |
derived column |
|
| 1047 |
|
|
| 1048 |
to modify expr: |
|
| 1049 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
|
| 1050 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1051 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1052 |
ELSE "*Accepted_name_species" |
|
| 1053 |
END$$)::util.derived_col_def); |
|
| 1054 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 1055 |
|
|
| 1056 |
to rename: |
|
| 1057 |
# rename column |
|
| 1058 |
# rename CHECK constraint |
|
| 1059 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 1060 |
'; |
|
| 1061 |
|
|
| 1062 |
|
|
| 1063 |
-- |
|
| 1064 | 1064 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
| 1065 | 1065 |
-- |
| 1066 | 1066 |
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15455 | 15455 |
`*Accepted_name_lsid` varchar(255), |
| 15456 | 15456 |
is_valid_match int(1) NOT NULL, |
| 15457 | 15457 |
scrubbed_unique_taxon_name varchar(255), |
| 15458 |
`[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org` varchar(255), |
|
| 15458 | 15459 |
`[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org` varchar(255), |
| 15459 | 15460 |
`[matched_]scientificName[_with_author]__@DwC__@vegpath.org` varchar(255), |
| 15460 | 15461 |
matched_has_accepted int(1), |
| 15461 | 15462 |
`__accepted_{genus,specific_epithet}` varchar(255),
|
| 15462 | 15463 |
`[accepted_]genus__@DwC__@vegpath.org` varchar(255), |
| 15463 | 15464 |
`[accepted_]specificEpithet__@DwC__@vegpath.org` varchar(255), |
| 15465 |
`[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org` varchar(255), |
|
| 15464 | 15466 |
__accepted_infraspecific_label varchar(255), |
| 15465 | 15467 |
`__accepted_infraspecific_{rank,epithet}` varchar(255),
|
| 15466 | 15468 |
`[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org` varchar(255), |
| ... | ... | |
| 15475 | 15477 |
`[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org` varchar(255), |
| 15476 | 15478 |
`[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org` varchar(255), |
| 15477 | 15479 |
`[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org` varchar(255), |
| 15478 |
`[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org` varchar(255), |
|
| 15479 |
`[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org` varchar(255), |
|
| 15480 |
`[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org` varchar(255) |
|
| 15480 |
`[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org` varchar(255) |
|
| 15481 | 15481 |
CASE |
| 15482 | 15482 |
WHEN (`*Accepted_name_rank` = CAST('family' AS varchar(255))) THEN concat_ws(CAST(' ' AS varchar(255)), `*Accepted_name_family`, `[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`)
|
| 15483 | 15483 |
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`)
|
| ... | ... | |
| 15534 | 15534 |
|
| 15535 | 15535 |
|
| 15536 | 15536 |
-- |
| 15537 |
-- Name: COLUMN taxon_match.`[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15538 |
-- |
|
| 15539 |
|
|
| 15540 |
|
|
| 15541 |
|
|
| 15542 |
|
|
| 15543 |
-- |
|
| 15537 | 15544 |
-- Name: COLUMN taxon_match.`[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org`; Type: COMMENT; Schema: TNRS; Owner: - |
| 15538 | 15545 |
-- |
| 15539 | 15546 |
|
| ... | ... | |
| 15576 | 15583 |
|
| 15577 | 15584 |
|
| 15578 | 15585 |
-- |
| 15586 |
-- Name: COLUMN taxon_match.`[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15587 |
-- |
|
| 15588 |
|
|
| 15589 |
|
|
| 15590 |
|
|
| 15591 |
|
|
| 15592 |
-- |
|
| 15579 | 15593 |
-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: - |
| 15580 | 15594 |
-- |
| 15581 | 15595 |
|
| ... | ... | |
| 15681 | 15695 |
|
| 15682 | 15696 |
|
| 15683 | 15697 |
-- |
| 15684 |
-- Name: COLUMN taxon_match.`[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15685 |
-- |
|
| 15686 |
|
|
| 15687 |
|
|
| 15688 |
|
|
| 15689 |
|
|
| 15690 |
-- |
|
| 15691 |
-- Name: COLUMN taxon_match.`[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org`; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15692 |
-- |
|
| 15693 |
|
|
| 15694 |
|
|
| 15695 |
|
|
| 15696 |
|
|
| 15697 |
-- |
|
| 15698 | 15698 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
| 15699 | 15699 |
-- |
| 15700 | 15700 |
|
| trunk/schemas/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