-- 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_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;

	new.matched_has_accepted = NULL;

	new."Accepted_family__@TNRS__@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_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);

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

CASE

END FROM (SELECT new.*) new);

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

    WHEN matched_has_accepted THEN "*Accepted_name"

END FROM (SELECT new.*) new);

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

    WHEN matched_has_accepted THEN "*Accepted_name_author"

    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;

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}"[2]))),

    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

    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"

END))),

    CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM

CASE

--

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

derived column

to modify expr:

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

to rename:

COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS '

= CASE

    WHEN matched_has_accepted THEN "*Accepted_name"

END

derived column

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"

END$$)::util.derived_col_def);

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