--
-- 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: col_cast; Type: TYPE; Schema: util; Owner: -
--

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


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

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


--
-- 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: restore_views_info; Type: TYPE; Schema: util; Owner: -
--

CREATE TYPE restore_views_info AS (
	views db_item[]
);


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

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


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

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


--
-- 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: _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: 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: 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: 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 plpgsql STRICT
    AS $_$
BEGIN
    -- not yet clustered (ARRAY[] compares NULLs literally)
    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
    END IF;
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
';


--
-- 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_ref); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION col__min(col col_ref) RETURNS integer
    LANGUAGE sql STABLE
    AS $_$
SELECT util.eval2val($$
SELECT $$||quote_ident($1.name)||$$
FROM $$||$1.table_||$$
ORDER BY $$||quote_ident($1.name)||$$ ASC
LIMIT 1
$$, NULL::integer)
$_$;


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

CREATE FUNCTION col_comment(col col_ref) RETURNS text
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
	comment text;
BEGIN
	SELECT description
	FROM pg_attribute
	LEFT JOIN pg_description ON objoid = attrelid
		AND classoid = 'pg_class'::regclass AND objsubid = attnum
	WHERE attrelid = col.table_ AND attname = col.name
	INTO STRICT comment
	;
	RETURN comment;
EXCEPTION
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
END;
$$;


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

CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
    LANGUAGE plpgsql STABLE STRICT
    AS $$
DECLARE
	default_sql text;
BEGIN
	SELECT adsrc
	FROM pg_attribute
	LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
	WHERE attrelid = col.table_ AND attname = col.name
	INTO STRICT default_sql
	;
	RETURN default_sql;
EXCEPTION
	WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
END;
$$;


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

CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
    LANGUAGE sql STABLE
    AS $_$
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
$_$;


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

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


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

CREATE FUNCTION col_exists(col col_ref) 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_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
--

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


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


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

CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.materialize_view($2, $1)
$_$;


--
-- 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: 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_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: 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_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
--

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


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

COMMENT ON FUNCTION drop_column(col col_ref, 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_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: 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 plpgsql
    AS $$
BEGIN
	IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
	EXECUTE sql;
END;
$$;


--
-- 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: 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: explain(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION explain(sql text) RETURNS SETOF text
    LANGUAGE sql
    SET client_min_messages TO 'error'
    AS $_$
/* `client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE functions in
the query, so this prevents displaying any log messages printed by them */
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
$_$;


--
-- 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 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: 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: 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: 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_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: 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_ref); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION is_constant(col col_ref) 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_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_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: 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: 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: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
$_$;


--
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
idempotent
';


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

CREATE FUNCTION mk_const_col(col col_ref, 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_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
--

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


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

CREATE FUNCTION mk_derived_col(col col_ref, 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_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION mk_derived_col(col col_ref, 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 $$||(util.regexp_match($1,
'(?m)^CREATE(?: OR REPLACE)? ((?:)??[[:upper:]]+ .*?)(?: AS(?: (?:SELECT\y.*)?)?)?$'))[1]||$$;$$
	/* (?:)?? 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) */
$_$;


--
-- 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 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_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

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


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

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


--
-- 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(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: 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: 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: 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_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(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$
SELECT util.eval($$
CREATE OR REPLACE FUNCTION pg_temp.__raise()
  RETURNS void AS
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
$__BODY1$
BEGIN
	RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
END;
$__BODY1$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
$$, verbose_ := false);

SELECT util.eval($$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 a condition name 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_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
--

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


--
-- 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(util.mk_drop_from_create(cmd));
		-- *not* CASCADE; it must trigger an exception
	
	PERFORM util.eval(cmd);
	
	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, dependent_view_changes text DEFAULT ''::text) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.recreate($$
CREATE VIEW $$||$1||$$ AS 
$$||$2||$$
;
$$||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'')||$$
$$);

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: 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_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); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
    LANGUAGE sql
    AS $_$
SELECT util.drop_table($1);
SELECT util.materialize_view($1, $2);
$_$;


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

COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
idempotent, but repeats action each time
';


--
-- 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: 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: 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(regclass, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION set_comment(table_ regclass, 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
$$||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_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
--

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


--
-- 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: 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: 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_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_types_and_data($1, 'pg_temp.__copy');

-- repopulate main table w/ copies column
SELECT util.truncate($1);
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
SELECT util.populate_table($1, $$
SELECT (table_).*, copies
FROM (
	SELECT table_, COUNT(*) AS copies
	FROM pg_temp.__copy table_
	GROUP BY table_
) s
$$);

-- delete temp table so it doesn't stay around until end of connection
SELECT util.drop_table('pg_temp.__copy');
$_$;


--
-- 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: 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_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_ref, text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION try_mk_derived_col(col col_ref, 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_ref, expr text); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION try_mk_derived_col(col col_ref, 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, regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
    LANGUAGE plpgsql STABLE
    AS $_$
DECLARE
    type regtype;
BEGIN
    EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
    RETURN type;
END;
$_$;


--
-- 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: 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 = "%==",
    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 = 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: 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();


--
-- PostgreSQL database dump complete
--