Project

General

Profile

# Date Author Comment
13499 05/21/2014 12:59 AM Aaron Marcuse-Kubitza

schemas/util.sql: force_recreate(): documented usage, which is somewhat complex and has several important parts

13497 05/20/2014 05:40 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: restore_views(): need to specify view name for a manual existence check, in case the view definition becomes invalid, which would produce a nonstandard (uncatchable) exception

13496 05/20/2014 05:33 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: explain(): don't display any log messages printed by IMMUTABLE functions in the query, which EXPLAIN apparently runs

13495 05/20/2014 05:21 PM Aaron Marcuse-Kubitza

schemas/util.sql: create_if_not_exists(): added relation param, which can be used to ensure that a standard exception is always generated if the relation exists, even if the table definition would be invalid (which generates a variety of exceptions)

13493 05/19/2014 09:08 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: try_cast(), is_castable(): also need to handle syntax_error_or_access_rule_violation, which is thrown by ::regclass

13492 05/19/2014 08:52 PM Aaron Marcuse-Kubitza

schemas/util.sql: added relation_exists()

13491 05/19/2014 08:27 PM Aaron Marcuse-Kubitza

schemas/util.sql: save_drop_views()/restore_views(): store the view path in addition to the definition so it can be used in restoring

13490 05/19/2014 08:24 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: in_reverse(): can't use `SELECT *` for composite types because this expands them

13489 05/19/2014 08:21 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: array_reverse(): can't use `SELECT *` for composite types because this expands them

13488 05/19/2014 07:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: save_drop_views()/restore_views(): use opaque return type so it can be changed without changing the local var type in functions that use this

13487 05/19/2014 07:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: force_recreate(): recreate_users_cmds: renamed to restore_views_info since this is now whatever is needed by util.restore_views()

13486 05/19/2014 07:16 PM Aaron Marcuse-Kubitza

schemas/util.sql: added restore_views() and use it in force_recreate()

13485 05/19/2014 02:07 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: save_drop_views(): views must be dropped in reverse dependency order, but returned in forward dependency order

13484 05/19/2014 01:36 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: force_recreate(): dependent views need to be re-created in forward dependency order. this is done by reversing the dependencies only for save_drop_views().

13483 05/19/2014 12:48 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_set_comment(): need to use VIEW instead of TABLE for views

13482 05/19/2014 12:29 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: show_create_view(): also need to include view comment

13481 05/19/2014 12:24 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: mk_set_comment(): added ;

13480 05/19/2014 12:20 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: show_set_comment(): don't display a COMMENT ON statement if no comment, because this will be appended to table defs, etc. and would create clutter

13479 05/19/2014 12:16 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_set_comment(): need to handle NULL comment properly

13478 05/19/2014 12:06 AM Aaron Marcuse-Kubitza

schemas/util.sql: added show_set_comment(regclass)

13477 05/18/2014 11:57 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_comment(): use util.mk_set_comment()

13474 05/17/2014 06:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added mk_set_comment()

13471 05/17/2014 05:36 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: force_recreate(): recreate_users_cmds: use create_if_not_exists() rather than eval(), because cmd might manually re-create a deleted dependent view, causing it to already exist

13470 05/17/2014 05:27 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: save_drop_view(): util.show_create_view(): don't include `OR REPLACE` because that causes nonuniform errors (eg. invalid_table_definition), instead of the standard duplicate_table exception caught by util.create_if_not_exists()

13469 05/17/2014 05:25 PM Aaron Marcuse-Kubitza

fix: schemas/util.sql: show_create_view(): removed extra trailing ; because pg_get_viewdef() now includes one

13468 05/17/2014 05:24 PM Aaron Marcuse-Kubitza

schemas/util.sql: show_create_view(): made inclusion of `OR REPLACE` configurable

13467 05/17/2014 05:10 PM Aaron Marcuse-Kubitza

schemas/util.sql: save_drop_view(): use util.drop_view()

13456 05/13/2014 11:19 AM Aaron Marcuse-Kubitza

schemas/util.sql: save_drop_views(): return text[] instead of concatenated text so that the queries will be run individually and be easier to debug

13455 05/13/2014 11:00 AM Aaron Marcuse-Kubitza

schemas/util.sql: added eval(queries text[])

13454 05/13/2014 09:43 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: force_recreate(): need to drop users in reverse to avoid dependencies that block the drop

13453 05/13/2014 09:25 AM Aaron Marcuse-Kubitza

schemas/util.sql: added array_reverse()

13452 05/13/2014 09:25 AM Aaron Marcuse-Kubitza

schemas/util.sql: added in_reverse()

13451 05/13/2014 08:39 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: force_recreate(): need to use :lower:, not [:lower:]

13450 05/13/2014 08:34 AM Aaron Marcuse-Kubitza

schemas/util.sql: force_recreate(): debug-print PG_EXCEPTION_DETAIL, users to assist in debugging

13449 05/13/2014 08:33 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: debug_print_value(): added trailing newline to visually distinguish the printed value

13448 05/13/2014 08:27 AM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_var()

13447 05/13/2014 07:06 AM Aaron Marcuse-Kubitza

schemas/util.sql: debug_print_return_value(): use new util.debug_print_value()

13446 05/13/2014 06:59 AM Aaron Marcuse-Kubitza

schemas/util.sql: added debug_print_value()

13445 05/13/2014 05:45 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: util.force_recreate(): also support dropping things besides tables

13403 05/03/2014 03:10 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: explain2notice_msg_if_can(): don't use util.is_explainable() because the list provided by Postgres (http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691) excludes some query types that are in fact EXPLAIN-able

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