DECLARE

		"Specific_epithet_is_plant" boolean :=

			WHEN   new."*Infraspecific_epithet_matched"	 IS NOT NULL

				OR new."*Infraspecific_epithet_2_matched" IS NOT NULL

				OR new."*Specific_epithet_score" >= 0.9 -- fuzzy match

				THEN true

			ELSE NULL -- ambiguous

			END);

		never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false);

			-- author disambiguates

		family_is_homonym boolean = NOT never_homonym

			AND "TNRS".family_is_homonym(new."*Family_matched");

		genus_is_homonym  boolean = NOT never_homonym

			AND "TNRS".genus_is_homonym(new."*Genus_matched");

	BEGIN

		/* exclude homonyms because these are not valid matches (TNRS provides a

		name, but the name is not meaningful because it is not unambiguous) */

		new.is_valid_match = new."*Taxonomic_status" != 'Invalid'

			AND COALESCE(CASE

			WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match

				THEN true

			ELSE -- consider genus

				WHEN new."*Genus_score" =  1	   -- exact match

					THEN

					(CASE

					WHEN NOT genus_is_homonym THEN true

					ELSE "Specific_epithet_is_plant"

					END)

				WHEN new."*Genus_score" >= 0.85 -- fuzzy match

					THEN "Specific_epithet_is_plant"

				ELSE NULL -- ambiguous

			END, false);

	END;

	DECLARE

		matched_taxon_name_with_author text = NULLIF(concat_ws(' '

			, NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'),

				new."*Name_matched")

			, NULLIF(new."*Name_matched", 'No suitable matches found.')

			, new."*Name_matched_author"

			), '');

		accepted_taxon_name_with_author text = NULLIF(concat_ws(' '

			, NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'),

				new."*Accepted_name")

			, new."*Accepted_name"

			, new."*Accepted_name_author"

			), '');

	BEGIN

		new.scrubbed_unique_taxon_name = COALESCE(

			accepted_taxon_name_with_author, matched_taxon_name_with_author);

	END;

-- Name: taxon_match__fill_derived(); Type: FUNCTION; Schema: TNRS; Owner: bien

--

CREATE FUNCTION taxon_match__fill_derived() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	new."__accepted_{genus,specific_epithet}" = (SELECT

		regexp_split_to_array("*Accepted_name_species", ' '::text)

		FROM (SELECT new.*) new);

	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT

		"__accepted_{genus,specific_epithet}"[1]

		FROM (SELECT new.*) new);

	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT

		"__accepted_{genus,specific_epithet}"[2]

		FROM (SELECT new.*) new);

	new.__accepted_infraspecific_label = (SELECT

		ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)

		FROM (SELECT new.*) new);

	new."__accepted_infraspecific_{rank,epithet}" = (SELECT

		regexp_split_to_array(__accepted_infraspecific_label, ' '::text)

		FROM (SELECT new.*) new);

	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT

		"__accepted_infraspecific_{rank,epithet}"[1]

		FROM (SELECT new.*) new);

	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT

		"__accepted_infraspecific_{rank,epithet}"[2]

		FROM (SELECT new.*) new);

	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT

		"*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text)

		FROM (SELECT new.*) new);

	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT

		"*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text)

		FROM (SELECT new.*) new);

	new.matched_has_accepted = (SELECT

		"*Accepted_name" IS NOT NULL

		FROM (SELECT new.*) new);

	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT

		("*Genus_matched" || ' '::text) || "*Specific_epithet_matched"

		FROM (SELECT new.*) new);

	RETURN new;

END;

$$;

ALTER FUNCTION "TNRS".taxon_match__fill_derived() OWNER TO bien;

--

-- Name: FUNCTION taxon_match__fill_derived(); Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON FUNCTION taxon_match__fill_derived() IS '

autogenerated, do not edit

to regenerate:

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

    "*Name_number" integer NOT NULL,

    "*Name_submitted" text NOT NULL,

    "*Overall_score" double precision,

    "*Name_matched" text,

    "*Name_matched_rank" text,

    "*Name_score" double precision,

    "*Name_matched_author" text,

    "*Name_matched_url" text,

    "*Author_matched" text,

    "*Author_score" double precision,

    "*Family_matched" text,

    "*Family_score" double precision,

    "*Name_matched_accepted_family" text,

    "*Genus_matched" text,

    "*Genus_score" double precision,

    "*Specific_epithet_matched" text,

    "*Specific_epithet_score" double precision,

    "*Infraspecific_rank" text,

    "*Infraspecific_epithet_matched" text,

    "*Infraspecific_epithet_score" double precision,

    "*Infraspecific_rank_2" text,

    "*Infraspecific_epithet_2_matched" text,

    "*Infraspecific_epithet_2_score" double precision,

    "*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,

    scrubbed_unique_taxon_name text,

    "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text,

    "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text,

    matched_has_accepted boolean,

    "__accepted_{genus,specific_epithet}" text[],

    "[accepted_]genus__@DwC__@vegpath.org" text,

    "[accepted_]specificEpithet__@DwC__@vegpath.org" text,

    __accepted_infraspecific_label text,

    "__accepted_infraspecific_{rank,epithet}" text[],

    "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,

    "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" text,

    "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" text,

    CONSTRAINT "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[1]))),

    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[1]))),

    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),

    CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),

    CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),

    CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))),

    CONSTRAINT "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM (("*Genus_matched" || ' '::text) || "*Specific_epithet_matched")))),

    CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),

    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),

    CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),

    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL))))

whenever columns are renamed:

SELECT util.derived_cols_sync(''"TNRS".taxon_match'');

CLUSTER "TNRS".taxon_match; -- ensure rows are exported in sorted order

-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS '

derived column; = ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$"*Genus_matched" || '' ''::text || "*Specific_epithet_matched"$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS '

derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match.matched_has_accepted; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match.matched_has_accepted IS '

derived column; = "*Accepted_name" IS NOT NULL

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '

derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS '

derived column; = "__accepted_{genus,specific_epithet}"[1]

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS '

derived column; = "__accepted_{genus,specific_epithet}"[2]

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS '

derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '

derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS '

derived column; = "__accepted_infraspecific_{rank,epithet}"[1]

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS '

derived column; = "__accepted_infraspecific_{rank,epithet}"[2]

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS '

derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);

SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);

to rename:

# rename column

# rename CHECK constraint

SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);

';

--

-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: bien

--

CREATE VIEW taxon_best_match AS

 SELECT taxon_match.batch,

    taxon_match.match_num,

    taxon_match."*Name_number",

    taxon_match."*Name_submitted",

    taxon_match."*Overall_score",

    taxon_match."*Name_matched",

    taxon_match."*Name_matched_rank",

    taxon_match."*Name_score",

    taxon_match."*Name_matched_author",

    taxon_match."*Name_matched_url",

    taxon_match."*Author_matched",

    taxon_match."*Author_score",

    taxon_match."*Family_matched",

    taxon_match."*Family_score",

    taxon_match."*Name_matched_accepted_family",

    taxon_match."*Genus_matched",

    taxon_match."*Genus_score",

    taxon_match."*Specific_epithet_matched",

    taxon_match."*Specific_epithet_score",

    taxon_match."*Infraspecific_rank",

    taxon_match."*Infraspecific_epithet_matched",

    taxon_match."*Infraspecific_epithet_score",

    taxon_match."*Infraspecific_rank_2",

    taxon_match."*Infraspecific_epithet_2_matched",

    taxon_match."*Infraspecific_epithet_2_score",

    taxon_match."*Annotations",

    taxon_match."*Unmatched_terms",

    taxon_match."*Taxonomic_status",

    taxon_match."*Accepted_name",

    taxon_match."*Accepted_name_author",

    taxon_match."*Accepted_name_rank",

    taxon_match."*Accepted_name_url",

    taxon_match."*Accepted_name_species",

    taxon_match."*Accepted_name_family",

    taxon_match."*Selected",

    taxon_match."*Source",

    taxon_match."*Warnings",

    taxon_match."*Accepted_name_lsid",

    taxon_match.is_valid_match,

    taxon_match.scrubbed_unique_taxon_name,

    taxon_match."__accepted_{genus,specific_epithet}",

    taxon_match."[accepted_]genus__@DwC__@vegpath.org",

    taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org",

    taxon_match.__accepted_infraspecific_label,

    taxon_match."__accepted_infraspecific_{rank,epithet}",

    taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",

    taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",

    taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",

    taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"

   FROM taxon_match

  WHERE (taxon_match."*Selected" = 'true'::text);

ALTER TABLE "TNRS".taxon_best_match OWNER TO bien;

--

-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: bien

--

COMMENT ON VIEW taxon_best_match IS '

to modify:

SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$

SELECT __

$$);

';

