--

-- PostgreSQL database dump

--

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: -

--

--CREATE SCHEMA "TNRS";

--

-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -

--

COMMENT ON SCHEMA "TNRS" IS '

IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:

on vegbiendev:

# back up existing TNRS schema (in case of an accidental incorrect change):

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")

$ svn up

$ svn di

# make the changes shown in the diff

## to change column types:

SELECT util.set_col_types(''"TNRS".taxon_match'', ARRAY[

  (''col'', ''new_type'')

]::util.col_cast[]); -- runtime: 9 min ("531282 ms")

$ rm=1 inputs/.TNRS/schema.sql.run

# repeat until `svn di` shows no diff

# back up new TNRS schema:

$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")

';

SET search_path = "TNRS", pg_catalog;

--

-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION batch__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	new.id_by_time = new.time_submitted;

	new.id = COALESCE(new.id, new.id_by_time);

	RETURN new;

END;

$$;

--

-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -

--

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)

$_$;

--

-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -

--

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)

$_$;

--

-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text

    LANGUAGE sql IMMUTABLE

    AS $_$

/* 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)" */

SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END

$_$;

--

-- Name: remove_prefix(text, text, boolean, boolean); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true, case_sensitive boolean DEFAULT true) RETURNS text

    LANGUAGE sql IMMUTABLE

    AS $$

SELECT util.remove_prefix(prefix, str, require, case_sensitive)

$$;

--

-- Name: FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean); Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean) IS '

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

';

--

-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	PERFORM util.seq__reset('pg_temp.taxon_match__match_num__seq', 0);

	RETURN NULL;

END;

$$;

--

-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION taxon_match__fill() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	DECLARE

		"Specific_epithet_is_plant" boolean :=

			(CASE

			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

				(CASE

				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)

			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;

	RETURN new;

END;

$$;

--

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

--

COMMENT ON FUNCTION taxon_match__fill() IS '

IMPORTANT: when changing this function, you must regenerate the derived cols:

UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted"

runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")

VACUUM ANALYZE "TNRS".taxon_match -- to remove previous rows

runtime: 1.5 min ("92633 ms")

';

--

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

--

CREATE FUNCTION taxon_match__fill_derived() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

	-- clear derived cols so old values won't be used in calculations

	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;

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

	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;

	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;

	new.matched_has_accepted = NULL;

	new."Accepted_family__@TNRS__@vegpath.org" = NULL;

	new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = NULL;

	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = NULL;

	new."[accepted_]genus__@DwC__@vegpath.org" = NULL;

	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;

	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;

	new.__accepted_infraspecific_label = NULL;

	new."__accepted_infraspecific_{rank,epithet}" = NULL;

	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;

	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;

	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;

	new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;

	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;

	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;

	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;

	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;

	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;

	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;

	-- populate derived cols

	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);

	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);

	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);

	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);

	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);

	new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family",

CASE

    WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"

    ELSE NULL::text

END) FROM (SELECT new.*) new);

	new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);

	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = (SELECT regexp_split_to_array("*Accepted_name", ' '::text) FROM (SELECT new.*) new);

	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1] FROM (SELECT new.*) new);

	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2] FROM (SELECT new.*) new);

	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE

    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"

END FROM (SELECT new.*) new);

	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);

	new."__accepted_infraspecific_{rank,epithet}" = (SELECT NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::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."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);

	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new);

	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE

    WHEN matched_has_accepted THEN "*Accepted_name_rank"

    ELSE "*Name_matched_rank"

END FROM (SELECT new.*) new);

	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE

    WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org"

    ELSE "*Name_matched_accepted_family"

END FROM (SELECT new.*) new);

	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_species[_binomial]__@TNRS__@vegpath.org"

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

END FROM (SELECT new.*) new);

	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE

    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "[scrubbed_]species[_binom]~(-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 "matched~Name[_no_author]___@TNRS__@vegpath.org"

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);

	RETURN new;

END;

$$;

--

-- 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);

';

--

-- 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

		new.match_num = "TNRS".taxon_match__match_num__next();

	END IF;

	RETURN new;

END;

$$;

--

-- 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');

$$;

--

-- 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()))

$_$;

--

-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: -

--

CREATE FUNCTION unsafe_taxon_names() RETURNS text[]

    LANGUAGE sql IMMUTABLE

    AS $$

