bugfix: inputs/.TNRS/schema.sql: taxon_match: use wrapper for util.remove_prefix() so CHECK constraints that use it don't get dropped when the util schema is reinstalled

schemas/public_.sql: *_view_modify(): use util.rematerialize_view(), which now supports table mods

schemas/util.sql: rematerialize_view(): added support for table mods to run after creating table

bugfix: schemas/util.sql: rematerialize_view(): don't populate unless recreate() can successfully recreate the dependent views, to avoid populating the entire table (which for some views can take hours) only to have the changes rolled back by a failing dependent view

schemas/util.sql: removed no longer used materialize_view(). use util.copy() instead.

schemas/util.sql: rematerialize_view(): call util.copy() directly instead of via util.materialize_view()

schemas/util.sql: removed no longer used copy_types_and_data(). use util.materialize_view() instead.

schemas/util.sql: to_freq(): use util.copy() instead of util.copy_types_and_data() to avoid a dependency on util.copy_types_and_data()

fix: schemas/util.sql: copy_data(): also need to ANALYZE table after loading data into it

schemas/public_.sql: analytical_stem_view: use viewFullOccurrence_individual_view to avoid duplicated code between the views. this moves the stem-related columns to the end of the view.

schemas/public_.sql: added viewFullOccurrence_individual_view

schemas/util.sql: derived_col_constraint_initially_enabled(): clarified that this is always based on whether the existing constraint is enabled

inputs/.TNRS/schema.sql: taxon_match: COMMENT: added steps to port derived column changes to vegbiendev

schemas/util.sql: added derived_cols_export(), which creates SQL to port the derived cols to another DB

bugfix: inputs/.TNRS/schema.sql: taxon_match: derived columns: use new "matched~Name[_no_author]___@TNRS__@vegpath.org" instead of "*Name_matched" so that "No suitable matches found." is removed before concatenating with other fields

inputs/.TNRS/schema.sql: taxon_match: added derived column "matched~Name[_no_author]___@TNRS__@vegpath.org", which removes the "No suitable matches found." string

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

bugfix: inputs/.TNRS/schema.sql: "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org": need to use "[accepted_]genus__@DwC__@vegpath.org" rather than "*Accepted_name" for this for rank = genus

inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"

bugfix: inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": need to populate this for rank = genus

inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"

bugfix: inputs/.TNRS/schema.sql: derived columns: use "Accepted_family__@TNRS__@vegpath.org" instead of "*Accepted_name_family" because "*Accepted_name_family" is sometimes missing

bugfix: derived_col_constraints_enable(): also need to update col comments, in addition to table__fill_derived()

schemas/util.sql: check_constraint_enabled(): made it IMMUTABLE

bugfix: schemas/util.sql: derived_col_constraints_enable(): need to update table__fill_derived() w/ formatting changes from enabling constraints

bugfix: schemas/util.sql: derived_col_constraints_enable(): need to update table__fill_derived() w/ formatting changes from enabling constraints

fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_family__@TNRS__@vegpath.org", which is needed because "*Accepted_name_family" isn't always populated

bugfix: inputs/.TNRS/schema.sql: taxon_match: to add columns in the middle: also need to run util.derived_cols_repopulate() since the dependency order has changed

fix: schemas/util.sql: derived_cols_populate(): documented that this function will sometimes not repopulate columns when needed, such as after changing the column order. in these cases, it is necessary to run util.derived_cols_repopulate() manually.

schemas/util.sql: derived_cols_populate(): use new derived_cols_repopulate()

schemas/util.sql: added derived_cols_repopulate()

fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: to add columns in the middle: also need to run util.derived_cols_update()

fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: updated util.derived_cols_sync() to util.derived_cols_update()

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

bugfix: schemas/util.sql: derived_cols_trigger_update(): need to clear derived cols first so old values won't be used in calculations

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): put expr on same line as var, to save space

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): removed lines between statements, which add too much space

fix: schemas/util.sql: derived_cols_trigger_update(): don't indent expr because may have multiple lines

bugfix: schemas/util.sql: derived_col_update(): set_comment(): vars can't have same name as params, which will be substituted

inputs/.TNRS/schema.sql: taxon_match: added derived column "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived column "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"

schemas/public_.sql: analytical_stem: synced to analytical_stem_view

bugfix: schemas/util.sql: view_body(): need to match view defs without trailing \n

bugfix: schemas/util.sql: view_body(): need to support both CREATE statements and view bodies

bugfix: schemas/util.sql: raise(): use eval_raw() so that functions called by eval() (such as view_def_to_orig()) can use raise() for debugging

schemas/util.sql: eval(): use new eval_raw()

schemas/util.sql: added eval_raw()

bugfix: schemas/util.sql: view_is_automatically_updatable(), view_is_subset(): use util.view_body() so not matching COMMENT statements, etc

schemas/util.sql: added view_body()

bugfix: schemas/util.sql: view_is_subset(): need to support "(x)" in col name

