--

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

--

CREATE TYPE range AS (

	lower numeric,

	upper numeric,

	bounds text

);

--

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

--

COMMENT ON TYPE range IS '

allows wraparound ranges (which use a modulus system such as geocoordinates)

';

--

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

--

CREATE TYPE restore_views_info AS (

	views db_item[]

);

--

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

--

CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

$_$;

--

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

--

CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT bool_and(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($3)

    , ($4)

    , ($5)

)

AS v (value)

$_$;

--

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

--

COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.

';

--

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

--

CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT avg(value)

FROM

(VALUES

      ($1)

    , ($2)

    , ($3)

    , ($4)

    , ($5)

)

AS v (value)

$_$;

--

-- Name: _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: bounding_box(range, range); Type: FUNCTION; Schema: util; Owner: -

--

CREATE FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geography

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT util.bounding_box__no_dateline($1, $2)::postgis.geography

$_$;

--

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

--

COMMENT ON FUNCTION bounding_box(latitude_range_deg range, longitude_range_deg range) IS '

usage:

SET search_path = util; -- for ~ operator

SELECT util.bounding_box(lower_lat ~ upper_lat, lower_long ~ upper_long);

**WARNING**: the geography type stores all edges as arcs of great circles,

resulting in the latitude lines bulging outward from the true bounding box.

this will create false positives above and below the bounding box.

';

--

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

--

CREATE FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) RETURNS postgis.geometry

    LANGUAGE sql IMMUTABLE

    AS $_$

/* don't use st_makebox2d() because it doesn't support geocoordinate wraparound

(it is not SRID-aware) */

SELECT postgis.st_makeenvelope(

  /*xmin=*/$2.lower, /*ymin=*/$1.lower

, /*xmax=*/$2.upper, /*ymax=*/$1.upper

, /*WGS84*/4326

)

$_$;

--

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

--

COMMENT ON FUNCTION bounding_box__no_dateline(latitude_range_deg range, longitude_range_deg range) IS '

the generated bounding box is more accurate than util.bounding_box() (latitude

lines will be straight), but geocoordinate wraparound is not supported

usage:

SET search_path = util; -- for ~ operator

SELECT util.bounding_box__no_dateline(lower_lat ~ upper_lat, lower_long ~ upper_long);

';

--

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