Project

General

Profile

1 5183 aaronmk
CREATE TABLE tnrs
2 5110 aaronmk
(
3 5737 aaronmk
  "Time_submitted" timestamp with time zone,
4 5110 aaronmk
  "Name_number" text,
5
  "Name_submitted" text NOT NULL,
6
  "Overall_score" text,
7
  "Name_matched" text,
8
  "Name_matched_rank" text,
9
  "Name_score" text,
10
  "Name_matched_author" text,
11
  "Name_matched_url" text,
12
  "Author_matched" text,
13
  "Author_score" text,
14
  "Family_matched" text,
15
  "Family_score" text,
16
  "Name_matched_accepted_family" text,
17
  "Genus_matched" text,
18
  "Genus_score" text,
19
  "Specific_epithet_matched" text,
20
  "Specific_epithet_score" text,
21
  "Infraspecific_rank" text,
22
  "Infraspecific_epithet_matched" text,
23
  "Infraspecific_epithet_score" text,
24
  "Infraspecific_rank_2" text,
25
  "Infraspecific_epithet_2_matched" text,
26
  "Infraspecific_epithet_2_score" text,
27
  "Annotations" text,
28
  "Unmatched_terms" text,
29
  "Taxonomic_status" text,
30
  "Accepted_name" text,
31
  "Accepted_name_author" text,
32
  "Accepted_name_rank" text,
33
  "Accepted_name_url" text,
34
  "Accepted_name_species" text,
35
  "Accepted_name_family" text,
36
  "Selected" text,
37
  "Source" text,
38
  "Warnings" text,
39
  "Accepted_name_lsid" text,
40 7133 aaronmk
  "Accepted_scientific_name" text,
41 7293 aaronmk
  "Max_score" double precision,
42 5110 aaronmk
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
43
)
44
WITH (
45
  OIDS=FALSE
46
);
47 7132 aaronmk
48 7292 aaronmk
CREATE OR REPLACE FUNCTION tnrs_populate_derived_fields()
49 7134 aaronmk
  RETURNS trigger AS
50
$BODY$
51
BEGIN
52
    new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
53 7139 aaronmk
        NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
54 7134 aaronmk
        , new."Accepted_name"
55
        , new."Accepted_name_author"
56
    ], ' '), '');
57 7293 aaronmk
    new."Max_score" = GREATEST(
58
          new."Overall_score"::double precision
59
        , new."Family_score"::double precision
60
        , new."Genus_score"::double precision
61
        , new."Specific_epithet_score"::double precision
62
    );
63 7134 aaronmk
64
    RETURN new;
65
END;
66
$BODY$
67
  LANGUAGE plpgsql VOLATILE
68
  COST 100;
69
70 7292 aaronmk
CREATE TRIGGER tnrs_populate_derived_fields
71 7134 aaronmk
  BEFORE INSERT OR UPDATE
72
  ON tnrs
73
  FOR EACH ROW
74 7292 aaronmk
  EXECUTE PROCEDURE tnrs_populate_derived_fields();
75 7251 aaronmk
76
77
CREATE OR REPLACE VIEW "tnrs+accepted" AS
78
SELECT
79
tnrs.*
80
, tnrs_accepted."Time_submitted"                  AS "Accepted_name.Time_submitted"
81
, tnrs_accepted."Name_number"                     AS "Accepted_name.Name_number"
82
, tnrs_accepted."Name_submitted"                  AS "Accepted_name.Name_submitted"
83
, tnrs_accepted."Overall_score"                   AS "Accepted_name.Overall_score"
84
, tnrs_accepted."Name_matched"                    AS "Accepted_name.Name_matched"
85
, tnrs_accepted."Name_matched_rank"               AS "Accepted_name.Name_matched_rank"
86
, tnrs_accepted."Name_score"                      AS "Accepted_name.Name_score"
87
, tnrs_accepted."Name_matched_author"             AS "Accepted_name.Name_matched_author"
88
, tnrs_accepted."Name_matched_url"                AS "Accepted_name.Name_matched_url"
89
, tnrs_accepted."Author_matched"                  AS "Accepted_name.Author_matched"
90
, tnrs_accepted."Author_score"                    AS "Accepted_name.Author_score"
91
, tnrs_accepted."Family_matched"                  AS "Accepted_name.Family_matched"
92
, tnrs_accepted."Family_score"                    AS "Accepted_name.Family_score"
93
, tnrs_accepted."Name_matched_accepted_family"    AS "Accepted_name.Name_matched_accepted_family"
94
, tnrs_accepted."Genus_matched"                   AS "Accepted_name.Genus_matched"
95
, tnrs_accepted."Genus_score"                     AS "Accepted_name.Genus_score"
96
, tnrs_accepted."Specific_epithet_matched"        AS "Accepted_name.Specific_epithet_matched"
97
, tnrs_accepted."Specific_epithet_score"          AS "Accepted_name.Specific_epithet_score"
98
, tnrs_accepted."Infraspecific_rank"              AS "Accepted_name.Infraspecific_rank"
99
, tnrs_accepted."Infraspecific_epithet_matched"   AS "Accepted_name.Infraspecific_epithet_matched"
100
, tnrs_accepted."Infraspecific_epithet_score"     AS "Accepted_name.Infraspecific_epithet_score"
101
, tnrs_accepted."Infraspecific_rank_2"            AS "Accepted_name.Infraspecific_rank_2"
102
, tnrs_accepted."Infraspecific_epithet_2_matched" AS "Accepted_name.Infraspecific_epithet_2_matched"
103
, tnrs_accepted."Infraspecific_epithet_2_score"   AS "Accepted_name.Infraspecific_epithet_2_score"
104
, tnrs_accepted."Annotations"                     AS "Accepted_name.Annotations"
105
, tnrs_accepted."Unmatched_terms"                 AS "Accepted_name.Unmatched_terms"
106
, tnrs_accepted."Taxonomic_status"                AS "Accepted_name.Taxonomic_status"
107
, tnrs_accepted."Accepted_name"                   AS "Accepted_name.Accepted_name"
108
, tnrs_accepted."Accepted_name_author"            AS "Accepted_name.Accepted_name_author"
109
, tnrs_accepted."Accepted_name_rank"              AS "Accepted_name.Accepted_name_rank"
110
, tnrs_accepted."Accepted_name_url"               AS "Accepted_name.Accepted_name_url"
111
, tnrs_accepted."Accepted_name_species"           AS "Accepted_name.Accepted_name_species"
112
, tnrs_accepted."Accepted_name_family"            AS "Accepted_name.Accepted_name_family"
113
, tnrs_accepted."Selected"                        AS "Accepted_name.Selected"
114
, tnrs_accepted."Source"                          AS "Accepted_name.Source"
115
, tnrs_accepted."Warnings"                        AS "Accepted_name.Warnings"
116
, tnrs_accepted."Accepted_name_lsid"              AS "Accepted_name.Accepted_name_lsid"
117
, tnrs_accepted."Accepted_scientific_name"        AS "Accepted_name.Accepted_scientific_name"
118 7293 aaronmk
, tnrs_accepted."Max_score"                       AS "Accepted_name.Max_score"
119 7251 aaronmk
FROM tnrs
120
LEFT JOIN tnrs tnrs_accepted ON
121
tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"
122
;