Project

General

Profile

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).';