schemas/util.sql: added _concat_nullify(), which uses || instead of concat()

fix: schemas/util.sql: view_is_subset_or_renaming(): views with CASE statements are also not subsets/renamings

bugfix: schemas/util.sql: added GRANT USAGE for bien_read, public_ so that util schema functions can be called by other users

bugfix: schemas/util.sql: drop_derived_col(): also need to run util.derived_cols_trigger_update()

fix: schemas/util.sql: view_def_to_orig(): need to support aliased columns (produced when a column is renamed)

schemas/util.sql: added aliased_col_re()

schemas/util.sql: added alias_re()

fix: schemas/util.sql: view_is_subset(): renamed to view_is_subset_or_renaming() because this also supports views that just rename columns, which should not be .*-ed by view_def_to_orig()

fix: schemas/util.sql: view_def_to_orig(): can't use FROM to alias util.col_re() because that prevents inlining the function

fix: schemas/util.sql: view_def_to_orig(): can't use FROM to alias util.col_re() because that prevents inlining the function

schemas/util.sql: view_def_to_orig(): use util.col_re() for clarity

schemas/util.sql: added col_re()

fix: schemas/util.sql: derived_col_update(): also need steps to drop column, because DROP __ CASCADE doesn't work when there are dependent views

bugfix: schemas/util.sql: derived_cols_export(): also need to first run util.derived_cols_remove() if changing the derived column order

schemas/util.sql: added derived_cols_remove(), which allows derived columns to be re-created in a different order

schemas/util.sql: added drop_derived_col(), which ensures that drop_column() only cascades to views

fix: schemas/util.sql: drop_column(): also need to recreate dependent views

schemas/util.sql: added drop_constraint()

schemas/util.sql: remove_prefix(), remove_suffix(): support case-insensitive matching

schemas/util.sql: added case-sensitive-optional versions of starts_with(), ends_with()

schemas/util.sql: added canon_case(str text, case_sensitive boolean)

schemas/util.sql: check_constraint_disable(): documented that we can't use ADD CONSTRAINT NOT VALID for this because it does not disable the constraint completely, but instead causes new rows to be validated against it. the ADD CONSTRAINT NOT VALID behavior does not work for the derived columns use case of this function.

schemas/util.sql: rematerialize_view(): support updating just the table's schema, without populating it

schemas/util.sql: rematerialize_view(): don't need extra eval() around table_mod_sql because recreate() performs eval()

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/util.sql: derived_col_constraint_initially_enabled(): clarified that this is always based on whether the existing constraint is enabled

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

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

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

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

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

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

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

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

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

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

bugfix: schemas/util.sql: canon_sql(): need to include the enclosing () because the DB includes them

fix: schemas/util.sql: canon_sql(): use explain()'s verbose mode to facilitate debugging

bugfix: schemas/util.sql: explain(): need to restore NOTICEs in verbose mode

schemas/util.sql: explain(): support running in verbose mode

bugfix: schemas/util.sql: check_constraint_replace(): need to canon the expr, using new util.canon_sql(), so it can be compared with the expr in the DB

schemas/util.sql: added canon_sql(expr text, table_ regclass)

fix: schemas/util.sql: check_constraint view: don't use pretty-print mode, because this format does not match util.canon_sql()

fix: schemas/util.sql: check_constraint_expr_enable(), derived_col_expr_from_check_constraint(): trim parens in order to support non-pretty-print mode, which is needed with canon_sql()

schemas/util.sql: added canon_sql(expr text)

schemas/util.sql: added trim_parens()

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