/trunk/inputs/.TNRS/schema.sql - Annotate - BIEN 3 - NCEAS Projects

root/trunk/inputs/.TNRS/schema.sql @ 14281

-            aaronmk
+--
 -- PostgreSQL database dump
 --
             aaronmk
-            aaronmk
+SET statement_timeout = 0;
-            aaronmk
+SET lock_timeout = 0;
-            aaronmk
+SET client_encoding = 'UTF8';
 SET standard_conforming_strings = on;
 SET check_function_bodies = false;
 SET client_min_messages = warning;
-            aaronmk
+--
-            aaronmk
+-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: -
 --
             aaronmk
-            aaronmk
+--CREATE SCHEMA "TNRS";
             aaronmk
-            aaronmk
+--
 -- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -
 --
-            aaronmk
+COMMENT ON SCHEMA "TNRS" IS '
 IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
-            aaronmk
+on vegbiendev:
 # back up existing TNRS schema (in case of an accidental incorrect change):
-            aaronmk
+$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
-            aaronmk
+$ svn up
 $ svn di
 # make the changes shown in the diff
 ## to change column types:
-            aaronmk
+SELECT util.set_col_types(''"TNRS".taxon_match'', ARRAY[
-            aaronmk
+  (''col'', ''new_type'')
-            aaronmk
+]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
-            aaronmk
+$ rm=1 inputs/.TNRS/schema.sql.run
 # repeat until `svn di` shows no diff
 # back up new TNRS schema:
-            aaronmk
+$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
 ';
             aaronmk
-            aaronmk
+SET search_path = "TNRS", pg_catalog;
 --
 -- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION batch__fill() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
-            aaronmk
+BEGIN
 	new.id_by_time = new.time_submitted;
 	new.id = COALESCE(new.id, new.id_by_time);
 	RETURN new;
 END;
-            aaronmk
+$$;
             aaronmk
 --
-            aaronmk
+-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
 --
             aaronmk
-            aaronmk
+CREATE FUNCTION family_is_homonym(family text) RETURNS boolean
     LANGUAGE sql STABLE STRICT
     AS $_$
 SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
 $_$;
             aaronmk
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
 --
             aaronmk
-            aaronmk
+CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean
     LANGUAGE sql STABLE STRICT
     AS $_$
 SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
 $_$;
             aaronmk
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -
 --
-            aaronmk
+CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields:
 "taxonomic_status should be accepted instead of synonym when an accepted name is
 available (this is not always the case when a name is marked as a synonym)" */
-            aaronmk
+SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
 $_$;
 --
-            aaronmk
+-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger
-            aaronmk
+    LANGUAGE plpgsql
     AS $$
 BEGIN
-            aaronmk
+	PERFORM util.seq__reset('pg_temp.taxon_match__match_num__seq', 0);
-            aaronmk
+	RETURN NULL;
 END;
 $$;
 --
-            aaronmk
+-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION taxon_match__fill() RETURNS trigger
-            aaronmk
+    LANGUAGE plpgsql
     AS $$
-            aaronmk
+BEGIN
-            aaronmk
+	DECLARE
 		"Specific_epithet_is_plant" boolean :=
-            aaronmk
+			(CASE
-            aaronmk
+			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
-            aaronmk
+				THEN true
 			ELSE NULL -- ambiguous
 			END);
-            aaronmk
+		never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false);
-            aaronmk
+			-- author disambiguates
 		family_is_homonym boolean = NOT never_homonym
-            aaronmk
+			AND "TNRS".family_is_homonym(new."*Family_matched");
-            aaronmk
+		genus_is_homonym  boolean = NOT never_homonym
-            aaronmk
+			AND "TNRS".genus_is_homonym(new."*Genus_matched");
-            aaronmk
+	BEGIN
 		/* exclude homonyms because these are not valid matches (TNRS provides a
 		name, but the name is not meaningful because it is not unambiguous) */
