Revision 7241
Added by Aaron Marcuse-Kubitza almost 12 years ago
| schema.sql | ||
|---|---|---|
| 65 | 65 |
ON tnrs |
| 66 | 66 |
FOR EACH ROW |
| 67 | 67 |
EXECUTE PROCEDURE tnrs_populate_accepted_scientific_name(); |
| 68 |
|
|
| 69 |
|
|
| 70 |
CREATE OR REPLACE VIEW tnrs_canon AS |
|
| 71 |
SELECT ( |
|
| 72 |
CASE |
|
| 73 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 74 |
ELSE tnrs.* |
|
| 75 |
END)."Time_submitted" AS "Time_submitted", ( |
|
| 76 |
CASE |
|
| 77 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 78 |
ELSE tnrs.* |
|
| 79 |
END)."Name_number" AS "Name_number", ( |
|
| 80 |
CASE |
|
| 81 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 82 |
ELSE tnrs.* |
|
| 83 |
END)."Name_submitted" AS "Name_submitted", tnrs."Overall_score", ( |
|
| 84 |
CASE |
|
| 85 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 86 |
ELSE tnrs.* |
|
| 87 |
END)."Name_matched" AS "Name_matched", ( |
|
| 88 |
CASE |
|
| 89 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 90 |
ELSE tnrs.* |
|
| 91 |
END)."Name_matched_rank" AS "Name_matched_rank", tnrs."Name_score", ( |
|
| 92 |
CASE |
|
| 93 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 94 |
ELSE tnrs.* |
|
| 95 |
END)."Name_matched_author" AS "Name_matched_author", ( |
|
| 96 |
CASE |
|
| 97 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 98 |
ELSE tnrs.* |
|
| 99 |
END)."Name_matched_url" AS "Name_matched_url", ( |
|
| 100 |
CASE |
|
| 101 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 102 |
ELSE tnrs.* |
|
| 103 |
END)."Author_matched" AS "Author_matched", tnrs."Author_score", ( |
|
| 104 |
CASE |
|
| 105 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 106 |
ELSE tnrs.* |
|
| 107 |
END)."Family_matched" AS "Family_matched", tnrs."Family_score", ( |
|
| 108 |
CASE |
|
| 109 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 110 |
ELSE tnrs.* |
|
| 111 |
END)."Name_matched_accepted_family" AS "Name_matched_accepted_family", ( |
|
| 112 |
CASE |
|
| 113 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 114 |
ELSE tnrs.* |
|
| 115 |
END)."Genus_matched" AS "Genus_matched", tnrs."Genus_score", ( |
|
| 116 |
CASE |
|
| 117 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 118 |
ELSE tnrs.* |
|
| 119 |
END)."Specific_epithet_matched" AS "Specific_epithet_matched", tnrs."Specific_epithet_score", ( |
|
| 120 |
CASE |
|
| 121 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 122 |
ELSE tnrs.* |
|
| 123 |
END)."Infraspecific_rank" AS "Infraspecific_rank", ( |
|
| 124 |
CASE |
|
| 125 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 126 |
ELSE tnrs.* |
|
| 127 |
END)."Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", ( |
|
| 128 |
CASE |
|
| 129 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 130 |
ELSE tnrs.* |
|
| 131 |
END)."Infraspecific_rank_2" AS "Infraspecific_rank_2", ( |
|
| 132 |
CASE |
|
| 133 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 134 |
ELSE tnrs.* |
|
| 135 |
END)."Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", tnrs."Annotations" AS "Annotations", tnrs."Unmatched_terms" AS "Unmatched_terms", ( |
|
| 136 |
CASE |
|
| 137 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 138 |
ELSE tnrs.* |
|
| 139 |
END)."Taxonomic_status" AS "Taxonomic_status", ( |
|
| 140 |
CASE |
|
| 141 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 142 |
ELSE tnrs.* |
|
| 143 |
END)."Selected" AS "Selected", ( |
|
| 144 |
CASE |
|
| 145 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 146 |
ELSE tnrs.* |
|
| 147 |
END)."Source" AS "Source", ( |
|
| 148 |
CASE |
|
| 149 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 150 |
ELSE tnrs.* |
|
| 151 |
END)."Warnings" AS "Warnings" |
|
| 152 |
FROM tnrs |
|
| 153 |
LEFT JOIN tnrs tnrs_accepted ON tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"; |
|
| 154 |
COMMENT ON VIEW tnrs_canon |
|
| 155 |
IS 'The most canonicalized name output by TNRS. This will be the accepted name if available, and the matched name otherwise. In either case, the match scores are always from the matched name, not the accepted name (whose match scores should always be close to 1).'; |
|
Also available in: Unified diff
inputs/.TNRS/schema.sql: Removed no longer used tnrs_canon. unscrubbed_taxondetermination_view uses its definition directly instead.