CREATE TABLE tnrs
(
  "Time_submitted" timestamp with time zone,
  "Name_number" text,
  "Name_submitted" text NOT NULL,
  "Overall_score" text,
  "Name_matched" text,
  "Name_matched_rank" text,
  "Name_score" text,
  "Name_matched_author" text,
  "Name_matched_url" text,
  "Author_matched" text,
  "Author_score" text,
  "Family_matched" text,
  "Family_score" text,
  "Name_matched_accepted_family" text,
  "Genus_matched" text,
  "Genus_score" text,
  "Specific_epithet_matched" text,
  "Specific_epithet_score" text,
  "Infraspecific_rank" text,
  "Infraspecific_epithet_matched" text,
  "Infraspecific_epithet_score" text,
  "Infraspecific_rank_2" text,
  "Infraspecific_epithet_2_matched" text,
  "Infraspecific_epithet_2_score" text,
  "Annotations" text,
  "Unmatched_terms" text,
  "Taxonomic_status" text,
  "Accepted_name" text,
  "Accepted_name_author" text,
  "Accepted_name_rank" text,
  "Accepted_name_url" text,
  "Accepted_name_species" text,
  "Accepted_name_family" text,
  "Selected" text,
  "Source" text,
  "Warnings" text,
  "Accepted_name_lsid" text,
  "Accepted_scientific_name" text,
  CONSTRAINT tnrs_pkey PRIMARY KEY ("Name_submitted" )
)
WITH (
  OIDS=FALSE
);

CREATE OR REPLACE FUNCTION tnrs_populate_accepted_scientific_name()
  RETURNS trigger AS
$BODY$
BEGIN
    new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
        NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
        , new."Accepted_name"
        , new."Accepted_name_author"
    ], ' '), '');
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER tnrs_populate_accepted_scientific_name
  BEFORE INSERT OR UPDATE
  ON tnrs
  FOR EACH ROW
  EXECUTE PROCEDURE tnrs_populate_accepted_scientific_name();