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

root/trunk/schemas/util.sql @ 12587

-            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: %==(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION "%=="(left_ anyelement, right_ anyelement) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT keys($1) = keys($2)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION "%=="(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION "%=="(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: _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: _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: 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: 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: 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_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: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
-            aaronmk
+    PERFORM util.eval(sql);
-            aaronmk
+EXCEPTION
-            aaronmk
+    WHEN duplicate_table  THEN NULL;
-            aaronmk
+    WHEN duplicate_object THEN NULL; -- e.g. constraint
-            aaronmk
+    WHEN duplicate_column THEN NULL;
-            aaronmk
+    WHEN invalid_table_definition THEN
         IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
-            aaronmk
+        ELSE RAISE;
-            aaronmk
+        END IF;
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION create_if_not_exists(sql text) IS '
 idempotent
 ';
             aaronmk
 --
-            aaronmk
+-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
     LANGUAGE sql IMMUTABLE
     AS $$
 SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
 $$;
 --
-            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.raise('NOTICE', 'returns: '
-            aaronmk
+||(CASE WHEN $2 THEN util.quote_typed($1) ELSE $1::text END));
-            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: 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
+SELECT * FROM util.diff($1::text, $2::text, $3,
-            aaronmk
+	single_row := util.has_single_row($1) AND util.has_single_row($2))
-            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 '
 col_type_null (*required*): NULL::shared_base_type
 usage:
 SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
 ';
 --
-            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
+)), $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: 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_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 $_$
 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, 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
+	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
     AS $_$
-            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
 SELECT $$
 EXPLAIN:
 $$||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
     LANGUAGE sql
     AS $_$
 SELECT (CASE WHEN util.is_explainable($1) THEN util.explain2notice_msg($1) END)
 $_$;
 --
-            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: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
     LANGUAGE plpgsql
     AS $_$
 DECLARE
 	PG_EXCEPTION_DETAIL text;
 	recreate_users_cmd text = util.save_drop_views(users);
 BEGIN
 	PERFORM util.eval(cmd);
 	PERFORM util.eval(recreate_users_cmd);
 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 table .*$'));
 	IF util.is_empty(users) THEN RAISE; END IF;
 	PERFORM util.force_recreate(cmd, users);
 END;
 $_$;
 --
 -- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
 idempotent
             aaronmk
-            aaronmk
+users: not necessary to provide this because it will be autopopulated
 ';
             aaronmk
 --
-            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: 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: 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_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
-            aaronmk
+    LANGUAGE sql STABLE
-            aaronmk
+    AS $_$
 SELECT COALESCE(util.col_comment($1) LIKE 'constant%', false)
 $_$;
 --
-            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: 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: 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:
 '||util.ltrim_nl($2)||';
 ');
-            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_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
     LANGUAGE sql IMMUTABLE
     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 IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$
-            aaronmk
+CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
 ($$||util.mk_typed_cols_list($2)||$$);
-            aaronmk
+CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
-            aaronmk
+||util.qual_name($1)||$$)
   RETURNS $$||util.prefixed_name($3||'_', $1)||$$ AS
-            aaronmk
+$BODY1$
-            aaronmk
+SELECT ROW($$||
-            aaronmk
+(SELECT COALESCE(string_agg($$$1.$$||quote_ident((unnest).col_name), ', '), '')
-            aaronmk
+FROM unnest($2)) ||$$)::$$||util.prefixed_name($3||'_', $1)||$$
-            aaronmk
+$BODY1$
   LANGUAGE sql IMMUTABLE
   COST 100;
 $$)
 $_$;
 --
-            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_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_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: 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: 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: 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: 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_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 $_$
 SELECT 'r=>TABLE, v=>VIEW'::hstore -> $1
 $_$;
 --
-            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.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
+');
-            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: 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
     AS $_$
 DECLARE
 	result text = NULL;
 BEGIN
 	BEGIN
 		result = util.show_create_view(view_);
 		PERFORM util.eval($$DROP VIEW $$||view_);
 	EXCEPTION
 		WHEN undefined_table THEN NULL;
 	END;
 	RETURN result;
 END;
 $_$;
 --
-            aaronmk
+-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION save_drop_views(views text[]) RETURNS text
     LANGUAGE sql
     AS $_$
-            aaronmk
+SELECT string_agg(util.save_drop_view(unnest), '') FROM unnest($1)
-            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: 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
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
     sql text = $$ALTER TABLE $$||table_||$$
 $$||NULLIF(array_to_string(ARRAY(
     SELECT
     $$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((table_, col_name)) AS curr_type
-            aaronmk
+        , type AS target_type
         FROM unnest(col_casts)
     ) s
     WHERE curr_type != target_type
 ), '
 , '), '');
 BEGIN
-            aaronmk
+    PERFORM util.debug_print_sql(sql);
-            aaronmk
+    EXECUTE COALESCE(sql, '');
 END;
 $_$;
 --
-            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 $_$
 SELECT util.eval($$COMMENT ON TABLE $$||$1||$$ IS $$||quote_nullable($2))
 $_$;
 --
-            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); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
     LANGUAGE sql STABLE
     AS $_$
-            aaronmk
+SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
 $$||util.show_grants_for($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: -
 --
 CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['r'::text, 'v'::text]) RETURNS SETOF regclass
     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_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: 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: 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_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); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
-            aaronmk
+    LANGUAGE sql
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.drop_column(($1, col)) FROM util.added_cols($1, $2) f (col);
-            aaronmk
+SELECT NULL::void; -- don't fold away functions called in previous query
 $_$;
 --
 -- Name: FUNCTION "trim"(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
 --
-            aaronmk
+COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
 trims table_ to include only columns in the original data.
 idempotent.
 ';
             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_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 wrong_object_type THEN NULL; -- trying to alter a view's columns
-            aaronmk
+    WHEN undefined_column THEN NULL;
     WHEN duplicate_column THEN NULL;
 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
     LANGUAGE sql IMMUTABLE
     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: 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 %== (
     PROCEDURE = "%==",
     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
+--
 -- 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: 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