schemas/util.sql: added table_item type

schemas/util.sql: col_ref: renamed to just col

fix: inputs/.TNRS/schema.sql: taxon_match: to populate a new column: also need to recluster table so rows are exported in sorted order

schemas/util.sql: cluster(): support omitting index to recluster a table

schemas/util.sql: cluster_once(): use util.cluster()

schemas/util.sql: cluster_once(): documented that this runs cluster only once if called repeatedly

schemas/util.sql: added cluster(), which reclusters each time

schemas/util.sql: cluster_once(): use simpler `LANGUAGE sql` now that we're using util.eval() instead of EXECUTE

schemas/util.sql: cluster_once(): use util.eval() instead of EXECUTE to print the CLUSTER statement (if any) it's using

fix: inputs/.TNRS/schema.sql: taxon_match: cluster the table so the rows are always exported in the same order, even when an UPDATE statement is used to run triggers

bugfix: schemas/util.sql: cluster_once(): need to use just the name of the index, without the schema prefix

schemas/util.sql: cluster_once(): use simpler IS DISTINCT FROM instead of array !=

inputs/.TNRS/schema.sql: added _accepted{genus,specific_epithet} derived column, using the steps at wiki.vegpath.org/Postgres_queries#maintaining-derived-column-formulas-across-column-renames

schemas/vegbien.ERD.mwb: updated

fix: inputs/.TNRS/schema.sql: taxon_match: added back * prefixes on TNRS-namespace column names

inputs/.TNRS/schema.sql: MatchedTaxon: taxon_best_match: don't alias to `s` since this is no longer a nested select

inputs/.TNRS/schema.sql: MatchedTaxon: nested select: use just taxon_best_match since this no longer performs renamings

inputs/.TNRS/schema.sql: MatchedTaxon: renamed output columns to match input columns, so that the nested select would not be performing any renamings

