--

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

/* 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

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

';

--

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

--

CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement

    LANGUAGE plpgsql IMMUTABLE

    AS $$

/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the

return value, causing a type mismatch */

BEGIN

	-- will then be assignment-cast to return type via INOUT

	RETURN value::cstring;

END;

$$;

--

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

--

COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '

allows casting to an arbitrary type without eval()

usage:

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

note that there does *not* need to be a cast from text to the output type,

because an INOUT cast is used instead

(http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)

ret_type_null: NULL::ret_type

';

--

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

--

CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass

    LANGUAGE sql STABLE

    AS $_$

SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered

$_$;

--

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

--

CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void

    LANGUAGE plpgsql STRICT

    AS $_$

BEGIN

    -- not yet clustered (ARRAY[] compares NULLs literally)

    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN

        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;

    END IF;

END;

$_$;

--

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

--

COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '

idempotent

';

--

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

--

CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement

    LANGUAGE sql IMMUTABLE

    AS $_$

SELECT value

FROM unnest($1) value

WHERE value IS NOT NULL

LIMIT 1

$_$;

--

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

--