Project

General

Profile

# Date Author Comment
13397 05/03/2014 01:28 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: materialize_query(): contents generated from: need to include search_path so query is runnable

13384 05/02/2014 03:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: added loop_ignore_errors()

13355 04/30/2014 05:32 PM Aaron Marcuse-Kubitza

schemas/util.sql: added _km_to_m() for use when public._km_to_m() is not defined yet

13139 04/15/2014 07:00 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: is_castable(): need to pass NULL through, for proper NULL propagation

13136 04/15/2014 06:12 PM Aaron Marcuse-Kubitza

schemas/util.sql: added is_castable()

13135 04/15/2014 06:10 PM Aaron Marcuse-Kubitza

schemas/util.sql: added try_cast()

13134 04/15/2014 05:51 PM Aaron Marcuse-Kubitza

schemas/util.sql: added util.cast(), which allows casting to an arbitrary type without eval()

13098 04/10/2014 05:26 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: remake_diff_table(): need to rm_freq() type_table, because left/right_table don't have freq yet

13097 04/10/2014 05:18 AM Aaron Marcuse-Kubitza

schemas/util.sql: auto_rm_freq(): use new rm_freq()

13096 04/10/2014 05:17 AM Aaron Marcuse-Kubitza

schemas/util.sql: added rm_freq(regclass[])

13092 04/10/2014 03:10 AM Aaron Marcuse-Kubitza

schemas/util.sql: remake_diff_table(): result table comment: documented how to display NULL values that are extra or missing

12874 03/24/2014 12:45 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: trim(): by default, cascadingly drop dependent columns so that they don't prevent trim() from succeeding. note that this requires the dependent columns to then be manually re-created.

12789 03/20/2014 10:53 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)

12756 03/18/2014 05:26 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output

12755 03/18/2014 05:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added fold_explain_msg()

12734 03/15/2014 05:47 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: set_col_types(): need to COALESCE the executed SQL to '' because util.eval() does not support NULL (and shouldn't, because this indicates a missing COALESCE in constructing the statement)

12733 03/15/2014 05:43 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()

12732 03/15/2014 05:37 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY) for clarity

12725 03/15/2014 05:00 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_not_null()

12686 03/13/2014 06:42 PM Aaron Marcuse-Kubitza

bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query

12685 03/13/2014 06:40 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)

12684 03/13/2014 06:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: try_create(): also handle "child table is missing column" errors

12683 03/13/2014 05:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE

12676 03/13/2014 02:06 AM Aaron Marcuse-Kubitza

schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block

12671 03/12/2014 11:56 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types

12670 03/12/2014 11:53 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())

12668 03/12/2014 11:40 PM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_func_call(text) and use it where applicable

12667 03/12/2014 11:33 PM Aaron Marcuse-Kubitza

schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match

12664 03/12/2014 05:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.

12663 03/12/2014 05:40 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s

12662 03/12/2014 05:38 PM Aaron Marcuse-Kubitza

schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s

12661 03/12/2014 05:36 PM Aaron Marcuse-Kubitza

schemas/util.sql: added freq_always_1(regclass[])

12660 03/12/2014 05:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: added drop_column(regclass[])

12659 03/12/2014 05:04 PM Aaron Marcuse-Kubitza

schemas/util.sql: added parent(regclass)

12658 03/12/2014 04:48 PM Aaron Marcuse-Kubitza

schemas/util.sql: try_create(): also handle not_null_violation, which is thrown when trying to add a NOT NULL column to a parent table, which cascades to a child table whose values for the new column will be NULL

12657 03/12/2014 04:44 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(text, text): also need to cast left_/right_ to base type for the IS DISTINCT FROM filter, because the WHERE clause apparently does not use columns from the SELECT list, even though GROUP BY and ORDER BY do

12656 03/12/2014 04:13 PM Aaron Marcuse-Kubitza

schemas/util.sql: added to_freq(regclass, drop_if_always_1)

12655 03/12/2014 04:04 PM Aaron Marcuse-Kubitza

schemas/util.sql: added auto_rm_freq(regclass)

12654 03/12/2014 03:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: added freq_always_1(regclass)

12653 03/12/2014 03:00 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: diff(regclass, regclass): need to create a diff when the # of copies of a row differs between the tables. this uses new util.to_freq().

12652 03/12/2014 02:44 PM Aaron Marcuse-Kubitza

schemas/util.sql: added to_freq(regclass)

12651 03/12/2014 02:43 PM Aaron Marcuse-Kubitza

schemas/util.sql: added populate_table(regclass, text)

12649 03/12/2014 12:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: added copy_types_and_data(regclass, text)

12595 03/06/2014 12:00 AM Aaron Marcuse-Kubitza

schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)

12594 03/05/2014 11:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(): create the keys() function directly from the keys record type, to support creating just a custom keys record type rather than also a custom keys() function (which is fairly complicated to do, thanks to the need to define a separate custom return type)

12593 03/05/2014 01:01 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: relation_type(relation_type_char "char"): need to handle TYPEs as well, because these are also listed in pg_class (and should be deleted by drop_relations_like())

