Project

General

Profile

1
CREATE OR REPLACE FUNCTION concat_delim(IN delim text, VARIADIC text[])
2
  RETURNS text AS
3
$BODY$
4
SELECT NULLIF(array_to_string($2, $1), ''::text)
5
$BODY$
6
  LANGUAGE sql IMMUTABLE
7
  COST 100;
8
COMMENT ON FUNCTION concat_delim(text, text[]) IS 'Similar to concat() but separates elements with a delimeter';
9

    
10

    
11
CREATE TABLE tnrs
12
(
13
  "Time_submitted" timestamp with time zone,
14
  "Name_number" text,
15
  "Name_submitted" text NOT NULL,
16
  "Overall_score" text,
17
  "Name_matched" text,
18
  "Name_matched_rank" text,
19
  "Name_score" text,
20
  "Name_matched_author" text,
21
  "Name_matched_url" text,
22
  "Author_matched" text,
23
  "Author_score" text,
24
  "Family_matched" text,
25
  "Family_score" text,
26
  "Name_matched_accepted_family" text,
27
  "Genus_matched" text,
28
  "Genus_score" text,
29
  "Specific_epithet_matched" text,
30
  "Specific_epithet_score" text,
31
  "Infraspecific_rank" text,
32
  "Infraspecific_epithet_matched" text,
33
  "Infraspecific_epithet_score" text,
34
  "Infraspecific_rank_2" text,
35
  "Infraspecific_epithet_2_matched" text,
36
  "Infraspecific_epithet_2_score" text,
37
  "Annotations" text,
38
  "Unmatched_terms" text,
39
  "Taxonomic_status" text,
40
  "Accepted_name" text,
41
  "Accepted_name_author" text,
42
  "Accepted_name_rank" text,
43
  "Accepted_name_url" text,
44
  "Accepted_name_species" text,
45
  "Accepted_name_family" text,
46
  "Selected" text,
47
  "Source" text,
48
  "Warnings" text,
49
  "Accepted_name_lsid" text,
50
  "Accepted_scientific_name" text,
51
  "Max_score" double precision,
52
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
53
)
54
WITH (
55
  OIDS=FALSE
56
);
57

    
58
CREATE OR REPLACE FUNCTION tnrs_populate_derived_fields()
59
  RETURNS trigger AS
60
$BODY$
61
BEGIN
62
    new."Accepted_scientific_name" = concat_delim(' '
63
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
64
        , new."Accepted_name"
65
        , new."Accepted_name_author"
66
    );
67
    new."Max_score" = GREATEST(
68
          new."Overall_score"::double precision
69
        , new."Family_score"::double precision
70
        , new."Genus_score"::double precision
71
        , new."Specific_epithet_score"::double precision
72
    );
73
    
74
    RETURN new;
75
END;
76
$BODY$
77
  LANGUAGE plpgsql VOLATILE
78
  COST 100;
79

    
80
CREATE TRIGGER tnrs_populate_derived_fields
81
  BEFORE INSERT OR UPDATE
82
  ON tnrs
83
  FOR EACH ROW
84
  EXECUTE PROCEDURE tnrs_populate_derived_fields();
85

    
86

    
87
CREATE OR REPLACE VIEW "MatchedTaxon" AS
88
SELECT
89
  "Time_submitted" AS "*Name_matched.Time_submitted"
90
, "Name_submitted" AS "concatenatedScientificName"
91
, "Name_matched" AS "matchedTaxonName"
92
, "Name_matched_rank" AS "matchedTaxonRank"
93
, "Name_score" AS "*Name_matched.Name_score"
94
, "Name_matched_author" AS "matchedScientificNameAuthorship"
95
, "Name_matched_url" AS "matchedScientificNameID"
96
, "Author_score" AS "*Name_matched.Author_score"
97
, "Family_score" AS "matchedFamilyConfidence_fraction"
98
, "Name_matched_accepted_family" AS family
99
, "Genus_matched" AS genus
100
, "Genus_score" AS "matchedGenusConfidence_fraction"
101
, "Specific_epithet_matched" AS "matchedSpecificEpithet"
102
, "Specific_epithet_score" AS "matchedSpeciesConfidence_fraction"
103
, "Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet"
104
, "Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score"
105
, "Annotations" AS "identificationQualifier"
106
, "Unmatched_terms" AS "morphospeciesSuffix"
107
, "Taxonomic_status" AS "taxonomicStatus"
108
, "Accepted_name" AS "acceptedTaxonName"
109
, "Accepted_name_author" AS "acceptedScientificNameAuthorship"
110
, "Accepted_name_rank" AS "acceptedTaxonRank"
111
, "Accepted_name_url" AS "acceptedScientificNameID"
112
, "Accepted_name_species" AS "*Name_matched.Accepted_name_species"
113
, "Accepted_name_family" AS "acceptedFamily"
114
, "Selected" AS "*Name_matched.Selected"
115
, "Source" AS "*Name_matched.Source"
116
, "Warnings" AS "*Name_matched.Warnings"
117
, "Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid"
118
, "Accepted_scientific_name" AS "acceptedScientificName"
119
, "Max_score" AS "matchedTaxonConfidence_fraction"
120
FROM tnrs;
121

    
122
CREATE OR REPLACE VIEW "AcceptedTaxon" AS
123
SELECT
124
  "Time_submitted" AS "*Accepted_name.Time_submitted"
125
, "Name_submitted" AS "acceptedScientificName"
126
, "Genus_matched" AS "acceptedGenus"
127
, "Specific_epithet_matched" AS "acceptedSpecificEpithet"
128
, "Infraspecific_epithet_matched" AS "acceptedInfraspecificEpithet"
129
, "Annotations" AS "*Accepted_name.Annotations"
130
, "Taxonomic_status" AS "acceptedTaxonomicStatus"
131
, "Selected" AS "*Accepted_name.Selected"
132
, "Source" AS "*Accepted_name.Source"
133
, "Warnings" AS "*Accepted_name.Warnings"
134
, "Accepted_name_lsid" AS "*Accepted_name.Accepted_name_lsid"
135
FROM tnrs;
136

    
137
CREATE OR REPLACE VIEW "ScrubbedTaxon" AS
138
SELECT *
139
FROM "MatchedTaxon"
140
NATURAL LEFT JOIN "AcceptedTaxon"
141
;
(3-3/3)