bugfix: schemas/util.sql: materialize_query(): contents generated from: need to include search_path so query is runnable

schemas/util.sql: added loop_ignore_errors()

schemas/util.sql: added _km_to_m() for use when public._km_to_m() is not defined yet

bugfix: schemas/util.sql: is_castable(): need to pass NULL through, for proper NULL propagation

schemas/util.sql: added is_castable()

schemas/util.sql: added try_cast()

schemas/util.sql: added util.cast(), which allows casting to an arbitrary type without eval()

bugfix: schemas/util.sql: remake_diff_table(): need to rm_freq() type_table, because left/right_table don't have freq yet

schemas/util.sql: auto_rm_freq(): use new rm_freq()

schemas/util.sql: added rm_freq(regclass[])

schemas/util.sql: remake_diff_table(): result table comment: documented how to display NULL values that are extra or missing

fix: schemas/util.sql: trim(): by default, cascadingly drop dependent columns so that they don't prevent trim() from succeeding. note that this requires the dependent columns to then be manually re-created.

bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)

fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output

schemas/util.sql: added fold_explain_msg()

bugfix: schemas/util.sql: set_col_types(): need to COALESCE the executed SQL to '' because util.eval() does not support NULL (and shouldn't, because this indicates a missing COALESCE in constructing the statement)

schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()

schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY) for clarity

schemas/util.sql: added mk_not_null()

bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query

fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)

schemas/util.sql: try_create(): also handle "child table is missing column" errors

schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE

schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types

bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())

schemas/util.sql: added debug_print_func_call(text) and use it where applicable

schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match

schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.

bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s

schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s

schemas/util.sql: added freq_always_1(regclass[])

schemas/util.sql: added drop_column(regclass[])

schemas/util.sql: added parent(regclass)

schemas/util.sql: try_create(): also handle not_null_violation, which is thrown when trying to add a NOT NULL column to a parent table, which cascades to a child table whose values for the new column will be NULL

bugfix: schemas/util.sql: diff(text, text): also need to cast left_/right_ to base type for the IS DISTINCT FROM filter, because the WHERE clause apparently does not use columns from the SELECT list, even though GROUP BY and ORDER BY do

schemas/util.sql: added to_freq(regclass, drop_if_always_1)

schemas/util.sql: added auto_rm_freq(regclass)

schemas/util.sql: added freq_always_1(regclass)

bugfix: schemas/util.sql: diff(regclass, regclass): need to create a diff when the # of copies of a row differs between the tables. this uses new util.to_freq().

schemas/util.sql: added to_freq(regclass)

schemas/util.sql: added populate_table(regclass, text)

schemas/util.sql: added copy_types_and_data(regclass, text)

schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)

schemas/util.sql: mk_keys_func(): create the keys() function directly from the keys record type, to support creating just a custom keys record type rather than also a custom keys() function (which is fairly complicated to do, thanks to the need to define a separate custom return type)

bugfix: schemas/util.sql: relation_type(relation_type_char "char"): need to handle TYPEs as well, because these are also listed in pg_class (and should be deleted by drop_relations_like())

schemas/util.sql: added show_types_like()

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

schemas/util.sql: added relation_type(regtype) so drop_relation() would work on TYPEs, too. TYPEs are sometimes used as a function return type linked to a particular table (eg. in the keys() functions), and should be dropped along with the table by util.drop_relations_like().

schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use `CREATE FUNCTION` instead of `CREATE OR REPLACE FUNCTION` so that the function is only created if it does not exist, to avoid overwriting a custom keys() function

bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use named return type to avoid complicated edge cases with 1-column records, which prevent (values_(...)).* from working correctly

schemas/util.sql: added mk_typed_cols_list(col_cast[])

schemas/util.sql: added prefixed_name()

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity

schemas/util.sql: util.remake_diff_table(): viewing the table in human-readable form: use just the output side's values_() columns, to avoid repeating columns on both sides that are the same and put just the changed columns side-by-side

bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword

schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table

schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function

schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.create_if_not_exists() to avoid trying to overwrite a custom keys() function the user has written

schemas/util.sql: diff(text, text): automatically create a keys() function for the base type. this avoids the need to create keys() functions manually for the numerous queries that need them.

schemas/util.sql: added mk_keys_func(regtype)

bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): need to handle 1-column records specially, because Postgres does not allow returning a 1-column record when there are OUT params

schemas/util.sql: added typed_cols(regtype)

schemas/util.sql: added mk_keys_func(regtype, col_cast[])

bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)

bugfix: schemas/util.sql: mk_out_params(): need COALESCE around string_agg()

bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL

schemas/util.sql: added mk_out_params()

schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names

schemas/util.sql: mk_diff_query(): removed special handling for CROSS JOIN because this is now handled by diff(text, text) using `FULL JOIN ON true`. this simplification allows mk_diff_query() to contain just the template structure of the FULL JOIN, without _if() calls that decrease readability.

schemas/util.sql: diff(text, text): handle the CROSS JOIN special case using `FULL JOIN ON true`, to allow mk_diff_query() to support just a straighforward FULL JOIN. this also ensures that all diff queries use the same FULL JOIN template.

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.

schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()

schemas/util.sql: raise(type text, msg text): documented the possible options for the type param

fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements

schemas/util.sql: eval(): added verbose_ option like for eval2set()

schemas/util.sql: added raise(type text, msg text)

schemas/util.sql: eval2val(): made it STABLE so that functions that use it can themselves be inlined. (Postgres apparently ignores the STABLE qualifier, without warning, if the function contains any VOLATILE statements.)

schemas/util.sql: added schema_regexp(regclass)

schemas/util.sql: drop_relations_like(): use util.schema_regexp()

schemas/util.sql: added schema_regexp(schema_anchor)

schemas/util.sql: diff(text, text): documented how to write a custom keys() function to match up rows using a subset of the columns (including a sample keys() function template)

bugfix: schemas/util.sql: diff(text, text): in the %== comparison, it turns out you do need to cast the values to the same base type, even though this is optional when using a custom %==

schemas/util.sql: remake_diff_table(): prepend the "view this table in human-readable form" comment instead of appending because it is more important than the "contents generated from" comment previously added

schemas/util.sql: added prepend_comment()

schemas/util.sql: removed no longer used mk_set_search_path(VARIADIC schemas text[]). use mk_search_path() instead.

schemas/util.sql, vegbien.sql: comment about "function option search_path": don't include `mk_set_search_path()` since that is no longer used

schemas/util.sql: diff(text, text): always use the schema of col_type_null (the common base type) as the search_path, since any custom %== operator for it will always be in the same schema as it

schemas/util.sql: use_own_schema(): renamed to use_schema() because this can be used for any type-linked schema

schemas/util.sql: use_own_schema(): auto-append util to the search_path to enable use of util operators

schemas/util.sql: mk_set_search_path(): no need to debug_print_return_value() anymore because functions now use set_search_path() (or something that calls it), which debug-prints the statement (`EXECUTE util.mk_set_search_path()` did not)

schemas/util.sql: mk_search_path(), mk_set_search_path(text...): auto-append util to the search_path to enable use of util operators

schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.

schemas/util.sql, vegbien.sql: use util.use_own_schema()/util.set_search_path() instead of EXECUTE util.mk_use_own_schema()/util.mk_set_search_path()

schemas/util.sql: added use_own_schema()