12592 03/05/2014 12:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: added show_types_like()

12591 03/05/2014 12:42 PM Aaron Marcuse-Kubitza

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

12590 03/05/2014 12:25 PM Aaron Marcuse-Kubitza

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

12588 03/05/2014 07:28 AM Aaron Marcuse-Kubitza

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

12587 03/05/2014 07:24 AM Aaron Marcuse-Kubitza

schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()

12581 03/04/2014 05:52 PM Aaron Marcuse-Kubitza

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

12579 03/04/2014 05:47 PM Aaron Marcuse-Kubitza

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

12577 03/04/2014 05:39 PM Aaron Marcuse-Kubitza

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

12576 03/04/2014 05:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_typed_cols_list(col_cast[])

12575 03/04/2014 05:20 PM Aaron Marcuse-Kubitza

schemas/util.sql: added prefixed_name()

12574 03/04/2014 05:18 PM Aaron Marcuse-Kubitza

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

12572 03/04/2014 04:53 PM Aaron Marcuse-Kubitza

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

12571 03/04/2014 04:45 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword

12570 03/04/2014 04:31 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table

12569 03/04/2014 04:19 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function

12568 03/04/2014 03:55 PM Aaron Marcuse-Kubitza

schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error

12567 03/04/2014 03:53 PM Aaron Marcuse-Kubitza

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

12565 03/04/2014 03:41 PM Aaron Marcuse-Kubitza

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.

12564 03/04/2014 03:35 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_keys_func(regtype)

12563 03/04/2014 03:34 PM Aaron Marcuse-Kubitza

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

12562 03/04/2014 03:02 PM Aaron Marcuse-Kubitza

schemas/util.sql: added typed_cols(regtype)

12561 03/04/2014 02:29 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_keys_func(regtype, col_cast[])

12560 03/04/2014 02:27 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)

12559 03/04/2014 02:23 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_out_params(): need COALESCE around string_agg()

12558 03/04/2014 02:22 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL

12556 03/04/2014 01:59 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_out_params()

12555 03/04/2014 01:42 AM Aaron Marcuse-Kubitza

schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names

12554 03/04/2014 01:40 AM Aaron Marcuse-Kubitza

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.

12553 03/04/2014 01:34 AM Aaron Marcuse-Kubitza

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.

12536 02/27/2014 07:49 PM Aaron Marcuse-Kubitza

schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match

12535 02/27/2014 07:45 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.

12534 02/27/2014 07:41 PM Aaron Marcuse-Kubitza

schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()

12533 02/27/2014 07:37 PM Aaron Marcuse-Kubitza

schemas/util.sql: raise(type text, msg text): documented the possible options for the type param

12532 02/27/2014 07:34 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements

12531 02/27/2014 07:32 PM Aaron Marcuse-Kubitza

schemas/util.sql: eval(): added verbose_ option like for eval2set()

12530 02/27/2014 07:28 PM Aaron Marcuse-Kubitza

schemas/util.sql: added raise(type text, msg text)

12514 02/27/2014 07:59 AM Aaron Marcuse-Kubitza

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

12504 02/26/2014 10:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: added schema_regexp(regclass)

12502 02/26/2014 10:05 PM Aaron Marcuse-Kubitza

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

12501 02/26/2014 10:02 PM Aaron Marcuse-Kubitza

schemas/util.sql: added schema_regexp(schema_anchor)

12498 02/26/2014 11:58 AM Aaron Marcuse-Kubitza

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)

12496 02/26/2014 10:42 AM Aaron Marcuse-Kubitza

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 %==

12495 02/25/2014 11:51 PM Aaron Marcuse-Kubitza

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

12494 02/25/2014 11:49 PM Aaron Marcuse-Kubitza

schemas/util.sql: added prepend_comment()

12493 02/25/2014 11:39 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used mk_set_search_path(VARIADIC schemas text[]). use mk_search_path() instead.

12492 02/25/2014 11:36 PM Aaron Marcuse-Kubitza

schemas/util.sql, vegbien.sql: comment about "function option search_path": don't include `mk_set_search_path()` since that is no longer used

12491 02/25/2014 11:35 PM Aaron Marcuse-Kubitza

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

12490 02/25/2014 11:28 PM Aaron Marcuse-Kubitza

schemas/util.sql: use_own_schema(): renamed to use_schema() because this can be used for any type-linked schema

12488 02/25/2014 11:19 PM Aaron Marcuse-Kubitza

schemas/util.sql: use_own_schema(): auto-append util to the search_path to enable use of util operators

12487 02/25/2014 11:11 PM Aaron Marcuse-Kubitza

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)

12486 02/25/2014 11:06 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_search_path(), mk_set_search_path(text...): auto-append util to the search_path to enable use of util operators

12485 02/25/2014 10:49 PM Aaron Marcuse-Kubitza

schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.

12484 02/25/2014 10:48 PM Aaron Marcuse-Kubitza

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

12483 02/25/2014 10:42 PM Aaron Marcuse-Kubitza

schemas/util.sql: added use_own_schema()