CREATE OR REPLACE FUNCTION array_to_string(anyarray, text)
  RETURNS text AS
$BODY$
SELECT pg_catalog.array_to_string($1, $2)
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

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 VIEW "tnrs+accepted" AS
SELECT
tnrs.*
, tnrs_accepted."Time_submitted"                  AS "Accepted_name.Time_submitted"
, tnrs_accepted."Name_number"                     AS "Accepted_name.Name_number"
, tnrs_accepted."Name_submitted"                  AS "Accepted_name.Name_submitted"
, tnrs_accepted."Overall_score"                   AS "Accepted_name.Overall_score"
, tnrs_accepted."Name_matched"                    AS "Accepted_name.Name_matched"
, tnrs_accepted."Name_matched_rank"               AS "Accepted_name.Name_matched_rank"
, tnrs_accepted."Name_score"                      AS "Accepted_name.Name_score"
, tnrs_accepted."Name_matched_author"             AS "Accepted_name.Name_matched_author"
, tnrs_accepted."Name_matched_url"                AS "Accepted_name.Name_matched_url"
, tnrs_accepted."Author_matched"                  AS "Accepted_name.Author_matched"
, tnrs_accepted."Author_score"                    AS "Accepted_name.Author_score"
, tnrs_accepted."Family_matched"                  AS "Accepted_name.Family_matched"
, tnrs_accepted."Family_score"                    AS "Accepted_name.Family_score"
, tnrs_accepted."Name_matched_accepted_family"    AS "Accepted_name.Name_matched_accepted_family"
, tnrs_accepted."Genus_matched"                   AS "Accepted_name.Genus_matched"
, tnrs_accepted."Genus_score"                     AS "Accepted_name.Genus_score"
, tnrs_accepted."Specific_epithet_matched"        AS "Accepted_name.Specific_epithet_matched"
, tnrs_accepted."Specific_epithet_score"          AS "Accepted_name.Specific_epithet_score"
, tnrs_accepted."Infraspecific_rank"              AS "Accepted_name.Infraspecific_rank"
, tnrs_accepted."Infraspecific_epithet_matched"   AS "Accepted_name.Infraspecific_epithet_matched"
, tnrs_accepted."Infraspecific_epithet_score"     AS "Accepted_name.Infraspecific_epithet_score"
, tnrs_accepted."Infraspecific_rank_2"            AS "Accepted_name.Infraspecific_rank_2"
, tnrs_accepted."Infraspecific_epithet_2_matched" AS "Accepted_name.Infraspecific_epithet_2_matched"
, tnrs_accepted."Infraspecific_epithet_2_score"   AS "Accepted_name.Infraspecific_epithet_2_score"
, tnrs_accepted."Annotations"                     AS "Accepted_name.Annotations"
, tnrs_accepted."Unmatched_terms"                 AS "Accepted_name.Unmatched_terms"
, tnrs_accepted."Taxonomic_status"                AS "Accepted_name.Taxonomic_status"
, tnrs_accepted."Accepted_name"                   AS "Accepted_name.Accepted_name"
, tnrs_accepted."Accepted_name_author"            AS "Accepted_name.Accepted_name_author"
, tnrs_accepted."Accepted_name_rank"              AS "Accepted_name.Accepted_name_rank"
, tnrs_accepted."Accepted_name_url"               AS "Accepted_name.Accepted_name_url"
, tnrs_accepted."Accepted_name_species"           AS "Accepted_name.Accepted_name_species"
, tnrs_accepted."Accepted_name_family"            AS "Accepted_name.Accepted_name_family"
, tnrs_accepted."Selected"                        AS "Accepted_name.Selected"
, tnrs_accepted."Source"                          AS "Accepted_name.Source"
, tnrs_accepted."Warnings"                        AS "Accepted_name.Warnings"
, tnrs_accepted."Accepted_name_lsid"              AS "Accepted_name.Accepted_name_lsid"
FROM tnrs
LEFT JOIN tnrs tnrs_accepted ON
tnrs_accepted."Name_submitted" = NULLIF(array_to_string(ARRAY[
    NULLIF(tnrs."Accepted_name_family", 'Unknown')
    , tnrs."Accepted_name"
    , tnrs."Accepted_name_author"
], ' '), '')
;