fix: inputs/.TNRS/schema.sql: MatchedTaxon: added all columns from taxon_match (some of them apparently hadn't been included in MatchedTaxon)

fix: inputs/.TNRS/schema.sql: reordered columns to match taxon_match

fix: inputs/.TNRS/schema.sql: MatchedTaxon: added all columns from taxon_match (some of them apparently hadn't been included in MatchedTaxon)

fix: inputs/.TNRS/schema.sql: added back Family_matched, which had gotten merged with Name_matched_accepted_family when TNRS temporarily stopped populating Name_matched_accepted_family

inputs/.TNRS/schema.sql: MatchedTaxon: taxonomicStatus: moved outside the inner SELECT so that the inner SELECT would consist solely of renamings

inputs/.TNRS/schema.sql: MatchedTaxon: removed unneeded "Name_matched." prefix on source-specific names (only the * is needed because there is only one table)

bugfix: schemas/util.sql: view_is_subset(): auto-updatable views that are not plain subsets: include ones with WHERE clauses

bugfix: schemas/util.sql: view_is_subset(): also need to handle auto-updatable views that are not plain subsets (there are some of these)

schemas/public_.sql: removed no longer used threatened_taxonlabel. use iucn_red_list instead.

fix: schemas/public_.sql: analytical_stem_view: don't use threatened_taxonlabel, which was never populated correctly. instead, this can eventually be rewritten to use new iucn_red_list.

bugfix: schemas/public_.sql: analytical_stem_view_modify(): added columns: need to include COMMENT statements

bugfix: schemas/util.sql: comment(element oid): need `objsubid = 0` filter so this doesn't use comments for any other objsubids

schemas/public_.sql: added iucn_red_list_view_modify() and use it in iucn_red_list_view's "after updating this" instructions

bugfix: schemas/util.sql: rematerialize_view(): also need to handle the target table's dependent views

bugfix: schemas/util.sql: mk_drop_from_create(): need to use `IF EXISTS` so that the resulting statement also works if the target object does not yet exist

schemas/util.sql: mk_drop_from_create(): also support `SELECT util.drop_*()` statements, in addition to DROP

schemas/util.sql: replace_words(): use map_words() to enable inlining the concatenated hstore, so that it doesn't need to be concatenated on each function call

schemas/util.sql: added map_words()

schemas/util.sql: added date_from_spanish()

schemas/util.sql: added replace_words()

schemas/util.sql: added words(text)

schemas/util.sql: spanish_date_words(): switched to using hstore because this is better for the algorithm that will translate these

schemas/util.sql: added spanish_date_words()

schemas/util.sql: added `replacement` type

bugfix: mk_set_relation_metadata(): need to include col comments as well so that these are re-created properly by recreate()

schemas/util.sql: added mk_set_comments(regclass)

schemas/util.sql: show_set_comment(regclass): renamed to mk_set_comment() to match other mk_set_comment() functions

schemas/util.sql: added mk_set_col_comments(regclass)

schemas/util.sql: added cols(regclass)

schemas/util.sql: added mk_set_comment(col_ref)

schemas/util.sql: mk_set_comment(table_ regclass, comment text): use new util.mk_set_comment(text, text)

schemas/util.sql: added mk_set_comment(col col_ref, comment text)

schemas/util.sql: added sql(col_ref)

schemas/util.sql: added mk_set_comment(on_ text, comment text)

schemas/util.sql: added comment(col_ref)

schemas/util.sql: added col_num(col_ref)

schemas/public_.sql: iucn_red_list_view: documented how to regenerate iucn_red_list from this

bugfix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: fixed slowdown in materializing the view, which was caused by source__observation_type(), by replacing this with a LEFT JOIN as was done for is_threatened_iucn

schemas/util.sql: in_south_america(): optimized by using BETWEEN instead of util.contained_within__no_dateline(). this reduces the filter time for 2014-6-4.Iara_Lacher.reserve_prioritization by about 10%.

fix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: is_threatened_iucn: generate this much faster by doing a LEFT JOIN, which can reuse the same in-memory hash index for every row

bugfix: schemas/public_.sql: wrapper functions can't use util-schema types because these will cause the wrapper function to be cascadingly dropped when the util schema is reinstalled

bugfix: in_south_america(): must use util.contained_within__no_dateline() instead of util.contained_within_approx() to ensure that the more accurate geometry logic is used

bugfix: schemas/public_.sql: source__observation_type(): don't display NOTICEs about the search_path, because this function will be called millions of times

bugfix: schemas/public_.sql: in_iucn_red_list(): don't display NOTICEs about the search_path, because this function will be called millions of times

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added is_threatened_iucn

schemas/public_.sql: added in_iucn_red_list()

schemas/public_.sql: added iucn_red_list, materialized from iucn_red_list_view

bugfix: schemas/vegbien.sql: iucn_red_list_view: need to include only names with an accepted name, and distinctify on the accepted names

schemas/public_.sql: added iucn_red_list

schemas/util.sql: materialize_view(): use util.copy() instead of util.materialize_query() so that all view metadata is transferred

schemas/public_.sql: iucn_red_list: renamed to iucn_red_list_view since this will be materialized

schemas/public_.sql: added iucn_red_list

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added collection_type (using source__observation_type())

schemas/public_.sql: added source__observation_type()

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added accepted_subspecies, and removed accepted_taxon_name_with_author which had been substituting for it

schemas/public_.sql: added subspecies() wrapper

schemas/util.sql: added subspecies()

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: include only coordinates in South America

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

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

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: 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: 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/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: include only georeferenced occurrences (lat/long NOT NULL)

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

schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: added functional traits that we have 1st-class columns for (dbh_cm, height_m)

inputs/.TNRS/schema.sql: matchedFamily: just use Name_matched_accepted_family, because TNRS has now been reloaded so that the names that were missing this have it populated

inputs/.TNRS/schema.sql: taxon_match: taxon_match__valid_match: replaced with taxon_best_match__valid_match, which also applies taxon_best_match's filters, since taxon_match is now accessed through taxon_best_match