fix: schemas/util.sql: diff(regclass, regclass): usage: col_type_null must actually be a shared base type of the tables

fix: schemas/util.sql: diff(regclass, regclass): usage: text arguments must be cast to regclass to bind the right variant of diff()

fix: schemas/util.sql: diff(regclass, regclass): renamed params to *_table because these actually can't be views, since views don't support inheritance

schemas/util.sql: added copy_struct()

schemas/util.sql: added inherit()

bugfix: schemas/util.sql: diff(): need to explicitly cast each side to the return type because this does not happen automatically even when an implicit cast is available

schemas/util.sql: renamed diff_views(), diff_any() to diff(...) because they are overloads of the same operation

schemas/util.sql: diff_any(): documented how to run EXPLAIN on the FULL JOIN query

schemas/util.sql: removed non-useful diff_queries() (it is not possible to match distinct `record` types to a common return type, so this would not have worked). use diff_views() instead.

bugfix: schemas/util.sql: diff_queries(), diff_views(): embed views verbatim as FROM items, so that the type of their corresponding columns is the view's rowtype. this is necessary in order to match the types of the columns to the return type (set by col_type_null).

schemas/util.sql: diff_cols(): renamed to diff_queries() because multiple columns are supported

fix: schemas/util.sql: diff_cols(): don't assume that each side will have one column, as many of our validations queries have multiple columns

bugfix: schemas/util.sql: diff_views(): need to use $2, not $1, for the 2nd query

fix: schemas/util.sql: materialize_query(): remove any extra blank line before the query, to improve readability when newlines are used as a log message separator and are therefore confusing in the middle of a log message

schemas/util.sql: added ltrim_nl()

schemas/util.sql: added mk_set_search_path(VARIADIC schemas text[])

schemas/util.sql: added mk_use_own_schema()

schemas/util.sql: added mk_set_search_path()

schemas/util.sql: use builtin string_agg(..., ) instead of concat()

schemas/util.sql: added qual_name(type unknown) to avoid needing to explicitly specify the type of an input string

schemas/util.sql: qual_name(table_ regclass): use ::text with search_path=pg_temp like qual_name(type regtype), rather than casting to text and then using qual_name(type regtype)

schemas/util.sql: type_qual_name(type regtype): renamed to qual_name(type regtype) to match qual_name(table_ regclass)

schemas/util.sql: added rematerialize_query()

schemas/vegbien.sql: materialize_query(): support schema-qualified tables. this requires escaping tables in calls to this function.

schemas/util.sql: added qual_name()

schemas/util.sql: use new util.debug_print_sql() instead of `RAISE NOTICE` to improve readability of the debug output (which is used throughout the runscripts)

schemas/util.sql: added debug_print_sql()

schemas/util.sql: added raise_notice(), to allow NOTICEs to be raised in sql-language functions

schemas/util.sql: added rematerialize_view()

schemas/util.sql: schema(regtype), schema(regclass): use new schema(pg_namespace_oid oid)

schemas/util.sql: added schema(pg_namespace_oid oid)

schemas/util.sql: schema(regclass): use joins instead of nested SELECTs for consistency with schema(regtype)

schemas/util.sql: regenerated from DB, which reorders the functions

schemas/util.sql: table_name(): renamed to name(regclass) for consistency with other name(...) functions

schemas/util.sql: table_schema(): renamed to schema(regclass) for consistency with other schema(...) functions

schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity

schemas/util.sql: added mk_search_path()

