schemas/util.sql: added subspecies()
schemas/util.sql: added south_america(), in_south_america()
fix: schemas/util.sql: OPERATOR ~(geocoord, postgis.geometry): renamed to because this is not an approximate comparison for geometry
(geocoord, postgis.geometry): renamed to
fix: schemas/util.sql: contained_within_approx(geocoord, postgis.geometry): renamed to contained_within__no_dateline(__) because this is not an approximate comparison for geometry
schemas/util.sql: lat_long_in_new_world(): renamed to just in_new_world() because the lat/long is implied by the param type
schemas/util.sql: lat_long_in_new_world(): take a geocoord param instead of separate lat/long params
schemas/util.sql: added contained_within_approx(geocoord, geometry) and corresponding OPERATOR ~@(geocoord, geometry)
schemas/util.sql: added OPERATOR ~@(geocoord, geography)
schemas/util.sql: lat_long_in_new_world(): use new contained_within_approx(geocoord, geography)
schemas/util.sql: added contained_within_approx(geocoord, postgis.geography), which enables specifying just `(lat, long)` without the ::util.geocoord type specifier
schemas/util.sql: OPERATOR (postgis.geography, postgis.geography): renamed to ~ because it's approximate
(postgis.geography, postgis.geography): renamed to ~
schemas/util.sql: contained_within(): renamed to contained_within_approx() because the latitude lines of geography type bounding boxes bulge outward, creating false positives above and below the bounding box
schemas/util.sql: added contained_within__no_dateline(geometry, geometry) and corresponding operator @
schemas/util.sql: geometry(geocoord): documented that it is not possible to create a cast for this, as a bug in pg_dump prevents the cast from being exported, even when no export filters are applied
schemas/util.sql: point(geocoord): renamed to geometry(geocoord) since this is now a cast
schemas/util.sql: point(): return geometry instead of geography to support using points with geometry arithmetic
schemas/util.sql: point(): take a single util.geocoord param instead of separate lat/long
schemas/util.sql: added geocoord type
schemas/util.sql: bounding_box(): use bounding_box__no_dateline() to construct the postgis.geometry object
schemas/util.sql: added bounding_box__no_dateline(), which is more accurate than util.bounding_box() (latitude lines will be straight), but geocoordinate wraparound is not supported
bugfix: schemas/util.sql: view_def_to_orig(): need to handle cases when list of cols from the same table is not an expanded * expression
schemas/util.sql: added view_is_subset(view_def text)
schemas/util.sql: added view_is_automatically_updatable(view_def text)
bugfix: schemas/util.sql: show_create_view(): use the overridden version of pg_get_viewdef(), which supports expanded * expressions. this was possibly being used already whenever util happened to be in the search_path.
schemas/util.sql: lat_long_in_new_world(): use function rather than operator+search_path to allow inlining, which enables util.new_world() to only be evaluated once
bugfix: schemas/util.sql: operator @(postgis.geography, postgis.geography): must use wrapper function because st_coveredby() needs postgis to be in the search_path
fix: schemas/util.sql: point(): hide benign "Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY" notices
schemas/util.sql: lat_long_in_new_world(): documented that this includes false positives above and below the New World bounding box, as described in util.bounding_box()
schemas/util.sql: bounding_box(): documented that the geography type stores all edges as arcs of great circles, resulting in the latitude lines bulging outward from the true bounding box. this will create false positives above and below the bounding box.
schemas/util.sql: added lat_long_in_new_world()
schemas/util.sql: added operator @(postgis.geography, postgis.geography). can't use && for this because it only compares 2D bounding boxes (which are geometry objects that do not support geocoordinate wraparound).
schemas/util.sql: added point()
schemas/util.sql: new_world(): removed no longer needed cast to postgis.geography
bugfix: schemas/util.sql: bounding_box(): must use postgis.geography (instead of postgis.geometry) because that handles geocoordinate wraparound correctly
bugfix: schemas/util.sql: bounding_box(): need to explicitly set SRID to make sure the correct value is used
bugfix: schemas/util.sql: bounding_box(): use st_makeenvelope() instead of st_makebox2d() because st_makebox2d() doesn't support geocoordinate wraparound (it is not SRID-aware)
schemas/util.sql: new_world(): removed no longer needed cast to postgis.geometry
schemas/util.sql: bounding_box(): return postgis.geometry instead of postgis.box2d because box2d is not directly used in postgis functions
schemas/util.sql: added new_world()
bugfix: schemas/util.sql: bounding_box(): use util.range instead of numrange to support wraparound ranges for geocoordinates
schemas/util.sql: range(numeric, numeric): use util.range instead of numrange to support wraparound ranges (which use a modulus system such as geocoordinates)
schemas/util.sql: added `range` type (and cast to numrange), which allows wraparound ranges such as for geocoordinates
schemas/util.sql: bounding_box(): documented usage
schemas/util.sql: added bounding_box()
schemas/util.sql: range(numeric, numeric): use simpler numrange() constructor instead of text input syntax
schemas/util.sql: operator functions: renamed to plain-text names to follow the Postgres convention of making the function name be the human-readable name of the operator
schemas/util.sql: added ~ operator for ranges
fix: schemas/util.sql: view_def_to_orig(): also need to merge .* expressions resulting from a SELECT * of a join, to avoid duplicated columns
fix: schemas/util.sql: view_def_to_orig(): require at least 6 cols to avoid false positives in the expansion match pattern
bugfix: schemas/util.sql: view_def_to_orig(): 1st col: can't prepend \y because it considers only \w chars, not "
bugfix: schemas/util.sql: view_def_to_orig(): don't match whitespace in the middle of a "" identifier, as this could throw off the parser
schemas/util.sql: eval(): restore user's intent by running util.view_def_to_orig() on the query to unexpand expanded * expressions
schemas/util.sql: pg_get_viewdef(): use util.view_def_to_orig()
schemas/util.sql: added view_def_to_orig()
fix: schemas/util.sql: pg_get_viewdef(): should be STABLE because it references system catalogs
schemas/util.sql: added pg_get_viewdef() wrapper, which unexpands expanded * expressions
bugfix: schemas/util.sql: mk_drop_from_create(): need to match first rather than last CREATE
schemas/util.sql: recreate_view(): support omitting the view_query if the view has already been modified (eg. for public.*_view, which allow changing the view as a separate step)
bugfix: schemas/util.sql: recreate(): need to handle case where util.mk_drop_from_create() is NULL
bugfix: schemas/util.sql: mk_drop_from_create(): only match CREATE if no custom DROP came before it
fix: schemas/util.sql: explain2notice_msg_if_can(): also need to catch invalid_cursor_definition ("cannot open multi-query plan as cursor")
schemas/util.sql: added copy()
schemas/util.sql: added copy_data()
schemas/util.sql: added seq__reset()
schemas/util.sql: added seq__create()
fix: schemas/util.sql: try_cast(), is_castable(): also catch invalid_schema_name, thrown by `'pg_temp.__'::regclass`
fix: schemas/util.sql: mk_drop_from_create(): also support CREATE queries that include the SELECT statement on the same line as the CREATE
schemas/util.sql: create_if_not_exists(): print message if already exists, so the function doesn't inexplicably appear not to have run at all
schemas/util.sql: added recreate_view(), a special case of util.recreate()
fix: schemas/util.sql: recreate(): usage: use `schema` instead of `schemas`
schemas/util.sql: recreate(): perform the correct DROP VIEW in the function itself so that the caller does not have to worry about forming it properly
bugfix: schemas/util.sql: mk_drop_from_create(): added `DROP`
schemas/util.sql: added mk_drop_from_create()
schemas/util.sql: added regexp_match()
fix: schemas/util.sql: force_recreate(): renamed to just recreate(), because "force" normally implies that things will be deleted, which this function does not do
schemas/util.sql: show_create_view(): use new mk_set_relation_metadata()
schemas/util.sql: added mk_set_relation_metadata()
schemas/util.sql: force_recreate(): documented usage, which is somewhat complex and has several important parts
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
bugfix: schemas/util.sql: explain(): don't display any log messages printed by IMMUTABLE functions in the query, which EXPLAIN apparently runs
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)
bugfix: schemas/util.sql: try_cast(), is_castable(): also need to handle syntax_error_or_access_rule_violation, which is thrown by ::regclass
schemas/util.sql: added relation_exists()
schemas/util.sql: save_drop_views()/restore_views(): store the view path in addition to the definition so it can be used in restoring
bugfix: schemas/util.sql: in_reverse(): can't use `SELECT *` for composite types because this expands them
bugfix: schemas/util.sql: array_reverse(): can't use `SELECT *` for composite types because this expands them
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
schemas/util.sql: force_recreate(): recreate_users_cmds: renamed to restore_views_info since this is now whatever is needed by util.restore_views()
schemas/util.sql: added restore_views() and use it in force_recreate()
bugfix: schemas/util.sql: save_drop_views(): views must be dropped in reverse dependency order, but returned in forward dependency order
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().
bugfix: schemas/util.sql: mk_set_comment(): need to use VIEW instead of TABLE for views
bugfix: schemas/util.sql: show_create_view(): also need to include view comment
fix: schemas/util.sql: mk_set_comment(): added ;
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
bugfix: schemas/util.sql: mk_set_comment(): need to handle NULL comment properly
schemas/util.sql: added show_set_comment(regclass)
schemas/util.sql: set_comment(): use util.mk_set_comment()
schemas/util.sql: added mk_set_comment()