| 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 |
|
|
CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
|
| 49 |
|
|
)
|
| 50 |
|
|
WITH (
|
| 51 |
|
|
OIDS=FALSE
|
| 52 |
|
|
);
|
| 53 |
5797
|
aaronmk
|
COMMENT ON TABLE tnrs
|
| 54 |
5526
|
aaronmk
|
IS 'tnrs_accepted_names sorts accepted names first (note that false sorts before true). Accepted names are defined as names that scrub to themselves.
|
| 55 |
5185
|
aaronmk
|
|
| 56 |
5526
|
aaronmk
|
Accepted names must be processed before any names that resolve to them, because the entry for the accepted name contains all the ranks parsed out but the resolved name of another entry contains just some ranks and the taxonomic name. Column-based import will do this automatically when the total # of rows is <= the partition_size (because _taxonconcept_set_matched_concept_id()''s accepted taxonconcept is created after the main taxonconcept), but TNRS has more rows than this so sorting is needed to ensure that all the accepted names are processed in the first partitions.';
|
| 57 |
|
|
|
| 58 |
5185
|
aaronmk
|
CREATE INDEX tnrs_accepted_names
|
| 59 |
|
|
ON tnrs
|
| 60 |
|
|
USING btree
|
| 61 |
5799
|
aaronmk
|
((NOT "Name_submitted" = NULLIF(:schema.array_to_string(ARRAY[NULLIF("Accepted_name_family", 'Unknown'::text), "Accepted_name", "Accepted_name_author"], ' '::text), ''::text)) , "Name_submitted" );
|
| 62 |
5185
|
aaronmk
|
ALTER TABLE tnrs CLUSTER ON tnrs_accepted_names;
|