schemas/*.sql: make sure every COMMENT starts and ends on its own line, so that it appears correctly in the formats it's most likely to be read in (ie. in the DDL export, not the COMMENT edit box in pgAdmin)

schemas/util.sql: added materialize_view()

schemas/util.sql: added table_name()

schemas/util.sql: table_schema(): removed unneeded STRICT, which prevents inlining

fix: schemas/util.sql: fix_array(): removed no longer accurate comment about effects of STRICT, which is no longer used

fix: schemas/util.sql: materialize_query(): 1st param should be named table_, not view_

schemas/util.sql: added drop_view()

schemas/util.sql: added materialize_query()

schemas/util.sql: ifnull(): use COALESCE instead, because it turns out that MySQL's IFNULL is just a special case of this

schemas/util.sql: added ifnull(), equivalent to MySQL's IFNULL (Postgres auto-lowercases the name)

schemas/util.sql: diff_cols(): documented how to run EXPLAIN on the FULL JOIN query

schemas/util.sql: diff_cols(): embed left_query/right_query directly in the main SQL statement, so that the query planner can take the sort order of the queries into account in planning the FULL JOIN (and where possible, use a fast merge join)

schemas/util.sql: added eval2col_pair()

schemas/util.sql: added functions for maintaining schema bundles, groups of schemas with a common prefix

schemas/util.sql: added schema_bundle_get_schemas()

schemas/util.sql: added schema_replace()

schemas/util.sql: added schema_rm(), schema_rename()

schemas/util.sql: added diff_views()

schemas/util.sql: added diff_cols(), for use with the aggregating validations

moved everything into /trunk/ to create the standard svn layout, for use with tools that require this (eg. git-svn). IMPORTANT: do NOT do an `svn up`. instead, re-use your working copy's existing files with `svn switch` (http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.switch.html).

schemas/util.sql: explain2table(): documented usage:
PERFORM util.explain2table($$
query
$$);

schemas/util.sql: explain2table(): by default, use the util.explain table

schemas/util.sql: added explain table

schemas/util.sql: added explain2notice()

schemas/util.sql: added explain2str()

schemas/util.sql: added explain2table()

schemas/util.sql: added explain()

schemas/util.sql: added force_recreate(), for use by sync_analytical_stem_to_view(). this uses the new `GET STACKED DIAGNOSTICS` in PostgreSQL 9.3 to access the DETAIL section of the dependent_objects_still_exist error.

schemas/*.sql: updated for PostgreSQL 9.3. this reorders some functions, adds empty comment headers for omitted SEQUENCE SET commands, and (best of all) finally splits view columns onto multiple lines, so that changes in the columns are actually legible (and produce their own svn diff!)

schemas/util.sql: added save_drop_views()

schemas/util.sql: added is_empty(anyarray)

schemas/util.sql: added regexp_matches_group()

schemas/util.sql: show_create_view(): also include GRANT statements, which are necessary to fully re-create the view

schemas/util.sql: added show_grants_for(table_ regclass), for use by show_create_view()

schemas/util.sql: added save_drop_view()

schemas/util.sql: added show_create_view()

schemas/util.sql: added mk_subset_by_row_num_func(regclass), which uses a sort_col instead of a row_num column

schemas/util.sql: mk_subset_by_row_num_func(): factored creation of no-sort subset function into mk_subset_by_row_num_no_sort_func() so it can be used by other functions

schemas/util.sql: added ||% operator to append to escaped strings (the % indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers

bugfix: schemas/util.sql: mk_subset_by_row_num_func(): need explicit ORDER BY on the row_num_col, to prevent PostgreSQL from sorting the rows in reverse (they will be sorted because of the index scan, but the direction is otherwise indeterminate)

schemas/util.sql: mk_subset_by_row_num_func(): support tables whose pkeys don't start with 1 (such as the VegBank tables), by calculating the smallest row_num from the table

schemas/util.sql: offset2row_num(), limit2row_num(): added optional min_row_num, for tables whose serial pkeys start from a value other than 1

schemas/util.sql: added qual_name(regclass)

schemas/util.sql: added esc_name__append()

schemas/util.sql: added col__min()

schemas/util.sql: added limit2row_num() and use it in mk_subset_by_row_num_func() for clarity

schemas/util.sql: added offset2row_num() and use it in mk_subset_by_row_num_func() for clarity

schemas/util.sql: added schema_ident()

schemas/util.sql: added schema(regtype), schema(anyelement)

bugfix: schemas/util.sql: set_col_names_with_metadata(): rename any metadata cols rather than re-adding them with new names

schemas/util.sql: combining functions taking anyelement params which could be text: take text param instead, so that other argument types (e.g. integer) will first be implicitly cast to text instead of trying to concatenate integers directly. this fixes a bug in the VegBank.stemcount_,stemlocation_ _join() of two integer pkeys, which first needed to be cast to text. anyelement was previously used so that other text-like types such as varchar could also be used, but varchar is implicitly castable to text so keeping anyelement should not be necessary.

schemas/util.sql: added ?*>= operator (is populated more often than) from wiki.vegpath.org/Postgres_queries#is-populated-more-often-than

schemas/util.sql: hstore(text[], anyelement): replaced with just hstore(text[], text) so that bare literals (of type unknown) are auto-cast to text instead of causing a "could not determine polymorphic type because input has type "unknown"" error

bugfix: schemas/util.sql: reset_col_names(): remove metadata value columns from the map table entirely, so that they are not considered to be in the table (old-style import does not use metadata value columns, so there would be no column or a different column in the metadata value column's slot)

bugfix: schemas/util.sql: trim(): need to remove all added columns, not just derived columns, because metadata value columns are also added by new-style import and need to be removed to restore a staging table to its pre-new-style state

schemas/util.sql: added added_cols()

bugfix: schemas/util.sql: reset_col_names(): need to avoid renaming metadata value columns, because the "from" entry is a value, not a column name

schemas/util.sql: added ?>= operator with is_more_complete_than() function

schemas/util.sql: SQL-language IMMUTABLE functions marked STRICT: removed STRICT to enable dynamic inlining, which speeds up the function up to 7x. STRICT was not removed where the function was particularly complex and the STRICT optimization would likely be more significant than inlining.

schemas/util.sql: schema comment: added note that IMMUTABLE SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding.

schemas/util.sql: map_nulls(): documented that inputs/REMIB/Specimen/postprocess.sql > country also shows that inlining is now happening properly. note that the speed improvement due to inlining is not as much, %wise, when the values util._map() is run on are long strings instead of the short strings used in the initial profiling. this is because a greater % of the time is spent in system functions such as hstore>text, which are not affected by the inlining because they are run either way.

schemas/util.sql: map_nulls(): use new nulls_map(). proper inlining (i.e. same runtime before and after change) has been verified with the following profiling query:
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array¹⁰⁰⁰⁰⁰)) f (v)

schemas/util.sql: added nulls_map(), for use with _map()