--

 SELECT taxon_best_match.batch,

    taxon_best_match.match_num,

    taxon_best_match."*Name_number",

    taxon_best_match."*Name_submitted",

    taxon_best_match."*Overall_score",

    taxon_best_match."*Name_matched",

    taxon_best_match."*Name_matched_rank",

    taxon_best_match."*Name_score",

    taxon_best_match."*Name_matched_author",

    taxon_best_match."*Name_matched_url",

    taxon_best_match."*Author_matched",

    taxon_best_match."*Author_score",

    taxon_best_match."*Family_matched",

    taxon_best_match."*Family_score",

    taxon_best_match."*Name_matched_accepted_family",

    taxon_best_match."*Genus_matched",

    taxon_best_match."*Genus_score",

    taxon_best_match."*Specific_epithet_matched",

    taxon_best_match."*Specific_epithet_score",

    taxon_best_match."*Infraspecific_rank",

    taxon_best_match."*Infraspecific_epithet_matched",

    taxon_best_match."*Infraspecific_epithet_score",

    taxon_best_match."*Infraspecific_rank_2",

    taxon_best_match."*Infraspecific_epithet_2_matched",

    taxon_best_match."*Infraspecific_epithet_2_score",

    taxon_best_match."*Annotations",

    taxon_best_match."*Unmatched_terms",

    taxon_best_match."*Taxonomic_status",

    taxon_best_match."*Accepted_name",

    taxon_best_match."*Accepted_name_author",

    taxon_best_match."*Accepted_name_rank",

    taxon_best_match."*Accepted_name_url",

    taxon_best_match."*Accepted_name_species",

    taxon_best_match."*Accepted_name_family",

    taxon_best_match."*Selected",

    taxon_best_match."*Source",

    taxon_best_match."*Warnings",

    taxon_best_match."*Accepted_name_lsid",

    taxon_best_match.is_valid_match,

    taxon_best_match.scrubbed_unique_taxon_name,

    taxon_best_match."__accepted_{genus,specific_epithet}",

    taxon_best_match."[accepted_]genus__@DwC__@vegpath.org",

    taxon_best_match."[accepted_]specificEpithet__@DwC__@vegpath.org",

    taxon_best_match.__accepted_infraspecific_label,

    taxon_best_match."__accepted_infraspecific_{rank,epithet}",

    taxon_best_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",

    taxon_best_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",

    taxon_best_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",

    taxon_best_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",

    map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus",

            WHEN (taxon_best_match."*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name_family", taxon_best_match."*Unmatched_terms")

            WHEN (taxon_best_match."*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name", taxon_best_match."*Unmatched_terms")

            ELSE taxon_best_match."*Accepted_name_species"

   FROM taxon_best_match;

    "datasetURL" text DEFAULT 'http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html'::text NOT NULL,

 SELECT "MatchedTaxon".batch,

    "MatchedTaxon".match_num,

    "MatchedTaxon"."*Name_number",

    "MatchedTaxon"."*Name_submitted",

    "MatchedTaxon"."*Overall_score",

    "MatchedTaxon"."*Name_matched",

    "MatchedTaxon"."*Name_matched_rank",

    "MatchedTaxon"."*Name_score",

    "MatchedTaxon"."*Name_matched_author",

    "MatchedTaxon"."*Name_matched_url",

    "MatchedTaxon"."*Author_matched",

    "MatchedTaxon"."*Author_score",

    "MatchedTaxon"."*Family_matched",

    "MatchedTaxon"."*Family_score",

    "MatchedTaxon"."*Name_matched_accepted_family",

    "MatchedTaxon"."*Genus_matched",

    "MatchedTaxon"."*Genus_score",

    "MatchedTaxon"."*Specific_epithet_matched",

    "MatchedTaxon"."*Specific_epithet_score",

    "MatchedTaxon"."*Infraspecific_rank",

    "MatchedTaxon"."*Infraspecific_epithet_matched",

    "MatchedTaxon"."*Infraspecific_epithet_score",

    "MatchedTaxon"."*Infraspecific_rank_2",

    "MatchedTaxon"."*Infraspecific_epithet_2_matched",

    "MatchedTaxon"."*Infraspecific_epithet_2_score",

    "MatchedTaxon"."*Annotations",

    "MatchedTaxon"."*Unmatched_terms",

    "MatchedTaxon"."*Taxonomic_status",

    "MatchedTaxon"."*Accepted_name",

    "MatchedTaxon"."*Accepted_name_author",

    "MatchedTaxon"."*Accepted_name_rank",

    "MatchedTaxon"."*Accepted_name_url",

    "MatchedTaxon"."*Accepted_name_species",

    "MatchedTaxon"."*Accepted_name_family",

    "MatchedTaxon"."*Selected",

    "MatchedTaxon"."*Source",

    "MatchedTaxon"."*Warnings",

    "MatchedTaxon"."*Accepted_name_lsid",

    "MatchedTaxon".is_valid_match,

    "MatchedTaxon".scrubbed_unique_taxon_name,

    "MatchedTaxon"."__accepted_{genus,specific_epithet}",

    "MatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org",

    "MatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org",

    "MatchedTaxon".__accepted_infraspecific_label,

    "MatchedTaxon"."__accepted_infraspecific_{rank,epithet}",

    "MatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",

    "MatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",

    "MatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",

    "MatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",

  WHERE "MatchedTaxon".is_valid_match;

 SELECT taxon_match."*Name_number" AS "Name_number",

    taxon_match."*Name_submitted" AS "Name_submitted",

    taxon_match."*Overall_score" AS "Overall_score",

    taxon_match."*Name_matched" AS "Name_matched",

    taxon_match."*Name_matched_rank" AS "Name_matched_rank",

    taxon_match."*Name_score" AS "Name_score",

    taxon_match."*Name_matched_author" AS "Name_matched_author",

    taxon_match."*Name_matched_url" AS "Name_matched_url",

    taxon_match."*Author_matched" AS "Author_matched",

    taxon_match."*Author_score" AS "Author_score",

    taxon_match."*Family_matched" AS "Family_matched",

    taxon_match."*Family_score" AS "Family_score",

    taxon_match."*Name_matched_accepted_family" AS "Name_matched_accepted_family",

    taxon_match."*Genus_matched" AS "Genus_matched",

    taxon_match."*Genus_score" AS "Genus_score",

    taxon_match."*Specific_epithet_matched" AS "Specific_epithet_matched",

    taxon_match."*Specific_epithet_score" AS "Specific_epithet_score",

    taxon_match."*Infraspecific_rank" AS "Infraspecific_rank",

    taxon_match."*Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched",

    taxon_match."*Infraspecific_epithet_score" AS "Infraspecific_epithet_score",

    taxon_match."*Infraspecific_rank_2" AS "Infraspecific_rank_2",

    taxon_match."*Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched",

    taxon_match."*Infraspecific_epithet_2_score" AS "Infraspecific_epithet_2_score",

    taxon_match."*Annotations" AS "Annotations",

    taxon_match."*Unmatched_terms" AS "Unmatched_terms",

    taxon_match."*Taxonomic_status" AS "Taxonomic_status",

    taxon_match."*Accepted_name" AS "Accepted_name",

    taxon_match."*Accepted_name_author" AS "Accepted_name_author",

    taxon_match."*Accepted_name_rank" AS "Accepted_name_rank",

    taxon_match."*Accepted_name_url" AS "Accepted_name_url",

    taxon_match."*Accepted_name_species" AS "Accepted_name_species",

    taxon_match."*Accepted_name_family" AS "Accepted_name_family",

    taxon_match."*Selected" AS "Selected",

    taxon_match."*Source" AS "Source",

    taxon_match."*Warnings" AS "Warnings",

    taxon_match."*Accepted_name_lsid" AS "Accepted_name_lsid"

 SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,

    taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,

    COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,

    taxon_match."*Genus_matched" AS scrubbed_genus,

    taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,

    taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,

    taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,

    taxon_match."*Name_matched_author" AS scrubbed_author,

    taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,

    (taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author

    "ValidMatchedTaxon".batch,

    "ValidMatchedTaxon".match_num,

    "ValidMatchedTaxon"."*Name_number",

    "ValidMatchedTaxon"."*Name_submitted",

    "ValidMatchedTaxon"."*Overall_score",

    "ValidMatchedTaxon"."*Name_matched",

    "ValidMatchedTaxon"."*Name_matched_rank",

    "ValidMatchedTaxon"."*Name_score",

    "ValidMatchedTaxon"."*Name_matched_author",

    "ValidMatchedTaxon"."*Name_matched_url",

    "ValidMatchedTaxon"."*Author_matched",

    "ValidMatchedTaxon"."*Author_score",

    "ValidMatchedTaxon"."*Family_matched",

    "ValidMatchedTaxon"."*Family_score",

    "ValidMatchedTaxon"."*Name_matched_accepted_family",

    "ValidMatchedTaxon"."*Genus_matched",

    "ValidMatchedTaxon"."*Genus_score",

    "ValidMatchedTaxon"."*Specific_epithet_matched",

    "ValidMatchedTaxon"."*Specific_epithet_score",

    "ValidMatchedTaxon"."*Infraspecific_rank",

    "ValidMatchedTaxon"."*Infraspecific_epithet_matched",

    "ValidMatchedTaxon"."*Infraspecific_epithet_score",

    "ValidMatchedTaxon"."*Infraspecific_rank_2",

    "ValidMatchedTaxon"."*Infraspecific_epithet_2_matched",

    "ValidMatchedTaxon"."*Infraspecific_epithet_2_score",

    "ValidMatchedTaxon"."*Annotations",

    "ValidMatchedTaxon"."*Unmatched_terms",

    "ValidMatchedTaxon"."*Taxonomic_status",

    "ValidMatchedTaxon"."*Accepted_name",

    "ValidMatchedTaxon"."*Accepted_name_author",

    "ValidMatchedTaxon"."*Accepted_name_rank",

    "ValidMatchedTaxon"."*Accepted_name_url",

    "ValidMatchedTaxon"."*Accepted_name_species",

    "ValidMatchedTaxon"."*Accepted_name_family",

    "ValidMatchedTaxon"."*Selected",

    "ValidMatchedTaxon"."*Source",

    "ValidMatchedTaxon"."*Warnings",

    "ValidMatchedTaxon"."*Accepted_name_lsid",

    "ValidMatchedTaxon".is_valid_match,

    "ValidMatchedTaxon"."__accepted_{genus,specific_epithet}",

    "ValidMatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org",

    "ValidMatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org",

    "ValidMatchedTaxon".__accepted_infraspecific_label,

    "ValidMatchedTaxon"."__accepted_infraspecific_{rank,epithet}",

    "ValidMatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",

    "ValidMatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",

    "ValidMatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",

    "ValidMatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",

            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."*Unmatched_terms")

            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."*Unmatched_terms")

