/trunk/schemas/util.sql - Annotate - BIEN 3 - NCEAS Projects

root/trunk/schemas/util.sql @ 13910

-            aaronmk
+--
 -- PostgreSQL database dump
 --
 SET statement_timeout = 0;
-            aaronmk
+SET lock_timeout = 0;
-            aaronmk
+SET client_encoding = 'UTF8';
-            aaronmk
+SET standard_conforming_strings = on;
-            aaronmk
+SET check_function_bodies = false;
 SET client_min_messages = warning;
 --
-            aaronmk
+-- Name: util; Type: SCHEMA; Schema: -; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE SCHEMA util;
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON SCHEMA util IS '
 IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
             aaronmk
-            aaronmk
+NOTE: SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding. 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.
-            aaronmk
+';
             aaronmk
             aaronmk
-            aaronmk
+SET search_path = util, pg_catalog;
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE col_cast AS (
 	col_name text,
 	type regtype
 );
 --
-            aaronmk
+-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE col_ref AS (
 	table_ regclass,
 	name text
 );
 --
-            aaronmk
+-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE compass_dir AS ENUM (
     'N',
     'E',
     'S',
     'W'
 );
 --
-            aaronmk
+-- Name: datatype; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE datatype AS ENUM (
     'str',
     'float'
 );
 --
-            aaronmk
+-- Name: db_item; Type: TYPE; Schema: util; Owner: -
 --
 CREATE TYPE db_item AS (
 	path text,
 	def text
 );
 --
-            aaronmk
+-- Name: geocoord; Type: TYPE; Schema: util; Owner: -
 --
 CREATE TYPE geocoord AS (
 	latitude_deg double precision,
 	longitude_deg double precision
 );
 --
-            aaronmk
+-- Name: range; Type: TYPE; Schema: util; Owner: -
 --
 CREATE TYPE range AS (
 	lower numeric,
 	upper numeric,
 	bounds text
 );
 --
 -- Name: TYPE range; Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON TYPE range IS '
 allows wraparound ranges (which use a modulus system such as geocoordinates)
 ';
 --
-            aaronmk
+-- Name: restore_views_info; Type: TYPE; Schema: util; Owner: -
 --
 CREATE TYPE restore_views_info AS (
-            aaronmk
+	views db_item[]
-            aaronmk
+);
 --