schemas/util.sql: added copy_struct(from_ regtype, to_ text) and copy_data(from_ regtype, to_ text), and use these in a general-purpose copy()

inputs/.TNRS/schema.sql: added new derived columns to derived views

fix: schemas/util.sql: derived_col_update(): column comment: formula: put on own line to support multiline exprs

bugfix: schemas/util.sql: derived_col_update(): column comment: need to use expr from DB, not as provided by user, to avoid this changing when derived_col_update() is rerun

inputs/.TNRS/schema.sql: taxon_match: added derived columns scrubbed_*

bugfix: schemas/util.sql: derived_col_expr_from_check_constraint(): need to handle strs with \n after IS DISTINCT FROM

inputs/.TNRS/schema.sql: taxon_match: put matched-taxon derived columns before accepted-taxon derived columns

inputs/.TNRS/schema.sql: taxon_match: added derived column "[matched_]species[_binomial]~(Accepted_)__@TNRS__@vegpath.org"

inputs/.TNRS/schema.sql: _matched_has_accepted: renamed to matched_has_accepted because the leading _ did not create vertical alignment anyway, due to different quoting syntaxes

schemas/util.sql: added derived_col_name_from_check_constraint()

schemas/util.sql: derived_col_update(): add steps to rename column

bugfix: schemas/util.sql: derived_col_update(): don't set_comment() until CHECK constraint exists so that we can use its canon-ed formula

bugfix: schemas/util.sql: check_constraint_expr_enable(): don't remove suffix without also removing prefix. this is done by using extract_by_regexp() instead. also changed check_constraint_enabled() to use regexps to match this.

bugfix: schemas/util.sql: extract_by_regexp(): needs surrounding COALESCE in case no match

schemas/util.sql: added extract_by_regexp()

inputs/.TNRS/schema.sql: taxon_match: added derived column _matched_has_accepted

inputs/.TNRS/schema.sql: added new derived columns to derived views

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org"

inputs/.TNRS/schema.sql: taxon_match: added derived column "__accepted_infraspecific_{rank,epithet}"

inputs/.TNRS/schema.sql: taxon_match: added derived column __accepted_infraspecific_label

bugfix: schemas/util.sql: canon_sql(): need to use the right search_path

bugfix: schemas/util.sql: check_constraint view: def: must use our wrapper so that the right search_path is used

schemas/util.sql: added pg_get_expr() wrapper, which sets search_path to pg_temp

bugfix: schemas/util.sql: check_constraint_expr_*(): updated strings for new Mac Postgres update, which was installed on reboot

bugfix: schemas/util.sql: check_constraint_expr_*(): updated strings for new Mac Postgres update, which was installed on reboot

bugfix: schemas/util.sql: derived_col_update(): steps to modify: also need to run util.derived_cols_populate()

inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org"

bugfix: schemas/util.sql: check_constraint view: can't use consrc because this doesn't get auto-updated with column renames

bugfix: schemas/util.sql: derived_col_constraints_enable(): need final `SELECT NULL::void` so don't fold away functions called in previous query

inputs/.TNRS/schema.sql: taxon_match: ran derived_cols_update()

bugfix: derived_col_constraint_initially_enabled(): must be both already enabled and unchanged to be initially enabled (r14240 was only partially correct)

schemas/util.sql: derived_cols_trigger_update(): to regenerate instructions: use util.derived_cols_update(), which also performs other updates

schemas/util.sql: derived_cols_sync(): renamed to derived_cols_trigger_update() for clarity

schemas/util.sql: added derived_cols_update()

schemas/util.sql: derived_cols_sync(): renamed to derived_cols_trigger_update() for clarity

schemas/util.sql: derived_col_update(): add comment describing how to modify derived column

schemas/util.sql: added quote_typed(derived_col_def)

schemas/util.sql: added quote_typed(col)

schemas/util.sql: derived_cols_sync(): table__fill_derived(): document in the function comment how to regenerate it

schemas/util.sql: added mk_set_comment(regprocedure, text)

schemas/util.sql: set_comment(): accept anything with a mk_set_comment()

fix: schemas/util.sql: check_constraint_expr_disable(): need to use CASE instead of `true OR ` because OR always evaluates both sides, slowing down constraint evaluation

fix: schemas/util.sql: check_constraint view: conrelid: don't cast to regclass so can use index scan

bugfix: derived_col_constraint_initially_enabled(): use whether existing constraint is already enabled, not whether expr is unchanged (which does not affect the enabled status)

schemas/util.sql: check_constraint_canon_sql(): use util.debug_print_return_value() to assist debugging

bugfix: schemas/util.sql: check_constraint_replace(): need to use util.check_constraint_canon_sql() instead of util.canon_sql()

schemas/util.sql: added check_constraint_canon_sql(). this must be used instead of plain util.canon_sql() because unlike queries, CHECK constraints are not simplified, just standardized.

schemas/util.sql: check_constraint_replace(): support not canon-ing def.expr

bugfix: schemas/util.sql: CHECK constraint exprs: need to remove the enclosing () to be consistent everywhere