schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)

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

fix: schemas/vegbien.sql: _plots_10_count_of_individuals_per_plot_in_each_project: fixed to handle subplots properly, using new locationevent.place_visit_id

schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.

schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.

fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger

bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix

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

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

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

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

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/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