-            aaronmk
+		new.is_valid_match = new."*Taxonomic_status" != 'Invalid'
-            aaronmk
+			AND COALESCE(CASE
-            aaronmk
+			WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match
-            aaronmk
+				THEN true
 			ELSE -- consider genus
-            aaronmk
+				(CASE
-            aaronmk
+				WHEN new."*Genus_score" =  1	   -- exact match
-            aaronmk
+					THEN
 					(CASE
 					WHEN NOT genus_is_homonym THEN true
 					ELSE "Specific_epithet_is_plant"
 					END)
-            aaronmk
+				WHEN new."*Genus_score" >= 0.85 -- fuzzy match
-            aaronmk
+					THEN "Specific_epithet_is_plant"
 				ELSE NULL -- ambiguous
-            aaronmk
+				END)
-            aaronmk
+			END, false);
 	END;
             aaronmk
-            aaronmk
+	DECLARE
 		matched_taxon_name_with_author text = NULLIF(concat_ws(' '
-            aaronmk
+			, NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'),
 				new."*Name_matched")
 			, NULLIF(new."*Name_matched", 'No suitable matches found.')
 			, new."*Name_matched_author"
-            aaronmk
+			), '');
 		accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
-            aaronmk
+			, NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'),
 				new."*Accepted_name")
 			, new."*Accepted_name"
 			, new."*Accepted_name_author"
-            aaronmk
+			), '');
 	BEGIN
 		new.scrubbed_unique_taxon_name = COALESCE(
 			accepted_taxon_name_with_author, matched_taxon_name_with_author);
 	END;
-            aaronmk
+	RETURN new;
-            aaronmk
+END;
-            aaronmk
+$$;
 --
-            aaronmk
+-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION taxon_match__fill() IS '
-            aaronmk
+IMPORTANT: when changing this function, you must regenerate the derived cols:
-            aaronmk
+UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted"
-            aaronmk
+runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
-            aaronmk
+VACUUM ANALYZE "TNRS".taxon_match -- to remove previous rows
-            aaronmk
+runtime: 1.5 min ("92633 ms")
 ';
             aaronmk
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: taxon_match__fill_derived(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION taxon_match__fill_derived() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
 BEGIN
-            aaronmk
+	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
 		("*Genus_matched" || ' '::text) || "*Specific_epithet_matched"
 		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.matched_has_accepted = (SELECT
 		"*Accepted_name" IS NOT NULL
 		FROM (SELECT new.*) new);
-            aaronmk
+	new."__accepted_{genus,specific_epithet}" = (SELECT
-            aaronmk
+		regexp_split_to_array("*Accepted_name_species", ' '::text)
-            aaronmk
+		FROM (SELECT new.*) new);
-            aaronmk
+	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);
-            aaronmk
+	new.__accepted_infraspecific_label = (SELECT
 		ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)
 		FROM (SELECT new.*) new);
-            aaronmk
+	new."__accepted_infraspecific_{rank,epithet}" = (SELECT
 		regexp_split_to_array(__accepted_infraspecific_label, ' '::text)
 		FROM (SELECT new.*) new);
-            aaronmk
+	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);
-            aaronmk
+	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
 		"*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text)
 		FROM (SELECT new.*) new);
-            aaronmk
+	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "*Accepted_name_rank"
         ELSE "*Name_matched_rank"
     END
-            aaronmk
+		FROM (SELECT new.*) new);
-            aaronmk
+	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "*Accepted_name_family"
         ELSE "*Name_matched_accepted_family"
     END
-            aaronmk
+		FROM (SELECT new.*) new);
-            aaronmk
+	new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
         ELSE "*Genus_matched"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
         ELSE "*Specific_epithet_matched"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "*Accepted_name_species"
         ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
         ELSE "*Infraspecific_rank"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
         ELSE "*Infraspecific_epithet_matched"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "*Accepted_name"
         ELSE "*Name_matched"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "*Accepted_name_author"
         ELSE "*Name_matched_author"
     END
 		FROM (SELECT new.*) new);
 	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
 		CASE
         WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
         ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
     END
 		FROM (SELECT new.*) new);
-            aaronmk
+	RETURN new;
 END;
 $$;
 --
-            aaronmk
+-- Name: FUNCTION taxon_match__fill_derived(); Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON FUNCTION taxon_match__fill_derived() IS '
 autogenerated, do not edit
 to regenerate:
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
 ';
 --
