Revision 14326
Added by Aaron Marcuse-Kubitza over 10 years ago
| schema.sql | ||
|---|---|---|
| 221 | 221 |
new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL; |
| 222 | 222 |
new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL; |
| 223 | 223 |
new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL; |
| 224 |
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL; |
|
| 224 | 225 |
|
| 225 | 226 |
-- populate derived cols |
| 226 | 227 |
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new); |
| ... | ... | |
| 290 | 291 |
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" |
| 291 | 292 |
END FROM (SELECT new.*) new); |
| 292 | 293 |
new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new);
|
| 294 |
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE |
|
| 295 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 296 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 297 |
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" |
|
| 298 |
END FROM (SELECT new.*) new); |
|
| 293 | 299 |
|
| 294 | 300 |
RETURN new; |
| 295 | 301 |
END; |
| ... | ... | |
| 446 | 452 |
"[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text, |
| 447 | 453 |
"[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text, |
| 448 | 454 |
"[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text, |
| 455 |
"[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text, |
|
| 449 | 456 |
CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family",
|
| 450 | 457 |
CASE |
| 451 | 458 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
|
| ... | ... | |
| 494 | 501 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
| 495 | 502 |
ELSE "*Infraspecific_epithet_matched" |
| 496 | 503 |
END))), |
| 504 |
CONSTRAINT "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
|
|
| 505 |
CASE |
|
| 506 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 507 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 508 |
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" |
|
| 509 |
END))), |
|
| 497 | 510 |
CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
| 498 | 511 |
CASE |
| 499 | 512 |
WHEN matched_has_accepted THEN "*Accepted_name" |
| ... | ... | |
| 1143 | 1156 |
|
| 1144 | 1157 |
|
| 1145 | 1158 |
-- |
| 1159 |
-- Name: COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 1160 |
-- |
|
| 1161 |
|
|
| 1162 |
COMMENT ON COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
|
| 1163 |
= CASE |
|
| 1164 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1165 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1166 |
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" |
|
| 1167 |
END |
|
| 1168 |
|
|
| 1169 |
derived column |
|
| 1170 |
|
|
| 1171 |
to modify expr: |
|
| 1172 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
|
| 1173 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1174 |
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1175 |
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" |
|
| 1176 |
END$$)::util.derived_col_def); |
|
| 1177 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
|
| 1178 |
|
|
| 1179 |
to rename: |
|
| 1180 |
# rename column |
|
| 1181 |
# rename CHECK constraint |
|
| 1182 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 1183 |
'; |
|
| 1184 |
|
|
| 1185 |
|
|
| 1186 |
-- |
|
| 1146 | 1187 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
| 1147 | 1188 |
-- |
| 1148 | 1189 |
|
Also available in: Unified diff
inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"