1	aggregator	http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html

2014-07-21 16:31:50.48692-07	2014-07-21 16:31:50.48692-07	2014-07-21 16:31:50.48692-07	\N

COPY taxon_match (batch, match_num, "*Name_number", "*Name_submitted", "*Overall_score", "*Name_matched", "*Name_matched_rank", "*Name_score", "*Name_matched_author", "*Name_matched_url", "*Author_matched", "*Author_score", "*Family_matched", "*Family_score", "*Name_matched_accepted_family", "*Genus_matched", "*Genus_score", "*Specific_epithet_matched", "*Specific_epithet_score", "*Infraspecific_rank", "*Infraspecific_epithet_matched", "*Infraspecific_epithet_score", "*Infraspecific_rank_2", "*Infraspecific_epithet_2_matched", "*Infraspecific_epithet_2_score", "*Annotations", "*Unmatched_terms", "*Taxonomic_status", "*Accepted_name", "*Accepted_name_author", "*Accepted_name_rank", "*Accepted_name_url", "*Accepted_name_species", "*Accepted_name_family", "*Selected", "*Source", "*Warnings", "*Accepted_name_lsid", is_valid_match, scrubbed_unique_taxon_name, "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", matched_has_accepted, "__accepted_{genus,specific_epithet}", "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org", __accepted_infraspecific_label, "__accepted_infraspecific_{rank,epithet}", "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org") FROM stdin;

2014-07-21 16:31:50.48692-07	0	0	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	\N	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Accepted	Compositae	\N	family	http://www.theplantlist.org/1.1/browse/A/Compositae/	\N	\N	true	tpl	 [Ambiguous match] 	\N	t	Compositae	\N	Compositae	t	\N	\N	\N	\N	\N	\N	\N	Compositae

2014-07-21 16:31:50.48692-07	1	0	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	\N	\N	\N	\N	Compositae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Accepted	Compositae	\N	family	\N	\N	\N	false	tpl	 [Ambiguous match] 	\N	t	Compositae	\N	Compositae	t	\N	\N	\N	\N	\N	\N	\N	Compositae

2014-07-21 16:31:50.48692-07	2	0	Compositae indet. sp.1	0.900000000000000022	Compositae	family	1	Giseke	http://www.tropicos.org/Name/50255940	\N	\N	Compositae	1	Compositae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	indet. sp.1	Synonym	Asteraceae	Bercht. & J. Presl	family	http://www.tropicos.org/Name/50307371	\N	Asteraceae	false	tropicos	 	\N	t	Asteraceae Bercht. & J. Presl	\N	Compositae Giseke	t	\N	\N	\N	\N	\N	\N	\N	Asteraceae Bercht. & J. Presl

2014-07-21 16:31:50.48692-07	3	1	Fabaceae Boyle#6500	0.839999999999999969	Fagaceae	family	0.939999999999999947	\N	;http://plants.usda.gov/java/nameSearch	\N	\N	Fagaceae	0.880000000000000004	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fagaceae	\N	family	;http://plants.usda.gov/java/nameSearch	\N	\N	true	tpl;usda	 	\N	f	Fagaceae	\N	Fagaceae	t	\N	\N	\N	\N	\N	\N	\N	Fagaceae

2014-07-21 16:31:50.48692-07	4	1	Fabaceae Boyle#6500	0.900000000000000022	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	false	tropicos	 	\N	t	Fabaceae Lindl.	\N	Fabaceae Lindl.	t	\N	\N	\N	\N	\N	\N	\N	Fabaceae Lindl.