-            aaronmk
+-- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION taxon_match__match_num__fill() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	IF new.match_num IS NULL THEN
-            aaronmk
+		new.match_num = "TNRS".taxon_match__match_num__next();
-            aaronmk
+	END IF;
 	RETURN new;
 END;
 $$;
 --
-            aaronmk
+-- Name: taxon_match__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION taxon_match__match_num__next() RETURNS bigint
     LANGUAGE sql
     AS $$
 SELECT nextval('pg_temp.taxon_match__match_num__seq');
 $$;
 --
-            aaronmk
+-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	INSERT INTO "TNRS".taxon_match_input SELECT new.*;
 	RETURN NULL;
 END;
 $$;
 --
 -- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
 $_$;
 --
-            aaronmk
+-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: -
 --
 CREATE FUNCTION unsafe_taxon_names() RETURNS text[]
     LANGUAGE sql IMMUTABLE
     AS $$
 SELECT ARRAY[
-            aaronmk
+]::text[]
-            aaronmk
+$$;
-            aaronmk
+SET default_tablespace = '';
 SET default_with_oids = false;
-            aaronmk
+--
-            aaronmk
+-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
-            aaronmk
+--
             aaronmk
-            aaronmk
+CREATE TABLE taxon_match (
-            aaronmk
+    batch text DEFAULT now() NOT NULL,
-            aaronmk
+    match_num integer NOT NULL,
-            aaronmk
+    "*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,
-            aaronmk
+    is_valid_match boolean NOT NULL,
-            aaronmk
+    scrubbed_unique_taxon_name text,
-            aaronmk
+    "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text,
     "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text,
     matched_has_accepted boolean,
-            aaronmk
+    "__accepted_{genus,specific_epithet}" text[],
-            aaronmk
+    "[accepted_]genus__@DwC__@vegpath.org" text,
     "[accepted_]specificEpithet__@DwC__@vegpath.org" text,
-            aaronmk
+    __accepted_infraspecific_label text,
-            aaronmk
+    "__accepted_infraspecific_{rank,epithet}" text[],
-            aaronmk
+    "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
     "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" text,
-            aaronmk
+    "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" text,
-            aaronmk
+    "[scrubbed_]taxonRank__@DwC__@vegpath.org" text,
     "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" text,
     "[scrubbed_]genus__@DwC__@vegpath.org" text,
     "[scrubbed_]specificEpithet__@DwC__@vegpath.org" text,
     "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" text,
     "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
     "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" text,
     "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text,
     "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text,
     "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text,
-            aaronmk
+    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]))),
-            aaronmk
+    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[1]))),
-            aaronmk
+    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
-            aaronmk
+    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))))),
-            aaronmk
+    CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
-            aaronmk
+    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))))),
-            aaronmk
+    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")))),
-            aaronmk
+    CONSTRAINT "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
     ELSE "*Infraspecific_rank"
 END))),
     CONSTRAINT "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "*Accepted_name_author"
     ELSE "*Name_matched_author"
 END))),
     CONSTRAINT "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "*Accepted_name_family"
     ELSE "*Name_matched_accepted_family"
 END))),
     CONSTRAINT "[scrubbed_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]genus__@DwC__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
     ELSE "*Genus_matched"
 END))),
     CONSTRAINT "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
     ELSE "*Infraspecific_epithet_matched"
 END))),
     CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "*Accepted_name"
     ELSE "*Name_matched"
 END))),
     CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
     ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
 END))),
     CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "*Accepted_name_species"
     ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
 END))),
     CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
     ELSE "*Specific_epithet_matched"
 END))),
     CONSTRAINT "[scrubbed_]taxonRank__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonRank__@DwC__@vegpath.org" IS DISTINCT FROM
 CASE
     WHEN matched_has_accepted THEN "*Accepted_name_rank"
     ELSE "*Name_matched_rank"
 END))),
-            aaronmk
+    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)))),
-            aaronmk
+    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
-            aaronmk
+    CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
-            aaronmk
+    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL))))
-            aaronmk
+);
             aaronmk
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: TABLE taxon_match; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
             aaronmk
-            aaronmk
+COMMENT ON TABLE taxon_match IS '
-            aaronmk
+whenever columns are renamed:
 SELECT util.derived_cols_sync(''"TNRS".taxon_match'');
