schemas/util.sql: diff(text, text): implemented the single_row feature

schemas/util.sql: diff(text, text): added single_row param (not implemented yet, to make reading the svn diff easier). to view this svn diff, it is helpful to use WinMerge, which offers moved block detection (/README.TXT > WinMerge setup).

fix: schemas/util.sql: remake_diff_table(): util.diff() call: include explicit types on arguments to ensure that the correct diff() variant is used

schemas/util.sql: added has_single_row()

schemas/vegbien.sql: remake_diff_table(): prepend ~ to the type table so that it sorts at the end, away from the main tables

bugfix: schemas/vegbien.sql: remake_diff_table(): can't have in_table/out_table inherit from each other, because inheritance also causes the rows of the parent table to be included in the child table. instead, they need to inherit from a common, empty table, as implemented by the new util.remake_diff_table().

schemas/util.sql: added remake_diff_table()

schemas/util.sql: drop_*(): added force param to use CASCADE mode

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/vegbien.sql: added remake_diff_table()

bugfix: inputs/bien2_traits/validations.sql: _traits_01_count_records: changed column names to match public_validations._traits_01_count_records

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

bugfix: schemas/vegbien.sql: setting search_path locally: also 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 (http://www.postgresql.org/docs/9.1/static/sql-set.html#AEN75423)

bugfix: schemas/vegbien.sql: setting search_path locally: need to use `SET LOCAL search_path TO` instead of `PERFORM set_config(search_path, ..., is_local := true)` because the meaning of is_local refers to the entire transaction, not just the current function (as for LOCAL)

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: added rematerialize_in_view()

schemas/vegbien.sql: rematerialize_out_view(): pass a schema-qualified name to util.rematerialize_view() to avoid depending on the search_path to create it in the right schema

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

schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)

schemas/util.sql: added qual_name()

schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)

bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path

schemas/vegbien.sql: functions with schema_anchors: documented that they must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime changes of search_path (schema elements are bound at inline time rather than runtime)

bugfix: schemas/vegbien.sql: functions with schema_anchors: must use plpgsql for these because the sql language does not support runtime changes of search_path (the schema elements they use are bound at inline time rather than runtime)

schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for

fix: schemas/vegbien.sql: schema_null: clarified that this should always be left as the default value, not just usually

schemas/vegbien.sql: schema_null params: use schema_anchor instead of source for this, since it is guaranteed to exist regardless of which tables are in the VegBIEN schema

schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)

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/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.

schemas/util.sql: added rematerialize_view()

bugfix: inputs/bien2_traits/validations.sql: use a wrapper function for util.ifnull() so that the views don't get dropped when the util schema is reinstalled

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

web/links/index.htm: updated to Firefox bookmarks: Redmine: added links to the REST API for exporting data. svn: added instructions for using svnsync to export a repository. PostgreSQL: removed duplicate pages that were linked for multiple versions of Postgres.

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

schemas/vegbien.sql: added schema_anchor, for use with schema_null params

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

/README.TXT: Schema changes: clarified that the staging tables should only be reinstalled if needed

/README.TXT: put ... around all uppercased text, for consistency

_license/non-open-source/applies_to.txt: clarified that this is an exclusions list, not an inclusions list, in terms of what's open-source

validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.

validation/aggregating/*/*.sql: renamed views to match the current naming scheme in the DB. these files still need to be kept up-to-date because there are some queries that haven't been implemented yet, and therefore exist only in these files, not the DB.

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

added inputs/bien2_traits/validations.sql, from validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql

inputs/input.Makefile: $(svnFilesGlob): added validations.sql

bugfix: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added util to search_path so that IFNULL would link to util.ifnull()

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

lib/sh/util.sh: stderr_matches(): documented that the purpose of not redirecting fd 2 back to fd 2 is to allow log-filtering out an otherwise-confusing benign error

lib/sh/util.sh: ignore_err_msg(): documented that unlike `|| true`, this suppresses only errors caused by a particular error message, rather than all error exit statuses

bugfix: lib/runscripts/validations.pg.sql.run: updated table match pattern to include the type prefix that validations queries now contain

bugfix: lib/runscripts/validations.pg.sql.run: --table: need to include explicit schema so that matching tables from other schemas are not included

added inputs/bien2_traits/validations.sql.run

added lib/runscripts/validations.pg.sql.run

lib/sh/util.sh: stdout_contains(): usage: documented that this requires a `{ ... } 41>&1` wrapper

lib/sh/util.sh: stderr2stdout(): clarified that fd 2 is not redirected back to fd 2

bugfix: lib/sh/util.sh: stderr_matches(): need to avoid redirecting stderr and stdout to the same place, because this prevents redirecting stdout back to the original stdout after stderr has been filtered using |

lib/sh/util.sh: local_export_array: renamed to just export_array because this was a replacement for export, not local_export

lib/sh/util.sh: local_*array: don't need -a because that it's an array is autodetected by the ()

lib/sh/util.sh: stderr_matches(): actually don't need to declare PIPESTATUS_ in a separate command, because local does support arrays

lib/sh/util.sh: added local_export_array

fix: lib/sh/util.sh: local_array: clarified that this is only needed for older versions of bash (the lack of support for arrays has apparently been fixed)

lib/sh/util.sh: added local_array

fix: lib/sh/util.sh: stderr_matches(): usage: documented that now need to manually rethrow any command error, if applicable

*{.sh,run}: stderr_matches calls: don't need to wrap the command in `"try"` because stderr_matches now does this

fix: lib/sh/util.sh: `shopt -s lastpipe`: suppress error message if not supported