-            aaronmk
+-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT bool_and(value)
 FROM
 (VALUES
       ($1)
     , ($2)
-            aaronmk
+    , ($3)
     , ($4)
     , ($5)
             aaronmk
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
 _and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT avg(value)
 FROM
 (VALUES
       ($1)
     , ($2)
     , ($3)
     , ($4)
     , ($5)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT (g[1]||'1')::integer*util._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::util.compass_dir)
-            aaronmk
+FROM
+(
     SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
     UNION ALL
-            aaronmk
+    SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
     FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
             aaronmk
 matches (g)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
-            aaronmk
+FROM
 (VALUES
-            aaronmk
+      ($1)
-            aaronmk
+    , ($2/60)
     , ($3/60/60)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 = $2
 $_$;
 --
-            aaronmk
+-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
     LANGUAGE sql IMMUTABLE
     AS $_$
 -- Fix dates after threshold date
 -- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
 SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
 $_$;
 --
-            aaronmk
+-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
 $_$;
 --
-            aaronmk
+-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util._if($1 != '', $2, $3)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _join("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _join_words("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1*1000.
 $_$;
 --
-            aaronmk
+-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _label(label text, value text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT coalesce($1 || ': ', '') || $2
 $_$;
             aaronmk
 --
-            aaronmk
+-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION _lowercase(value text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT lower($1)
 $_$;
 --
-            aaronmk
+-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
     LANGUAGE plpgsql IMMUTABLE STRICT
     AS $$
 DECLARE
     result value%TYPE := util._map(map, value::text)::unknown;
 BEGIN
     RETURN result;
 END;
 $$;
 --
-            aaronmk
+-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _map(map hstore, value text) RETURNS text
-            aaronmk
+    LANGUAGE plpgsql IMMUTABLE STRICT
-            aaronmk
+    AS $$
 DECLARE
-            aaronmk
+    match text := map -> value;
-            aaronmk
+BEGIN
-            aaronmk
+    IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
         match := map -> '*'; -- use default entry
         IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
         END IF;
     END IF;
     -- Interpret result
-            aaronmk
+    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
     ELSIF match = '*' THEN RETURN value;
     ELSE RETURN match;
-            aaronmk
+    END IF;
 END;
 $$;
 --
-            aaronmk
+-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _merge("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.join_strs(value, '; ')
-            aaronmk
+FROM
+(
     SELECT *
     FROM
+    (
         SELECT
         DISTINCT ON (value)
+        *
         FROM
         (VALUES
-            aaronmk
+              (1, $1)
             , (2, $2)
             , (3, $3)
             , (4, $4)
             , (5, $5)
             , (6, $6)
             , (7, $7)
             , (8, $8)
             , (9, $9)
             , (10, $10)
             aaronmk
         AS v (sort_order, value)
-            aaronmk
+        WHERE value IS NOT NULL
             aaronmk
     AS v
     ORDER BY sort_order
+)
 AS v
 $_$;
 --
-            aaronmk
+-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
 $_$;
 --
-            aaronmk
+-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _merge_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.join_strs(value, ' ')
-            aaronmk
+FROM
+(
     SELECT *
     FROM
+    (
         SELECT
         DISTINCT ON (value)
+        *
         FROM
         (VALUES
               (1, $1)
             , (2, $2)
             , (3, $3)
             , (4, $4)
             , (5, $5)
             , (6, $6)
             , (7, $7)
             , (8, $8)
             , (9, $9)
             , (10, $10)
+        )
         AS v (sort_order, value)
         WHERE value IS NOT NULL
+    )
     AS v
     ORDER BY sort_order
+)
 AS v
 $_$;
 --
-            aaronmk
+-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _not(value boolean) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT NOT $1
 $_$;
 --
-            aaronmk
+-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _now() RETURNS timestamp with time zone
     LANGUAGE sql STABLE
     AS $$
 SELECT now()
 $$;
 --
-            aaronmk
+-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util."_nullIf"($1, $2, $3::util.datatype)
 $_$;
 --
-            aaronmk
+-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
-            aaronmk
+    LANGUAGE plpgsql IMMUTABLE
     AS $$
 DECLARE
-            aaronmk
+    type util.datatype NOT NULL := type; -- add NOT NULL
-            aaronmk
+BEGIN
-            aaronmk
+    IF type = 'str' THEN RETURN nullif(value::text, "null");
-            aaronmk
+    -- Invalid value is ignored, but invalid null value generates error
-            aaronmk
+    ELSIF type = 'float' THEN
-            aaronmk
+        DECLARE
             -- Outside the try block so that invalid null value generates error
-            aaronmk
+            "null" double precision := "null"::double precision;
-            aaronmk
+        BEGIN
-            aaronmk
+            RETURN nullif(value::double precision, "null");
-            aaronmk
+        EXCEPTION
-            aaronmk
+            WHEN data_exception THEN RETURN value; -- ignore invalid value
-            aaronmk
+        END;
-            aaronmk
+    END IF;
 END;
 $$;
 --
-            aaronmk
+-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT bool_or(value)
 FROM
 (VALUES
       ($1)
     , ($2)
     , ($3)
     , ($4)
     , ($5)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
 _or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $2 - $1
 $_$;
 --
-            aaronmk
+-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT regexp_split_to_table($1, $2)
 $_$;
 --
-            aaronmk
+-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT util.derived_cols($1, $2)
 UNION
 SELECT util.eval2set($$
 SELECT col
 FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
 JOIN $$||$2||$$ ON "to" = col
 WHERE "from" LIKE ':%'
 $$, NULL::text)
 $_$;
 --
 -- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
 gets table_''s added columns (all the columns not in the original data)
 ';
             aaronmk
 --
-            aaronmk
+-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
 $_$;
 --
 -- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
     LANGUAGE plpgsql IMMUTABLE
     AS $$
 DECLARE
 	value_cmp         state%TYPE = ARRAY[value];
 	state             state%TYPE = COALESCE(state, value_cmp);
 	no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
 BEGIN
 	RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
 END;
 $$;
 --
-            aaronmk
+-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION analyze_(table_ regclass) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$ANALYZE VERBOSE $$||$1)
 $_$;
 --
-            aaronmk
+-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.set_comment($1, concat(util.comment($1), $2))
 $_$;
 --
 -- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
 comment: must start and end with a newline
 ';
 --
-            aaronmk
+-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT pg_catalog.array_fill($1, ARRAY[$2])
 $_$;
 --
-            aaronmk
+-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION array_length("array" anyarray) RETURNS integer
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.array_length($1, 1)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
 returns 0 instead of NULL for empty arrays
 ';
             aaronmk
 --
-            aaronmk
+-- Name: array_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION array_reverse("array" anyarray) RETURNS anyarray
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT array(SELECT elem FROM util.in_reverse($1) elem)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT CASE WHEN util.freq_always_1($1, $2)
-            aaronmk
+THEN util.rm_freq($1, $2)
-            aaronmk
+ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
-            aaronmk
+END
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: bounding_box(range, range); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geography
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.bounding_box__no_dateline($1, $2)::postgis.geography
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) IS '
-            aaronmk
+usage:
 SET search_path = util; -- for ~ operator
 SELECT util.bounding_box(lower_lat ~ upper_lat, lower_long ~ upper_long);
             aaronmk
 **WARNING**: 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.
-            aaronmk
+';
 --
-            aaronmk
+-- Name: bounding_box__no_dateline(range, range); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geometry
     LANGUAGE sql IMMUTABLE
     AS $_$
 /* don't use st_makebox2d() because it doesn't support geocoordinate wraparound
 (it is not SRID-aware) */
 SELECT postgis.st_makeenvelope(
   /*xmin=*/$2.lower, /*ymin=*/$1.lower
 , /*xmax=*/$2.upper, /*ymax=*/$1.upper
 , /*WGS84*/4326
+)
 $_$;
 --
 -- Name: FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) IS '
 the generated bounding box is more accurate than util.bounding_box() (latitude
 lines will be straight), but geocoordinate wraparound is not supported
 usage:
 SET search_path = util; -- for ~ operator
 SELECT util.bounding_box__no_dateline(lower_lat ~ upper_lat, lower_long ~ upper_long);
 ';
 --
-            aaronmk
+-- Name: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
     LANGUAGE plpgsql IMMUTABLE
     AS $$
 /* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
 return value, causing a type mismatch */
 BEGIN
 	-- will then be assignment-cast to return type via INOUT
 	RETURN value::cstring;
 END;
 $$;
 --
 -- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
 allows casting to an arbitrary type without eval()
 usage:
 SELECT util.cast(''value'', NULL::integer);
 note that there does *not* need to be a cast from text to the output type,
 because an INOUT cast is used instead
 (http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)
 ret_type_null: NULL::ret_type
 ';
 --
-            aaronmk
+-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
 $_$;
 --
-            aaronmk
+-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 BEGIN
     -- not yet clustered (ARRAY[] compares NULLs literally)
-            aaronmk
+    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
-            aaronmk
+        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
     END IF;
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT value
 FROM unnest($1) value
 WHERE value IS NOT NULL
 LIMIT 1
 $_$;
 --
 -- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
 uses:
 * coalescing array elements or rows together
 * forcing evaluation of all values of a COALESCE()
 ';
 --
-            aaronmk
+-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION col__min(col col_ref) RETURNS integer
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.eval2val($$
 SELECT $$||quote_ident($1.name)||$$
 FROM $$||$1.table_||$$
 ORDER BY $$||quote_ident($1.name)||$$ ASC
 LIMIT 1
 $$, NULL::integer)
 $_$;
 --
-            aaronmk
+-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION col_comment(col col_ref) RETURNS text
     LANGUAGE plpgsql STABLE STRICT
     AS $$
 DECLARE
 	comment text;
 BEGIN
 	SELECT description
 	FROM pg_attribute
 	LEFT JOIN pg_description ON objoid = attrelid
 		AND classoid = 'pg_class'::regclass AND objsubid = attnum
 	WHERE attrelid = col.table_ AND attname = col.name
 	INTO STRICT comment
+	;
 	RETURN comment;
 EXCEPTION
 	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
 END;
 $$;
 --
-            aaronmk
+-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
     LANGUAGE plpgsql STABLE STRICT
     AS $$
 DECLARE
 	default_sql text;
 BEGIN
 	SELECT adsrc
 	FROM pg_attribute
 	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
 	WHERE attrelid = col.table_ AND attname = col.name
 	INTO STRICT default_sql
+	;
 	RETURN default_sql;
 EXCEPTION
 	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
 END;
 $$;
 --
-            aaronmk
+-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
 $_$;
 --
 -- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
 ret_type_null: NULL::ret_type
 ';
             aaronmk
 --
-            aaronmk
+-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
-            aaronmk
+    PERFORM util.col_type(col);
-            aaronmk
+    RETURN true;
 EXCEPTION
     WHEN undefined_column THEN RETURN false;
 END;
 $$;
 --
-            aaronmk
+-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
-            aaronmk
+    AS $$
 DECLARE
-            aaronmk
+    prefix text := util.name(type)||'.';
-            aaronmk
+BEGIN
     RETURN QUERY
-            aaronmk
+        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
         FROM util.col_names(type) f (name_);
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT attname::text
 FROM pg_attribute
-            aaronmk
+WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
-            aaronmk
+ORDER BY attnum
 $_$;
 --
-            aaronmk
+-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
     LANGUAGE plpgsql STABLE STRICT
     AS $_$
 BEGIN
     RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
 END;
 $_$;
 --
-            aaronmk
+-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_type(col col_ref) RETURNS regtype
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
     AS $$
 DECLARE
     type regtype;
 BEGIN
     SELECT atttypid FROM pg_attribute
     WHERE attrelid = col.table_ AND attname = col.name
     INTO STRICT type
+    ;
     RETURN type;
 EXCEPTION
-            aaronmk
+    WHEN no_data_found THEN
-            aaronmk
+        RAISE undefined_column USING MESSAGE =
             concat('undefined column: ', col.name);
-            aaronmk
+END;
 $$;
             aaronmk
 --
-            aaronmk
+-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION comment(element oid) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT description FROM pg_description WHERE objoid = $1
 $_$;
 --
-            aaronmk
+-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.esc_name__append($2, $1)
 $_$;
 --
-            aaronmk
+-- Name: contained_within__no_dateline(postgis.geometry, postgis.geometry); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     SET search_path TO postgis
     AS $_$
 /* search_path: st_coveredby() needs postgis to be in the search_path */
 /* must be st_coveredby() rather than st_within() to avoid unexpected behavior
 at the shape border */
 SELECT postgis.st_coveredby($1, $2)
 $_$;
 --
 -- Name: FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry) IS '
 **WARNING**: this will not work on shapes that cross the date line, as the
 geometry type does not support geocoordinate wraparound
 ';
 --
-            aaronmk
+-- Name: contained_within__no_dateline(geocoord, postgis.geometry); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.contained_within__no_dateline(util.geometry($1), $2)
 $_$;
 --
 -- Name: FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry) IS '
 defining this in addition to contained_within_approx(geometry, geometry) enables
 specifying just `(lat, long)` without the ::util.geocoord type specifier
 ';
 --
-            aaronmk
+-- Name: contained_within_approx(postgis.geography, postgis.geography); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     SET search_path TO postgis
     AS $_$
 /* search_path: st_coveredby() needs postgis to be in the search_path */
 SELECT postgis.st_coveredby($1, $2)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography) IS '
 **WARNING**: the geography type stores all edges as arcs of great circles,
 resulting in the latitude lines of bounding boxes bulging outward from the true
 bounding box. this will create false positives above and below the bounding box.
 ';
 --
-            aaronmk
+-- Name: contained_within_approx(geocoord, postgis.geography); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION contained_within_approx(point geocoord, region postgis.geography) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.contained_within_approx(util.geometry($1), $2)
 $_$;
 --
 -- Name: FUNCTION contained_within_approx(point geocoord, region postgis.geography); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION contained_within_approx(point geocoord, region postgis.geography) IS '
 defining this in addition to contained_within_approx(geography, geography)
 enables specifying just `(lat, long)` without the ::util.geocoord type specifier
 ';
 --
-            aaronmk
+-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT position($1 in $2) > 0 /*1-based offset*/
 $_$;
 --
-            aaronmk
+-- Name: copy(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION copy(from_ regclass, to_ text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.copy_struct($1, $2);
 SELECT util.copy_data($1, $2);
 $_$;
 --
-            aaronmk
+-- Name: copy_data(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION copy_data(from_ regclass, to_ regclass) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT * FROM $$||$1)
 $_$;
 --
-            aaronmk
+-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
 $_$;
 --
-            aaronmk
+-- Name: copy_types_and_data(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.materialize_view($2, $1)
 $_$;
 --
-            aaronmk
+-- Name: create_if_not_exists(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION create_if_not_exists(sql text, relation text DEFAULT NULL::text) RETURNS void
     LANGUAGE plpgsql
-            aaronmk
+    AS $$
 BEGIN
-            aaronmk
+	/* always generate standard exception if exists, even if table definition
 	would be invalid (which generates a variety of exceptions) */
-            aaronmk
+	IF util.relation_exists(relation) THEN
 		PERFORM util.raise('NOTICE', relation||' already exists, skipping');
 		RAISE duplicate_table;
 	END IF;
-            aaronmk
+	PERFORM util.eval(sql);
-            aaronmk
+EXCEPTION
-            aaronmk
+WHEN   duplicate_table
 	OR duplicate_object -- eg. constraint
 	OR duplicate_column
 	OR duplicate_function
 THEN NULL;
-            aaronmk
+WHEN invalid_table_definition THEN
 	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
 	ELSE RAISE;
 	END IF;
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: FUNCTION create_if_not_exists(sql text, relation text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION create_if_not_exists(sql text, relation text) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $$
 SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
 $$;
 --
-            aaronmk
+-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.raise('NOTICE', $$SELECT $$||$1)
 $_$;
 --
-            aaronmk
+-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.debug_print_value('returns: ', $1, $2);
-            aaronmk
+SELECT $1;
 $_$;
 --
-            aaronmk
+-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION debug_print_sql(sql text) RETURNS void
     LANGUAGE sql IMMUTABLE
     AS $_$
 /* newline before so the query starts at the beginning of the line.
 newline after to visually separate queries from one another. */
-            aaronmk
+SELECT util.raise('NOTICE', $$
-            aaronmk
+$$||util.runnable_sql($1)||$$
-            aaronmk
+$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.raise('NOTICE', concat($1,
-            aaronmk
+(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
 $$)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION debug_print_var(var text, value anyelement, encode boolean DEFAULT false) RETURNS void
     LANGUAGE sql IMMUTABLE
     AS $_$
 /* can't use EXECUTE in the caller because "No substitution of PL/pgSQL
 variables is done on the computed command string"
 (http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) */
 SELECT util.debug_print_value($1||' = ', $2, $3)
 $_$;
 --
-            aaronmk
+-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT util.eval2set($$
 SELECT col
 FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
 LEFT JOIN $$||$2||$$ ON "to" = col
 WHERE "from" IS NULL
 $$, NULL::text)
 $_$;
 --
 -- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
 gets table_''s derived columns (all the columns not in the names table)
 ';
             aaronmk
 --
-            aaronmk
+-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+-- create a diff when the # of copies of a row differs between the tables
 SELECT util.to_freq($1);
 SELECT util.to_freq($2);
-            aaronmk
+SELECT util.auto_rm_freq(ARRAY[$1, $2]);
             aaronmk
 SELECT * FROM util.diff($1, $2, $3, has_freq := true)
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
 usage:
 SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
             aaronmk
 col_type_null (*required*): NULL::shared_base_type
-            aaronmk
+';
 --
-            aaronmk
+-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean DEFAULT false, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
-            aaronmk
+    LANGUAGE plpgsql
     SET search_path TO pg_temp
-            aaronmk
+    AS $_$
-            aaronmk
+/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
 changes of search_path (schema elements are bound at inline time rather than
 runtime) */
 /* function option search_path is needed to limit the effects of
-            aaronmk
+`SET LOCAL search_path` to the current function */
-            aaronmk
+BEGIN
-            aaronmk
+	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
             aaronmk
-            aaronmk
+	PERFORM util.mk_keys_func(pg_typeof($3));
-            aaronmk
+	RETURN QUERY
-            aaronmk
+		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
-            aaronmk
+$$/* need to explicitly cast each side to the return type because this does not
-            aaronmk
+happen automatically even when an implicit cast is available */
-            aaronmk
+  left_::$$||util.typeof($3)||$$
 , right_::$$||util.typeof($3)
-            aaronmk
+/* when using the util.%==(anyelement, anyelement) operator, you must cast to
 the *same* base type, *even though* this is optional when using a custom %== */
-            aaronmk
+, util._if($4, $$true/*= CROSS JOIN*/$$,
 $$ left_::$$||util.typeof($3)||$$
-            aaronmk
+%== right_::$$||util.typeof($3)||$$
 	-- refer to EXPLAIN output for expansion of %==$$
             aaronmk
 ,     $$         left_::$$||util.typeof($3)||$$
 IS DISTINCT FROM right_::$$||util.typeof($3)
 ), $3)
             aaronmk
 END;
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
-            aaronmk
+col_type_null (*required*): NULL::col_type
-            aaronmk
+single_row: whether the tables consist of a single row, which should be
 	displayed side-by-side
             aaronmk
-            aaronmk
+to match up rows using a subset of the columns, create a custom keys() function
 which returns this subset as a record:
 -- note that OUT parameters for the returned fields are *not* needed
 CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
   RETURNS record AS
 $BODY$
 SELECT ($1.key_field_0, $1.key_field_1)
 $BODY$
   LANGUAGE sql IMMUTABLE
   COST 100;
-            aaronmk
+to run EXPLAIN on the FULL JOIN query:
 # run this function
 # look for a NOTICE containing the expanded query that it ran
 # run EXPLAIN on this expanded query
-            aaronmk
+';
 --
-            aaronmk
+-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
     LANGUAGE sql
     AS $_$
 SELECT * FROM util.diff($1::text, $2::text, $3,
 	single_row := util.has_single_row($1) AND util.has_single_row($2))
 $_$;
 --
 -- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
 helper function used by diff(regclass, regclass)
 usage:
 SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
 col_type_null (*required*): NULL::shared_base_type
 ';
 --
-            aaronmk
+-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
 $_$;
 --
 -- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
-            aaronmk
+quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
     LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
 SELECT NULL::void; -- don't fold away functions called in previous query
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
 idempotent
 ';
 --
-            aaronmk
+-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+/* use util.qual_name() instead of ::text so that the schema qualifier is always
 included in the debug SQL */
 SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
-            aaronmk
+||util._if($3, $$ CASCADE$$, ''::text))
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
-            aaronmk
+idempotent
 ';
 --
-            aaronmk
+-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
     LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
-            aaronmk
+$_$;
 --
 -- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
     LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.debug_print_func_call(util.quote_func_call(
 'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
 util.quote_typed($3)))
             aaronmk
-            aaronmk
+SELECT util.drop_relation(relation, $3)
 FROM util.show_relations_like($1, $2) relation
+;
 SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
-            aaronmk
+-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.drop_relation('TABLE', $1, $2)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.drop_relation('VIEW', $1, $2)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.array_fill($1, 0)
 $_$;
 --
 -- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION empty_array(elem_type_null anyelement) IS '
 constructs proper empty 1-dimensional array whose dimensions are not NULL ( ''{}''::text[] does not do this)
 ';
             aaronmk
 --
-            aaronmk
+-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT regexp_replace($2, '("?)$', $1||'\1')
 $_$;
 --
-            aaronmk
+-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION eval(queries text[]) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval(query) FROM unnest($1) query;
 SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
-            aaronmk
+-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
-            aaronmk
+    LANGUAGE plpgsql
-            aaronmk
+    AS $$
 BEGIN
-            aaronmk
+	sql = util.view_def_to_orig(sql); -- restore user's intent
-            aaronmk
+	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
-            aaronmk
+	EXECUTE sql;
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
     LANGUAGE plpgsql
     AS $$
 BEGIN
-            aaronmk
+	PERFORM util.debug_print_sql(sql);
-            aaronmk
+	RETURN QUERY EXECUTE sql;
 END;
 $$;
 --
 -- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
 col_type_null (*required*): NULL::col_type
 ';
             aaronmk
 --
-            aaronmk
+-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	PERFORM util.debug_print_sql(sql);
 	RETURN QUERY EXECUTE sql;
 END;
 $$;
 --
-            aaronmk
+-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
-            aaronmk
+    LANGUAGE plpgsql
     AS $$
 BEGIN
-            aaronmk
+	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
-            aaronmk
+	RETURN QUERY EXECUTE sql;
 END;
 $$;
 --
-            aaronmk
+-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
-            aaronmk
+    LANGUAGE plpgsql STABLE
-            aaronmk
+    AS $$
 DECLARE
 	ret_val ret_type_null%TYPE;
 BEGIN
-            aaronmk
+	PERFORM util.debug_print_sql(sql);
-            aaronmk
+	EXECUTE sql INTO STRICT ret_val;
 	RETURN ret_val;
 END;
 $$;
 --
-            aaronmk
+-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
 ret_type_null: NULL::ret_type
 ';
             aaronmk
 --
-            aaronmk
+-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
     LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.eval2val($$SELECT $$||$1, $2)
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
 ret_type_null: NULL::ret_type
 ';
             aaronmk
 --
-            aaronmk
+-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
     LANGUAGE sql
     AS $_$
 SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
 $_$;
 --
 -- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
 sql: can be NULL, which will be passed through
 ret_type_null: NULL::ret_type
 ';
             aaronmk
 --
-            aaronmk
+-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT col_name
 FROM unnest($2) s (col_name)
-            aaronmk
+WHERE util.col_exists(($1, col_name))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain(sql text) RETURNS SETOF text
     LANGUAGE sql
-            aaronmk
+    SET client_min_messages TO 'error'
-            aaronmk
+    AS $_$
-            aaronmk
+/* `client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE functions in
 the query, so this prevents displaying any log messages printed by them */
-            aaronmk
+SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain2notice(sql text) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.raise('NOTICE', util.explain2notice_msg($1))
-            aaronmk
+$_$;
             aaronmk
 --
 -- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
     LANGUAGE sql
     AS $_$
-            aaronmk
+-- newline before and after to visually separate it from other debug info
-            aaronmk
+SELECT COALESCE($$
-            aaronmk
+EXPLAIN:
-            aaronmk
+$$||util.fold_explain_msg(util.explain2str($1))||$$
 $$, '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
-            aaronmk
+    LANGUAGE plpgsql
     AS $$
 BEGIN
 	RETURN util.explain2notice_msg(sql);
 EXCEPTION
-            aaronmk
+WHEN   syntax_error
 	OR invalid_cursor_definition -- "cannot open multi-query plan as cursor"
 	THEN RETURN NULL; -- non-explainable query
-            aaronmk
+	/* 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 */
 END;
 $$;
             aaronmk
 --
-            aaronmk
+-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain2str(sql text) RETURNS text
     LANGUAGE sql
     AS $_$
 SELECT util.join_strs(explain, $$
 $$) FROM util.explain($1)
 $_$;
-            aaronmk
+SET default_tablespace = '';
 SET default_with_oids = false;
-            aaronmk
+--
-            aaronmk
+-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace:
-            aaronmk
+--
-            aaronmk
+CREATE TABLE explain (
     line text NOT NULL
 );
 --
 -- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
 $$||quote_nullable($1)||$$
-            aaronmk
+)$$)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
 usage:
-            aaronmk
+PERFORM util.explain2table($$
 query
-            aaronmk
+$$);
 ';
             aaronmk
 --
-            aaronmk
+-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION first_word(str text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
 	CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
 ) END
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION fix_array("array" anyarray) IS '
 ensures that an array will always have proper non-NULL dimensions
 ';
             aaronmk
 --
-            aaronmk
+-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
 $_$;
 --
-            aaronmk
+-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
 	mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
 $$||query;
 BEGIN
 	EXECUTE mk_view;
 EXCEPTION
 WHEN invalid_table_definition THEN
-            aaronmk
+	IF SQLERRM = 'cannot drop columns from view'
 	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
 	THEN
-            aaronmk
+		EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
 		EXECUTE mk_view;
 	ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
 	END IF;
 END;
 $_$;
 --
 -- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: freq_always_1(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.eval2val(
 $$SELECT NOT EXISTS( -- there is no row that is != 1
 	SELECT NULL
 	FROM $$||$1||$$
 	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
 	LIMIT 1
+)
 $$, NULL::boolean)
 $_$;
 --
-            aaronmk
+-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
     LANGUAGE sql STABLE
     AS $_$
 SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
 $_$;
 --
-            aaronmk
+-- Name: geometry(geocoord); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION geometry(geocoord geocoord) RETURNS postgis.geometry
     LANGUAGE sql IMMUTABLE
     SET client_min_messages TO 'warning'
     AS $_$
 SELECT postgis.st_setsrid(postgis.st_point(
 /*x_lon=*/$1.longitude_deg, /*y_lat=*/$1.latitude_deg),
 /*WGS84*/4326)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION geometry(geocoord geocoord); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION geometry(geocoord geocoord) IS '
 *note*: 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
 ';
 --
-            aaronmk
+-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION grants_users() RETURNS SETOF text
     LANGUAGE sql IMMUTABLE
     AS $$
 VALUES ('bien_read'), ('public_')
 $$;
 --
-            aaronmk
+-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT substring($2 for length($1)) = $1
 $_$;
 --
-            aaronmk
+-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
 $_$;
 --
-            aaronmk
+-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION hstore(keys text[], value text) IS '
 avoids repeating the same value for each key
 ';
             aaronmk
 --
-            aaronmk
+-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT COALESCE($1, $2)
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
 equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
 ';
             aaronmk
 --
-            aaronmk
+-- Name: in_new_world(geocoord); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION in_new_world(point geocoord) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 /* use function rather than operator+search_path to allow inlining, which
 enables util.new_world() to only be evaluated once */
 SELECT util.contained_within_approx($1, util.new_world())
 $_$;
 --
 -- Name: FUNCTION in_new_world(point geocoord); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION in_new_world(point geocoord) IS '
 **WARNING**: this includes false positives above and below the New World
 bounding box, as described in util.bounding_box()
 ';
 --
-            aaronmk
+-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: in_south_america(geocoord); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION in_south_america(point geocoord) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 /* use function rather than operator+search_path to allow inlining, which
 enables util.south_america() to only be evaluated once */
 SELECT util.contained_within_approx($1, util.south_america())
 $_$;
 --
-            aaronmk
+-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
 $_$;
 --
-            aaronmk
+-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
     LANGUAGE plpgsql IMMUTABLE
     AS $$
 BEGIN
 	PERFORM util.cast(value, ret_type_null);
-            aaronmk
+	-- must happen *after* cast check, because NULL is not valid for some types
 	IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
-            aaronmk
+	RETURN true;
 EXCEPTION
-            aaronmk
+WHEN   data_exception
-            aaronmk
+	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
-            aaronmk
+	OR syntax_error_or_access_rule_violation -- eg. ::regclass
 	THEN
 	RETURN false;
-            aaronmk
+END;
 $$;
 --
 -- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
-            aaronmk
+passes NULL through. however, if NULL is not valid for the type, false will be
 returned instead.
-            aaronmk
+ret_type_null: NULL::ret_type
 ';
 --
-            aaronmk
+-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT COALESCE(util.col_comment($1) LIKE '
 constant
 %', false)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.array_length($1) = 0
 $_$;
 --
-            aaronmk
+-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_explain(sql text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT upper(util.first_word($1)) = 'EXPLAIN'
 $_$;
 --
-            aaronmk
+-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_explainable(sql text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT upper(util.first_word($1)) = ANY(
 '{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
 /*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
+)
 $_$;
 --
-            aaronmk
+-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
 $_$;
 --
-            aaronmk
+-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
 $_$;
 --
-            aaronmk
+-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT upper(util.first_word($1)) = 'SET'
 $_$;
 --
-            aaronmk
+-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_table(relation regclass) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT relkind = 'r' FROM pg_class WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_view(relation regclass) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT relkind = 'v' FROM pg_class WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT $1 || $3 || $2
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
 must be declared STRICT to use the special handling of STRICT aggregating functions
 ';
 --
-            aaronmk
+-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION keys(value anyelement) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 -- compare on the entire value
 $_$;
 --
-            aaronmk
+-- Name: keys_eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION keys_eq(left_ anyelement, right_ anyelement) RETURNS boolean
     LANGUAGE sql STABLE
     AS $_$
 SELECT keys($1) = keys($2)
 $_$;
 --
 -- Name: FUNCTION keys_eq(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION keys_eq(left_ anyelement, right_ anyelement) IS '
 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**)
 ';
 --
-            aaronmk
+-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
     LANGUAGE plpgsql
     AS $$
 DECLARE
 	errors_ct integer = 0;
 	loop_var loop_type_null%TYPE;
 BEGIN
 	FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
 	LOOP
 		BEGIN
 			EXECUTE loop_body_sql USING loop_var;
 		EXCEPTION
 		WHEN OTHERS THEN
 			errors_ct = errors_ct+1;
 			PERFORM util.raise_error_warning(SQLERRM);
 		END;
 	END LOOP;
 	IF errors_ct > 0 THEN
 		-- can't raise exception because this would roll back the transaction
 		PERFORM util.raise_error_warning('there were '||errors_ct
 			||' errors: see the WARNINGs for details');
 	END IF;
 END;
 $$;
 --
-            aaronmk
+-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION ltrim_nl(str text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT ltrim($1, $$
 $$)
 $_$;
 --
-            aaronmk
+-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION map_filter_insert() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
 	RETURN new;
 END;
 $$;
 --
-            aaronmk
+-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION map_get(map regclass, key text) RETURNS text
     LANGUAGE plpgsql STABLE STRICT
     AS $_$
 DECLARE
     value text;
 BEGIN
     EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
-            aaronmk
+        INTO value USING key;
-            aaronmk
+    RETURN value;
 END;
 $_$;
 --
-            aaronmk
+-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util._map(util.nulls_map($1), $2)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION map_nulls(nulls text[], value anyelement) IS '
 due to dynamic inlining[1], this is just as fast as util._map() which it wraps[2].
             aaronmk
 [1] inlining of function calls, which is different from constant folding
 [2] _map()''s profiling query
 SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
 and map_nulls()''s profiling query
 SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
-            aaronmk
+both take ~920 ms.
-            aaronmk
+also, /inputs/REMIB/Specimen/postprocess.sql > country takes the same amount of time (56000 ms) to build with map_nulls() as with a literal hstore.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
     LANGUAGE plpgsql STABLE STRICT
     AS $_$
 BEGIN
     RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
 END;
 $_$;
 --
-            aaronmk
+-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
-            aaronmk
+$$||util.ltrim_nl($2));
 -- make sure the created table has the correct estimated row count
 SELECT util.analyze_($1);
             aaronmk
 SELECT util.append_comment($1, '
 contents generated from:
-            aaronmk
+'||util.ltrim_nl(util.runnable_sql($2))||';
-            aaronmk
+');
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
 SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
-            aaronmk
+idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$
 ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
-            aaronmk
+||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
-            aaronmk
+||quote_literal($2)||$$;
-            aaronmk
+COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
 constant
 ';
-            aaronmk
+$$)
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
-            aaronmk
+    LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
     type regtype = util.typeof(expr, col.table_::text::regtype);
     col_name_sql text = quote_ident(col.name);
 BEGIN
-            aaronmk
+    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
 ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
-            aaronmk
+ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
 $$||expr||$$;
 $$);
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION mk_diff_query(left_ text, right_ text, cols text DEFAULT 'left_, right_'::text, join_cond text DEFAULT 'left_ %== right_ -- refer to EXPLAIN output for expansion of %=='::text, filter text DEFAULT 'left_ IS DISTINCT FROM right_'::text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT
-            aaronmk
+$$SELECT
-            aaronmk
+$$||$3||$$
-            aaronmk
+FROM      $$||$1||$$ left_
-            aaronmk
+FULL JOIN $$||$2||$$ right_
 ON $$||$4||$$
 WHERE $$||$5||$$
-            aaronmk
+ORDER BY left_, right_
 $$
 $_$;
 --
-            aaronmk
+-- Name: mk_drop_from_create(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_drop_from_create(create_cmd text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT $$DROP $$||(util.regexp_match($1,
-            aaronmk
+-- match first CREATE, *if* no DROP came before it
-            aaronmk
+'(?m)\A(?:^(?!DROP\y).*\n)*?^CREATE(?: OR REPLACE)? ((?:)??[[:upper:]]+ .*?)(?: AS(?: (?:SELECT\y.*)?)?)?$'
-            aaronmk
+	/* (?:)?? makes the surrounding () group non-greedy, so that (?: AS ?)?
 	works properly (due to nonstandard Postgres regexp behavior:
 	http://www.postgresql.org/docs/9.3/static/functions-matching.html#POSIX-MATCHING-RULES) */
-            aaronmk
+))[1]||$$;$$
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+-- keys()
-            aaronmk
+SELECT util.mk_keys_func($1, ARRAY(
 SELECT col FROM util.typed_cols($1) col
 WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
-            aaronmk
+));
-            aaronmk
+-- values_()
-            aaronmk
+SELECT util.mk_keys_func($1, COALESCE(
 	NULLIF(ARRAY(
 	SELECT col FROM util.typed_cols($1) col
 	WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
 	), ARRAY[]::util.col_cast[])
 , ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
-            aaronmk
+, 'values_');
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$
-            aaronmk
+CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
 ($$||util.mk_typed_cols_list($2)||$$);
-            aaronmk
+COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
 autogenerated
 ';
-            aaronmk
+$$);
             aaronmk
-            aaronmk
+SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
 $_$;
 --
 -- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.create_if_not_exists($$
-            aaronmk
+CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
-            aaronmk
+||util.qual_name($1)||$$)
-            aaronmk
+  RETURNS $$||util.qual_name($2)||$$ AS
-            aaronmk
+$BODY1$
-            aaronmk
+SELECT ROW($$||
-            aaronmk
+(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
 FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
-            aaronmk
+$BODY1$
   LANGUAGE sql IMMUTABLE
   COST 100;
-            aaronmk
+$$);
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION mk_map_table(table_ text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$
-            aaronmk
+CREATE TABLE $$||$1||$$
             aaronmk
-            aaronmk
+    LIKE util.map INCLUDING ALL
-            aaronmk
+);
 CREATE TRIGGER map_filter_insert
   BEFORE INSERT
   ON $$||$1||$$
   FOR EACH ROW
   EXECUTE PROCEDURE util.map_filter_insert();
-            aaronmk
+$$)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_not_null(text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT COALESCE($1, '<NULL>')
 $_$;
 --
-            aaronmk
+-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
 util.qual_name((unnest).type), ''), '')
-            aaronmk
+FROM unnest($1)
 $_$;
 --
-            aaronmk
+-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
 auto-appends util to the search_path to enable use of util operators
 ';
 --
-            aaronmk
+-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT COALESCE($$COMMENT ON $$||util.relation_type($1)||$$ $$||$1||$$ IS $$
-            aaronmk
+||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_set_relation_metadata(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_set_relation_metadata(relation regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.show_grants_for($1)
 ||util.show_set_comment($1)||$$
 $$
 $_$;
 --
-            aaronmk
+-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.mk_set_search_path(current_setting('search_path'), $1)
 $_$;
 --
-            aaronmk
+-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+/* debug_print_return_value() needed because this function is used with EXECUTE
 rather than util.eval() (in order to affect the calling function), so the
 search_path would not otherwise be printed */
-            aaronmk
+SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
 ||$$ search_path TO $$||$1
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.mk_const_col(($1, 'source'), util.schema($1))
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
 	view_qual_name text = util.qual_name(view_);
 BEGIN
 	EXECUTE $$
 CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
   RETURNS SETOF $$||view_||$$ AS
 $BODY1$
 SELECT * FROM $$||view_qual_name||$$
 ORDER BY sort_col
 LIMIT $1 OFFSET $2
 $BODY1$
   LANGUAGE sql STABLE
   COST 100
   ROWS 1000
 $$;
 	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
 END;
 $_$;
 --
-            aaronmk
+-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
-            aaronmk
+DECLARE
 	view_qual_name text = util.qual_name(view_);
 	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
-            aaronmk
+BEGIN
 	EXECUTE $$
-            aaronmk
+CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
   RETURNS integer AS
 $BODY1$
 SELECT $$||quote_ident(row_num_col)||$$
 FROM $$||view_qual_name||$$
 ORDER BY $$||quote_ident(row_num_col)||$$ ASC
 LIMIT 1
 $BODY1$
   LANGUAGE sql STABLE
   COST 100;
 $$;
 	EXECUTE $$
-            aaronmk
+CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
   RETURNS SETOF $$||view_||$$ AS
 $BODY1$
-            aaronmk
+SELECT * FROM $$||view_qual_name||$$
 WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
 	util.offset2row_num(    $2, $$||row_num__min__fn||$$())
 AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
-            aaronmk
+ORDER BY $$||quote_ident(row_num_col)||$$
-            aaronmk
+$BODY1$
   LANGUAGE sql STABLE
   COST 100
   ROWS 1000
 $$;
             aaronmk
 	PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
 END;
 $_$;
 --
 -- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
 	view_qual_name text = util.qual_name(view_);
 BEGIN
-            aaronmk
+	EXECUTE $$
 CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
   RETURNS SETOF $$||view_||$$
   SET enable_sort TO 'off'
   AS
 $BODY1$
-            aaronmk
+SELECT * FROM $$||view_qual_name||$$($2, $3)
-            aaronmk
+$BODY1$
   LANGUAGE sql STABLE
   COST 100
   ROWS 1000
+;
 COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
 Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
 If you want to run EXPLAIN and get expanded output, use the regular subset
 function instead. (When a config param is set on a function, EXPLAIN produces
 just a function scan.)
 ';
 $$;
-            aaronmk
+END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
 creates subset function which turns off enable_sort
 ';
             aaronmk
 --
-            aaronmk
+-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
-            aaronmk
+util.qual_name((unnest).type), ', '), '')
 FROM unnest($1)
 $_$;
 --
-            aaronmk
+-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION name(table_ regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
 SELECT relname::text FROM pg_class WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION name(type regtype) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT typname::text FROM pg_type WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT octet_length($1) >= util.namedatalen() - $2
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION namedatalen() RETURNS integer
     LANGUAGE sql IMMUTABLE
     AS $$
 SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
 $$;
 --
-            aaronmk
+-- Name: new_world(); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION new_world() RETURNS postgis.geography
     LANGUAGE sql IMMUTABLE
-            aaronmk
+    SET search_path TO util
-            aaronmk
+    AS $$
-            aaronmk
+SELECT util.bounding_box(-56 ~ 83, 172 ~ -34)
-            aaronmk
+$$;
 --
-            aaronmk
+-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT $1 IS NOT NULL AND util.array_length($1) > 0
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION not_null(value anyelement) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT $1 IS NOT NULL
 $_$;
 --
-            aaronmk
+-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.hstore($1, NULL) || '*=>*'
 $_$;
 --
 -- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
 for use with _map()
 ';
             aaronmk
 --
-            aaronmk
+-- Name: numrange(range); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION numrange(value range) RETURNS numrange
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT numrange($1.lower, $1.upper, $1.bounds)
 $_$;
 --
-            aaronmk
+-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT $2 + COALESCE($1, 0)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION parent(table_ regclass) RETURNS regclass
     LANGUAGE sql STABLE
     AS $_$
 SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
 $_$;
 --
-            aaronmk
+-- Name: pg_get_viewdef(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION pg_get_viewdef(view_ regclass) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.view_def_to_orig(pg_catalog.pg_get_viewdef($1))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$INSERT INTO $$||$1||$$
 $$||util.ltrim_nl($2));
 -- make sure the created table has the correct estimated row count
 SELECT util.analyze_($1);
 $_$;
 --
-            aaronmk
+-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.qual_name(util.schema($2), $1||util.name($2))
 $_$;
 --
-            aaronmk
+-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.set_comment($1, concat($2, util.comment($1)))
 $_$;
 --
 -- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
 comment: must start and end with a newline
 ';
 --
-            aaronmk
+-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
 $_$;
 --
-            aaronmk
+-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION qual_name(table_ regclass) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    SET search_path TO pg_temp
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT $1::text
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION qual_name(type regtype) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    SET search_path TO pg_temp
     AS $_$
 SELECT $1::text
 $_$;
 --
 -- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION qual_name(type regtype) IS '
 a type''s schema-qualified name
 ';
 --
-            aaronmk
+-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION qual_name(type unknown) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT util.qual_name($1::text::regtype)
 $_$;
 --
-            aaronmk
+-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
 $_$;
 --
 -- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
 $_$;
 --
-            aaronmk
+-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION quote_typed(value anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION raise(type text, msg text) RETURNS void
     LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_X$
-            aaronmk
+SELECT util.eval($$
 CREATE OR REPLACE FUNCTION pg_temp.__raise()
   RETURNS void AS
-            aaronmk
+-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
 $__BODY1$
-            aaronmk
+BEGIN
 	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
 END;
-            aaronmk
+$__BODY1$
-            aaronmk
+  LANGUAGE plpgsql IMMUTABLE
   COST 100;
-            aaronmk
+$$, verbose_ := false);
             aaronmk
-            aaronmk
+SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
-            aaronmk
+$_X$;
             aaronmk
 --
-            aaronmk
+-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION raise(type text, msg text) IS '
 type: a log level from
 http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
 or a condition name from
 http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
 ';
 --
-            aaronmk
+-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION raise_error_warning(msg text) RETURNS void
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.raise('WARNING', 'ERROR:  '||$1)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
     LANGUAGE plpgsql IMMUTABLE STRICT
     AS $$
 BEGIN
 	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
 END;
 $$;
 --
-            aaronmk
+-- Name: range(numeric, numeric); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION range(lower numeric, upper numeric) RETURNS range
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT ($1, $2, '[]')::util.range
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
     LANGUAGE plpgsql
     AS $_$
 DECLARE
 	PG_EXCEPTION_DETAIL text;
 	restore_views_info util.restore_views_info;
 BEGIN
 	restore_views_info = util.save_drop_views(users);
             aaronmk
 	-- trigger the dependent_objects_still_exist exception
-            aaronmk
+	PERFORM util.eval(COALESCE(util.mk_drop_from_create(cmd), '')||cmd);
-            aaronmk
+		-- *not* CASCADE; it must trigger an exception
-            aaronmk
+	PERFORM util.restore_views(restore_views_info);
 EXCEPTION
 WHEN dependent_objects_still_exist THEN
 	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
 	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
 	users = array(SELECT * FROM util.regexp_matches_group(
 		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
 		-- will be in forward dependency order
 	PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
 	PERFORM util.debug_print_var('users', users);
 	IF util.is_empty(users) THEN RAISE; END IF;
 	PERFORM util.recreate(cmd, users);
 END;
 $_$;
 --
 -- Name: FUNCTION recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION recreate(cmd text, users text[]) IS '
-            aaronmk
+the appropriate drop statement will be added automatically.
-            aaronmk
+usage:
 SELECT util.recreate($$
-            aaronmk
+CREATE VIEW schema.main_view AS _;
             aaronmk
 -- manually restore views that need to be updated for the changes
-            aaronmk
+CREATE VIEW schema.dependent_view AS _;
-            aaronmk
+$$);
 idempotent
 users: not necessary to provide this because it will be autopopulated
 ';
 --
-            aaronmk
+-- Name: recreate_view(regclass, text, text); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION recreate_view(view_ regclass, view_query text DEFAULT NULL::text, dependent_view_changes text DEFAULT ''::text) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
 SELECT util.recreate($$
 CREATE VIEW $$||$1||$$ AS
-            aaronmk
+$$||COALESCE($2, pg_get_viewdef($1))||$$;
-            aaronmk
+$$||util.mk_set_relation_metadata($1)||$$
 -- manually restore views that need to be updated for the changes
 $$||$3||$$
 $$);
 $_$;
 --
 -- Name: FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text) IS '
 usage:
 SELECT util.recreate_view(''schema.main_view'', $$
 SELECT __
 $$, $$
 CREATE VIEW schema.dependent_view AS
 __;
 $$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
 $$);
-            aaronmk
+if view has already been modified:
 SELECT util.recreate_view(''schema.main_view'', dependent_view_changes := $$
 CREATE VIEW schema.dependent_view AS
 __;
 $$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
 $$);
-            aaronmk
+idempotent
 ';
 --
-            aaronmk
+-- Name: regexp_match(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION regexp_match(str text, re text) RETURNS text[]
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT match FROM regexp_matches($1, $2) match LIMIT 1/*only 1st match*/
 $_$;
 --
-            aaronmk
+-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
 $_$;
 --
-            aaronmk
+-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION regexp_quote(str text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
 $_$;
 --
-            aaronmk
+-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT (CASE WHEN right($1, 1) = ')'
-            aaronmk
+THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: relation_exists(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION relation_exists(relation text) RETURNS boolean
     LANGUAGE sql STABLE
     AS $_$
 SELECT $1 IS NOT NULL AND util.is_castable($1, NULL::regclass)
 $_$;
 --
-            aaronmk
+-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION relation_type(relation regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.relation_type(util.relation_type_char($1))
 $_$;
 --
-            aaronmk
+-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION relation_type(type regtype) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $$
 SELECT 'TYPE'::text
 $$;
 --
-            aaronmk
+-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
     LANGUAGE sql STABLE
     AS $_$
 SELECT relkind FROM pg_class WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
     LANGUAGE sql
     AS $_$
 /* can't have in_table/out_table inherit from *each other*, because inheritance
 also causes the rows of the parent table to be included in the child table.
 instead, they need to inherit from a common, empty table. */
-            aaronmk
+SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
 'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
-            aaronmk
+SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
-            aaronmk
+SELECT util.inherit($2, $4);
 SELECT util.inherit($3, $4);
 SELECT util.rematerialize_query($1, $$
 SELECT * FROM util.diff(
-            aaronmk
+  $$||util.quote_typed($2)||$$
 , $$||util.quote_typed($3)||$$
-            aaronmk
+, NULL::$$||$4||$$)
 $$);
             aaronmk
 /* the table unfortunately cannot be *materialized* in human-readable form,
 because this would create column name collisions between the two sides */
-            aaronmk
+SELECT util.prepend_comment($1, '
-            aaronmk
+to view this table in human-readable form (with each side''s tuple column
 expanded to its component fields):
-            aaronmk
+SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
             aaronmk
 to display NULL values that are extra or missing:
 SELECT * FROM '||$1||';
-            aaronmk
+');
-            aaronmk
+$_$;
 --
 -- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
 type_table (*required*): table to create as the shared base type
 ';
 --
-            aaronmk
+-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.drop_table($1);
 SELECT util.materialize_query($1, $2);
 $_$;
 --
 -- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
 idempotent, but repeats action each time
 ';
 --
-            aaronmk
+-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
 SELECT util.drop_table($1);
 SELECT util.materialize_view($1, $2);
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
-            aaronmk
+idempotent, but repeats action each time
 ';
 --
-            aaronmk
+-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
-            aaronmk
+||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
-            aaronmk
+FROM util.col_names($1::text::regtype) f (name);
 SELECT NULL::void; -- don't fold away functions called in previous query
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
     LANGUAGE sql
     AS $_$
-            aaronmk
+/* use util.qual_name() instead of ::text so that the schema qualifier is always
 included in the debug SQL */
 SELECT util.rename_relation(util.qual_name($1), $2)
-            aaronmk
+$_$;
 --
 -- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
 /* 'ALTER TABLE can be used with views too'
 (http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
-            aaronmk
+SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
 ||quote_ident($2))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
-            aaronmk
+idempotent
 ';
 --
-            aaronmk
+-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
 max_prefix_len: when str may have been truncated (eg. as a table name) due to the prepending of a prefix, support prefixes up to this length
 ';
 --
-            aaronmk
+-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
 SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
-            aaronmk
+SELECT util.set_col_names($1, $2);
 $_$;
 --
 -- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
 idempotent.
 alters the names table, so it will need to be repopulated after running this function.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION reset_map_table(table_ text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.drop_table($1);
-            aaronmk
+SELECT util.mk_map_table($1);
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION restore_views(restore_views_info) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
 SELECT util.debug_print_var('views', $1);
-            aaronmk
+SELECT util.create_if_not_exists((view_).def, (view_).path)
 	/* need to specify view name for manual existence check, in case view def
 	becomes invalid, which would produce nonstandard (uncatchable) exception */
-            aaronmk
+FROM unnest($1.views) view_; -- in forward dependency order
-            aaronmk
+	/* create_if_not_exists() rather than eval(), because cmd might manually
 	re-create a deleted dependent view, causing it to already exist */
 SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
-            aaronmk
+-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.drop_column($1, $2, force := true)
 $_$;
 --
-            aaronmk
+-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
 $_$;
 --
-            aaronmk
+-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION runnable_sql(sql text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
 ELSE util.mk_set_search_path(for_printing := true)||$$;
 $$ END)||$1
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION save_drop_view(view_ text) RETURNS text
     LANGUAGE plpgsql STRICT
-            aaronmk
+    AS $$
-            aaronmk
+DECLARE
 	result text = NULL;
 BEGIN
 	BEGIN
-            aaronmk
+		result = util.show_create_view(view_, replace := false);
 			/* replace: no `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() */
-            aaronmk
+		PERFORM util.drop_view(view_);
-            aaronmk
+	EXCEPTION
 		WHEN undefined_table THEN NULL;
 	END;
 	RETURN result;
 END;
-            aaronmk
+$$;
             aaronmk
 --
-            aaronmk
+-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
-            aaronmk
+SELECT (view_, util.save_drop_view(view_))::util.db_item
-            aaronmk
+FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
-            aaronmk
+)))::util.restore_views_info
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
 SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
 $_$;
 --
-            aaronmk
+-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema(table_ regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema(type regtype) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
-            aaronmk
+$_$;
 --
 -- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema(type_null anyelement) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
 SELECT util.schema(pg_typeof($1))
 $_$;
 --
-            aaronmk
+-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
     LANGUAGE sql STABLE
     AS $_$
 SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
 a schema bundle is a group of schemas with a common prefix
 ';
             aaronmk
 --
 -- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.schema_rename(old_schema,
 	overlay(old_schema placing new from 1 for length(old))) -- replace prefix
 FROM util.schema_bundle_get_schemas($1) f (old_schema);
 SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
 -- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	-- don't schema_bundle_rm() the schema_bundle to keep!
 	IF replace = with_ THEN RETURN; END IF;
 	PERFORM util.schema_bundle_rm(replace);
 	PERFORM util.schema_bundle_rename(with_, replace);
 END;
 $$;
 --
 -- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.schema_rm(schema)
 FROM util.schema_bundle_get_schemas($1) f (schema);
 SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
-            aaronmk
+-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
     AS $_$
 SELECT quote_ident(util.schema($1))
 $_$;
 --
-            aaronmk
+-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
-            aaronmk
+    LANGUAGE sql IMMUTABLE
-            aaronmk
+    AS $_$
 SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
 $_$;
 --
-            aaronmk
+-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_oid(schema text) RETURNS oid
     LANGUAGE sql STABLE
     AS $_$
 SELECT oid FROM pg_namespace WHERE nspname = $1
 $_$;
 --
-            aaronmk
+-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.schema_regexp(schema_anchor := $1)
 $_$;
 --
-            aaronmk
+-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.str_equality_regexp(util.schema($1))
 $_$;
 --
-            aaronmk
+-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_rename(old text, new text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
 $_$;
 --
-            aaronmk
+-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
     LANGUAGE plpgsql
     AS $$
 BEGIN
 	-- don't schema_rm() the schema to keep!
 	IF replace = with_ THEN RETURN; END IF;
 	PERFORM util.schema_rm(replace);
 	PERFORM util.schema_rename(with_, replace);
 END;
 $$;
 --
-            aaronmk
+-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION schema_rm(schema text) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
 $_$;
 --
-            aaronmk
+-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION search_path_append(schemas text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.eval(
 $$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
 $_$;
 --
-            aaronmk
+-- Name: seq__create(text, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION seq__create(seq text, start integer DEFAULT 0) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.create_if_not_exists($$CREATE SEQUENCE $$||$1||$$ MINVALUE $$||$2,
 $1)
 $_$;
 --
 -- Name: FUNCTION seq__create(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION seq__create(seq text, start integer) IS '
 idempotent
 ';
 --
-            aaronmk
+-- Name: seq__reset(text, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION seq__reset(seq text, start integer DEFAULT 0) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.seq__create($1, $2);
 SELECT util.eval($$ALTER SEQUENCE $$||$1||$$ RESTART$$);
 $_$;
 --
 -- Name: FUNCTION seq__reset(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION seq__reset(seq text, start integer) IS '
 creates sequence if doesn''t exist
 idempotent
 start: *note*: only used if sequence doesn''t exist
 ';
 --
-            aaronmk
+-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
-            aaronmk
+    old text[] = ARRAY(SELECT util.col_names(table_));
     new text[] = ARRAY(SELECT util.map_values(names));
-            aaronmk
+BEGIN
     old = old[1:array_length(new, 1)]; -- truncate to same length
-            aaronmk
+    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
 ||$$ TO $$||quote_ident(value))
-            aaronmk
+    FROM each(hstore(old, new))
     WHERE value != key -- not same name
+    ;
-            aaronmk
+END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
 	row_ util.map;
 BEGIN
-            aaronmk
+	-- rename any metadata cols rather than re-adding them with new names
 	BEGIN
 		PERFORM util.set_col_names(table_, names);
 	EXCEPTION
 		WHEN array_subscript_error THEN -- selective suppress
 			IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
 				-- metadata cols not yet added
-            aaronmk
+			ELSE RAISE;
-            aaronmk
+			END IF;
 	END;
-            aaronmk
+	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
-            aaronmk
+	LOOP
-            aaronmk
+		PERFORM util.mk_const_col((table_, row_."to"),
 			substring(row_."from" from 2));
-            aaronmk
+	END LOOP;
 	PERFORM util.set_col_names(table_, names);
 END;
 $_$;
 --
 -- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
 idempotent.
 the metadata mappings must be *last* in the names table.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.eval(COALESCE(
 $$ALTER TABLE $$||$1||$$
-            aaronmk
+$$||(
 	SELECT
 	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
 	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
 , $$)
 	FROM
+	(
 		SELECT
 		  quote_ident(col_name) AS col_name_sql
-            aaronmk
+		, util.col_type(($1, col_name)) AS curr_type
-            aaronmk
+		, type AS target_type
-            aaronmk
+		FROM unnest($2)
-            aaronmk
+	) s
 	WHERE curr_type != target_type
-            aaronmk
+), ''))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.eval(util.mk_set_comment($1, $2))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
     LANGUAGE sql
     AS $_$
 SELECT util.eval(util.mk_set_search_path($1, $2))
 $_$;
 --
-            aaronmk
+-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
 ||$1||$$ AS
-            aaronmk
+$$||util.pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
-            aaronmk
+$$||util.mk_set_relation_metadata($1)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT string_agg(cmd, '')
-            aaronmk
+FROM
+(
 	SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
 $$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
 $$ ELSE '' END) AS cmd
 	FROM util.grants_users() f (user_)
 ) s
 $_$;
 --
-            aaronmk
+-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['c'::text, 'r'::text, 'v'::text]) RETURNS SETOF regclass
-            aaronmk
+    LANGUAGE sql STABLE
     AS $_$
 SELECT oid FROM pg_class
 WHERE relkind = ANY($3) AND relname ~ $1
 AND util.schema_matches(util.schema(relnamespace), $2)
 ORDER BY relname
 $_$;
 --
-            aaronmk
+-- Name: show_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION show_set_comment(table_ regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT util.mk_set_comment($1, util.comment($1))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
     LANGUAGE sql STABLE
     AS $_$
 SELECT oid
 FROM pg_type
 WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
 ORDER BY typname
 $_$;
 --
-            aaronmk
+-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
-            aaronmk
+    LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: south_america(); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION south_america() RETURNS postgis.geometry
     LANGUAGE sql IMMUTABLE
     SET search_path TO util
     AS $$
 SELECT util.bounding_box__no_dateline(-56 ~ 13, -82 ~ -34)
 $$;
 --
-            aaronmk
+-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT '^'||util.regexp_quote($1)||'$'
 $_$;
 --
-            aaronmk
+-- Name: subspecies(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION subspecies(taxon_name text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT (regexp_matches($1, '\ysubsp\. (\S+)'))[1]
 $_$;
 --
-            aaronmk
+-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
-            aaronmk
+    AS $_$
 DECLARE
     hstore hstore;
 BEGIN
     EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
         table_||$$))$$ INTO STRICT hstore;
     RETURN hstore;
 END;
 $_$;
 --
-            aaronmk
+-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT COUNT(*) > 0 FROM pg_constraint
 WHERE conrelid = $1 AND contype = 'c' AND conname = $2
 $_$;
 --
 -- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
 gets whether a status flag is set by the presence of a table constraint
 ';
             aaronmk
 --
-            aaronmk
+-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
 ||quote_ident($2)||$$ CHECK (true)$$)
 $_$;
 --
 -- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
 stores a status flag by the presence of a table constraint.
 idempotent.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT util.table_flag__get($1, 'nulls_mapped')
 $_$;
 --
 -- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
 gets whether a table''s NULL-equivalent strings have been replaced with NULL
 ';
             aaronmk
 --
-            aaronmk
+-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.table_flag__set($1, 'nulls_mapped')
 $_$;
 --
 -- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
 sets that a table''s NULL-equivalent strings have been replaced with NULL.
 idempotent.
 ';
             aaronmk
 --
-            aaronmk
+-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION to_freq(table_ regclass) RETURNS void
     LANGUAGE sql
     AS $_$
 -- save data before truncating main table
 SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
 -- repopulate main table w/ copies column
 SELECT util.truncate($1);
 SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
 SELECT util.populate_table($1, $$
 SELECT (table_).*, copies
 FROM (
 	SELECT table_, COUNT(*) AS copies
 	FROM pg_temp.__copy table_
 	GROUP BY table_
 ) s
 $$);
 -- delete temp table so it doesn't stay around until end of connection
 SELECT util.drop_table('pg_temp.__copy');
 $_$;
 --
-            aaronmk
+-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
     row record;
 BEGIN
-            aaronmk
+    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
-            aaronmk
+    LOOP
         IF row.global_name != row.name THEN
             EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
                 ||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
         END IF;
     END LOOP;
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
-            aaronmk
+SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION "trim"(table_ regclass, names regclass, force boolean) IS '
 trims table_ to include only columns in the original data
 by default, cascadingly drops dependent columns so that they don''t prevent
 trim() from succeeding. note that this requires the dependent columns to then be
 manually re-created.
 idempotent
-            aaronmk
+';
             aaronmk
 --
-            aaronmk
+-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION truncate(table_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 BEGIN
     EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION truncate(table_ regclass) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
-            aaronmk
+util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
     LANGUAGE plpgsql IMMUTABLE
     AS $$
 BEGIN
 	/* need explicit cast because some types not implicitly-castable, and also
 	to make the cast happen inside the try block. (*implicit* casts to the
 	return type happen at the end of the function, outside any block.) */
 	RETURN util.cast(value, ret_type_null);
 EXCEPTION
-            aaronmk
+WHEN   data_exception
-            aaronmk
+	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
-            aaronmk
+	OR syntax_error_or_access_rule_violation -- eg. ::regclass
 	THEN
-            aaronmk
+	PERFORM util.raise('WARNING', SQLERRM);
 	RETURN NULL;
 END;
 $$;
 --
 -- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
 ret_type_null: NULL::ret_type
 ';
 --
-            aaronmk
+-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION try_create(sql text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
-            aaronmk
+	PERFORM util.eval(sql);
-            aaronmk
+EXCEPTION
-            aaronmk
+WHEN   not_null_violation
 		/* trying to add NOT NULL column to parent table, which cascades to
 		child table whose values for the new column will be NULL */
 	OR wrong_object_type -- trying to alter a view's columns
 	OR undefined_column
 	OR duplicate_column
 THEN NULL;
-            aaronmk
+WHEN datatype_mismatch THEN
 	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
 	ELSE RAISE; -- rethrow
 	END IF;
-            aaronmk
+END;
 $$;
 --
 -- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION try_create(sql text) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
 SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
 $_$;
 --
 -- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION type_qual(value anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION type_qual(value anyelement) IS '
 a type''s NOT NULL qualifier
 ';
             aaronmk
 --
-            aaronmk
+-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT (attname::text, atttypid)::util.col_cast
 FROM pg_attribute
 WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
 ORDER BY attnum
 $_$;
 --
-            aaronmk
+-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION typeof(value anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.qual_name(pg_typeof($1))
 $_$;
 --
-            aaronmk
+-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
 --
-            aaronmk
+CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
     LANGUAGE plpgsql STABLE
-            aaronmk
+    AS $_$
 DECLARE
     type regtype;
 BEGIN
-            aaronmk
+    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
 COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
-            aaronmk
+    RETURN type;
 END;
 $_$;
 --
-            aaronmk
+-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
-            aaronmk
+    LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
-            aaronmk
+auto-appends util to the search_path to enable use of util operators
 ';
 --
-            aaronmk
+-- Name: view_def_to_orig(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION view_def_to_orig(view_def text) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT CASE
 WHEN util.view_is_subset($1) THEN $1
 	-- list of cols from the same table is not an expanded * expression
 ELSE
-            aaronmk
+regexp_replace(
 regexp_replace(
 $1
+,
-            aaronmk
+/* unexpand expanded * expressions. any list of 6+ cols from the same table is
-            aaronmk
+treated as a * expression. */
-            aaronmk
+'((?:"[^"\s]+"|\w+)\.)(?:"[^"\s]+"|\w+)'||
 	/* 1st col, which lacks separator before.
 	*note*: can't prepend \y because it considers only \w chars, not " */
-            aaronmk
+'(,[[:blank:]]*
-            aaronmk
+[[:blank:]]*)\1(?:"[^"\s]+"|\w+)'|| -- 2nd col, which has separator before
 '(?:\2\1(?:"[^"\s]+"|\w+)){4,}', -- later cols, w/ same table name and separator
-            aaronmk
+'\1*'/*prefix w/ table*/,
 'g')
+,
 /* merge .* expressions resulting from a SELECT * of a join. any list of
 multiple .* expressions is treated as a SELECT * . */
 '(?:"[^"\s]+"|\w+)\.\*'||
 	/* 1st table, which lacks separator before.
 	*note*: can't prepend \y because it considers only \w chars, not " */
 '(,[[:blank:]]*
 [[:blank:]]*)(?:"[^"\s]+"|\w+)\.\*'|| -- 2nd table, which has separator before
 '(?:\1(?:"[^"\s]+"|\w+)\.\*)*', -- later tables, w/ same separator
 '*',
 'g')
-            aaronmk
+END
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: view_is_automatically_updatable(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION view_is_automatically_updatable(view_def text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 !~ '\y(?:JOIN|WITH|DISTINCT|GROUP BY|HAVING|LIMIT|OFFSET)\y'
 	/* from http://www.postgresql.org/docs/9.3/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS */
 $_$;
 --
-            aaronmk
+-- Name: view_is_subset(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION view_is_subset(view_def text) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT util.view_is_automatically_updatable($1)
 $_$;
 --
-            aaronmk
+-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
 --
 CREATE AGGREGATE all_same(anyelement) (
     SFUNC = all_same_transform,
     STYPE = anyarray,
     FINALFUNC = all_same_final
 );
 --
 -- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON AGGREGATE all_same(anyelement) IS '
 includes NULLs in comparison
 ';
             aaronmk
 --
-            aaronmk
+-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE AGGREGATE join_strs(text, text) (
-            aaronmk
+    SFUNC = join_strs_transform,
-            aaronmk
+    STYPE = text
-            aaronmk
+);
-            aaronmk
+--
-            aaronmk
+-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR %== (
-            aaronmk
+    PROCEDURE = keys_eq,
-            aaronmk
+    LEFTARG = anyelement,
     RIGHTARG = anyelement
 );
 --
 -- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
 returns whether the map-keys of the compared values are the same
 (mnemonic: % is the Perl symbol for a hash map)
 should be overridden for types that store both keys and values
 used in a FULL JOIN to select which columns to join on
 ';
 --
-            aaronmk
+-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
-            aaronmk
+--
 CREATE OPERATOR -> (
     PROCEDURE = map_get,
     LEFTARG = regclass,
     RIGHTARG = text
 );
-            aaronmk
+--
 -- Name: =>; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR => (
     PROCEDURE = hstore,
-            aaronmk
+    LEFTARG = text[],
-            aaronmk
+    RIGHTARG = text
-            aaronmk
+);
 --
-            aaronmk
+-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON OPERATOR => (text[], text) IS '
 usage: array[''key1'', ...]::text[] => ''value''
 ';
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ?*>= (
     PROCEDURE = is_populated_more_often_than,
     LEFTARG = anyelement,
     RIGHTARG = anyelement
 );
 --
-            aaronmk
+-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ?>= (
     PROCEDURE = is_more_complete_than,
     LEFTARG = anyelement,
     RIGHTARG = anyelement
 );
-            aaronmk
+--
-            aaronmk
+-- Name: @; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR @ (
-            aaronmk
+    PROCEDURE = contained_within__no_dateline,
     LEFTARG = postgis.geometry,
     RIGHTARG = postgis.geometry
 );
 --
-            aaronmk
+-- Name: @; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR @ (
     PROCEDURE = contained_within__no_dateline,
     LEFTARG = geocoord,
     RIGHTARG = postgis.geometry
 );
 --
-            aaronmk
+-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ||% (
     PROCEDURE = concat_esc,
     LEFTARG = text,
     RIGHTARG = text
 );
 --
 -- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON OPERATOR ||% (text, text) IS '
 % indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
 ';
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: ~; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ~ (
-            aaronmk
+    PROCEDURE = range,
-            aaronmk
+    LEFTARG = numeric,
     RIGHTARG = numeric
 );
-            aaronmk
+--
 -- Name: ~@; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ~@ (
     PROCEDURE = contained_within_approx,
     LEFTARG = postgis.geography,
     RIGHTARG = postgis.geography
 );
 --
 -- Name: OPERATOR ~@ (postgis.geography, postgis.geography); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON OPERATOR ~@ (postgis.geography, postgis.geography) IS '
 can''t use && because it only compares 2D bounding boxes (which are geometry
 objects that do not support geocoordinate wraparound)
 ';
-            aaronmk
+--
 -- Name: ~@; Type: OPERATOR; Schema: util; Owner: -
 --
 CREATE OPERATOR ~@ (
     PROCEDURE = contained_within_approx,
     LEFTARG = geocoord,
     RIGHTARG = postgis.geography
 );
-            aaronmk
+SET search_path = pg_catalog;
-            aaronmk
+--
-            aaronmk
+-- Name: CAST (util.range AS numrange); Type: CAST; Schema: pg_catalog; Owner: -
 --
 CREATE CAST (util.range AS numrange) WITH FUNCTION util.numrange(util.range) AS IMPLICIT;
 SET search_path = util, pg_catalog;
 --
-            aaronmk
+-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
-            aaronmk
+--
 CREATE TABLE map (
     "from" text NOT NULL,
-            aaronmk
+    "to" text,
     filter text,
     notes text
-            aaronmk
+);
 --
-            aaronmk
+-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
 --
 --
-            aaronmk
+-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
-            aaronmk
+--
 --
-            aaronmk
+-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
-            aaronmk
+--
 ALTER TABLE ONLY map
-            aaronmk
+    ADD CONSTRAINT map__unique__from UNIQUE ("from");
             aaronmk
 --
-            aaronmk
+-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
 --
 ALTER TABLE ONLY map
     ADD CONSTRAINT map__unique__to UNIQUE ("to");
 --
-            aaronmk
+-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
 --
 CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
 --
-            aaronmk
+-- PostgreSQL database dump complete
 --

Project

General

Profile