-            aaronmk
+to remove columns or add columns at the end:
 $ rm=1 inputs/.TNRS/data.sql.run
 $ make schemas/remake
 to add columns in the middle:
 make the changes in inputs/.TNRS/schema.sql
 $ inputs/.TNRS/data.sql.run refresh # re-run TNRS
 $ make schemas/remake
             aaronmk
 to populate a new column:
-            aaronmk
+ALTER TABLE "TNRS".taxon_match DISABLE TRIGGER taxon_match__fill; --speeds up update
-            aaronmk
+UPDATE "TNRS".taxon_match SET "col" = value;
-            aaronmk
+	-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
-            aaronmk
+CLUSTER "TNRS".taxon_match; -- ensure rows are exported in sorted order
-            aaronmk
+ALTER TABLE "TNRS".taxon_match ENABLE TRIGGER taxon_match__fill;
-            aaronmk
+VACUUM ANALYZE "TNRS".taxon_match --remove previous rows; runtime: 1.5 min ("92633 ms")
             aaronmk
 to add a constraint: runtime: 3 min ("173620 ms")
-            aaronmk
+';
             aaronmk
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
 derived column; = ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
 derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match.matched_has_accepted; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match.matched_has_accepted IS '
 derived column; = "*Accepted_name" IS NOT NULL
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet}"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
 derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS '
 derived column; = "__accepted_{genus,specific_epithet}"[1]
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS '
 derived column; = "__accepted_{genus,specific_epithet}"[2]
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+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)
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
 derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS '
 derived column; = "__accepted_infraspecific_{rank,epithet}"[1]
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+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);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS '
 derived column; = "__accepted_infraspecific_{rank,epithet}"[2]
             aaronmk
-            aaronmk
+to modify expr:
-            aaronmk
+SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def);
-            aaronmk
+SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
             aaronmk
 to rename:
 # rename column
 # rename CHECK constraint
 SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
 derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
             aaronmk
 to modify expr:
-            aaronmk
+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);
-            aaronmk
+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);
 ';
 --
-            aaronmk
+-- Name: COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "*Accepted_name_rank"
         ELSE "*Name_matched_rank"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonRank__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_rank" ELSE "*Name_matched_rank" END$$)::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."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "*Accepted_name_family"
         ELSE "*Name_matched_accepted_family"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_family" ELSE "*Name_matched_accepted_family" END$$)::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."[scrubbed_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
         ELSE "*Genus_matched"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]genus__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" ELSE "*Genus_matched" END$$)::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."[scrubbed_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
         ELSE "*Specific_epithet_matched"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Specific_epithet_matched" END$$)::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."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "*Accepted_name_species"
         ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_species" ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" END$$)::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."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
         ELSE "*Infraspecific_rank"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" ELSE "*Infraspecific_rank" END$$)::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."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
         ELSE "*Infraspecific_epithet_matched"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" ELSE "*Infraspecific_epithet_matched" END$$)::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."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "*Accepted_name"
         ELSE "*Name_matched"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name" ELSE "*Name_matched" END$$)::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."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "*Accepted_name_author"
         ELSE "*Name_matched_author"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "*Accepted_name_author" ELSE "*Name_matched_author" END$$)::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."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
 derived column; = CASE
         WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
         ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
     END
 to modify expr:
 SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$CASE WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" END$$)::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);
 ';
 --
-            aaronmk
+-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW taxon_best_match AS
  SELECT taxon_match.batch,
     taxon_match.match_num,
-            aaronmk
+    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",
-            aaronmk
+    taxon_match.is_valid_match,
-            aaronmk
+    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"
-            aaronmk
+   FROM taxon_match
-            aaronmk
+  WHERE (taxon_match."*Selected" = 'true'::text);
             aaronmk
 --
 -- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON VIEW taxon_best_match IS '
 to modify:
 SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
 SELECT __
 $$);
 ';
 --
-            aaronmk
+-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW "MatchedTaxon" AS
-            aaronmk
+ SELECT taxon_best_match.batch,
     taxon_best_match.match_num,
