bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables

schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name

bugfix: schemas/util.sql: diff(text, text) and types that use its custom FULL JOIN matching: need to use separate operator %== for determining which records to put on the same row, so that the = filter for identical rows only excludes rows that are actually the same, not all rows with the same keys (which would usually remove all rows)

schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

schemas/vegbien.sql: _plots_02_list_of_project_names: added = operator that causes FULL JOIN to only use some columns, so that rows for the same label column are put on the same row in the diff table

schemas/util.sql: diff(): support custom = operator for the table type (to FULL JOIN on only some columns). this requires casting to the base type, to avoid an ambiguity with the default = operator for record.

schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

fix: schemas/vegbien.sql: rm_query_group(): schemas/vegbien.sql: util.drop_relations_like(): also need to drop ~type tables, so that they will be re-created for the new queries of that group

schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

schemas/util.sql: added drop_relations_like()

fix: schemas/vegbien.sql: public_validations._plots_* that use projectname: re-alias to project_name to match input queries. (reexporting apparently changes the relative order of the ~type tables.)

fix: schemas/vegbien.sql: _plots_03_count_of_all_plots_in_this_source: use top_plot instead of location because the corresponding input query includes just the top-level plots

schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully

schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables

schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)

fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage

schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources

schemas/vegbien.sql: added validatable_datasources()

schemas/vegbien.sql: added validatable_datasources()

schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources

schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas

schemas/util.sql: added str_equality_regexp(literal text)

schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables

schemas/util.sql: util.remake_diff_table(): don't force-re-create type_table. this allows type_table to be shared between multiple diff tables.

schemas/vegbien.sql: public_validations.remake_diff_table(in_view regclass): document how to regenerate the diff table, in a place that's readily available to someone using the table

schemas/util.sql: added curr_func(func text, schema_anchor anyelement)

schemas/util.sql: removed unneeded regproc(regprocedure), regprocedure(regproc) because these can actually be directly cast to one another, not just via oid

schemas/util.sql: added quote_func_call()

schemas/util.sql: added regprocedure(text)

schemas/util.sql: func2regproc*(): removed func2* so that this has the format of a cast function (which it is)

schemas/util.sql: added func2regproc(regprocedure)

schemas/util.sql: added func2regprocedure(regproc)

schemas/util.sql: added quote_typed()

schemas/util.sql: remake_diff_table(): use util.append_comment() instead of util.set_comment() to allow other functions to add comments to the diff table

schemas/util.sql: added append_comment(regclass)

schemas/util.sql: added comment(oid)

fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.

bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param

schemas/vegbien.sql: added query_view_relation_max_prefix_len()

fix: schemas/util.sql: drop_relation(text), rename_relation(text, text): appended _esc to params that should be passed in escaped, and _name to those that shouldn't

bugfix: schemas/util.sql: rename_relation(): need quote_ident() around to_

bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to match the entire string, so that the regexp can be used with util.replace_suffix()

bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to pass max_prefix_len to util.name_was_truncated() because although the name itself might not have been truncated, it might become truncated once prefixed

schemas/util.sql: name_was_truncated(): support max_prefix_len param to determine truncation of something that will be prefixed

fix: name_was_truncated(): should use util.namedatalen() instead of hardcoding the value

schemas/util.sql: replace_suffix(): support strings that have been truncated (eg. as a table name) due to the prepending of a prefix

schemas/util.sql: added truncated_prefixed_name_regexp()

schemas/util.sql: added rtrim_n()

schemas/util.sql: added name_was_truncated()

schemas/util.sql: added namedatalen()

schemas/util.sql: drop_relation(regclass), rename_relation(regclass): use util.qual_name() instead of ::text so that the schema qualifier is always included in the debug SQL

schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query

schemas/vegbien.sql: added new_query_view_relation_name()

schemas/util.sql: added replace_suffix()

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

fix: schemas/util.sql: drop_relation(): documented that it is actually not idempotent, because the regclass will not be valid after the relation is dropped

schemas/util.sql: drop_relation(relation text, type text): put type param first because it is usually constant

schemas/util.sql: drop_table(), drop_view(): use drop_relation(relation text, type text)

schemas/util.sql: drop_relation(regclass): use simpler drop_relation(relation text, type text)

schemas/util.sql: added relation_type(regclass)

schemas/util.sql: added drop_relation(relation text, type text)

bugfix: schemas/util.sql: drop_relation(): need to pass force param to util.drop_*()

schemas/util.sql: added relation_type_char(regclass)

bugfix: schemas/util.sql: relation_type(character): must use "char" type for single character, as character actually allows multiple characters. corrected return type.

schemas/util.sql: added relation_type(character)

schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query

fix: schemas/vegbien.sql: remake_diff_tables(): need to set function option search_path in order to limit the effects of `SET LOCAL search_path` (mk_set_search_path()) to the current function

fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views

schemas/vegbien.sql: added public_validations.query_views()

schemas/util.sql: schema_matches(): should be IMMUTABLE, not STABLE, because it uses only parameter values

schemas/util.sql: added regexp_quote()

fix: schemas/util.sql: is_table(), is_view(): should be STABLE, not IMMUTABLE, because they reference tables

schemas/util.sql: added drop_relation()

schemas/util.sql: added is_table()

schemas/util.sql: added is_view()

bugfix: schemas/util.sql: show_views_like(): need to match the schema exactly, not via regexp like util.show_relations_like() does

schemas/util.sql: show_views_like(): use util.show_relations_like()

schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()

schemas/util.sql: added show_relations_like()

schemas/util.sql: added schema_matches()

schemas/vegbien.sql: remake_diff_tables(): updated runtime now that row limit has been removed (same, 45 min)

fix: schemas/vegbien.sql: _traits_07_trait_value_and_units, _traits_08_taxonname_trait_and_value_for_first_5000_records: removed LIMIT to match the input queries

schemas/util.sql: materialize_query(): ANALYZE the created table so that it has the correct estimated rowcount

schemas/util.sql: added analyze_()

schemas/vegbien.sql: remake_diff_tables(): updated bien2_traits runtime (45 min) to include the now-properly-renamed views 6-8

schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)

fix: schemas/vegbien.sql: renamed output views to match input views

schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)

bugfix: schemas/vegbien.sql: source_by_shortname(): resolved ambiguous column references by renaming PL/pgSQL vars and adding table qualifiers

bugfix: schemas/vegbien.sql: source_by_shortname(): changed from STABLE to VOLATILE because the search_path can only be set in VOLATILE functions

bugfix: schemas/vegbien.sql: source_by_shortname(): can't be declared STRICT because it now has a 2nd param (schema_anchor) which is always NULL

bugfix: schemas/vegbien.sql: remake_diff_tables(): can't raise exception at end if had errors, because this would roll back the transaction. instead use new util.raise_error_notice().

schemas/util.sql: added raise_error_notice()

schemas/vegbien.sql: added remake_diff_tables()

bugfix: schemas/util.sql: diff(text, text): also need to include only different rows when comparing single-row tables

schemas/vegbien.sql: added validation_views()

schemas/util.sql: show_views_like(): added predictable ordering