2014-07-21 16:31:50.48692-07	5	1	Fabaceae Boyle#6500	0.839999999999999969	Fagaceae	family	0.939999999999999947	Dumort.	http://www.tropicos.org/Name/42000062	\N	\N	Fagaceae	0.880000000000000004	Fagaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fagaceae	Dumort.	family	http://www.tropicos.org/Name/42000062	\N	Fagaceae	false	tropicos	 	\N	f	Fagaceae Dumort.	\N	Fagaceae Dumort.	t	\N	\N	\N	\N	\N	\N	\N	Fagaceae Dumort.

2014-07-21 16:31:50.48692-07	6	1	Fabaceae Boyle#6500	0.67000000000000004	Ficaceae	family	0.770000000000000018	Bercht. & J. Presl	http://www.tropicos.org/Name/100353631	\N	\N	Ficaceae	0.75	Ficaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Ficaceae Bercht. & J. Presl	\N	Ficaceae Bercht. & J. Presl	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	7	1	Fabaceae Boyle#6500	0.67000000000000004	Fucaceae	family	0.770000000000000018	\N	http://www.tropicos.org/Name/100371040	\N	\N	Fucaceae	0.75	Fucaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Fucaceae	\N	Fucaceae	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	8	1	Fabaceae Boyle#6500	0.900000000000000022	Fabaceae	family	1	\N	http://plants.usda.gov/java/nameSearch	\N	\N	Fabaceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	Boyle#6500	Accepted	Fabaceae	\N	family	http://plants.usda.gov/java/nameSearch	\N	\N	false	usda	 	\N	t	Fabaceae	\N	Fabaceae	t	\N	\N	\N	\N	\N	\N	\N	Fabaceae

2014-07-21 16:31:50.48692-07	9	2	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga	\N	Inga	t	\N	\N	\N	\N	\N	\N	\N	Inga

2014-07-21 16:31:50.48692-07	10	2	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	false	tropicos	 	\N	t	Fabaceae Inga Mill.	\N	Inga Mill.	t	\N	\N	\N	\N	\N	\N	\N	Inga Mill.

2014-07-21 16:31:50.48692-07	11	2	Fabaceae Inga "fuzzy leaf"	0.900000000000000022	Inga	genus	1	Scop.	http://www.tropicos.org/Name/50215121	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	"fuzzy leaf"	Illegitimate	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	t	Fabaceae Inga Scop.	\N	Inga Scop.	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	12	3	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	\N	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Accepted	Inga	\N	genus	http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA	\N	Fabaceae	true	tpl;usda	 	\N	t	Fabaceae Inga	\N	Inga	t	\N	\N	\N	\N	\N	\N	\N	Inga

2014-07-21 16:31:50.48692-07	13	3	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	Mill.	http://www.tropicos.org/Name/40031040	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Accepted	Inga	Mill.	genus	http://www.tropicos.org/Name/40031040	\N	Fabaceae	false	tropicos	 	\N	t	Fabaceae Inga Mill.	\N	Inga Mill.	t	\N	\N	\N	\N	\N	\N	\N	Inga Mill.

2014-07-21 16:31:50.48692-07	14	3	Fabaceae Inga sp.3	0.900000000000000022	Inga	genus	1	Scop.	http://www.tropicos.org/Name/50215121	\N	\N	Fabaceae	1	Fabaceae	Inga	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	sp.3	Illegitimate	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	t	Fabaceae Inga Scop.	\N	Inga Scop.	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	15	4	Fabaceae unknown #2	0.839999999999999969	Fagaceae	family	0.939999999999999947	\N	;http://plants.usda.gov/java/nameSearch	\N	\N	Fagaceae	0.880000000000000004	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fagaceae	\N	family	;http://plants.usda.gov/java/nameSearch	\N	\N	true	tpl;usda	 	\N	f	Fagaceae	\N	Fagaceae	t	\N	\N	\N	\N	\N	\N	\N	Fagaceae

2014-07-21 16:31:50.48692-07	16	4	Fabaceae unknown #2	0.900000000000000022	Fabaceae	family	1	Lindl.	http://www.tropicos.org/Name/42000184	\N	\N	Fabaceae	1	Fabaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fabaceae	Lindl.	family	http://www.tropicos.org/Name/42000184	\N	Fabaceae	false	tropicos	 	\N	t	Fabaceae Lindl.	\N	Fabaceae Lindl.	t	\N	\N	\N	\N	\N	\N	\N	Fabaceae Lindl.

2014-07-21 16:31:50.48692-07	17	4	Fabaceae unknown #2	0.839999999999999969	Fagaceae	family	0.939999999999999947	Dumort.	http://www.tropicos.org/Name/42000062	\N	\N	Fagaceae	0.880000000000000004	Fagaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fagaceae	Dumort.	family	http://www.tropicos.org/Name/42000062	\N	Fagaceae	false	tropicos	 	\N	f	Fagaceae Dumort.	\N	Fagaceae Dumort.	t	\N	\N	\N	\N	\N	\N	\N	Fagaceae Dumort.

2014-07-21 16:31:50.48692-07	18	4	Fabaceae unknown #2	0.67000000000000004	Ficaceae	family	0.770000000000000018	Bercht. & J. Presl	http://www.tropicos.org/Name/100353631	\N	\N	Ficaceae	0.75	Ficaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Ficaceae Bercht. & J. Presl	\N	Ficaceae Bercht. & J. Presl	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	19	4	Fabaceae unknown #2	0.67000000000000004	Fucaceae	family	0.770000000000000018	\N	http://www.tropicos.org/Name/100371040	\N	\N	Fucaceae	0.75	Fucaceae	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	No opinion	\N	\N	\N	\N	\N	\N	false	tropicos	 	\N	f	Fucaceae	\N	Fucaceae	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	20	4	Fabaceae unknown #2	0.900000000000000022	Fabaceae	family	1	\N	http://plants.usda.gov/java/nameSearch	\N	\N	Fabaceae	1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	unknown #2	Accepted	Fabaceae	\N	family	http://plants.usda.gov/java/nameSearch	\N	\N	false	usda	 	\N	t	Fabaceae	\N	Fabaceae	t	\N	\N	\N	\N	\N	\N	\N	Fabaceae