-            aaronmk
+    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",
-            aaronmk
+    taxon_best_match.is_valid_match,
     taxon_best_match.scrubbed_unique_taxon_name,
-            aaronmk
+    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",
-            aaronmk
+    map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus",
-            aaronmk
+        CASE
-            aaronmk
+            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"
-            aaronmk
+        END AS accepted_morphospecies_binomial
-            aaronmk
+   FROM taxon_best_match;
             aaronmk
 --
-            aaronmk
+-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
 --
 COMMENT ON VIEW "MatchedTaxon" IS '
-            aaronmk
+to modify:
-            aaronmk
+SELECT util.recreate_view(''"TNRS"."MatchedTaxon"'', $$
-            aaronmk
+SELECT __
-            aaronmk
+$$);
-            aaronmk
+';
 --
-            aaronmk
+-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW "ValidMatchedTaxon" AS
-            aaronmk
+ SELECT "MatchedTaxon".batch,
-            aaronmk
+    "MatchedTaxon".match_num,
-            aaronmk
+    "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",
-            aaronmk
+    "MatchedTaxon".is_valid_match,
-            aaronmk
+    "MatchedTaxon".scrubbed_unique_taxon_name,
-            aaronmk
+    "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",
-            aaronmk
+    "MatchedTaxon"."taxonomicStatus",
-            aaronmk
+    "MatchedTaxon".accepted_morphospecies_binomial
-            aaronmk
+   FROM "MatchedTaxon"
-            aaronmk
+  WHERE "MatchedTaxon".is_valid_match;
             aaronmk
 --
 -- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON VIEW "ValidMatchedTaxon" IS '
 to update, use * as the column list
 ';
             aaronmk
 --
 -- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
 --
 CREATE TABLE batch (
     id text NOT NULL,
     id_by_time text,
     time_submitted timestamp with time zone DEFAULT now(),
     client_version text
 );
 --
 -- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
 --
 CREATE TABLE batch_download_settings (
     id text NOT NULL,
     "E-mail" text,
     "Id" text,
     "Job type" text,
     "Contains Id" boolean,
     "Start time" text,
     "Finish time" text,
     "TNRS version" text,
     "Sources selected" text,
     "Match threshold" double precision,
     "Classification" text,
     "Allow partial matches?" boolean,
     "Sort by source" boolean,
     "Constrain by higher taxonomy" boolean
 );
 --
 -- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON TABLE batch_download_settings IS '
 stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt
 ';
             aaronmk
 --
 -- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
 --
 CREATE TABLE client_version (
     id text NOT NULL,
     global_rev integer NOT NULL,
     "/lib/tnrs.py rev" integer,
     "/bin/tnrs_db rev" integer
 );
 --
 -- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON TABLE client_version IS '
 contains svn revisions
 ';
             aaronmk
 --
 -- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON COLUMN client_version.global_rev IS '
 from `svn info .` > Last Changed Rev
 ';
             aaronmk
 --
 -- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS '
 from `svn info lib/tnrs.py` > Last Changed Rev
 ';
             aaronmk
 --
 -- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS '
 from `svn info bin/tnrs_db` > Last Changed Rev
 ';
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW taxon_match_input AS
-            aaronmk
+ 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"
-            aaronmk
+   FROM taxon_match;
 --
 -- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
 --
 CREATE TABLE taxon_match_input__copy_to (
     "Name_number" integer,
     "Name_submitted" text,
     "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
 );
 --
-            aaronmk
+-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
-            aaronmk
+ 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
-            aaronmk
+   FROM taxon_match;
             aaronmk
 --
-            aaronmk
+-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
-            aaronmk
+to modify:
 SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
 SELECT __
 $$);
-            aaronmk
+scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
 --
 CREATE VIEW taxon_scrub AS
-            aaronmk
+ SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
-            aaronmk
+    "ValidMatchedTaxon".batch,
-            aaronmk
+    "ValidMatchedTaxon".match_num,
-            aaronmk
+    "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",
-            aaronmk
+    "ValidMatchedTaxon".is_valid_match,
-            aaronmk
+    "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",
-            aaronmk
+    "ValidMatchedTaxon"."taxonomicStatus",
-            aaronmk
+    "ValidMatchedTaxon".accepted_morphospecies_binomial,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
     "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