SELECT ARRAY[

]::text[]

$$;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: 

--

CREATE TABLE taxon_match (

    batch text DEFAULT now() NOT NULL,

    match_num integer NOT NULL,

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

    is_valid_match boolean NOT NULL,

    scrubbed_unique_taxon_name text,

    "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text,

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

    "matched~Name[_no_author]___@TNRS__@vegpath.org" text,

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

    matched_has_accepted boolean,

    "Accepted_family__@TNRS__@vegpath.org" text,

    "Accepted_species[_binomial]__@TNRS__@vegpath.org" text,

    "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" text[],

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

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

    "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@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,

    "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text,

    "[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_]morphospecies[_binomial]__@Brad__.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,

    CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family",

CASE

    WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"

    ELSE NULL::text

END)))),

    CONSTRAINT "Accepted_species[_binomial]__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_species[_binomial]__@TNRS__@vegpath.org" IS DISTINCT FROM rtrim("*Accepted_name_species", ' '::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,infra_{rank,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_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM

CASE

    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"

END))),

    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,infra_{rank,epithet}}"[2]))),

    CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("matched~Name[_no_author]___@TNRS__@vegpath.org" || 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 "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" CHECK ((NOT ("[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS DISTINCT FROM "*Unmatched_terms"))),

    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_family__@TNRS__@vegpath.org"

    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_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM

CASE

    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"

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 "matched~Name[_no_author]___@TNRS__@vegpath.org"

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_species[_binomial]__@TNRS__@vegpath.org"

    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))),

    CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),

    CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),

    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM NULLIF("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[3:4], '{}'::text[])))),

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

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

    CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))

);

--

-- Name: TABLE taxon_match; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON TABLE taxon_match IS '

whenever columns are renamed:

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

to port derived column changes to vegbiendev:

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

# run the returned SQL on vegbiendev

	-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")

SELECT util.recreate_view(''"TNRS".taxon_best_match'');

to add a new derived column:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''col''), $$

expr

$$));

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

SELECT util.recreate_view(''"TNRS".taxon_best_match'');

$ make schemas/remake

to remove a column:

SELECT util.recreate_view(''"TNRS".taxon_best_match'');

$ make schemas/remake

to move a derived column to the middle or to add a non-derived column:

make the changes in inputs/.TNRS/schema.sql

$ inputs/.TNRS/data.sql.run refresh # re-run TNRS; runtime: 1 min ("1m2.629s")

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

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

SELECT util.recreate_view(''"TNRS".taxon_best_match'');

$ make schemas/remake

to add a constraint: runtime: 3 min ("173620 ms")

';

--

-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS '

= "*Unmatched_terms"

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

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

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

derived column

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org" IS '

= NULLIF("*Name_matched", ''No suitable matches found.''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col, $$NULLIF("*Name_matched", ''No suitable matches found.''::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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

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

= "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"matched~Name[_no_author]___@TNRS__@vegpath.org" || 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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

COMMENT ON COLUMN taxon_match.matched_has_accepted IS '

= "*Accepted_name" IS NOT NULL

derived column

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org" IS '

= COALESCE("*Accepted_name_family",

CASE

    WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"

    ELSE NULL::text

END)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col, $$COALESCE("*Accepted_name_family",

CASE

    WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"

    ELSE NULL::text

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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org" IS '

= rtrim("*Accepted_name_species", '' ''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_species[_binomial]__@TNRS__@vegpath.org'')::util.col, $$rtrim("*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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''Accepted_species[_binomial]__@TNRS__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

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

= regexp_split_to_array("*Accepted_name", '' ''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet,infra_{rank,epithet}}'')::util.col, $$regexp_split_to_array("*Accepted_name", '' ''::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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet,infra_{rank,epithet}}'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

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

= "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet,infra_{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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

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

= "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2]

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet,infra_{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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -

--

COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '

= CASE

    WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "Accepted_species[_binomial]__@TNRS__@vegpath.org"

END

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE

    WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")

    ELSE "Accepted_species[_binomial]__@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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';

--

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

--

COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS '

= ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)

derived column

to modify expr:

SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("Accepted_species[_binomial]__@TNRS__@vegpath.org", "*Accepted_name", require := true, case_sensitive := false), ''''::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);

to drop:

SELECT util.drop_derived_col((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col);

	-- DROP __ CASCADE doesn''t work when there are dependent views

';