--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA util;


--
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
--

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.

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.
';


SET search_path = util, pg_catalog;

--
-- Name: table_item; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE table_item AS (
	table_ regclass,
	name text
);


--
-- Name: check_constraint_def; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE check_constraint_def AS (
	constraint_ table_item,
	expr text
);


--
-- Name: col; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE col AS (
	table_ regclass,
	name text
);


--
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE col_cast AS (
	col_name text,
	type regtype
);


--
-- Name: col_def; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE col_def AS (
	col col,
	type regtype
);


--
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE compass_dir AS ENUM (
    'N',
    'E',
    'S',
    'W'
);


--
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE datatype AS ENUM (
    'str',
    'float'
);


--
-- Name: db_item; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE db_item AS (
	path text,
	def text
);


--
-- Name: derived_col_def; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE derived_col_def AS (
	col col,
	expr text
);


--
-- Name: geocoord; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE geocoord AS (
	latitude_deg double precision,
	longitude_deg double precision
);


--
-- Name: range; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE range AS (
	lower numeric,
	upper numeric,
	bounds text
);


--
-- Name: TYPE range; Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON TYPE range IS '
allows wraparound ranges (which use a modulus system such as geocoordinates)
';


--
-- Name: replacement; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE replacement AS (
	replace text,
	with_ text
);


--
-- Name: restore_views_info; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE restore_views_info AS (
	views db_item[]
);


--
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
$_$;


--
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT bool_and(value)
FROM
(VALUES
      ($1)
    , ($2)
    , ($3)
    , ($4)
    , ($5)
)
AS v (value)
$_$;


--
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
--

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.
';


--
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
--

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)
$_$;


--
-- Name: _concat_nullify(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _concat_nullify("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 || $2
$_$;


--
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
    LANGUAGE sql IMMUTABLE
    AS $_$
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)
FROM 
(
    SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
    UNION ALL
    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
)
matches (g)
$_$;


--
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
FROM
(VALUES
      ($1)
    , ($2/60)
    , ($3/60/60)
)
AS v (value)
$_$;


--
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

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 $_$
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
$_$;


--
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 = $2
$_$;


--
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
--

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)
$_$;


--
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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)
$_$;


--
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util._if($1 != '', $2, $3)
$_$;


--
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
$_$;


--
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
$_$;


--
-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1*1000.
$_$;


--
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _label(label text, value text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT coalesce($1 || ': ', '') || $2
$_$;


--
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _lowercase(value text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT lower($1)
$_$;


--
-- 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;
$$;


--
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _map(map hstore, value text) RETURNS text
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
DECLARE
    match text := map -> value;
BEGIN
    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
    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
    ELSIF match = '*' THEN RETURN value;
    ELSE RETURN match;
    END IF;
END;
$$;


--
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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 $_$
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
$_$;


--
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.join_strs(value, '; ')
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
$_$;


--
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
--

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)
$_$;


--
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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 $_$
SELECT util.join_strs(value, ' ')
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
$_$;


--
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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 $_$
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
$_$;


--
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _not(value boolean) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT NOT $1
$_$;


--
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _now() RETURNS timestamp with time zone
    LANGUAGE sql STABLE
    AS $$
SELECT now()
$$;


--
-- 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)
$_$;


--
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
    type util.datatype NOT NULL := type; -- add NOT NULL
BEGIN
    IF type = 'str' THEN RETURN nullif(value::text, "null");
    -- Invalid value is ignored, but invalid null value generates error
    ELSIF type = 'float' THEN
        DECLARE
            -- Outside the try block so that invalid null value generates error
            "null" double precision := "null"::double precision;
        BEGIN
            RETURN nullif(value::double precision, "null");
        EXCEPTION
            WHEN data_exception THEN RETURN value; -- ignore invalid value
        END;
    END IF;
END;
$$;


--
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
--

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)
$_$;


--
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
--

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.
';


--
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
--

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
$_$;


--
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT regexp_split_to_table($1, $2)
$_$;


--
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
    LANGUAGE sql STABLE
    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: -
--

COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
gets table_''s added columns (all the columns not in the original data)
';


--
-- Name: alias_re(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION alias_re() RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT '(?: AS '||util.col_re()||')'::text
$$;


--
-- Name: aliased_col_re(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION aliased_col_re() RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT '(?:'||util.col_re()||util.alias_re()||'?)'::text
$$;


--
-- 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;
$$;


--
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION analyze_(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
$_$;


--
-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
    LANGUAGE sql
    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
';


--
-- 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])
$_$;


--
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION array_length("array" anyarray) RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.array_length($1, 1)
$_$;


--
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
$_$;


--
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
returns 0 instead of NULL for empty arrays
';


--
-- Name: array_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION array_reverse("array" anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT array(SELECT elem FROM util.in_reverse($1) elem)
$_$;


--
-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT CASE WHEN util.freq_always_1($1, $2)
THEN util.rm_freq($1, $2)
ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
END
$_$;


--
-- Name: bounding_box(range, range); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geography
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.bounding_box__no_dateline($1, $2)::postgis.geography
$_$;


--
-- Name: FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) IS '
usage:
SET search_path = util; -- for ~ operator
SELECT util.bounding_box(lower_lat ~ upper_lat, lower_long ~ upper_long);

**WARNING**: the geography type stores all edges as arcs of great circles,
resulting in the latitude lines bulging outward from the true bounding box.
this will create false positives above and below the bounding box.
';


--
-- Name: bounding_box__no_dateline(range, range); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geometry
    LANGUAGE sql IMMUTABLE
    AS $_$
/* don't use st_makebox2d() because it doesn't support geocoordinate wraparound
(it is not SRID-aware) */
SELECT postgis.st_makeenvelope(
  /*xmin=*/$2.lower, /*ymin=*/$1.lower
, /*xmax=*/$2.upper, /*ymax=*/$1.upper
, /*WGS84*/4326
)
$_$;


--
-- Name: FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) IS '
the generated bounding box is more accurate than util.bounding_box() (latitude
lines will be straight), but geocoordinate wraparound is not supported

usage:
SET search_path = util; -- for ~ operator
SELECT util.bounding_box__no_dateline(lower_lat ~ upper_lat, lower_long ~ upper_long);
';