-            aaronmk
+        CASE
-            aaronmk
+            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")
-            aaronmk
+            ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
         END AS scrubbed_morphospecies_binomial
-            aaronmk
+   FROM ("ValidMatchedTaxon"
    LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
 --
 -- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -
 --
-            aaronmk
+COMMENT ON VIEW taxon_scrub IS '
-            aaronmk
+to modify:
-            aaronmk
+SELECT util.recreate_view(''"TNRS".taxon_scrub'', $$
-            aaronmk
+SELECT __
-            aaronmk
+$$);
-            aaronmk
+';
             aaronmk
 --
-            aaronmk
+-- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
 --
 ALTER TABLE ONLY batch_download_settings
     ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id);
 --
 -- Name: batch_id_by_time_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
 --
 ALTER TABLE ONLY batch
     ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);
 --
 -- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
 --
 ALTER TABLE ONLY batch
     ADD CONSTRAINT batch_pkey PRIMARY KEY (id);
 --
 -- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
 --
 ALTER TABLE ONLY client_version
     ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);
 --
-            aaronmk
+-- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
-            aaronmk
+--
-            aaronmk
+ALTER TABLE ONLY taxon_match
-            aaronmk
+    ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num);
             aaronmk
-            aaronmk
+ALTER TABLE taxon_match CLUSTER ON taxon_match_pkey;
             aaronmk
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
 --
 CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);
 --
-            aaronmk
+-- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
-            aaronmk
+--
-            aaronmk
+CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match);
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
-            aaronmk
+--
-            aaronmk
+CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted");
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
-            aaronmk
+--
-            aaronmk
+CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "*Name_number") WHERE ("*Selected" = 'true'::text);
             aaronmk
 --
-            aaronmk
+-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: -
 --
 CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();
 --
-            aaronmk
+-- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE TRIGGER taxon_match__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE taxon_match__batch_begin();
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE TRIGGER taxon_match__fill BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill();
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match__fill_derived; Type: TRIGGER; Schema: TNRS; Owner: -
 --
 CREATE TRIGGER taxon_match__fill_derived BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill_derived();
 --
-            aaronmk
+-- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE TRIGGER taxon_match__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__match_num__fill();
             aaronmk
 --
-            aaronmk
+-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
             aaronmk
 --
 -- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
 --
 ALTER TABLE ONLY batch
     ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;
 --
 -- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
 --
 ALTER TABLE ONLY batch_download_settings
     ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
 --
-            aaronmk
+-- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+ALTER TABLE ONLY taxon_match
-            aaronmk
+    ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
             aaronmk
 --
 -- Name: TNRS; Type: ACL; Schema: -; Owner: -
 --
 REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC;
 REVOKE ALL ON SCHEMA "TNRS" FROM bien;
 GRANT ALL ON SCHEMA "TNRS" TO bien;
 GRANT USAGE ON SCHEMA "TNRS" TO bien_read;
 --
-            aaronmk
+-- Name: taxon_match; Type: ACL; Schema: TNRS; Owner: -
-            aaronmk
+--
-            aaronmk
+REVOKE ALL ON TABLE taxon_match FROM PUBLIC;
 REVOKE ALL ON TABLE taxon_match FROM bien;
 GRANT ALL ON TABLE taxon_match TO bien;
 GRANT SELECT ON TABLE taxon_match TO bien_read;
             aaronmk
 --
-            aaronmk
+-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: -
 --
 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;
 --
-            aaronmk
+-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
 --
 REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;
 REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;
 GRANT ALL ON TABLE "MatchedTaxon" TO bien;
 GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;
 --
 -- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
 --
 REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;
 REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;
 GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;
 GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;
 --
-            aaronmk
+-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: -
 --
 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;
 --
-            aaronmk
+-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
 --
 REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;
 REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;
 GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;
 GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;
 --
 -- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
 --
 REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
 REVOKE ALL ON TABLE taxon_scrub FROM bien;
 GRANT ALL ON TABLE taxon_scrub TO bien;
 GRANT SELECT ON TABLE taxon_scrub TO bien_read;
 --
-            aaronmk
+-- PostgreSQL database dump complete
 --

Project

General

Profile