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


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

CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
    LANGUAGE sql STABLE STRICT
    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: 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 STRICT
    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: 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: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
    PERFORM util.eval(sql);
EXCEPTION
    WHEN duplicate_table  THEN NULL;
    WHEN duplicate_object THEN NULL; -- e.g. constraint
    WHEN duplicate_column THEN NULL;
    WHEN invalid_table_definition THEN
        IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
        ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
        END IF;
END;
$$;


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

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


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


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

CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
    LANGUAGE sql STABLE STRICT
    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_cols(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION diff_cols(left_query text, right_query text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
    LANGUAGE sql STABLE
    AS $_$
SELECT * FROM
util.eval2col_pair($$
SELECT * FROM
(
$$||$1||$$
) left_ (left_)
FULL JOIN
(
$$||$2||$$
) right_ (right_)
ON left_ = right_
WHERE left_ IS DISTINCT FROM right_
ORDER BY left_, right_
$$, $3)
$_$;


--
-- Name: FUNCTION diff_cols(left_query text, right_query text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION diff_cols(left_query text, right_query text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
col_type_null (*required*): NULL::col_type
usage:
SELECT * FROM util.diff_cols($$VALUES (''1''), (''2''), (''4'')$$, $$VALUES (''1''), (''3''), (''4'')$$, NULL::text)

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

CREATE FUNCTION diff_views(left_view regclass, right_view regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
    LANGUAGE sql STABLE
    AS $_$
SELECT * FROM util.diff_cols($$SELECT * FROM $$||$1, $$SELECT * FROM $$||$1, $3)
$_$;


--
-- Name: FUNCTION diff_views(left_view regclass, right_view regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION diff_views(left_view regclass, right_view regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
col_type_null (*required*): NULL::col_type
usage:
SELECT * FROM util.diff_views(''"left_view"'', ''"right_view"'', NULL::text)
';


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

CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
    LANGUAGE sql STRICT
    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); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION drop_column(col col_ref) RETURNS void
    LANGUAGE sql STRICT
    AS $_$
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
quote_ident($1.name))
$_$;


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

COMMENT ON FUNCTION drop_column(col col_ref) IS '
idempotent
';


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

CREATE FUNCTION drop_table(table_ text) RETURNS void
    LANGUAGE sql STRICT
    AS $_$
SELECT util.eval($$DROP TABLE IF EXISTS $$||$1)
$_$;


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

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


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

CREATE FUNCTION drop_view(view_ text) RETURNS void
    LANGUAGE sql STRICT
    AS $_$
SELECT util.eval($$DROP VIEW IF EXISTS $$||$1)
$_$;


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

COMMENT ON FUNCTION drop_view(view_ text) 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(sql text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
BEGIN
	PERFORM util.debug_print_sql(sql);
	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: eval2set(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
--

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


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

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


--
-- 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
    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 STRICT
    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
    AS $_$
SELECT util.eval2set($$EXPLAIN $$||$1)
$_$;


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

CREATE FUNCTION explain2notice(sql text) RETURNS void
    LANGUAGE plpgsql
    AS $_$
BEGIN
	RAISE NOTICE '%', $$EXPLAIN:
$$||util.explain2str(sql);
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: 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: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
    LANGUAGE plpgsql
    AS $_$
DECLARE
	PG_EXCEPTION_DETAIL text;
	recreate_users_cmd text = util.save_drop_views(users);
BEGIN
	PERFORM util.eval(cmd);
	PERFORM util.eval(recreate_users_cmd);
EXCEPTION
WHEN dependent_objects_still_exist THEN
	IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
	GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
	users = array(SELECT * FROM util.regexp_matches_group(
		PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on table .*$'));
	IF util.is_empty(users) THEN RAISE; END IF;
	PERFORM util.force_recreate(cmd, users);
END;
$_$;


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

COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
idempotent

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


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

CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
    LANGUAGE sql STABLE STRICT
    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_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: 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: 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: 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
$$||$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 STRICT
    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_map_table(text); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION mk_map_table(table_ text) RETURNS void
    LANGUAGE sql STRICT
    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_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)
$_$;


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

CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
    LANGUAGE sql STRICT
    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: 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 STRICT
    AS $_$
SELECT typname::text FROM pg_type WHERE oid = $1
$_$;


--
-- 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: 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
    AS $_$
SELECT util.type_qual_name($1::text::regtype)
$_$;


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

CREATE FUNCTION raise_notice(msg text) RETURNS void
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
BEGIN
	RAISE NOTICE '%', msg;
END;
$$;


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

CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
    LANGUAGE sql STRICT
    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 STRICT
    AS $_$
SELECT util.drop_table($1);
SELECT util.mk_map_table($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_);
		PERFORM util.eval($$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 text
    LANGUAGE sql
    AS $_$
SELECT concat(util.save_drop_view(unnest)) FROM unnest($1)
$_$;


--
-- 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_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 STRICT
    AS $_$
SELECT util.eval(
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
$_$;


--
-- 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 USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; -- rethrow
			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 plpgsql STRICT
    AS $_$
DECLARE
    sql text = $$ALTER TABLE $$||table_||$$
$$||NULLIF(array_to_string(ARRAY(
    SELECT
    $$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
    ||$$ USING $$||col_name_sql||$$::$$||target_type
    FROM
    (
        SELECT
          quote_ident(col_name) AS col_name_sql
        , util.col_type((table_, col_name)) AS curr_type
        , type AS target_type
        FROM unnest(col_casts)
    ) s
    WHERE curr_type != target_type
), '
, '), '');
BEGIN
    PERFORM util.debug_print_sql(sql);
    EXECUTE COALESCE(sql, '');
END;
$_$;


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

CREATE FUNCTION show_create_view(view_ regclass) RETURNS text
    LANGUAGE sql STABLE
    AS $_$
SELECT $$CREATE OR REPLACE VIEW $$||$1||$$ AS $$||pg_get_viewdef($1)||$$;
$$||util.show_grants_for($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 concat(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: 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 STRICT
    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 STRICT
    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 STRICT
    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 STRICT
    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_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); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION "trim"(table_ regclass, names regclass) RETURNS void
    LANGUAGE sql STRICT
    AS $_$
SELECT util.drop_column(($1, col)) 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); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '
trims table_ to include only columns in the original data.
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: 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 wrong_object_type THEN NULL; -- trying to alter a view's columns
    WHEN undefined_column THEN NULL;
    WHEN duplicate_column THEN NULL;
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 STRICT
    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: type_qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
--

CREATE FUNCTION type_qual_name(type regtype) RETURNS text
    LANGUAGE sql STABLE STRICT
    SET search_path TO pg_temp
    AS $_$
SELECT $1::text
$_$;


--
-- Name: FUNCTION type_qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
--

COMMENT ON FUNCTION type_qual_name(type regtype) IS '
a type''s schema-qualified name
';


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