Revision 7238
Added by Aaron Marcuse-Kubitza almost 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 4558 | 4558 |
-- |
| 4559 | 4559 |
|
| 4560 | 4560 |
CREATE VIEW unscrubbed_taxondetermination_view AS |
| 4561 |
SELECT taxondetermination.taxonoccurrence_id, tnrs_canon."Time_submitted", tnrs_canon."Name_number", tnrs_canon."Name_submitted", tnrs_canon."Overall_score", tnrs_canon."Name_matched", tnrs_canon."Name_matched_rank", tnrs_canon."Name_score", tnrs_canon."Name_matched_author", tnrs_canon."Name_matched_url", tnrs_canon."Author_matched", tnrs_canon."Author_score", tnrs_canon."Family_matched", tnrs_canon."Family_score", tnrs_canon."Name_matched_accepted_family", tnrs_canon."Genus_matched", tnrs_canon."Genus_score", tnrs_canon."Specific_epithet_matched", tnrs_canon."Specific_epithet_score", tnrs_canon."Infraspecific_rank", tnrs_canon."Infraspecific_epithet_matched", tnrs_canon."Infraspecific_epithet_score", tnrs_canon."Infraspecific_rank_2", tnrs_canon."Infraspecific_epithet_2_matched", tnrs_canon."Infraspecific_epithet_2_score", tnrs_canon."Annotations", tnrs_canon."Unmatched_terms", tnrs_canon."Taxonomic_status", tnrs_canon."Selected", tnrs_canon."Source", tnrs_canon."Warnings" FROM (((taxondetermination JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel USING (taxonlabel_id)) JOIN "TNRS".tnrs_canon ON ((tnrs_canon."Name_submitted" = taxonlabel.taxonomicname))) WHERE (taxondetermination.iscurrent AND (taxondetermination.source_id <> COALESCE((SELECT source.source_id FROM source WHERE (source.shortname = 'TNRS'::text)), 2147483647))) ORDER BY taxondetermination.taxonoccurrence_id;
|
|
| 4561 |
SELECT taxondetermination.taxonoccurrence_id, (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Time_submitted" AS "Time_submitted", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_number" AS "Name_number", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_submitted" AS "Name_submitted", tnrs."Overall_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_matched" AS "Name_matched", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_matched_rank" AS "Name_matched_rank", tnrs."Name_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_matched_author" AS "Name_matched_author", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_matched_url" AS "Name_matched_url", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Author_matched" AS "Author_matched", tnrs."Author_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Family_matched" AS "Family_matched", tnrs."Family_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Name_matched_accepted_family" AS "Name_matched_accepted_family", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Genus_matched" AS "Genus_matched", tnrs."Genus_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Specific_epithet_matched" AS "Specific_epithet_matched", tnrs."Specific_epithet_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Infraspecific_rank" AS "Infraspecific_rank", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Infraspecific_rank_2" AS "Infraspecific_rank_2", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", tnrs."Annotations", tnrs."Unmatched_terms", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Taxonomic_status" AS "Taxonomic_status", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Selected" AS "Selected", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Source" AS "Source", (CASE WHEN (tnrs_accepted.* IS NOT NULL) THEN tnrs_accepted.* ELSE tnrs.* END)."Warnings" AS "Warnings" FROM ((((taxondetermination JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel USING (taxonlabel_id)) JOIN "TNRS".tnrs ON ((tnrs."Name_submitted" = taxonlabel.taxonomicname))) LEFT JOIN "TNRS".tnrs tnrs_accepted ON ((tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"))) WHERE (taxondetermination.iscurrent AND (taxondetermination.source_id <> COALESCE((SELECT source.source_id FROM source WHERE (source.shortname = 'TNRS'::text)), 2147483647))) ORDER BY taxondetermination.taxonoccurrence_id;
|
|
| 4562 | 4562 |
|
| 4563 | 4563 |
|
| 4564 | 4564 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: unscrubbed_taxondetermination_view: Do the tnrs_canon joins manually instead of using tnrs_canon, to allow PostgreSQL to use a nested loop join on just the needed tnrs rows instead of a hash self-join of all tnrs rows. The query planner is not yet advanced enough to automatically integrate the select on the view into the top-level joins list, which would make this change automatically.