2014-07-21 16:31:50.48692-07	21	5	Fam_indet. Boyle#6501	0	No suitable matches found.	\N	0	\N	\N	\N	0	\N	0	\N	\N	0	\N	0	\N	\N	0	\N	\N	0	\N	\N	\N	\N	\N	\N	\N	\N	\N	true	\N	 	\N	f	\N	\N	No suitable matches found.	f	\N	\N	\N	\N	\N	\N	\N	\N

2014-07-21 16:31:50.48692-07	22	6	Poa annua	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.	Poa annua	Poa annua L.	t	{Poa,annua}	Poa	annua	\N	\N	\N	\N	Poa annua L.

2014-07-21 16:31:50.48692-07	23	6	Poa annua	1	Poa annua	species	1	Cham. & Schltdl.	http://www.theplantlist.org/tpl1.1/record/kew-435195	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	false	tpl	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua Cham. & Schltdl.	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	24	7	Poa annua L.	1	Poa annua	species	1	L.	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	L.	1	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Accepted	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN	Poa annua	Poaceae	true	tpl;tropicos;usda	 	\N	t	Poaceae Poa annua L.	Poa annua	Poa annua L.	t	{Poa,annua}	Poa	annua	\N	\N	\N	\N	Poa annua L.

2014-07-21 16:31:50.48692-07	25	7	Poa annua L.	0.800000000000000044	Poa annua	species	1	Cham. & Schltdl.	http://www.theplantlist.org/tpl1.1/record/kew-435195	Cham. & Schltdl.	0	\N	\N	Poaceae	Poa	1	annua	1	\N	\N	\N	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	false	tpl	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua Cham. & Schltdl.	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	26	8	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	fo.	1	Sennen	http://www.theplantlist.org/tpl1.1/record/tro-50267771	\N	\N	\N	\N	\N	Poa	1	annua	1	fo.	lanuginosa	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.	Poa annua	Poa annua fo. lanuginosa Sennen	t	{Poa,annua}	Poa	annua	\N	\N	\N	\N	Poa annua L.

2014-07-21 16:31:50.48692-07	27	8	Poa annua fo. lanuginosa	1	Poa annua fo. lanuginosa	forma	1	Sennen	http://www.tropicos.org/Name/50267771	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	fo.	lanuginosa	1	\N	\N	\N	\N	\N	Synonym	Poa annua var. annua	\N	variety	http://www.tropicos.org/Name/25517736	Poa annua	Poaceae	false	tropicos	 	\N	t	Poaceae Poa annua var. annua	Poa annua	Poa annua fo. lanuginosa Sennen	t	{Poa,annua}	Poa	annua	var. annua	{var.,annua}	var.	annua	Poa annua var. annua

2014-07-21 16:31:50.48692-07	28	9	Poa annua ssp. exilis	1	Poa annua subsp. exilis	subspecies	1	(Tomm. ex Freyn) Asch. & Graebn.	http://www.tropicos.org/Name/50063800	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	true	tropicos	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn.	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	29	9	Poa annua ssp. exilis	0.959999999999999964	Poa annua var. exilis	variety	0.959999999999999964	Tomm. ex Freyn	http://www.tropicos.org/Name/25547854	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	exilis	0.699999999999999956	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	false	tropicos	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua var. exilis Tomm. ex Freyn	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	30	10	Poa annua subsp. exilis	1	Poa annua subsp. exilis	subsp.	1	(Tomm. ex Freyn.) Asch. & Graebn.	http://www.theplantlist.org/tpl1.1/record/kew-435202	\N	\N	\N	\N	\N	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.theplantlist.org/tpl1.1/record/kew-436189	Poa infirma	Poaceae	true	tpl	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua subsp. exilis (Tomm. ex Freyn.) Asch. & Graebn.	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	31	10	Poa annua subsp. exilis	1	Poa annua subsp. exilis	subspecies	1	(Tomm. ex Freyn) Asch. & Graebn.	http://www.tropicos.org/Name/50063800	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subsp.	exilis	1	\N	\N	\N	\N	\N	Synonym	Poa infirma	Kunth	species	http://www.tropicos.org/Name/25514158	Poa infirma	Poaceae	false	tropicos	 	\N	t	Poaceae Poa infirma Kunth	Poa annua	Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn.	t	{Poa,infirma}	Poa	infirma	\N	\N	\N	\N	Poa infirma Kunth

2014-07-21 16:31:50.48692-07	32	11	Poa annua subvar. minima	1	Poa annua subvar. minima	subvariety	1	(Schur) Asch. & Graebn.	http://www.tropicos.org/Name/50158097	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	subvar.	minima	1	\N	\N	\N	\N	\N	Accepted	Poa annua subvar. minima	(Schur) Asch. & Graebn.	subvariety	http://www.tropicos.org/Name/50158097	Poa annua	Poaceae	true	tropicos	 	\N	t	Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn.	Poa annua	Poa annua subvar. minima (Schur) Asch. & Graebn.	t	{Poa,annua}	Poa	annua	subvar. minima	{subvar.,minima}	subvar.	minima	Poa annua subvar. minima (Schur) Asch. & Graebn.

