schemas/util.sql: added show_types_like()

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

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

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

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

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

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)

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

schemas/util.sql: added schema_regexp(regclass)

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

schemas/util.sql: added schema_regexp(schema_anchor)

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)

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

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

schemas/util.sql: debug_print_sql(): use runnable_sql()

schemas/util.sql: added runnable_sql()

fix: schemas/util.sql: eval2*(): added search_path function option in order to limit the effects of any `SET LOCAL search_path` in the invoked query to the current function. however, plain eval() is not changed because it is often used to execute a `SET LOCAL search_path` in the calling function.

schemas/util.sql: diff(text, text): removed unnecessary casts to the base type in the join condition and the WHERE filter. these had been presumed necessary due to errors, but the errors turned out to be caused by the operator not being in the search_path. note that the casts in the columns list are still needed, as described in the associated comment.

schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from

schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL

schemas/util.sql: mk_set_search_path(text): include LOCAL commented out, because the user might want to run it with another statement as a single command, in which case it would be useful because they will be in the same transaction (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)

schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)

schemas/util.sql: mk_set_search_path(): support creating a SET search_path statement for display as well, which would exclude LOCAL because it doesn't work as a standalone command (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)

schemas/util.sql: debug_print_sql(): include the search_path in case the query contains search_path-dependent elements (such as operators)

schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info

schemas/util.sql: added mk_set_search_path() (which uses the current search_path)

schemas/util.sql: diff(text, text): in the outputted FULL JOIN query, documented that you should refer to the EXPLAIN output for the expansion of %==

bugfix: schemas/util.sql: first_word(): ignore leading whitespace: need to use util.ltrim_nl(), as ltrim() only removes spaces

bugfix: schemas/util.sql: first_word(): need to ignore leading whitespace. this applies to many of our queries, which have a leading newline.

schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output

schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements

schemas/util.sql: added is_explain()

fix: schemas/util.sql: diff(text, text): FULL JOIN SELECT statement: don't put a comment at the very beginning, because this prevents it from being autoexplained (this is the query we particularly want EXPLAIN output for)

fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any

fix: schemas/util.sql: debug_print_sql(): need newline before and after EXPLAIN output to visually separate it from other debug info

schemas/util.sql: util.debug_print_sql(): include the EXPLAIN output of applicable queries, for easier query plan debugging (just like lib/sql.py's db.autoexplain)

schemas/util.sql: added explain2notice_msg_if_can()

schemas/util.sql: added is_explainable(sql)

schemas/util.sql: added first_word()

schemas/util.sql: explain2notice(): use util.raise_notice()

schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually

fix: schemas/util.sql: schema comment: noted that avoiding use of STRICT also makes functions much easier to troubleshoot, because they won't mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements

schemas/util.sql, vegbien.sql: do not declare any SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment). the STRICT qualifier has been preserved where its behavior is necessary, rather than just used for optimization.

fix: schemas/util.sql: schema comment: indicate that all SQL-language functions should never be declared STRICT, not just IMMUTABLE ones, since inlining is not limited to IMMUTABLE (or STABLE) functions (although constant folding is)

bugfix: schemas/util.sql: join_strs_transform(): added back STRICT qualifier because it must be declared STRICT to use the special handling of STRICT aggregating functions

schemas/util.sql: %==(anyelement, anyelement): needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)

schemas/util.sql, vegbien.sql: do not declare IMMUTABLE SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment)

fix: schemas/util.sql: diff(text, text): use util.typeof() instead of pg_typeof() so the outputted query is runnable in any search_path

schemas/util.sql: added typeof(), which unlike pg_typeof() uses util.qual_name() to create a search_path-independent name

fix: schemas/util.sql: quote_typed(): schema-qualify the type so the expression does not depend on the search_path

schemas/util.sql: %== : use custom keys() function for the type, so that the user only has to get the keys from their value, not also write the comparison of those keys

schemas/util.sql: diff(regclass, regclass): support custom %== by including the col_type_null's schema in the search_path