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

schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries

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/vegbien.sql: public_validations: auto-added keys() functions for all queries

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/vegbien.sql: synced with DB, which reorders ~type tables

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.

bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()

bugfix: schemas/vegbien.sql: added back keys() matchup functions, which get cascadingly deleted when the queries are re-created to change column names, etc. note that this causes the associated ~type tables to sort before them.

schemas/vegbien.sql: added new ~type tables

fix: lib/phpPgAdmin.login.php.diff: "For user public_, leave password blank" instruction: moved this to right under the Password field and increased the font size so people would be more likely to see it

/Makefile: added separate phppgadmin-Linux target to avoid needing to run the entire postgres-Linux target whenever http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down (after some system updates)

schemas/vegbien.sql: rm_all_queries(schema): documented this function's unexpected behavior when schema = public_validations (it removes in all schemas, not just public_validations). this is because this uses rm_query_view().

fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries

schemas/vegbien.sql: rm_query_group(): support removing all validations as well as just those with a particular group prefix (and renamed to rm_output_queries())

fix: schemas/vegbien.sql: public_validations: schema comment: removed incorrect "" in `SET search_path = "public_validations", pg_catalog;` now that they are no longer auto-added

bugfix: schemas/Makefile: `%/install: vegbien.sql`: don't enclose public_validations in "" because this causes a comment that says to search for `SET search_path = public_validations, pg_catalog;` to become mangled

schemas/vegbien.sql: synced with DB, which reorders ~type tables

fix: schemas/Makefile: moved comment about publishing a schema to %/publish

lib/Firefox_bookmarks.reformat.csv: "page's description": changed to "page's own description" to clarify that this is a description provided by the page itself

web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for error reporting levels and how to hide stack traces in psql. SQL: added links for recursive queries, which can be used to traverse hierarchical (parent-pointer) tables.

fix: inputs/SALVIAS/validations.sql: renamed SiteCode to plot_code to match output queries

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

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)

fix: schemas/vegbien.sql: added back traits* ~type tables

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)

inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability

fix: schemas/vegbien.sql: rm_all_queries(): all-schemas mode: also remove queries that are only present on the input side, so that the input side can also be re-created from the DDL file

fix: schemas/vegbien.sql: rm_all_queries(): documented that this actually does remove queries in all schemas when schema is NULL, because rm_query_view(public_validations.view) searches all schemas

schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)

schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql

schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)

schemas/vegbien.sql: rm_query_group(): documented that this only removes validations output queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB

bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .

schemas/vegbien.sql: source_by_shortname(): documented that it must be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for each row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)

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

fix: schemas/vegbien.sql: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)

fix: schemas/vegbien.sql: location: added index on top_plot

fix: schemas/vegbien.sql: location: added index on top_plot

fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query

schemas/vegbien.sql: added more ~type tables for plots queries

fix: inputs/SALVIAS/validations.sql: plotMetadata.SiteCode: need to match types with the output query column

schemas/vegbien.sql: added public_validations.rm_all_queries(schema), which removes all validations queries in a schema, or in all schemas (useful when recreating validations queries from the DDL export file)

schemas/util.sql: validation_views(): moved filtering out of _validations schemas to validatable_datasources() so that calling this function with no args gives the validation views in *all schemas as one would expect

schemas/util.sql: query_relations(): support passing in an input query instead of an output query, which will remove only in the datasource's schema. (note that it was not clear that passing in an input-side query was not previously supported.)

schemas/util.sql: added schema_regexp(regclass)

schemas/vegbien.sql: schema_anchor: clarified that this identifies this function's schema (for use in locating helper functions), but is not necessarily the schema operated on, as the comment implied

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

schemas/util.sql: added schema_regexp(schema_anchor)

fix: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: count top-level plots to match the input query

schemas/vegbien.sql: remake_diff_table(): instructions for how to regenerate the diff table: put these at the beginning of the table comment instead of the end, because they are the most important info about the table that a user needs to know

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)

schemas/vegbien.sql: replaced custom %== operators with much simpler custom keys() functions. this avoids both the need to write out an = comparison for each field, and especially, also the need to write both an operator and a function implementing that operator.

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

bugfix: rematerialize_out_view(): set_search_path() does not take the same arguments as mk_set_search_path() did, so need to use mk_search_path() instead

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

schemas/util.sql: added set_search_path()

schemas/util.sql: runnable_sql(): don't output search_path before a SET statement

schemas/util.sql: added is_set_stmt()

schemas/util.sql: diff(text, text): use mk_diff_query()'s new cols param to avoid a nested SELECT

schemas/util.sql: mk_diff_query(): parameterized the customizable parts of the query, to allow them to be replaced with their EXPLAIN expansion (which is what we ultimately want, so that the query does not refer to any internal tables or views)

schemas/util.sql: diff(text, text): use util.mk_diff_query(). this splits the complex query-generation code apart from the query-execution code.

bugfix: schemas/util.sql: undid r12472 because the command needed to limit the effects of any `SET LOCAL search_path` to the current function will unfortunately also clear any existing search_path, which may be needed for the eval query to execute

schemas/util.sql: added mk_diff_query()