2014-07-21 16:31:50.48692-07	33	12	Poa annua var. eriolepis	1	Poa annua var. eriolepis	var.	1	̉ۡ.Desv.	http://www.theplantlist.org/tpl1.1/record/kew-435206	\N	\N	\N	\N	\N	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.theplantlist.org/tpl1.1/record/kew-435194	Poa annua	Poaceae	true	tpl	 	\N	t	Poaceae Poa annua L.	Poa annua	Poa annua var. eriolepis ̉ۡ.Desv.	t	{Poa,annua}	Poa	annua	\N	\N	\N	\N	Poa annua L.

2014-07-21 16:31:50.48692-07	34	12	Poa annua var. eriolepis	1	Poa annua var. eriolepis	variety	1	E. Desv.	http://www.tropicos.org/Name/50119145	\N	\N	\N	\N	Poaceae	Poa	1	annua	1	var.	eriolepis	1	\N	\N	\N	\N	\N	Synonym	Poa annua	L.	species	http://www.tropicos.org/Name/25509881	Poa annua	Poaceae	false	tropicos	 	\N	t	Poaceae Poa annua L.	Poa annua	Poa annua var. eriolepis E. Desv.	t	{Poa,annua}	Poa	annua	\N	\N	\N	\N	Poa annua L.

2014-07-21 16:31:50.48692-07	35	13	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	subsp. pringlei	{subsp.,pringlei}	subsp.	pringlei	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-07-21 16:31:50.48692-07	36	13	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	false	tropicos	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	subsp. pringlei	{subsp.,pringlei}	subsp.	pringlei	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-07-21 16:31:50.48692-07	37	13	Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://plants.usda.gov/java/profile?symbol=SISCP	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri ssp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://plants.usda.gov/java/profile?symbol=SISCP	Silene scouleri	Caryophyllaceae	false	usda	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	ssp. pringlei	{ssp.,pringlei}	ssp.	pringlei	Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-07-21 16:31:50.48692-07	38	14	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subsp.	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.theplantlist.org/tpl1.1/record/tro-6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subsp.	http://www.theplantlist.org/tpl1.1/record/tro-6303627	Silene scouleri	Caryophyllaceae	true	tpl	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	subsp. pringlei	{subsp.,pringlei}	subsp.	pringlei	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-07-21 16:31:50.48692-07	39	14	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://www.tropicos.org/Name/6303627	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri subsp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://www.tropicos.org/Name/6303627	Silene scouleri	Caryophyllaceae	false	tropicos	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	subsp. pringlei	{subsp.,pringlei}	subsp.	pringlei	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire

2014-07-21 16:31:50.48692-07	40	14	Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire	0.770000000000000018	Silene scouleri subsp. pringlei	subspecies	0.770000000000000018	(S. Watson) C.L. Hitchc. & Maguire	http://plants.usda.gov/java/profile?symbol=SISCP	\N	\N	\N	\N	Caryophyllaceae	Silene	1	scouleri	1	subsp.	pringlei	1	\N	\N	\N	\N	var. grisea	Accepted	Silene scouleri ssp. pringlei	(S. Watson) C.L. Hitchc. & Maguire	subspecies	http://plants.usda.gov/java/profile?symbol=SISCP	Silene scouleri	Caryophyllaceae	false	usda	 [Partial match] 	\N	t	Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire	Silene scouleri	Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire	t	{Silene,scouleri}	Silene	scouleri	ssp. pringlei	{ssp.,pringlei}	ssp.	pringlei	Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire

:http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html	datasetURL	\N	\N

ALTER TABLE taxon_match CLUSTER ON taxon_match_pkey;

-- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

--

CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match);

--

CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted");

-- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace: 

CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "*Name_number") WHERE ("*Selected" = 'true'::text);

-- Name: taxon_match__fill_derived; Type: TRIGGER; Schema: TNRS; Owner: bien

--

CREATE TRIGGER taxon_match__fill_derived BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill_derived();

--

-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC;

REVOKE ALL ON TABLE taxon_best_match FROM bien;

GRANT ALL ON TABLE taxon_best_match TO bien;

GRANT SELECT ON TABLE taxon_best_match TO bien_read;

--

-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: bien

--

REVOKE ALL ON TABLE taxon_match_input FROM PUBLIC;

REVOKE ALL ON TABLE taxon_match_input FROM bien;

GRANT ALL ON TABLE taxon_match_input TO bien;

GRANT SELECT ON TABLE taxon_match_input TO bien_read;

--

COMMENT ON SCHEMA "public.test_taxonomic_names" IS 'Version: public (2014-7-21 16:32:02 PDT)';

COMMENT ON COLUMN analytical_stem."TAXONOBSERVATION_ID__@VegBank__.taxonObservation@vegpath.org" IS 'VegBIEN-autogenerated "identifier assigned to each unique observation of a taxon in a plot"';

