| 1 |
5798
|
aaronmk
|
CREATE OR REPLACE FUNCTION array_to_string(anyarray, text)
|
| 2 |
|
|
RETURNS text AS
|
| 3 |
|
|
$BODY$
|
| 4 |
|
|
SELECT pg_catalog.array_to_string($1, $2)
|
| 5 |
|
|
$BODY$
|
| 6 |
|
|
LANGUAGE sql IMMUTABLE STRICT
|
| 7 |
|
|
COST 100;
|
| 8 |
|
|
|
| 9 |
5183
|
aaronmk
|
CREATE TABLE tnrs
|
| 10 |
5110
|
aaronmk
|
(
|
| 11 |
5737
|
aaronmk
|
"Time_submitted" timestamp with time zone,
|
| 12 |
5110
|
aaronmk
|
"Name_number" text,
|
| 13 |
|
|
"Name_submitted" text NOT NULL,
|
| 14 |
|
|
"Overall_score" text,
|
| 15 |
|
|
"Name_matched" text,
|
| 16 |
|
|
"Name_matched_rank" text,
|
| 17 |
|
|
"Name_score" text,
|
| 18 |
|
|
"Name_matched_author" text,
|
| 19 |
|
|
"Name_matched_url" text,
|
| 20 |
|
|
"Author_matched" text,
|
| 21 |
|
|
"Author_score" text,
|
| 22 |
|
|
"Family_matched" text,
|
| 23 |
|
|
"Family_score" text,
|
| 24 |
|
|
"Name_matched_accepted_family" text,
|
| 25 |
|
|
"Genus_matched" text,
|
| 26 |
|
|
"Genus_score" text,
|
| 27 |
|
|
"Specific_epithet_matched" text,
|
| 28 |
|
|
"Specific_epithet_score" text,
|
| 29 |
|
|
"Infraspecific_rank" text,
|
| 30 |
|
|
"Infraspecific_epithet_matched" text,
|
| 31 |
|
|
"Infraspecific_epithet_score" text,
|
| 32 |
|
|
"Infraspecific_rank_2" text,
|
| 33 |
|
|
"Infraspecific_epithet_2_matched" text,
|
| 34 |
|
|
"Infraspecific_epithet_2_score" text,
|
| 35 |
|
|
"Annotations" text,
|
| 36 |
|
|
"Unmatched_terms" text,
|
| 37 |
|
|
"Taxonomic_status" text,
|
| 38 |
|
|
"Accepted_name" text,
|
| 39 |
|
|
"Accepted_name_author" text,
|
| 40 |
|
|
"Accepted_name_rank" text,
|
| 41 |
|
|
"Accepted_name_url" text,
|
| 42 |
|
|
"Accepted_name_species" text,
|
| 43 |
|
|
"Accepted_name_family" text,
|
| 44 |
|
|
"Selected" text,
|
| 45 |
|
|
"Source" text,
|
| 46 |
|
|
"Warnings" text,
|
| 47 |
|
|
"Accepted_name_lsid" text,
|
| 48 |
7133
|
aaronmk
|
"Accepted_scientific_name" text,
|
| 49 |
5110
|
aaronmk
|
CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
|
| 50 |
|
|
)
|
| 51 |
|
|
WITH (
|
| 52 |
|
|
OIDS=FALSE
|
| 53 |
|
|
);
|
| 54 |
7132
|
aaronmk
|
|
| 55 |
7134
|
aaronmk
|
CREATE OR REPLACE FUNCTION tnrs_populate_accepted_scientific_name()
|
| 56 |
|
|
RETURNS trigger AS
|
| 57 |
|
|
$BODY$
|
| 58 |
|
|
BEGIN
|
| 59 |
|
|
new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
|
| 60 |
7139
|
aaronmk
|
NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
|
| 61 |
7134
|
aaronmk
|
, new."Accepted_name"
|
| 62 |
|
|
, new."Accepted_name_author"
|
| 63 |
|
|
], ' '), '');
|
| 64 |
|
|
|
| 65 |
|
|
RETURN new;
|
| 66 |
|
|
END;
|
| 67 |
|
|
$BODY$
|
| 68 |
|
|
LANGUAGE plpgsql VOLATILE
|
| 69 |
|
|
COST 100;
|
| 70 |
|
|
|
| 71 |
|
|
CREATE TRIGGER tnrs_populate_accepted_scientific_name
|
| 72 |
|
|
BEFORE INSERT OR UPDATE
|
| 73 |
|
|
ON tnrs
|
| 74 |
|
|
FOR EACH ROW
|
| 75 |
|
|
EXECUTE PROCEDURE tnrs_populate_accepted_scientific_name();
|
| 76 |
|
|
|
| 77 |
|
|
|
| 78 |
7144
|
aaronmk
|
CREATE OR REPLACE VIEW tnrs_canon AS
|
| 79 |
|
|
SELECT (
|
| 80 |
|
|
CASE
|
| 81 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 82 |
|
|
ELSE tnrs.*
|
| 83 |
|
|
END)."Time_submitted" AS "Time_submitted", (
|
| 84 |
|
|
CASE
|
| 85 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 86 |
|
|
ELSE tnrs.*
|
| 87 |
|
|
END)."Name_number" AS "Name_number", (
|
| 88 |
|
|
CASE
|
| 89 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 90 |
|
|
ELSE tnrs.*
|
| 91 |
|
|
END)."Name_submitted" AS "Name_submitted", tnrs."Overall_score", (
|
| 92 |
|
|
CASE
|
| 93 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 94 |
|
|
ELSE tnrs.*
|
| 95 |
|
|
END)."Name_matched" AS "Name_matched", (
|
| 96 |
|
|
CASE
|
| 97 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 98 |
|
|
ELSE tnrs.*
|
| 99 |
|
|
END)."Name_matched_rank" AS "Name_matched_rank", tnrs."Name_score", (
|
| 100 |
|
|
CASE
|
| 101 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 102 |
|
|
ELSE tnrs.*
|
| 103 |
|
|
END)."Name_matched_author" AS "Name_matched_author", (
|
| 104 |
|
|
CASE
|
| 105 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 106 |
|
|
ELSE tnrs.*
|
| 107 |
|
|
END)."Name_matched_url" AS "Name_matched_url", (
|
| 108 |
|
|
CASE
|
| 109 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 110 |
|
|
ELSE tnrs.*
|
| 111 |
|
|
END)."Author_matched" AS "Author_matched", tnrs."Author_score", (
|
| 112 |
|
|
CASE
|
| 113 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 114 |
|
|
ELSE tnrs.*
|
| 115 |
|
|
END)."Family_matched" AS "Family_matched", tnrs."Family_score", (
|
| 116 |
|
|
CASE
|
| 117 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 118 |
|
|
ELSE tnrs.*
|
| 119 |
|
|
END)."Name_matched_accepted_family" AS "Name_matched_accepted_family", (
|
| 120 |
|
|
CASE
|
| 121 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 122 |
|
|
ELSE tnrs.*
|
| 123 |
|
|
END)."Genus_matched" AS "Genus_matched", tnrs."Genus_score", (
|
| 124 |
|
|
CASE
|
| 125 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 126 |
|
|
ELSE tnrs.*
|
| 127 |
|
|
END)."Specific_epithet_matched" AS "Specific_epithet_matched", tnrs."Specific_epithet_score", (
|
| 128 |
|
|
CASE
|
| 129 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 130 |
|
|
ELSE tnrs.*
|
| 131 |
|
|
END)."Infraspecific_rank" AS "Infraspecific_rank", (
|
| 132 |
|
|
CASE
|
| 133 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 134 |
|
|
ELSE tnrs.*
|
| 135 |
|
|
END)."Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", (
|
| 136 |
|
|
CASE
|
| 137 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 138 |
|
|
ELSE tnrs.*
|
| 139 |
|
|
END)."Infraspecific_rank_2" AS "Infraspecific_rank_2", (
|
| 140 |
|
|
CASE
|
| 141 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 142 |
|
|
ELSE tnrs.*
|
| 143 |
|
|
END)."Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", (
|
| 144 |
|
|
CASE
|
| 145 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 146 |
|
|
ELSE tnrs.*
|
| 147 |
7145
|
aaronmk
|
END)."Annotations" AS "Annotations", tnrs."Unmatched_terms" AS "Unmatched_terms", (
|
| 148 |
7144
|
aaronmk
|
CASE
|
| 149 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 150 |
|
|
ELSE tnrs.*
|
| 151 |
|
|
END)."Taxonomic_status" AS "Taxonomic_status", (
|
| 152 |
|
|
CASE
|
| 153 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 154 |
|
|
ELSE tnrs.*
|
| 155 |
|
|
END)."Selected" AS "Selected", (
|
| 156 |
|
|
CASE
|
| 157 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 158 |
|
|
ELSE tnrs.*
|
| 159 |
|
|
END)."Source" AS "Source", (
|
| 160 |
|
|
CASE
|
| 161 |
|
|
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.*
|
| 162 |
|
|
ELSE tnrs.*
|
| 163 |
|
|
END)."Warnings" AS "Warnings"
|
| 164 |
|
|
FROM tnrs
|
| 165 |
|
|
LEFT JOIN tnrs tnrs_accepted ON tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name";
|
| 166 |
|
|
COMMENT ON VIEW tnrs_canon
|
| 167 |
|
|
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).';
|