--
-- Name: canon_case(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION canon_case(str text, case_sensitive boolean DEFAULT true) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT CASE WHEN case_sensitive THEN str ELSE lower(str) END
$$;


--
-- Name: canon_sql(text, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION canon_sql(expr text, table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $$
SELECT util.canon_sql(expr, util.regtype(table_))
$$;


--
-- Name: canon_sql(text, regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION canon_sql(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS text
    LANGUAGE sql STABLE
    SET search_path TO pg_temp
    AS $_$
-- **IMPORTANT**: need to remove the enclosing () to be consistent everywhere
SELECT util.trim_parens((regexp_matches(line,
	'^  Filter: \((.*) IS DISTINCT FROM result_type_null\)$'))[1])
FROM util.explain($$
-- declare types (use WITH to avoid `types.` prefix)
WITH types AS
(
	SELECT
	  NULL::$$||util.typeof(expr, table_)||$$ AS result_type_null
	, /*col types:*/(NULL::$$||table_||$$).*
)
SELECT * FROM types WHERE ($$||expr||$$) IS DISTINCT FROM result_type_null
	/* can't just use IS NOT NULL, or even `= result_type_null`, because these
	will simplify NULL exprs too far */
$$
, verbose_ := true) line
LIMIT 1
$_$;


--
-- Name: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
    LANGUAGE plpgsql IMMUTABLE
    AS $$
/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
return value, causing a type mismatch */
BEGIN
	-- will then be assignment-cast to return type via INOUT
	RETURN value::cstring;
END;
$$;


--
-- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
allows casting to an arbitrary type without eval()

usage:
SELECT util.cast(''value'', NULL::integer);

note that there does *not* need to be a cast from text to the output type,
because an INOUT cast is used instead
(http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)

ret_type_null: NULL::ret_type
';


--
-- Name: check_constraint(check_constraint_def, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint(constraint_def check_constraint_def, enabled boolean DEFAULT true) RETURNS void
    LANGUAGE sql
    AS $$
-- auto-create
SELECT CASE WHEN enabled THEN util.check_constraint_enable(constraint_def)
ELSE util.check_constraint_disable(constraint_def)
END
$$;


--
-- Name: FUNCTION check_constraint(constraint_def check_constraint_def, enabled boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint(constraint_def check_constraint_def, enabled boolean) IS '
idempotent
';


--
-- Name: check_constraint_canon_sql(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_canon_sql(def check_constraint_def) RETURNS text
    LANGUAGE plpgsql STRICT
    AS $$
-- must be declared STRICT to handle NULL properly
/* **IMPORTANT**: must be declared VOLATILE to be able to see concurrent
modifications to system catalogs */
DECLARE
	result text;
	table_ regclass = (def).constraint_.table_;
	temp_table text = util.qual_name('pg_temp', util.name(table_));
	temp_constraint_def util.check_constraint_def;
BEGIN
	BEGIN
		-- use empty temp table so don't try to run constraint on all rows
		PERFORM util.copy_struct(table_, temp_table);
		-- now that table's regclass exists
		temp_constraint_def = ((temp_table, (def).constraint_.name), def.expr);
		
		PERFORM util.check_constraint_replace(temp_constraint_def,
			canon := false/*avoid infinite recursion*/);
		result = util.check_constraint_expr(temp_constraint_def.constraint_);
		
		-- delete temp tables
		PERFORM util.raise('ROLBK', 'sandbox block finished and rolled back');
	EXCEPTION
	WHEN SQLSTATE 'ROLBK' THEN NULL;
	END;
	
	RETURN util.debug_print_return_value(result);
END;
$$;


--
-- Name: FUNCTION check_constraint_canon_sql(def check_constraint_def); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint_canon_sql(def check_constraint_def) IS '
**WARNING**: this must be used instead of plain util.canon_sql() because unlike
queries, CHECK constraints are not simplified, just standardized
';


--
-- Name: check_constraint_def(table_item); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_def(constraint_ table_item) RETURNS check_constraint_def
    LANGUAGE sql STABLE
    AS $$
SELECT constraint_ FROM util.check_constraint
WHERE table_ = constraint_.table_ AND name = constraint_.name
$$;


--
-- Name: check_constraint_defs(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_defs(table_ regclass) RETURNS SETOF check_constraint_def
    LANGUAGE sql STABLE
    AS $_$
SELECT constraint_ FROM util.check_constraint WHERE table_ = $1
$_$;


--
-- Name: check_constraint_disable(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_disable(constraint_def check_constraint_def) RETURNS void
    LANGUAGE sql
    AS $$
/* can't use ADD CONSTRAINT NOT VALID for this because it does not disable the
constraint completely, but instead causes new rows to be validated against it */
SELECT util.check_constraint_replace((constraint_def.constraint_,
util.check_constraint_expr_disable(constraint_def.expr)))
$$;


--
-- Name: FUNCTION check_constraint_disable(constraint_def check_constraint_def); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint_disable(constraint_def check_constraint_def) IS '
also accepts a util.table_item via implicit cast

usage:
SELECT util.check_constraint_disable((table, constraint_name)::util.table_item);

**WARNING**: *not* idempotent, unlike util.check_constraint_enable()
';


--
-- Name: check_constraint_drop(table_item); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_drop(constraint_ table_item) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$ALTER TABLE $$||constraint_.table_
||$$ DROP CONSTRAINT IF EXISTS $$||quote_ident(constraint_.name))
$_$;


--
-- Name: check_constraint_enable(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_enable(constraint_def check_constraint_def) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.check_constraint_replace((constraint_def.constraint_,
util.check_constraint_expr_enable(constraint_def.expr)))
$$;


--
-- Name: FUNCTION check_constraint_enable(constraint_def check_constraint_def); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint_enable(constraint_def check_constraint_def) IS '
also accepts a util.table_item via implicit cast

usage:
SELECT util.check_constraint_enable((table, constraint_name)::util.table_item);

idempotent
';


--
-- Name: check_constraint_enabled(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_enabled(constraint_def check_constraint_def) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_X$
-- **WARNING**: whitespace in strings is significant
SELECT NOT /*disabled*/constraint_def.expr ~ $_$^
CASE
    WHEN true THEN true
    ELSE $_$
$_X$;


--
-- Name: FUNCTION check_constraint_enabled(constraint_def check_constraint_def); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint_enabled(constraint_def check_constraint_def) IS '
also accepts a util.table_item via implicit cast

usage:
SELECT util.check_constraint_enabled((table, constraint_name)::util.table_item);
';


--
-- Name: check_constraint_expr(table_item); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_expr(constraint_ table_item) RETURNS text
    LANGUAGE sql STABLE
    AS $$
SELECT (constraint_::util.check_constraint_def).expr
$$;


--
-- Name: check_constraint_expr_disable(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_expr_disable(expr text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- always returns true, but stores the expr for later use
-- can't use `true OR ` because OR always evaluates both sides
SELECT $$CASE WHEN true THEN true ELSE $$||expr||$$ END$$
$_$;


--
-- Name: check_constraint_expr_enable(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_expr_enable(expr text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_X$
-- **WARNING**: whitespace in strings is significant
-- **IMPORTANT**: need to remove the enclosing () to be consistent everywhere
SELECT util.trim_parens(
util.extract_by_regexp($_$^
CASE
    WHEN true THEN true
    ELSE (.*)
END$$_$,
util.trim_parens(expr)))
$_X$;


--
-- Name: check_constraint_replace(check_constraint_def, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION check_constraint_replace(def check_constraint_def, canon boolean DEFAULT true) RETURNS void
    LANGUAGE sql
    AS $_$
-- **IMPORTANT**: need to canon so it can be compared with the expr in the DB
SELECT CASE
WHEN expr IS DISTINCT FROM util.check_constraint_expr(def.constraint_) THEN
-- only re-run constraint on all rows if expr has changed
(SELECT NULL::void FROM (SELECT
util.check_constraint_drop(def.constraint_)
,
util.eval($$ALTER TABLE $$||(def).constraint_.table_
||$$ ADD CONSTRAINT $$||quote_ident((def).constraint_.name)
||$$ CHECK ($$||expr||$$)$$)
) s)
END
FROM (SELECT
CASE WHEN canon THEN util.check_constraint_canon_sql(def) ELSE def.expr END
	AS expr
) s
$_$;


--
-- Name: FUNCTION check_constraint_replace(def check_constraint_def, canon boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION check_constraint_replace(def check_constraint_def, canon boolean) IS '
idempotent, and avoids re-running constraint on all rows if expr hasn''t changed
';


--
-- Name: cluster(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION cluster(table_ regclass, index regclass DEFAULT NULL::regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$CLUSTER $$||table_
||COALESCE($$ USING $$||quote_ident(util.name(index)), ''))
$_$;


--
-- Name: FUNCTION cluster(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION cluster(table_ regclass, index regclass) IS '
idempotent, but reclusters each time
';


--
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
    LANGUAGE sql STABLE
    AS $_$
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
$_$;


--
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT CASE
WHEN util.cluster_index($1) IS DISTINCT FROM $2 THEN -- not yet clustered
	util.cluster(table_, index)
END
$_$;


--
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
idempotent, and runs cluster only *once* if called repeatedly
';


--
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT value
FROM unnest($1) value
WHERE value IS NOT NULL
LIMIT 1
$_$;


--
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
uses:
* coalescing array elements or rows together
* forcing evaluation of all values of a COALESCE()
';


--
-- Name: col__min(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col__min(col col) 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)
$_$;


--
-- Name: col_add(col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_add(col_def col_def) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$ALTER TABLE $$||(col_def).col.table_
||$$ ADD COLUMN $$||quote_ident((col_def).col.name)||$$ $$||col_def.type)
$_$;


--
-- Name: col_add(derived_col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_add(def derived_col_def) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.col_add((def.col,
util.typeof(def.expr, (def).col.table_))::util.col_def);
$$;


--
-- Name: col_comment(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_comment(col col) 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;
$$;


--
-- Name: col_default_sql(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_default_sql(col col) 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;
$$;


--
-- Name: col_default_value(col, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_default_value(col col, 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, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION col_default_value(col col, ret_type_null anyelement) IS '
ret_type_null: NULL::ret_type
';


--
-- Name: col_exists(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_exists(col col) RETURNS boolean
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
    PERFORM util.col_type(col);
    RETURN true;
EXCEPTION
    WHEN undefined_column THEN RETURN false;
END;
$$;


--
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
    prefix text := util.name(type)||'.';
BEGIN
    RETURN QUERY
        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
        FROM util.col_names(type) f (name_);
END;
$$;


--
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
    LANGUAGE sql STABLE
    AS $_$
SELECT attname::text
FROM pg_attribute
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
ORDER BY attnum
$_$;


--
-- 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;
$_$;


--
-- Name: col_num(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_num(col col) RETURNS smallint
    LANGUAGE sql STABLE
    AS $_$
SELECT attnum FROM pg_attribute WHERE attrelid = $1.table_ AND attname = $1.name
$_$;


--
-- Name: col_re(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_re() RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT '(?:"(?:[^"\s]|"")+"|\w+)'::text
$$;


--
-- Name: col_type(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col_type(col col) RETURNS regtype
    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
    WHEN no_data_found THEN
        RAISE undefined_column USING MESSAGE =
            concat('undefined column: ', col.name);
END;
$$;


--
-- Name: cols(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION cols(table_ regclass) RETURNS SETOF col
    LANGUAGE sql STABLE
    AS $_$
SELECT ($1, name)::util.col FROM util.col_names($1) name
$_$;


--
-- Name: comment(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION comment(col col) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT description
FROM pg_description
WHERE
	objoid = $1.table_
AND classoid = 'pg_class'::regclass
AND objsubid = util.col_num($1)
$_$;


--
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION comment(element oid) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT description FROM pg_description WHERE objoid = $1 AND objsubid = 0
$_$;


--
-- 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)
$_$;


--
-- Name: contained_within__no_dateline(postgis.geometry, postgis.geometry); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    SET search_path TO postgis
    AS $_$
/* search_path: st_coveredby() needs postgis to be in the search_path */
/* must be st_coveredby() rather than st_within() to avoid unexpected behavior
at the shape border */
SELECT postgis.st_coveredby($1, $2)
$_$;


--
-- Name: FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION contained_within__no_dateline("inner" postgis.geometry, "outer" postgis.geometry) IS '
**WARNING**: this will not work on shapes that cross the date line, as the
geometry type does not support geocoordinate wraparound
';


--
-- Name: contained_within__no_dateline(geocoord, postgis.geometry); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.contained_within__no_dateline(util.geometry($1), $2)
$_$;


--
-- Name: FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION contained_within__no_dateline(point geocoord, region postgis.geometry) IS '
defining this in addition to contained_within_approx(geometry, geometry) enables
specifying just `(lat, long)` without the ::util.geocoord type specifier
';


--
-- Name: contained_within_approx(postgis.geography, postgis.geography); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    SET search_path TO postgis
    AS $_$
/* search_path: st_coveredby() needs postgis to be in the search_path */
SELECT postgis.st_coveredby($1, $2)
$_$;


--
-- Name: FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION contained_within_approx("inner" postgis.geography, "outer" postgis.geography) IS '
**WARNING**: the geography type stores all edges as arcs of great circles,
resulting in the latitude lines of bounding boxes bulging outward from the true
bounding box. this will create false positives above and below the bounding box.
';


--
-- Name: contained_within_approx(geocoord, postgis.geography); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION contained_within_approx(point geocoord, region postgis.geography) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
/* use util.geography() instead of implicit cast to suppress "Coordinate values
were coerced into range [-180 -90, 180 90] for GEOGRAPHY" NOTICEs */
SELECT util.contained_within_approx(util.geography($1), $2)
$_$;


--
-- Name: FUNCTION contained_within_approx(point geocoord, region postgis.geography); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION contained_within_approx(point geocoord, region postgis.geography) IS '
defining this in addition to contained_within_approx(geography, geography)
enables specifying just `(lat, long)` without the ::util.geocoord type specifier
';


--
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT position($1 in $2) > 0 /*1-based offset*/
$_$;


--
-- Name: copy(anyelement, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION copy(from_ anyelement, to_ text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.copy_struct($1, $2);
SELECT util.copy_data($1, $2);
$_$;


--
-- Name: FUNCTION copy(from_ anyelement, to_ text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION copy(from_ anyelement, to_ text) IS '
from_: regclass/regtype
';


--
-- Name: copy_comment(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION copy_comment(from_ regclass, to_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.set_comment(to_, util.comment(from_))
$$;


--
-- Name: copy_data(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION copy_data(from_ regclass, to_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT * FROM $$||$1);
SELECT util.analyze_(to_);
$_$;


--
-- Name: copy_data(regtype, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION copy_data(from_ regtype, to_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
SELECT NULL::void -- no-op
$$;


--
-- 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)$$);
SELECT util.copy_comment(from_, to_); -- INCLUDING ALL is missing this
$_$;


--
-- Name: copy_struct(regtype, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION copy_struct(from_ regtype, to_ text) RETURNS void
    LANGUAGE sql STRICT
    AS $_$
-- must be declared STRICT to handle NULL properly
SELECT util.materialize_query(to_, $$SELECT (NULL::$$||from_||$$).*$$)
$_$;


--
-- Name: create_if_not_exists(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION create_if_not_exists(sql text, relation text DEFAULT NULL::text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	/* always generate standard exception if exists, even if table definition
	would be invalid (which generates a variety of exceptions) */
	IF util.relation_exists(relation) THEN
		PERFORM util.raise('NOTICE', relation||' already exists, skipping');
		RAISE duplicate_table;
	END IF;
	PERFORM util.eval(sql);
EXCEPTION
WHEN   duplicate_table
	OR duplicate_object -- eg. constraint
	OR duplicate_column
	OR duplicate_function
THEN NULL;
WHEN invalid_table_definition THEN
	IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
	ELSE RAISE;
	END IF;
END;
$$;


--
-- Name: FUNCTION create_if_not_exists(sql text, relation text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION create_if_not_exists(sql text, relation text) IS '
idempotent
';


--
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
    LANGUAGE sql STABLE
    AS $$
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
$$;


--
-- Name: date_from_spanish(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION date_from_spanish(date text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.replace_words(util.spanish_date_words(), lower($1))
$_$;


--
-- Name: date_part_fix(integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION date_part_fix(date_part integer) RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT CASE WHEN date_part = 0 THEN 1 ELSE date_part END
$$;


--
-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.raise('NOTICE', $$SELECT $$||$1)
$_$;


--
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.debug_print_value('returns: ', $1, $2);
SELECT $1;
$_$;


--
-- 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. */
SELECT util.raise('NOTICE', $$
$$||util.runnable_sql($1)||$$
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
$_$;


--
-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.raise('NOTICE', concat($1,
(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
$$)
$_$;


--
-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION debug_print_var(var text, value anyelement, encode boolean DEFAULT false) RETURNS void
    LANGUAGE sql IMMUTABLE
    AS $_$
/* can't use EXECUTE in the caller because "No substitution of PL/pgSQL
variables is done on the computed command string"
(http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) */
SELECT util.debug_print_value($1||' = ', $2, $3)
$_$;


--
-- Name: derived_col_constraint_enable(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_constraint_enable(col col) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.check_constraint_enable(col::util.table_item)
$$;


--
-- Name: FUNCTION derived_col_constraint_enable(col col); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_col_constraint_enable(col col) IS '
idempotent, and avoids re-running constraint on all rows if already enabled
';


--
-- Name: derived_col_constraint_expr(derived_col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_constraint_expr(def derived_col_def) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT quote_ident((def).col.name)||$$ IS NOT DISTINCT FROM ($$||def.expr||$$)$$
$_$;


--
-- Name: derived_col_constraint_initially_enabled(derived_col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_constraint_initially_enabled(def derived_col_def) RETURNS boolean
    LANGUAGE sql STABLE
    AS $$
SELECT
	-- existing constraint enabled, and no expr change that would disable it
		util.check_constraint_enabled(def.col::util.table_item)
	AND def.expr IS NOT DISTINCT FROM util.derived_col_expr(def.col)
OR
	NOT util.table_has_data((def).col.table_) -- empty, so would always pass
$$;


--
-- Name: derived_col_constraints_enable(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_constraints_enable(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.derived_col_constraint_enable(derived_col_def.col)
FROM util.derived_col_defs(table_) derived_col_def
;
SELECT NULL::void; -- don't fold away functions called in previous query

/* update col comments and table__fill_derived() with formatting changes from
enabling constraints */
SELECT util.derived_cols_update(table_);
$$;


--
-- Name: derived_col_def(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_def(check_constraint_def check_constraint_def) RETURNS derived_col_def
    LANGUAGE sql STABLE
    AS $$
SELECT (
  check_constraint_def.constraint_::util.col
, util.derived_col_expr_from_check_constraint(check_constraint_def)
)::util.derived_col_def
$$;


--
-- Name: derived_col_def(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_def(col col) RETURNS derived_col_def
    LANGUAGE sql STABLE
    AS $$
SELECT util.check_constraint_def(col)::util.derived_col_def
$$;


--
-- Name: derived_col_defs(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_defs(table_ regclass) RETURNS SETOF derived_col_def
    LANGUAGE sql STABLE
    AS $$
/* **IMPORTANT**: derived columns *must* be returned in table order (which
should = dependency order), so that they are populated in dependency order */
SELECT * FROM
(SELECT col::util.derived_col_def def FROM util.cols(table_) col) s
WHERE def IS NOT NULL/*is a derived col*/
$$;


--
-- Name: derived_col_expr(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_expr(col col) RETURNS text
    LANGUAGE sql STABLE
    AS $$
SELECT (col::util.derived_col_def).expr
$$;


--
-- Name: derived_col_expr_from_check_constraint(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_expr_from_check_constraint(check_constraint_def check_constraint_def) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- **IMPORTANT**: need to remove the enclosing () to be consistent everywhere
SELECT util.trim_parens(ltrim(util.extract_str(
quote_ident((check_constraint_def).constraint_.name)||$$ IS DISTINCT FROM$$,'',
	util.trim_parens(util.remove_prefix($$NOT $$, util.trim_parens(
		util.check_constraint_expr_enable(check_constraint_def.expr)
	), require := true))
), E' \n'))
$_$;


--
-- Name: derived_col_name_from_check_constraint(check_constraint_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_name_from_check_constraint(check_constraint_def check_constraint_def) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- **IMPORTANT**: need to remove the enclosing () to be consistent everywhere
SELECT (SELECT regexp_matches(
	util.trim_parens(util.remove_prefix($$NOT $$, util.trim_parens(
		util.check_constraint_expr_enable(check_constraint_def.expr)
	), require := true))
, $$^(.*?) IS DISTINCT FROM $$))[1]
$_$;


--
-- Name: derived_col_populated(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_populated(col col) RETURNS boolean
    LANGUAGE sql STABLE
    AS $$
SELECT util.check_constraint_enabled(col::util.table_item)
$$;


--
-- Name: derived_col_update(derived_col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_col_update(def derived_col_def) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.col_add(def);

-- if table has data, initially disabled because not yet populated
SELECT util.check_constraint((def.col, util.derived_col_constraint_expr(def)),
	enabled := util.derived_col_constraint_initially_enabled(def));

-- once CHECK constraint exists so that we can use its canon-ed formula
SELECT util.set_comment((def_).col, '
= '||(def_).expr/*on own line to support multiline exprs*/||'

derived column

to modify expr:
SELECT util.derived_col_update('||util.quote_typed(def_)||');
SELECT util.derived_cols_populate('||util.quote_typed((def_).col.table_)||');

to rename:
# rename column
# rename CHECK constraint
SELECT util.derived_cols_update('||util.quote_typed((def_).col.table_)||');

to drop:
SELECT util.drop_derived_col('||util.quote_typed((def_).col)||');
	-- DROP __ CASCADE doesn''t work when there are dependent views
')
-- **IMPORTANT**: vars can't have same name as params, which will be substituted
FROM (SELECT util.derived_col_def(def.col)/*expr from DB*/ AS def_) s
;

SELECT util.derived_cols_trigger_update((def).col.table_);
$$;


--
-- Name: FUNCTION derived_col_update(def derived_col_def); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_col_update(def derived_col_def) IS '
**IMPORTANT**: unless the table is empty, remember to run
util.derived_cols_populate() after this

usage:
SELECT util.derived_col_update(((''table'', ''col1''), $$col1_expr$$));
SELECT util.derived_col_update(((''table'', ''col2''), $$col2_expr$$));
-- if table has data:
SELECT util.derived_cols_populate(''table'');

idempotent, and avoids re-running constraint on all rows if expr hasn''t changed
';


--
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
    LANGUAGE sql STABLE
    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: -
--

COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
gets table_''s derived columns (all the columns not in the names table)
';


--
-- Name: derived_cols_export(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_export(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT $$
-- only needed if changing the derived column order:
SELECT util.derived_cols_remove($$||util.quote_typed(table_)||$$);

$$||
string_agg(
$$SELECT util.derived_col_update($$||util.quote_typed(def)||$$);
$$, '')
||$$
SELECT util.derived_cols_populate($$||util.quote_typed(table_)||$$);
$$
FROM util.derived_col_defs(table_) def
$_$;


--
-- Name: FUNCTION derived_cols_export(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_cols_export(table_ regclass) IS '
creates SQL to port the derived cols to another DB
';


--
-- Name: derived_cols_populate(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_populate(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
SELECT CASE WHEN NOT util.derived_cols_populated(table_)
THEN util.derived_cols_repopulate(table_) END
$$;


--
-- Name: FUNCTION derived_cols_populate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_cols_populate(table_ regclass) IS '
**WARNING**: this function will sometimes not repopulate columns when needed,
such as after changing the column order. in these cases, it is necessary to run
util.derived_cols_repopulate() manually.

idempotent, and only runs if needed
';


--
-- Name: derived_cols_populated(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_populated(table_ regclass) RETURNS boolean
    LANGUAGE sql STABLE
    AS $$
SELECT bool_and(util.derived_col_populated(derived_col_def.col))
FROM util.derived_col_defs(table_) derived_col_def
$$;


--
-- Name: derived_cols_remove(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_remove(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
-- in reverse dependency order so nothing cascades to other derived cols
SELECT util.drop_derived_col(def.col)
FROM util.in_reverse(ARRAY(SELECT util.derived_col_defs(table_))) def
;
SELECT NULL::void; -- don't fold away functions called in previous query
$$;


--
-- Name: FUNCTION derived_cols_remove(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_cols_remove(table_ regclass) IS '
allows derived columns to be re-created in a different order

idempotent
';


--
-- Name: derived_cols_repopulate(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_repopulate(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
-- populate derived columns by triggering table__fill_derived()
SELECT util.run_triggers(table_);

SELECT util.derived_col_constraints_enable(table_);
$$;


--
-- Name: FUNCTION derived_cols_repopulate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION derived_cols_repopulate(table_ regclass) IS '
idempotent, but repopulates each time
';


--
-- Name: derived_cols_trigger_update(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_trigger_update(table_ regclass) RETURNS void
    LANGUAGE plpgsql
    SET search_path TO util
    AS $_$
DECLARE
	trigger_func_name_sql text = table_::text||%'__fill_derived';
	trigger_name text;
BEGIN
	PERFORM util.eval($$
CREATE OR REPLACE FUNCTION $$||trigger_func_name_sql||$$()
  RETURNS trigger AS
$BODY1$
BEGIN
	-- clear derived cols so old values won't be used in calculations
$$||(
	SELECT string_agg(
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = NULL;
$$, '')
	FROM util.derived_col_defs(table_) derived_col_def
)||
$$	
	-- populate derived cols
$$||(
	SELECT string_agg(
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = (SELECT $$||(derived_col_def).expr||$$ FROM (SELECT new.*) new);
$$, '')
	FROM util.derived_col_defs(table_) derived_col_def
)||
$$	
	RETURN new;
END;
$BODY1$
  LANGUAGE plpgsql VOLATILE
  COST 100;
$$);
	PERFORM util.set_comment(trigger_func_name_sql::regproc, '
autogenerated, do not edit

to regenerate:
SELECT util.derived_cols_update('||util.quote_typed(table_)||');
');
	
	-- util.name() only works once func exists
	trigger_name = util.name(trigger_func_name_sql::regproc);
	
	PERFORM util.create_if_not_exists($$
CREATE TRIGGER $$||quote_ident(trigger_name)||$$
  BEFORE INSERT OR UPDATE
  ON $$||table_||$$
  FOR EACH ROW
  EXECUTE PROCEDURE $$||trigger_func_name_sql||$$();$$);
END;
$_$;


--
-- Name: derived_cols_update(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION derived_cols_update(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.derived_col_update(def.col) FROM util.derived_col_defs(table_) def;
SELECT NULL::void; -- don't fold away functions called in previous query
$$;


--
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
    LANGUAGE sql
    AS $_$
-- create a diff when the # of copies of a row differs between the tables
SELECT util.to_freq($1);
SELECT util.to_freq($2);
SELECT util.auto_rm_freq(ARRAY[$1, $2]);

SELECT * FROM util.diff($1, $2, $3, has_freq := true)
$_$;


--
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
usage:
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)

col_type_null (*required*): NULL::shared_base_type
';


--
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE plpgsql
    SET search_path TO pg_temp
    AS $_$
/* 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
`SET LOCAL search_path` to the current function */
BEGIN
	PERFORM util.use_schema($3); -- includes util.%== as default/fallback
	
	PERFORM util.mk_keys_func(pg_typeof($3));
	RETURN QUERY
		SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
$$/* need to explicitly cast each side to the return type because this does not
happen automatically even when an implicit cast is available */
  left_::$$||util.typeof($3)||$$
, right_::$$||util.typeof($3)
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
the *same* base type, *even though* this is optional when using a custom %== */
, util._if($4, $$true/*= CROSS JOIN*/$$,
$$ left_::$$||util.typeof($3)||$$
%== right_::$$||util.typeof($3)||$$
	-- refer to EXPLAIN output for expansion of %==$$
)
,     $$         left_::$$||util.typeof($3)||$$
IS DISTINCT FROM right_::$$||util.typeof($3)
), $3)
	;
END;
$_$;


--
-- 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: -
--

COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
col_type_null (*required*): NULL::col_type
single_row: whether the tables consist of a single row, which should be
	displayed side-by-side

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;


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
';


--
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
    LANGUAGE sql
    AS $_$
SELECT * FROM util.diff($1::text, $2::text, $3,
	single_row := util.has_single_row($1) AND util.has_single_row($2))
$_$;


--
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
helper function used by diff(regclass, regclass)

usage:
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)

col_type_null (*required*): NULL::shared_base_type
';


--
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
    LANGUAGE sql
    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: -
--

COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
idempotent
';


--
-- Name: drop_column(col, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_column(col col, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.recreate($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
$_$;


--
-- Name: FUNCTION drop_column(col col, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_column(col col, force boolean) IS '
idempotent
';


--
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
SELECT NULL::void; -- don't fold away functions called in previous query
$_$;


--
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
idempotent
';


--
-- Name: drop_constraint(table_item, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_constraint(constraint_ table_item, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$ALTER TABLE $$||constraint_.table_
||$$ DROP CONSTRAINT IF EXISTS $$||quote_ident(constraint_.name)
||util._if(force, $$ CASCADE$$, ''::text))
$_$;


--
-- Name: FUNCTION drop_constraint(constraint_ table_item, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_constraint(constraint_ table_item, force boolean) IS '
idempotent
';


--
-- Name: drop_derived_col(col, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_derived_col(col col, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $$
-- ensure that drop_column() only cascades to views
SELECT util.drop_constraint(col);

SELECT util.drop_column(col);

SELECT util.derived_cols_trigger_update(col.table_);
$$;


--
-- Name: FUNCTION drop_derived_col(col col, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_derived_col(col col, force boolean) IS '
idempotent
';


--
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
/* 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)
$_$;


--
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
||util._if($3, $$ CASCADE$$, ''::text))
$_$;


--
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
idempotent
';


--
-- 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 $_$
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
$_$;


--
-- 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.debug_print_func_call(util.quote_func_call(
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
util.quote_typed($3)))
;
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
$_$;


--
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_relation('TABLE', $1, $2)
$_$;


--
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
idempotent
';


--
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_relation('VIEW', $1, $2)
$_$;


--
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
idempotent
';


--
-- 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: -
--

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)
';


--
-- Name: ends_with(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION ends_with(suffix text, str text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT substring(str from 1+(length(str)-length(suffix))) = suffix
$$;


--
-- Name: ends_with(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION ends_with(suffix text, str text, case_sensitive boolean) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.ends_with(util.canon_case(suffix, case_sensitive),
util.canon_case(str, case_sensitive))
$$;


--
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
$_$;


--
-- 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')
$_$;


--
-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION eval(queries text[]) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval(query) FROM unnest($1) query;
SELECT NULL::void; -- don't fold away functions called in previous query
$_$;


--
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
    LANGUAGE sql
    AS $$
SELECT util.eval_raw(util.view_def_to_orig(sql)/*restore user's intent*/,
verbose_)
$$;


--
-- 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
	PERFORM util.debug_print_sql(sql);
	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: -
--

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
';


--
-- 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;
$$;


--
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
	RETURN QUERY EXECUTE sql;
END;
$$;


--
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
	ret_val ret_type_null%TYPE;
BEGIN
	PERFORM util.debug_print_sql(sql);
	EXECUTE sql INTO STRICT ret_val;
	RETURN ret_val;
END;
$$;


--
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
ret_type_null: NULL::ret_type
';


--
-- 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 $_$
SELECT util.eval2val($$SELECT $$||$1, $2)
$_$;


--
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
ret_type_null: NULL::ret_type
';


--
-- 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: -
--

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
';


--
-- Name: eval_raw(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION eval_raw(sql text, verbose_ boolean DEFAULT true) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
	EXECUTE sql;
END;
$$;


--
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
    LANGUAGE sql STABLE
    AS $_$
SELECT col_name
FROM unnest($2) s (col_name)
WHERE util.col_exists(($1, col_name))
$_$;


--
-- Name: expansion_min_cols(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION expansion_min_cols() RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT 12
$$;


--
-- Name: FUNCTION expansion_min_cols(); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION expansion_min_cols() IS '
minimum # of cols from the same table to be treated as a * expression
';


--
-- Name: explain(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION explain(sql text, verbose_ boolean DEFAULT false) RETURNS SETOF text
    LANGUAGE plpgsql
    SET client_min_messages TO 'error'
    AS $_$
/* `SET client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE
functions in the query, so this prevents displaying any log messages printed by
them. note that this *must* be set as a function option, so that the effects
will be limited to the function. */
BEGIN
	IF verbose_ THEN SET client_min_messages = NOTICE; END IF;
	
	RETURN QUERY
		SELECT * FROM util.eval2set($$EXPLAIN $$||$1, verbose_ := verbose_);
END;
$_$;


--
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION explain2notice(sql text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
$_$;


--
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
    LANGUAGE sql
    AS $_$
-- newline before and after to visually separate it from other debug info
SELECT COALESCE($$
EXPLAIN:
$$||util.fold_explain_msg(util.explain2str($1))||$$
$$, '')
$_$;


--
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
	RETURN util.explain2notice_msg(sql);
EXCEPTION
WHEN   syntax_error
	OR invalid_cursor_definition -- "cannot open multi-query plan as cursor"
	THEN RETURN NULL; -- non-explainable query
	/* don't use util.is_explainable() because the list provided by Postgres
	(http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691)
	excludes some query types that are in fact EXPLAIN-able */
END;
$$;


--
-- 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)
$_$;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace: 
--

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
    LANGUAGE sql
    AS $_$
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
$$||quote_nullable($1)||$$
)$$)
$_$;


--
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
usage:
PERFORM util.explain2table($$
query
$$);
';


--
-- Name: extract_by_regexp(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION extract_by_regexp(pattern text, str text, group_ integer DEFAULT 1) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT COALESCE((SELECT regexp_matches(str, pattern))[group_], str)
$$;


--
-- Name: extract_str(text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION extract_str(prefix text, suffix text, str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.remove_suffix(suffix,
util.remove_prefix(prefix, str, require := true), require := true)
$$;


--
-- Name: first_col(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION first_col(table_ regclass) RETURNS col
    LANGUAGE sql STABLE
    AS $$
SELECT * FROM util.cols(table_) LIMIT 1
$$;


--
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION first_word(str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
$_$;


--
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
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
$_$;


--
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION fix_array("array" anyarray) IS '
ensures that an array will always have proper non-NULL dimensions
';


--
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
$_$;


--
-- 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
	IF SQLERRM = 'cannot drop columns from view'
	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
	THEN
		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: -
--

COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
idempotent
';


--
-- Name: freq_always_1(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT util.eval2val(
$$SELECT NOT EXISTS( -- there is no row that is != 1
	SELECT NULL
	FROM $$||$1||$$
	WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
	LIMIT 1
)
$$, NULL::boolean)
$_$;


--
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
$_$;


--
-- Name: geography(geocoord); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION geography(geocoord geocoord) RETURNS postgis.geography
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.geography(util.geometry(geocoord))
$$;


--
-- Name: FUNCTION geography(geocoord geocoord); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION geography(geocoord geocoord) IS '
suppresses "Coordinate values were coerced into range [-180 -90, 180 90] for
GEOGRAPHY" NOTICEs
';


--
-- Name: geography(postgis.geometry); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION geography(geometry postgis.geometry) RETURNS postgis.geography
    LANGUAGE sql IMMUTABLE
    SET client_min_messages TO 'warning'
    AS $$
/* `SET client_min_messages = WARNING`: hide "Coordinate values were coerced
into range [-180 -90, 180 90] for GEOGRAPHY" NOTICEs, which clutter up the view
export output */
SELECT geometry::postgis.geography
$$;


--
-- Name: geometry(geocoord); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION geometry(geocoord geocoord) RETURNS postgis.geometry
    LANGUAGE sql IMMUTABLE
    SET client_min_messages TO 'warning'
    AS $_$
SELECT postgis.st_setsrid(postgis.st_point(
/*x_lon=*/$1.longitude_deg, /*y_lat=*/$1.latitude_deg),
/*WGS84*/4326)
$_$;


--
-- Name: FUNCTION geometry(geocoord geocoord); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION geometry(geocoord geocoord) IS '
*note*: it is not possible to create a cast for this, as a bug in pg_dump
prevents the cast from being exported, even when no export filters are applied
';


--
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION grants_users() RETURNS SETOF text
    LANGUAGE sql IMMUTABLE
    AS $$
VALUES ('bien_read'), ('public_')
$$;


--
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT substring($2 for length($1)) = $1
$_$;


--
-- 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)
$_$;


--
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
$_$;


--
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION hstore(keys text[], value text) IS '
avoids repeating the same value for each key
';


--
-- Name: identifier_replace(text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION identifier_replace(replace text, with_ text, str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT regexp_replace(str, '\y'||util.regexp_quote(replace)||'\y', with_, 'g')
$$;


--
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT COALESCE($1, $2)
$_$;


--
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
';


--
-- Name: in_new_world(geocoord); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION in_new_world(point geocoord) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
/* use function rather than operator+search_path to allow inlining, which
enables util.new_world() to only be evaluated once */
SELECT util.contained_within_approx($1, util.new_world())
$_$;


--
-- Name: FUNCTION in_new_world(point geocoord); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION in_new_world(point geocoord) IS '
**WARNING**: this includes false positives above and below the New World
bounding box, as described in util.bounding_box()
';


--
-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
$_$;


--
-- Name: in_south_america(geocoord); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION in_south_america(point geocoord) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
/* use function rather than operator+search_path to allow inlining, which
enables util.south_america() to only be evaluated once */
SELECT $1.latitude_deg BETWEEN -56 AND 13 AND $1.longitude_deg BETWEEN -82 AND -34
$_$;


--
-- 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)
$_$;


--
-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
	PERFORM util.cast(value, ret_type_null);
	-- must happen *after* cast check, because NULL is not valid for some types
	IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
	RETURN true;
EXCEPTION
WHEN   data_exception
	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
	THEN
	RETURN false;
END;
$$;


--
-- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
passes NULL through. however, if NULL is not valid for the type, false will be
returned instead.

ret_type_null: NULL::ret_type
';


--
-- Name: is_constant(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_constant(col col) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT COALESCE(util.col_comment($1) LIKE '
constant
%', false)
$_$;


--
-- 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
$_$;


--
-- 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'
$_$;


--
-- 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*/
)
$_$;


--
-- Name: is_log_level(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_log_level(raise_type text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT raise_type ~ '^[[:upper:]]{3,}[[:digit:]]?$'
$_$;


--
-- 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)
$_$;


--
-- 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
$_$;


--
-- 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'
$_$;


--
-- Name: is_sqlstate(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_sqlstate(error_code text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT error_code ~ '^[[:digit:][:upper:]]{5}$'
$_$;


--
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_table(relation regclass) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
$_$;


--
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_view(relation regclass) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
$_$;


--
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$
SELECT $1 || $3 || $2
$_$;


--
-- 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
';


--
-- 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
$_$;


--
-- Name: keys_eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION keys_eq(left_ anyelement, right_ anyelement) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT keys($1) = keys($2)
$_$;


--
-- Name: FUNCTION keys_eq(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION keys_eq(left_ anyelement, right_ anyelement) IS '
needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
';


--
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
$_$;


--
-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	errors_ct integer = 0;
	loop_var loop_type_null%TYPE;
BEGIN
	FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
	LOOP
		BEGIN
			EXECUTE loop_body_sql USING loop_var;
		EXCEPTION
		WHEN OTHERS THEN
			errors_ct = errors_ct+1;
			PERFORM util.raise_error_warning(SQLERRM);
		END;
	END LOOP;
	IF errors_ct > 0 THEN
		-- can't raise exception because this would roll back the transaction
		PERFORM util.raise_error_warning('there were '||errors_ct
			||' errors: see the WARNINGs for details');
	END IF;
END;
$$;


--
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION ltrim_nl(str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT ltrim($1, $$
$$)
$_$;


--
-- 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;
$$;


--
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

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$$
        INTO value USING key;
    RETURN value;
END;
$_$;


--
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util._map(util.nulls_map($1), $2)
$_$;


--
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
--

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].

[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)
both take ~920 ms.
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.
';


--
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
    LANGUAGE plpgsql STABLE STRICT
    AS $_$
BEGIN
    RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
END;
$_$;


--
-- Name: map_words(hstore, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION map_words(map hstore, str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT string_agg(util._map($1, word), '') FROM util.words($2) word
$_$;


--
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
$$||util.ltrim_nl($2));
-- make sure the created table has the correct estimated row count
SELECT util.analyze_($1);

SELECT util.append_comment($1, '
contents generated from:
'||util.ltrim_nl(util.runnable_sql($2))||';
');
$_$;


--
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
idempotent
';


--
-- Name: mk_const_col(col, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_const_col(col col, value anyelement) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
||quote_literal($2)||$$;
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
constant
';
$$)
$_$;


--
-- Name: FUNCTION mk_const_col(col col, value anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION mk_const_col(col col, value anyelement) IS '
idempotent
';


--
-- Name: mk_derived_col(col, text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_derived_col(col col, expr text, overwrite boolean DEFAULT false) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $_$
DECLARE
    type regtype = util.typeof(expr, col.table_::text::regtype);
    col_name_sql text = quote_ident(col.name);
BEGIN
    PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;$$ END)||$$
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
$$||expr||$$;
$$);
END;
$_$;


--
-- Name: FUNCTION mk_derived_col(col col, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION mk_derived_col(col col, expr text, overwrite boolean) IS '
idempotent
';


--
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
--

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
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT
$$SELECT
$$||$3||$$
FROM      $$||$1||$$ left_
FULL JOIN $$||$2||$$ right_
ON $$||$4||$$
WHERE $$||$5||$$
ORDER BY left_, right_
$$
$_$;


--
-- Name: mk_drop_from_create(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_drop_from_create(create_cmd text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $$DROP $$||match[1]||$$ IF EXISTS $$||match[2]||$$;$$
FROM util.regexp_match($1,
-- match first CREATE, *if* no DROP came before it
'(?m)\A(?:^(?!DROP\y|SELECT util\.drop).*\n)*?^CREATE(?: OR REPLACE)? ([[:upper:]]+) ((?:)??.*?)(?: AS(?: (?:SELECT\y.*)?)?)?$'
	/* (?:)?? makes the surrounding () group non-greedy, so that (?: AS ?)?
	works properly (due to nonstandard Postgres regexp behavior:
	http://www.postgresql.org/docs/9.3/static/functions-matching.html#POSIX-MATCHING-RULES) */
) match
$_$;


--
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
    LANGUAGE sql
    AS $_$
-- keys()
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
));

-- values_()
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
, 'values_');
$_$;


--
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
($$||util.mk_typed_cols_list($2)||$$);
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
autogenerated
';
$$);

SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
$_$;


--
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$
CREATE OR REPLACE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
||util.qual_name($1)||$$)
  RETURNS $$||util.qual_name($2)||$$ AS
$BODY1$
SELECT ROW($$||
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
$BODY1$
  LANGUAGE sql IMMUTABLE
  COST 100;
$$);
$_$;


--
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_map_table(table_ text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$
CREATE TABLE $$||$1||$$
(
    LIKE util.map INCLUDING ALL
);

CREATE TRIGGER map_filter_insert
  BEFORE INSERT
  ON $$||$1||$$
  FOR EACH ROW
  EXECUTE PROCEDURE util.map_filter_insert();
$$)
$_$;


--
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_not_null(text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT COALESCE($1, '<NULL>')
$_$;


--
-- 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 $_$
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
util.qual_name((unnest).type), ''), '')
FROM unnest($1)
$_$;


--
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
$_$;


--
-- 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
';


--
-- Name: mk_set_col_comments(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_col_comments(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT string_agg(util.mk_set_comment(col)||$$
$$, '') FROM util.cols($1) col
$_$;


--
-- Name: mk_set_comment(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(col col) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.mk_set_comment($1, util.comment($1))
$_$;


--
-- Name: mk_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.mk_set_comment($1, util.comment($1))
$_$;


--
-- Name: mk_set_comment(col, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(col col, comment text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.mk_set_comment('COLUMN '||util.sql($1), $2)
$_$;


--
-- Name: mk_set_comment(regprocedure, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(func regprocedure, comment text) RETURNS text
    LANGUAGE sql STABLE
    AS $$
SELECT util.mk_set_comment('FUNCTION '||func, comment)
$$;


--
-- Name: mk_set_comment(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(on_ text, comment text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT COALESCE($$COMMENT ON $$||$1||$$ IS $$
||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
$_$;


--
-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.mk_set_comment(util.relation_type($1)||' '||$1, $2)
$_$;


--
-- Name: mk_set_comments(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_comments(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.mk_set_comment($1)||$$
$$||util.mk_set_col_comments($1)
$_$;


--
-- Name: mk_set_relation_metadata(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_relation_metadata(relation regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.show_grants_for($1)
||util.mk_set_comments($1)||$$
$$
$_$;


--
-- 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)
$_$;


--
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
/* 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 */
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
||$$ search_path TO $$||$1
$_$;


--
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
$_$;


--
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
idempotent
';


--
-- 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;
$_$;


--
-- 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 $_$
DECLARE
	view_qual_name text = util.qual_name(view_);
	row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
BEGIN
	EXECUTE $$
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 $$
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
  RETURNS SETOF $$||view_||$$ AS
$BODY1$
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||$$())
ORDER BY $$||quote_ident(row_num_col)||$$
$BODY1$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000
$$;
	
	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
	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$
SELECT * FROM $$||view_qual_name||$$($2, $3)
$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.)
';
$$;
END;
$_$;


--
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
creates subset function which turns off enable_sort
';


--
-- 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 $_$
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
util.qual_name((unnest).type), ', '), '')
FROM unnest($1)
$_$;


--
-- Name: name(regprocedure); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION name(func regprocedure) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT proname::text FROM pg_proc WHERE oid = $1
$_$;


--
-- 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
$_$;


--
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION name(type regtype) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT typname::text FROM pg_type WHERE oid = $1
$_$;


--
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT octet_length($1) >= util.namedatalen() - $2
$_$;


--
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION namedatalen() RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
$$;


--
-- Name: new_world(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION new_world() RETURNS postgis.geography
    LANGUAGE sql IMMUTABLE
    SET search_path TO util
    AS $$
SELECT util.bounding_box(-56 ~ 83, 172 ~ -34)
$$;


--
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
$_$;


--
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION not_null(value anyelement) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 IS NOT NULL
$_$;


--
-- 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: -
--

COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
for use with _map()
';


--
-- Name: numrange(range); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION numrange(value range) RETURNS numrange
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT numrange($1.lower, $1.upper, $1.bounds)
$_$;


--
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $2 + COALESCE($1, 0)
$_$;


--
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION parent(table_ regclass) RETURNS regclass
    LANGUAGE sql STABLE
    AS $_$
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
$_$;


--
-- Name: pg_get_expr(pg_node_tree, oid, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION pg_get_expr(node_tree pg_node_tree, table_ oid, pretty_print boolean DEFAULT false) RETURNS text
    LANGUAGE sql STABLE
    SET search_path TO pg_temp
    AS $$
SELECT pg_catalog.pg_get_expr(node_tree, table_, pretty_print)
$$;


--
-- Name: pg_get_viewdef(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION pg_get_viewdef(view_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.view_def_to_orig(pg_catalog.pg_get_viewdef($1))
$_$;


--
-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$INSERT INTO $$||$1||$$
$$||util.ltrim_nl($2));
-- make sure the created table has the correct estimated row count
SELECT util.analyze_($1);
$_$;


--
-- 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))
$_$;


--
-- 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
';


--
-- 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)
$_$;


--
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION qual_name(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    SET search_path TO pg_temp
    AS $_$
SELECT $1::text
$_$;


--
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION qual_name(type regtype) RETURNS text
    LANGUAGE sql STABLE
    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
';


--
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION qual_name(type unknown) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.qual_name($1::text::regtype)
$_$;


--
-- Name: quote_as_str(anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION quote_as_str(value anyelement) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT quote_nullable(COALESCE(value::text, '<NULL>'))
$$;


--
-- 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)||')'
$_$;


--
-- Name: quote_typed(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION quote_typed(col col) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $$($$||quote_nullable(col.table_)||$$, $$||quote_nullable(col.name)||$$)::$$||util.qual_name(pg_typeof($1))
$_$;


--
-- Name: quote_typed(derived_col_def); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION quote_typed(def derived_col_def) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $$($$||util.quote_typed(def.col)||$$, $$||'$$'||def.expr||'$$'||$$)::$$||util.qual_name(pg_typeof($1))
$_$;


--
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION quote_typed(value anyelement) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
$_$;


--
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION raise(type text, msg text) RETURNS void
    LANGUAGE sql IMMUTABLE
    AS $_X$
/* **IMPORTANT**: must use eval_raw() so that functions called by eval() (such
as view_def_to_orig()) can use raise() for debugging */
SELECT util.eval_raw($$
CREATE OR REPLACE FUNCTION pg_temp.__raise()
  RETURNS void AS
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
$__BODY1$
BEGIN
	RAISE $$||util.raise_expr(type)||$$ USING MESSAGE = $$||
util.quote_as_str($2)||$$;
		--quote_as_str() to avoid "RAISE statement option cannot be null" errors
END;
$__BODY1$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
$$, verbose_ := false);

SELECT util.eval_raw($$SELECT pg_temp.__raise()$$, verbose_ := false);
$_X$;


--
-- 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 an error condition name or SQLSTATE from
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
';


--
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION raise_error_warning(msg text) RETURNS void
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.raise('WARNING', 'ERROR:  '||$1)
$_$;


--
-- Name: raise_expr(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION raise_expr(raise_type text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT CASE
WHEN util.is_sqlstate(raise_type) THEN $$SQLSTATE $$||quote_literal(raise_type)
WHEN util.is_log_level(raise_type) THEN raise_type -- only if not SQLSTATE
ELSE raise_type
END
$_$;


--
-- Name: raise_undefined_column(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION raise_undefined_column(col col) RETURNS text
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
BEGIN
	RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
END;
$$;


--
-- Name: range(numeric, numeric); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION range(lower numeric, upper numeric) RETURNS range
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT ($1, $2, '[]')::util.range
$_$;


--
-- Name: recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE
	PG_EXCEPTION_DETAIL text;
	restore_views_info util.restore_views_info;
BEGIN
	restore_views_info = util.save_drop_views(users);
	
	-- trigger the dependent_objects_still_exist exception
	PERFORM util.eval(COALESCE(util.mk_drop_from_create(cmd), '')||cmd);
		-- *not* CASCADE; it must trigger an exception
	
	PERFORM util.restore_views(restore_views_info);
EXCEPTION
WHEN dependent_objects_still_exist THEN
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
	users = array(SELECT * FROM util.regexp_matches_group(
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
		-- will be in forward dependency order
	PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
	PERFORM util.debug_print_var('users', users);
	IF util.is_empty(users) THEN RAISE; END IF;
	PERFORM util.recreate(cmd, users);
END;
$_$;


--
-- Name: FUNCTION recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION recreate(cmd text, users text[]) IS '
the appropriate drop statement will be added automatically.

usage:
SELECT util.recreate($$
CREATE VIEW schema.main_view AS _;

-- manually restore views that need to be updated for the changes
CREATE VIEW schema.dependent_view AS _;
$$);

idempotent

users: not necessary to provide this because it will be autopopulated
';


--
-- Name: recreate_view(regclass, text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION recreate_view(view_ regclass, view_query text DEFAULT NULL::text, dependent_view_changes text DEFAULT ''::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.recreate($$
CREATE VIEW $$||$1||$$ AS 
$$||COALESCE($2, pg_get_viewdef($1))||$$;
$$||util.mk_set_relation_metadata($1)||$$

-- manually restore views that need to be updated for the changes
$$||$3||$$
$$);
$_$;


--
-- Name: FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION recreate_view(view_ regclass, view_query text, dependent_view_changes text) IS '
usage:
SELECT util.recreate_view(''schema.main_view'', $$
SELECT __
$$, $$
CREATE VIEW schema.dependent_view AS 
__;
$$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
$$);

if view has already been modified:
SELECT util.recreate_view(''schema.main_view'', dependent_view_changes := $$
CREATE VIEW schema.dependent_view AS 
__;
$$||util.mk_set_relation_metadata(''schema.dependent_view'')||$$
$$);

idempotent
';


--
-- Name: regexp_match(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION regexp_match(str text, re text) RETURNS text[]
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT match FROM regexp_matches($1, $2) match LIMIT 1/*only 1st match*/
$_$;


--
-- 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')
$_$;


--
-- 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')
$_$;


--
-- 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) = ')'
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
$_$;


--
-- Name: regtype(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION regtype(regclass regclass) RETURNS regtype
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT regclass::text::regtype
$$;


--
-- Name: relation_exists(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION relation_exists(relation text) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT $1 IS NOT NULL AND util.is_castable($1, NULL::regclass)
$_$;


--
-- Name: relation_replace(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION relation_replace(replace text, with_ anyelement) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT CASE WHEN with_::text != replace THEN -- don't drop the relation to keep!
util.recreate($$
SELECT util.drop_relation(util.relation_type($$||util.quote_typed(with_)
	||$$), $$||util.quote_typed(replace)||$$);
SELECT util.rename_relation($$||util.quote_typed(with_)||$$, $$||
	util.quote_typed(replace)||$$);
$$)
END
$_$;


--
-- 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))
$_$;


--
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
$_$;


--
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION relation_type(type regtype) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT 'TYPE'::text
$$;


--
-- 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
$_$;


--
-- 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. */
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
SELECT util.inherit($2, $4);
SELECT util.inherit($3, $4);

SELECT util.rematerialize_query($1, $$
SELECT * FROM util.diff(
  $$||util.quote_typed($2)||$$
, $$||util.quote_typed($3)||$$
, NULL::$$||$4||$$)
$$);

/* the table unfortunately cannot be *materialized* in human-readable form,
because this would create column name collisions between the two sides */
SELECT util.prepend_comment($1, '
to view this table in human-readable form (with each side''s tuple column
expanded to its component fields):
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';

to display NULL values that are extra or missing:
SELECT * FROM '||$1||';
');
$_$;


--
-- 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
';


--
-- 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
';


--
-- Name: rematerialize_view(text, regclass, text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass, table_mod_sql text DEFAULT ''::text, repopulate boolean DEFAULT true) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
	temp_table_esc text = util.concat_esc(table_esc, '__new');
		-- _ not . to avoid requiring "" around name
	temp_table regclass;
	temp_table_mod_sql text = util.identifier_replace(table_esc, temp_table_esc,
		table_mod_sql);
BEGIN
	IF repopulate THEN 
		/* test if recreate() can successfully re-create the dependent views, to
		avoid populating the entire table (which for some views can take hours)
		only to have the changes rolled back by a failing dependent view */
		BEGIN
			PERFORM util.rematerialize_view(table_esc, view_, table_mod_sql,
				repopulate := false);
			
			-- roll back DB changes
			PERFORM util.raise('ROLBK', 'sandbox block finished and rolled back');
		EXCEPTION
		WHEN SQLSTATE 'ROLBK' THEN NULL;
		END;
	END IF;
	
	-- create new table, without blocking original table
	PERFORM util.copy_struct(view_, temp_table_esc);
	temp_table = temp_table_esc::regclass; -- once it exists
	PERFORM util.eval(temp_table_mod_sql);
	PERFORM CASE WHEN repopulate THEN util.copy_data(view_, temp_table) END;
	
	-- move new table into place (fast operation)
	PERFORM util.relation_replace(table_esc, temp_table);
END;
$$;


--
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass, table_mod_sql text, repopulate boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass, table_mod_sql text, repopulate boolean) IS '
usage:
SELECT util.rematerialize_view(''table'', ''view'', $$
ALTER TABLE table ALTER COLUMN col SET NOT NULL;
-- ...
$$);

non-blocking: allows full access to original materialized table during operation

idempotent, but repeats action each time
';


--
-- Name: remove_prefix(text, text, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true, case_sensitive boolean DEFAULT true) RETURNS text
    LANGUAGE sql IMMUTABLE STRICT
    AS $$
-- must be declared STRICT to handle NULL properly
SELECT CASE
WHEN util.starts_with(prefix, str, case_sensitive) THEN
	substring(str from 1+length(prefix))
WHEN NOT require THEN str
ELSE
	util.raise('data_exception', 'str does not start with prefix '
	||quote_nullable(prefix)||': '||quote_nullable(str))::text
END
$$;


--
-- Name: remove_suffix(text, text, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION remove_suffix(suffix text, str text, require boolean DEFAULT true, case_sensitive boolean DEFAULT true) RETURNS text
    LANGUAGE sql IMMUTABLE STRICT
    AS $$
-- must be declared STRICT to handle NULL properly
SELECT CASE
WHEN util.ends_with(suffix, str, case_sensitive) THEN
	substring(str from 1 for length(str)-length(suffix))
WHEN NOT require THEN str
ELSE
	util.raise('data_exception', 'str does not end with suffix '
	||quote_nullable(suffix)||': '||quote_nullable(str))::text
END
$$;


--
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
FROM util.col_names($1::text::regtype) f (name);
SELECT NULL::void; -- don't fold away functions called in previous query
$_$;


--
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
idempotent
';


--
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
    LANGUAGE sql
    AS $_$
/* 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)
$_$;


--
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
    LANGUAGE sql
    AS $_$
/* 'ALTER TABLE can be used with views too'
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
||quote_ident($2))
$_$;


--
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
idempotent
';


--
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
$_$;


--
-- 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 
';


--
-- Name: replace_words(hstore, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION replace_words(replacements hstore, str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT util.map_words($1||'*=>*', $2)
$_$;


--
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
SELECT util.set_col_names($1, $2);
$_$;


--
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
--

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.
';


--
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION reset_map_table(table_ text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_table($1);
SELECT util.mk_map_table($1);
$_$;


--
-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION restore_views(restore_views_info) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.debug_print_var('views', $1);
SELECT util.create_if_not_exists((view_).def, (view_).path)
	/* need to specify view name for manual existence check, in case view def
	becomes invalid, which would produce nonstandard (uncatchable) exception */
FROM unnest($1.views) view_; -- in forward dependency order
	/* create_if_not_exists() rather than eval(), because cmd might manually
	re-create a deleted dependent view, causing it to already exist */
SELECT NULL::void; -- don't fold away functions called in previous query
$_$;


--
-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_column($1, $2, force := true)
$_$;


--
-- 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)
$_$;


--
-- Name: run_triggers(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION run_triggers(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval($$UPDATE $$||table_||$$ SET $$||col_sql||$$ = $$||col_sql)
FROM (SELECT quote_ident((util.first_col(table_)).name) AS col_sql) s;
SELECT util.try_cluster(table_); -- re-sort rows if needed
$_$;


--
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION runnable_sql(sql text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
ELSE util.mk_set_search_path(for_printing := true)||$$;
$$ END)||$1
$_$;


--
-- 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_, replace := false);
			/* replace: no `OR REPLACE` because that causes nonuniform errors
			(eg. invalid_table_definition), instead of the standard
			duplicate_table exception caught by util.create_if_not_exists() */
		PERFORM util.drop_view(view_);
	EXCEPTION
		WHEN undefined_table THEN NULL;
	END;
	RETURN result;
END;
$$;


--
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
    LANGUAGE sql
    AS $_$
SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
SELECT (view_, util.save_drop_view(view_))::util.db_item
FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
)))::util.restore_views_info
$_$;


--
-- 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
$_$;


--
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION schema(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
$_$;


--
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION schema(type regtype) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
$_$;


--
-- 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))
$_$;


--
-- 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|_)')
$_$;


--
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
a schema bundle is a group of schemas with a common prefix
';


--
-- 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
$_$;


--
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT quote_ident(util.schema($1))
$_$;


--
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
$_$;


--
-- 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
$_$;


--
-- 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)
$_$;


--
-- 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))
$_$;


--
-- 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));
$_$;


--
-- 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;
$$;


--
-- 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$$);
$_$;


--
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION search_path_append(schemas text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval(
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
$_$;


--
-- Name: seq__create(text, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION seq__create(seq text, start integer DEFAULT 0) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.create_if_not_exists($$CREATE SEQUENCE $$||$1||$$ MINVALUE $$||$2,
$1)
$_$;


--
-- Name: FUNCTION seq__create(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION seq__create(seq text, start integer) IS '
idempotent
';


--
-- Name: seq__reset(text, integer); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION seq__reset(seq text, start integer DEFAULT 0) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.seq__create($1, $2);
SELECT util.eval($$ALTER SEQUENCE $$||$1||$$ RESTART$$);
$_$;


--
-- Name: FUNCTION seq__reset(seq text, start integer); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION seq__reset(seq text, start integer) IS '
creates sequence if doesn''t exist

idempotent

start: *note*: only used if sequence doesn''t exist
';


--
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $_$
DECLARE
    old text[] = ARRAY(SELECT util.col_names(table_));
    new text[] = ARRAY(SELECT util.map_values(names));
BEGIN
    old = old[1:array_length(new, 1)]; -- truncate to same length
    PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
||$$ TO $$||quote_ident(value))
    FROM each(hstore(old, new))
    WHERE value != key -- not same name
    ;
END;
$_$;


--
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
idempotent
';


--
-- 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
	-- 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
			ELSE RAISE;
			END IF;
	END;
	
	FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
	LOOP
		PERFORM util.mk_const_col((table_, row_."to"),
			substring(row_."from" from 2));
	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: -
--

COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
idempotent.
the metadata mappings must be *last* in the names table.
';


--
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval(COALESCE(
$$ALTER TABLE $$||$1||$$
$$||(
	SELECT
	string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
	||$$ USING $$||col_name_sql||$$::$$||target_type, $$
, $$)
	FROM
	(
		SELECT
		  quote_ident(col_name) AS col_name_sql
		, util.col_type(($1, col_name)) AS curr_type
		, type AS target_type
		FROM unnest($2)
	) s
	WHERE curr_type != target_type
), ''))
$_$;


--
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
idempotent
';


--
-- Name: set_comment(anyelement, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION set_comment(item anyelement, comment text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.eval(util.mk_set_comment($1, $2))
$_$;


--
-- 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))
$_$;


--
-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
||$1||$$ AS
$$||util.pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
$$||util.mk_set_relation_metadata($1)
$_$;


--
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT string_agg(cmd, '')
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
$_$;


--
-- 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['c'::text, '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
$_$;


--
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
    LANGUAGE sql STABLE
    AS $_$
SELECT oid
FROM pg_type
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
ORDER BY typname
$_$;


--
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
    LANGUAGE sql STABLE
    AS $_$
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
$_$;


--
-- Name: south_america(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION south_america() RETURNS postgis.geometry
    LANGUAGE sql IMMUTABLE
    SET search_path TO util
    AS $$
SELECT util.bounding_box__no_dateline(-56 ~ 13, -82 ~ -34)
$$;


--
-- Name: spanish_date_words(); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION spanish_date_words() RETURNS hstore
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT '
 de => ""
,al => -
,enero => January
,febrero => February
,marzo => March
,abril => April
,mayo => May
,junio => June
,julio => July
,agosto => August
,septiembre => September
,setiembre => September
,octubre => October
,noviembre => November
,diciembre => December
'::hstore
$$;


--
-- Name: sql(col); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION sql(col col) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT $1.table_||'.'||quote_ident($1.name)
$_$;


--
-- Name: starts_with(text, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION starts_with(prefix text, str text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT substring(str from 1 for length(prefix)) = prefix
$$;


--
-- Name: starts_with(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION starts_with(prefix text, str text, case_sensitive boolean) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.starts_with(util.canon_case(prefix, case_sensitive),
util.canon_case(str, case_sensitive))
$$;


--
-- 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)||'$'
$_$;


--
-- Name: subspecies(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION subspecies(taxon_name text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT (regexp_matches($1, '\ysubsp\. (\S+)'))[1]
$_$;


--
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
    LANGUAGE plpgsql STABLE STRICT
    AS $_$
DECLARE
    hstore hstore;
BEGIN
    EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
        table_||$$))$$ INTO STRICT hstore;
    RETURN hstore;
END;
$_$;


--
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
    LANGUAGE sql STABLE
    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: -
--

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
';


--
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
    LANGUAGE sql
    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: -
--

COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
stores a status flag by the presence of a table constraint.
idempotent.
';


--
-- Name: table_has_data(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table_has_data(table_ regclass) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT util.eval2val($$SELECT EXISTS(SELECT NULL FROM $$||table_||$$ LIMIT 1)$$,
NULL::boolean)
$_$;


--
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
    LANGUAGE sql STABLE
    AS $_$
SELECT util.table_flag__get($1, 'nulls_mapped')
$_$;


--
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
gets whether a table''s NULL-equivalent strings have been replaced with NULL
';


--
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.table_flag__set($1, 'nulls_mapped')
$_$;


--
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
sets that a table''s NULL-equivalent strings have been replaced with NULL.
idempotent.
';


--
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION to_freq(table_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
-- save data before truncating main table
SELECT util.copy($1, 'pg_temp.__copy');

-- repopulate main table w/ copies column
SELECT util.truncate($1);
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
SELECT util.populate_table($1, $$
SELECT (table_).*, copies
FROM (
	SELECT table_, COUNT(*) AS copies
	FROM pg_temp.__copy table_
	GROUP BY table_
) s
$$);

-- delete temp table so it doesn't stay around until end of connection
SELECT util.drop_table('pg_temp.__copy');
$_$;


--
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $_$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
    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;
$_$;


--
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
idempotent
';


--
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
SELECT NULL::void; -- don't fold away functions called in previous query
$_$;


--
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION "trim"(table_ regclass, names regclass, force boolean) IS '
trims table_ to include only columns in the original data

by default, cascadingly drops dependent columns so that they don''t prevent
trim() from succeeding. note that this requires the dependent columns to then be
manually re-created.

idempotent
';


--
-- Name: trim_parens(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION trim_parens(str text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT CASE WHEN /*base case*/inner_str IS NULL THEN str
ELSE util.trim_parens(inner_str) END
FROM (SELECT (SELECT regexp_matches(str, '^\((.*)\)$'))[1] AS inner_str) s
$_$;


--
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION truncate(table_ regclass) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $_$
BEGIN
    EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
END;
$_$;


--
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION truncate(table_ regclass) IS '
idempotent
';


--
-- 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 $_$
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
$_$;


--
-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
	/* need explicit cast because some types not implicitly-castable, and also
	to make the cast happen inside the try block. (*implicit* casts to the
	return type happen at the end of the function, outside any block.) */
	RETURN util.cast(value, ret_type_null);
EXCEPTION
WHEN   data_exception
	OR invalid_schema_name -- eg. 'pg_temp.__'::regclass
	OR syntax_error_or_access_rule_violation -- eg. ::regclass
	THEN
	PERFORM util.raise('WARNING', SQLERRM);
	RETURN NULL;
END;
$$;


--
-- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
ret_type_null: NULL::ret_type
';


--
-- Name: try_cluster(regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION try_cluster(table_ regclass) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
	PERFORM util.cluster(table_);
EXCEPTION
WHEN undefined_object THEN
	IF SQLERRM LIKE 'there is no previously clustered index for table %' THEN NULL;
	ELSE RAISE; -- rethrow
	END IF;
END;
$$;


--
-- Name: FUNCTION try_cluster(table_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION try_cluster(table_ regclass) IS '
idempotent, but reclusters each time
';


--
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION try_create(sql text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
	PERFORM util.eval(sql);
EXCEPTION
WHEN   not_null_violation
		/* trying to add NOT NULL column to parent table, which cascades to
		child table whose values for the new column will be NULL */
	OR wrong_object_type -- trying to alter a view's columns
	OR undefined_column
	OR duplicate_column
THEN NULL;
WHEN datatype_mismatch THEN
	IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
	ELSE RAISE; -- rethrow
	END IF;
END;
$$;


--
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION try_create(sql text) IS '
idempotent
';


--
-- Name: try_mk_derived_col(col, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION try_mk_derived_col(col col, expr text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
$_$;


--
-- Name: FUNCTION try_mk_derived_col(col col, expr text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION try_mk_derived_col(col col, expr text) IS '
idempotent
';


--
-- 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
$_$;


--
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION type_qual(value anyelement) IS '
a type''s NOT NULL qualifier
';


--
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
    LANGUAGE sql STABLE
    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
$_$;


--
-- 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))
$_$;


--
-- Name: typeof(text, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION typeof(expr text, table_ regclass) RETURNS regtype
    LANGUAGE sql STABLE
    AS $$
SELECT util.typeof(expr, util.regtype(table_))
$$;


--
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
    LANGUAGE sql STABLE
    AS $_$
SELECT util.eval2val($$SELECT pg_typeof($$||expr||$$)$$||
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, ''), NULL::regtype)
$_$;


--
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
$_$;


--
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
auto-appends util to the search_path to enable use of util operators
';


--
-- Name: view_body(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION view_body(view_def text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
-- support both CREATE statements and view bodies
SELECT (SELECT regexp_matches(view_def,
'(?w).*?^ *(?:CREATE|SELECT|VALUES)\y.*?;$'))[1]
	-- (?w) : ^$ multiline but . dotall
$_$;


--
-- Name: view_def_to_orig(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION view_def_to_orig(view_def text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT CASE
WHEN util.view_is_subset_or_renaming($1) THEN $1
	-- list of cols from the same table is not an expanded * expression
ELSE
regexp_replace(
regexp_replace(
$1
,
/* unexpand expanded * expressions. any list of >= util.expansion_min_cols()
cols from the same table is treated as a * expression. */
'('||util.aliased_col_re()||'\.)'||util.aliased_col_re()||
	/* 1st col, which lacks separator before.
	*note*: can't prepend \y because it considers only \w chars, not " */
'(,[[:blank:]]*
[[:blank:]]*)\1'||util.aliased_col_re()|| -- 2nd col, which has separator before
'(?:\2\1'||util.aliased_col_re()||'){'||(util.expansion_min_cols()-2)||',}',
	-- later cols, with same table and separator as 1st 2
'\1*'/*prefix w/ table*/,
'g')
,
/* merge .* expressions resulting from a SELECT * of a join. any list of
multiple .* expressions is treated as a SELECT * . */
''||util.aliased_col_re()||'\.\*'||
	/* 1st table, which lacks separator before.
	*note*: can't prepend \y because it considers only \w chars, not " */
'(,[[:blank:]]*
[[:blank:]]*)'||util.aliased_col_re()||'\.\*'|| --2nd table, w/ separator before
'(?:\1'||util.aliased_col_re()||'\.\*)*', -- later tables, w/ same separator
'*',
'g')
END
$_$;


--
-- Name: view_is_automatically_updatable(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION view_is_automatically_updatable(view_def text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.view_body(view_def)
!~ '\y(?:JOIN|WITH|DISTINCT|GROUP BY|HAVING|LIMIT|OFFSET)\y'
	/* from http://www.postgresql.org/docs/9.3/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS */
$$;


--
-- Name: view_is_subset_or_renaming(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION view_is_subset_or_renaming(view_def text) RETURNS boolean
    LANGUAGE sql IMMUTABLE
    AS $$
SELECT util.view_is_automatically_updatable(view_def)
AND util.view_body(view_def) !~ '\y(?:CASE|WHERE)\y|\([^-]|[^-]\)'
	-- "(-x-)" in col name OK
$$;


--
-- Name: words(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION words(str text) RETURNS SETOF text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT * FROM regexp_split_to_table($1, '\y')
$_$;


--
-- 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: -
--

COMMENT ON AGGREGATE all_same(anyelement) IS '
includes NULLs in comparison
';


--
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
--

CREATE AGGREGATE join_strs(text, text) (
    SFUNC = join_strs_transform,
    STYPE = text
);


--
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR %== (
    PROCEDURE = keys_eq,
    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
';


--
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR -> (
    PROCEDURE = map_get,
    LEFTARG = regclass,
    RIGHTARG = text
);


--
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR => (
    PROCEDURE = hstore,
    LEFTARG = text[],
    RIGHTARG = text
);


--
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON OPERATOR => (text[], text) IS '
usage: array[''key1'', ...]::text[] => ''value''
';


--
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ?*>= (
    PROCEDURE = is_populated_more_often_than,
    LEFTARG = anyelement,
    RIGHTARG = anyelement
);


--
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ?>= (
    PROCEDURE = is_more_complete_than,
    LEFTARG = anyelement,
    RIGHTARG = anyelement
);


--
-- Name: @; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR @ (
    PROCEDURE = contained_within__no_dateline,
    LEFTARG = postgis.geometry,
    RIGHTARG = postgis.geometry
);


--
-- Name: @; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR @ (
    PROCEDURE = contained_within__no_dateline,
    LEFTARG = geocoord,
    RIGHTARG = postgis.geometry
);


--
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ||% (
    PROCEDURE = concat_esc,
    LEFTARG = text,
    RIGHTARG = text
);


--
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON OPERATOR ||% (text, text) IS '
% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
';


--
-- Name: ~; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ~ (
    PROCEDURE = range,
    LEFTARG = numeric,
    RIGHTARG = numeric
);


--
-- Name: ~@; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ~@ (
    PROCEDURE = contained_within_approx,
    LEFTARG = postgis.geography,
    RIGHTARG = postgis.geography
);


--
-- Name: OPERATOR ~@ (postgis.geography, postgis.geography); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON OPERATOR ~@ (postgis.geography, postgis.geography) IS '
can''t use && because it only compares 2D bounding boxes (which are geometry
objects that do not support geocoordinate wraparound)
';


--
-- Name: ~@; Type: OPERATOR; Schema: util; Owner: -
--

CREATE OPERATOR ~@ (
    PROCEDURE = contained_within_approx,
    LEFTARG = geocoord,
    RIGHTARG = postgis.geography
);


SET search_path = pg_catalog;

--
-- Name: CAST (util.check_constraint_def AS util.derived_col_def); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.check_constraint_def AS util.derived_col_def) WITH FUNCTION util.derived_col_def(util.check_constraint_def);


--
-- Name: CAST (util.col AS util.derived_col_def); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.col AS util.derived_col_def) WITH FUNCTION util.derived_col_def(util.col) AS IMPLICIT;


--
-- Name: CAST (util.col AS util.table_item); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.col AS util.table_item) WITH INOUT AS IMPLICIT;


--
-- Name: CAST (util.range AS numrange); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.range AS numrange) WITH FUNCTION util.numrange(util.range) AS IMPLICIT;


--
-- Name: CAST (util.table_item AS util.check_constraint_def); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.table_item AS util.check_constraint_def) WITH FUNCTION util.check_constraint_def(util.table_item) AS IMPLICIT;


--
-- Name: CAST (util.table_item AS util.col); Type: CAST; Schema: pg_catalog; Owner: -
--

CREATE CAST (util.table_item AS util.col) WITH INOUT;


SET search_path = util, pg_catalog;

--
-- Name: check_constraint; Type: VIEW; Schema: util; Owner: -
--

CREATE VIEW check_constraint AS
 SELECT s.table_,
    s.name,
    s.def,
    ROW(ROW((s.table_)::regclass, (s.name)::text), s.def)::check_constraint_def AS constraint_
   FROM ( SELECT pg_constraint.conrelid AS table_,
            pg_constraint.conname AS name,
            trim_parens(pg_get_expr(pg_constraint.conbin, pg_constraint.conrelid, pretty_print := false)) AS def
           FROM pg_constraint
          WHERE (pg_constraint.contype = 'c'::"char")) s;


--
-- Name: VIEW check_constraint; Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON VIEW check_constraint IS '
conrelid: don''t cast to regclass so can use index scan

def:
can''t use consrc because this doesn''t get auto-updated with column renames.
must use our wrapper so that the right search_path is used.
*don''t* use pretty-print, because this format does not match util.canon_sql().
need to remove the enclosing () to be consistent everywhere.
';


--
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace: 
--

CREATE TABLE map (
    "from" text NOT NULL,
    "to" text,
    filter text,
    notes text
);


--
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
--



--
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
--



--
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
--

ALTER TABLE ONLY map
    ADD CONSTRAINT map__unique__from UNIQUE ("from");


--
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace: 
--

ALTER TABLE ONLY map
    ADD CONSTRAINT map__unique__to UNIQUE ("to");


--
-- 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();


--
-- Name: util; Type: ACL; Schema: -; Owner: -
--

REVOKE ALL ON SCHEMA util FROM PUBLIC;
REVOKE ALL ON SCHEMA util FROM bien;
GRANT ALL ON SCHEMA util TO bien;
GRANT USAGE ON SCHEMA util TO bien_read;
GRANT USAGE ON SCHEMA util TO public_;


--
-- PostgreSQL database dump complete
--