-- Name: in_iucn_red_list(text, text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

CREATE FUNCTION in_iucn_red_list(accepted_family text, accepted_species_binomial text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS boolean

    LANGUAGE plpgsql STABLE

    SET client_min_messages TO 'warning'

    SET search_path TO pg_temp

    AS $$

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

DECLARE

	accepted_family_ text = accepted_family;

	accepted_species_binomial_ text = accepted_species_binomial;

BEGIN

	PERFORM util.use_schema(schema_anchor);

	RETURN EXISTS(

		SELECT NULL FROM iucn_red_list t

		WHERE

			t.accepted_family = accepted_family_

		AND	t.accepted_species_binomial = accepted_species_binomial_

	);

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".in_iucn_red_list(accepted_family text, accepted_species_binomial text, schema_anchor anyelement) OWNER TO bien;

--

-- Name: in_new_world(double precision, double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION in_new_world(latitude_deg double precision, longitude_deg double precision) RETURNS boolean

SELECT util.in_new_world(($1, $2))

ALTER FUNCTION "public.test_taxonomic_names".in_new_world(latitude_deg double precision, longitude_deg double precision) OWNER TO bien;

-- Name: FUNCTION in_new_world(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

COMMENT ON FUNCTION in_new_world(latitude_deg double precision, longitude_deg double precision) IS '

-- Name: in_south_america(double precision, double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION in_south_america(latitude_deg double precision, longitude_deg double precision) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT util.in_south_america(($1, $2))

$_$;

ALTER FUNCTION "public.test_taxonomic_names".in_south_america(latitude_deg double precision, longitude_deg double precision) OWNER TO bien;

--

-- Name: FUNCTION in_south_america(latitude_deg double precision, longitude_deg double precision); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION in_south_america(latitude_deg double precision, longitude_deg double precision) IS '

wrapper that prevents views from getting dropped when the util schema is reinstalled

';

--

-- Name: iucn_red_list_view_modify(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION iucn_red_list_view_modify() RETURNS void

    LANGUAGE sql

    AS $$

SELECT util.rematerialize_view('iucn_red_list', 'iucn_red_list_view');

ALTER TABLE iucn_red_list ADD PRIMARY KEY(accepted_family, accepted_species_binomial);

$$;

ALTER FUNCTION "public.test_taxonomic_names".iucn_red_list_view_modify() OWNER TO bien;

--

-- Name: source__observation_type(text, anyelement); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION source__observation_type(shortname text, schema_anchor anyelement DEFAULT NULL::schema_anchor) RETURNS text

    LANGUAGE plpgsql STABLE

    SET client_min_messages TO 'warning'

    SET search_path TO pg_temp

    AS $$

/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime

changes of search_path (schema elements are bound at inline time rather than

runtime) */

/* function option search_path is needed to limit the effects of

`SET LOCAL search_path` to the current function */

DECLARE

	shortname_ text = shortname;

BEGIN

	PERFORM util.use_schema(schema_anchor);

	RETURN (SELECT observationtype FROM source

		WHERE source.shortname = shortname_);

END;

$$;

ALTER FUNCTION "public.test_taxonomic_names".source__observation_type(shortname text, schema_anchor anyelement) OWNER TO bien;

--

-- Name: subspecies(text); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien

--

CREATE FUNCTION subspecies(taxon_name text) RETURNS text

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT util.subspecies($1)

$_$;

ALTER FUNCTION "public.test_taxonomic_names".subspecies(taxon_name text) OWNER TO bien;

--

-- Name: FUNCTION subspecies(taxon_name text); Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien

--

COMMENT ON FUNCTION subspecies(taxon_name text) IS '

wrapper that prevents views from getting dropped when the util schema is reinstalled

';

--

  WHERE ((((((COALESCE((analytical_stem.geovalid_bien)::boolean, true) AND (COALESCE((analytical_stem."isNewWorld_bien")::boolean, false) OR in_new_world(analytical_stem."decimalLatitude__@DwC__@vegpath.org", analytical_stem."decimalLongitude__@DwC__@vegpath.org"))) AND (NOT COALESCE((analytical_stem.cultivated_bien)::boolean, false))) AND (analytical_stem.scrubbed_family IS NOT NULL)) AND (analytical_stem."decimalLatitude__@DwC__@vegpath.org" IS NOT NULL)) AND (analytical_stem."decimalLongitude__@DwC__@vegpath.org" IS NOT NULL)) AND COALESCE((analytical_stem."coordinateUncertaintyInMeters__@DwC__@vegpath.org" <= _km_to_m((10)::double precision)), true));

-- Name: iucn_red_list; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: 

--

CREATE TABLE iucn_red_list (

    accepted_family text NOT NULL,

    accepted_species_binomial text NOT NULL

);

ALTER TABLE "public.test_taxonomic_names".iucn_red_list OWNER TO bien;

--

-- Name: source; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: 

--

CREATE TABLE source (

    source_id integer NOT NULL,

    matched_source_id integer,

    parent_id integer,

    shortname text NOT NULL,

    citation text,

    sourcetype sourcetype,

    accesslevel accesslevel,

    accessconditions text,

    observationtype observationtype,

    title text,

    titlesuperior text,

    volume text,

    issue text,

    pagerange text,

    totalpages integer,

    publisher text,

    publicationplace text,

    isbn text,

    edition text,

    numberofvolumes integer,

    chapternumber integer,

    reportnumber integer,

    communicationtype text,

    degree text,

    url text,

    doi text,

    additionalinfo text,

    pubdate date,

    accessdate date,

    conferencedate date,

    datecreated date DEFAULT now() NOT NULL,

    createdby text,

    datelastmodified date DEFAULT now() NOT NULL,

    lastmodifiedby text,

    import_revision text

);

ALTER TABLE "public.test_taxonomic_names".source OWNER TO bien;

--

    source.observationtype AS collection_type,

    subspecies(analytical_stem.scrubbed_taxon_name_no_author) AS accepted_subspecies,

    analytical_stem."stem_dbh[_cm]__@SALVIAS__.Plot_data@vegpath.org" AS dbh_cm,

    analytical_stem."stemHeight[_m]__@VegBank__.stemCount@vegpath.org" AS height_m,

    analytical_stem."[growth_form=]habit__@SALVIAS__.Plot_data@vegpath.org" AS lifeform,

    (iucn_red_list.accepted_family IS NOT NULL) AS is_threatened_iucn

   FROM ((analytical_stem

   LEFT JOIN source ON ((source.shortname = analytical_stem."datasource__@Brad__.identifier_examples@vegpath.org")))

   LEFT JOIN iucn_red_list ON (((iucn_red_list.accepted_family = analytical_stem.scrubbed_family) AND (iucn_red_list.accepted_species_binomial = analytical_stem.scrubbed_species_binomial))))