/schemas/util.sql - Annotate - BIEN 3 - NCEAS Projects

root/schemas/util.sql @ 8328

-            aaronmk
+--
 -- PostgreSQL database dump
 --
 SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
-            aaronmk
+SET standard_conforming_strings = on;
-            aaronmk
+SET check_function_bodies = false;
 SET client_min_messages = warning;
 --
-            aaronmk
+-- Name: util; Type: SCHEMA; Schema: -; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE SCHEMA util;
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
-            aaronmk
+--
-            aaronmk
+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.';
             aaronmk
-            aaronmk
+SET search_path = util, pg_catalog;
             aaronmk
-            aaronmk
+--
-            aaronmk
+-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE col_cast AS (
 	col_name text,
 	type regtype
 );
 --
-            aaronmk
+-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE col_ref AS (
 	table_ regclass,
 	name text
 );
 --
-            aaronmk
+-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE compass_dir AS ENUM (
     'N',
     'E',
     'S',
     'W'
 );
 --
-            aaronmk
+-- Name: datatype; Type: TYPE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE TYPE datatype AS ENUM (
     'str',
     'float'
 );
 --
-            aaronmk
+-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+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
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+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
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT bool_and(value)
 FROM
 (VALUES
       ($1)
     , ($2)
-            aaronmk
+    , ($3)
     , ($4)
     , ($5)
             aaronmk
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+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.';
             aaronmk
 --
-            aaronmk
+-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
 $_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
-            aaronmk
+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)
-            aaronmk
+FROM
+(
     SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
     UNION ALL
-            aaronmk
+    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
             aaronmk
 matches (g)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+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
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
-            aaronmk
+FROM
 (VALUES
-            aaronmk
+      ($1)
-            aaronmk
+    , ($2/60)
     , ($3/60/60)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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 $_$
-            aaronmk
+SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 = $2
 $_$;
 --
-            aaronmk
+-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
 $_$;
 --
-            aaronmk
+-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
 $_$;
 --
-            aaronmk
+-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util._if($1 != '', $2, $3)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _join("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 $_$
-            aaronmk
+SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _join_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _join_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 $_$
-            aaronmk
+SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT coalesce($1 || ': ', '') || $2
 $_$;
             aaronmk
 --
-            aaronmk
+-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _map(map hstore, value text) RETURNS text
-            aaronmk
+    LANGUAGE plpgsql IMMUTABLE STRICT
-            aaronmk
+    AS $$
 DECLARE
-            aaronmk
+    match text := map -> value;
-            aaronmk
+BEGIN
-            aaronmk
+    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
-            aaronmk
+    IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
     ELSIF match = '*' THEN RETURN value;
     ELSE RETURN match;
-            aaronmk
+    END IF;
 END;
 $$;
 --
-            aaronmk
+-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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 $_$
-            aaronmk
+SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+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
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util.join_strs(value, '; ')
-            aaronmk
+FROM
+(
     SELECT *
     FROM
+    (
         SELECT
         DISTINCT ON (value)
+        *
         FROM
         (VALUES
-            aaronmk
+              (1, $1)
             , (2, $2)
             , (3, $3)
             , (4, $4)
             , (5, $5)
             , (6, $6)
             , (7, $7)
             , (8, $8)
             , (9, $9)
             , (10, $10)
             aaronmk
         AS v (sort_order, value)
-            aaronmk
+        WHERE value IS NOT NULL
             aaronmk
     AS v
     ORDER BY sort_order
+)
 AS v
 $_$;
 --
-            aaronmk
+-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
 $_$;
 --
-            aaronmk
+-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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 $_$
-            aaronmk
+SELECT util.join_strs(value, ' ')
-            aaronmk
+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
 $_$;
 --
-            aaronmk
+-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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 $_$
-            aaronmk
+SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _not(value boolean) RETURNS boolean
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT NOT $1
 $_$;
 --
-            aaronmk
+-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _now() RETURNS timestamp with time zone
     LANGUAGE sql STABLE
     AS $$
 SELECT now()
 $$;
 --
-            aaronmk
+-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
-            aaronmk
+    LANGUAGE plpgsql IMMUTABLE
     AS $$
 DECLARE
-            aaronmk
+    type util.datatype NOT NULL := type; -- add NOT NULL
-            aaronmk
+BEGIN
-            aaronmk
+    IF type = 'str' THEN RETURN nullif(value::text, "null");
-            aaronmk
+    -- Invalid value is ignored, but invalid null value generates error
-            aaronmk
+    ELSIF type = 'float' THEN
-            aaronmk
+        DECLARE
             -- Outside the try block so that invalid null value generates error
-            aaronmk
+            "null" double precision := "null"::double precision;
-            aaronmk
+        BEGIN
-            aaronmk
+            RETURN nullif(value::double precision, "null");
-            aaronmk
+        EXCEPTION
-            aaronmk
+            WHEN data_exception THEN RETURN value; -- ignore invalid value
-            aaronmk
+        END;
-            aaronmk
+    END IF;
 END;
 $$;
 --
-            aaronmk
+-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
-            aaronmk
+SELECT util."_nullIf"($1, $2, $3::util.datatype)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 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.';
 --
-            aaronmk
+-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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
 $_$;
 --
-            aaronmk
+-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT regexp_split_to_table($1, $2)
 $_$;
 --
-            aaronmk
+-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
     LANGUAGE sql STABLE STRICT
     AS $_$
 SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
 $_$;
 --
-            aaronmk
+-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 BEGIN
     -- not yet clustered (ARRAY[] compares NULLs literally)
-            aaronmk
+    IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
-            aaronmk
+        EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
     END IF;
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS 'idempotent';
 --
-            aaronmk
+-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
-            aaronmk
+    PERFORM util.col_type(col);
-            aaronmk
+    RETURN true;
 EXCEPTION
     WHEN undefined_column THEN RETURN false;
 END;
 $$;
 --
-            aaronmk
+-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
-            aaronmk
+    AS $$
 DECLARE
-            aaronmk
+    prefix text := util.name(type)||'.';
-            aaronmk
+BEGIN
     RETURN QUERY
-            aaronmk
+        SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
         FROM util.col_names(type) f (name_);
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
-            aaronmk
+    AS $_$
 BEGIN
     RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
 END;
 $_$;
 --
-            aaronmk
+-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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
 ORDER BY attnum
 $_$;
 --
-            aaronmk
+-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION col_type(col col_ref) RETURNS regtype
-            aaronmk
+    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
-            aaronmk
+    WHEN no_data_found THEN
-            aaronmk
+        RAISE undefined_column USING MESSAGE =
             concat('undefined column: ', col.name);
-            aaronmk
+END;
 $$;
             aaronmk
 --
-            aaronmk
+-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT position($1 in $2) > 0 /*1-based offset*/
 $_$;
 --
-            aaronmk
+-- Name: create_if_not_exists(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION create_if_not_exists(sql text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
     EXECUTE sql;
 EXCEPTION
-            aaronmk
+    WHEN duplicate_table  THEN NULL;
     WHEN duplicate_column THEN NULL;
-            aaronmk
+END;
 $$;
 --
-            aaronmk
+-- Name: FUNCTION create_if_not_exists(sql text); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION create_if_not_exists(sql text) IS 'idempotent';
 --
-            aaronmk
+-- 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';
 --
-            aaronmk
+-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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)
-            aaronmk
+WHERE util.col_exists(($1, col_name))
-            aaronmk
+$_$;
 --
-            aaronmk
+-- 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
-            aaronmk
+	IF SQLERRM = 'cannot drop columns from view'
 	OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
 	THEN
-            aaronmk
+		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';
 --
-            aaronmk
+-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT substring($2 for length($1)) = $1
 $_$;
 --
-            aaronmk
+-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT $1 || $3 || $2
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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$$
-            aaronmk
+        INTO value USING key;
-            aaronmk
+    RETURN value;
 END;
 $_$;
 --
-            aaronmk
+-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
     LANGUAGE plpgsql STABLE STRICT
     AS $_$
 BEGIN
     RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
 END;
 $_$;
 --
-            aaronmk
+-- 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))
 $_$;
 --
 -- 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';
 --
-            aaronmk
+-- Name: mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION mk_derived_col(col col_ref, expr text) 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($$
 ALTER TABLE $$||col.table_||$$ ADD   COLUMN $$||col_name_sql||$$      $$||type||$$;
 ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
 $$||expr||$$;
 $$);
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
 --
 COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text) IS 'idempotent';
 --
-            aaronmk
+-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION mk_map_table(table_ text) RETURNS void
-            aaronmk
+    LANGUAGE sql STRICT
-            aaronmk
+    AS $_$
-            aaronmk
+SELECT util.create_if_not_exists($$
-            aaronmk
+CREATE TABLE $$||$1||$$
             aaronmk
-            aaronmk
+    LIKE util.map INCLUDING ALL
             aaronmk
-            aaronmk
+$$)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- 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 $_$
 BEGIN
 	EXECUTE $$
 CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
   RETURNS SETOF $$||view_||$$ AS
 $BODY1$
 SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$
 WHERE $$||quote_ident(row_num_col)||$$ BETWEEN COALESCE($2, 0) AND COALESCE($2+$1, 2147483647)
 $BODY1$
   LANGUAGE sql STABLE
   COST 100
   ROWS 1000
 $$;
-            aaronmk
+-- Also create subset function which turns off enable_sort
 	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$
-            aaronmk
+SELECT * FROM $$||util.type_qual_name(view_::text::regtype)||$$($2, $3)
-            aaronmk
+$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.)
 ';
 $$;
-            aaronmk
+END;
 $_$;
 --
-            aaronmk
+-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION name(type regtype) RETURNS text
-            aaronmk
+    LANGUAGE sql STABLE STRICT
-            aaronmk
+    AS $_$
 SELECT typname::text FROM pg_type WHERE oid = $1
 $_$;
 --
-            aaronmk
+-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
-            aaronmk
+    LANGUAGE sql STRICT
     AS $_$
-            aaronmk
+SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
-            aaronmk
+||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
-            aaronmk
+FROM util.col_names($1::text::regtype) f (name)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
-            aaronmk
+COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS 'idempotent';
             aaronmk
 --
-            aaronmk
+-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION reset_map_table(table_ text) RETURNS void
     LANGUAGE sql STRICT
     AS $_$
-            aaronmk
+SELECT util.mk_map_table($1);
 SELECT util.truncate($1);
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
-            aaronmk
+    old text[] = ARRAY(SELECT util.col_names(table_));
     new text[] = ARRAY(SELECT util.map_values(names));
-            aaronmk
+BEGIN
     old = old[1:array_length(new, 1)]; -- truncate to same length
-            aaronmk
+    PERFORM util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
-            aaronmk
+        ||quote_ident(key)||$$ TO $$||quote_ident(value))
     FROM each(hstore(old, new));
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS 'idempotent';
 --
-            aaronmk
+-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 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
-            aaronmk
+        , util.col_type((table_, col_name)) AS curr_type
-            aaronmk
+        , type AS target_type
         FROM unnest(col_casts)
     ) s
     WHERE curr_type != target_type
 ), '
 , '), '');
 BEGIN
     RAISE NOTICE '%', sql;
     EXECUTE COALESCE(sql, '');
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS 'idempotent';
 --
-            aaronmk
+-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
-            aaronmk
+    LANGUAGE plpgsql STABLE STRICT
-            aaronmk
+    AS $_$
 DECLARE
     hstore hstore;
 BEGIN
     EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
         table_||$$))$$ INTO STRICT hstore;
     RETURN hstore;
 END;
 $_$;
 --
-            aaronmk
+-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 DECLARE
     row record;
 BEGIN
-            aaronmk
+    FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
-            aaronmk
+    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;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS 'idempotent';
 --
-            aaronmk
+-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
-            aaronmk
+--
 CREATE FUNCTION truncate(table_ regclass) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $_$
 BEGIN
     EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
 END;
 $_$;
 --
-            aaronmk
+-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
-            aaronmk
+--
 COMMENT ON FUNCTION truncate(table_ regclass) IS 'idempotent';
 --
-            aaronmk
+-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION try_create(sql text) RETURNS void
     LANGUAGE plpgsql STRICT
     AS $$
 BEGIN
     EXECUTE sql;
 EXCEPTION
     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';
 --
-            aaronmk
+-- 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';
 --
-            aaronmk
+-- 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
 $_$;
 --
-            aaronmk
+-- 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
 $_$;
 --
-            aaronmk
+-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
 --
 CREATE FUNCTION typeof(expr text, table_ regtype) RETURNS regtype
     LANGUAGE plpgsql STABLE STRICT
     AS $_$
 DECLARE
     type regtype;
 BEGIN
     EXECUTE $$SELECT pg_typeof($$||expr||$$) FROM (SELECT (NULL::$$||table_||
     $$).*) _s$$ INTO STRICT type;
     RETURN type;
 END;
 $_$;
 --
-            aaronmk
+-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
-            aaronmk
+--
 CREATE AGGREGATE join_strs(text, text) (
-            aaronmk
+    SFUNC = join_strs_transform,
-            aaronmk
+    STYPE = text
-            aaronmk
+);
-            aaronmk
+--
-            aaronmk
+-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
-            aaronmk
+--
 CREATE OPERATOR -> (
     PROCEDURE = map_get,
     LEFTARG = regclass,
     RIGHTARG = text
 );
-            aaronmk
+SET default_tablespace = '';
 SET default_with_oids = false;
-            aaronmk
+--
-            aaronmk
+-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
-            aaronmk
+--
 CREATE TABLE map (
     "from" text NOT NULL,
-            aaronmk
+    "to" text,
     filter text,
     notes text
-            aaronmk
+);
 --
-            aaronmk
+-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
-            aaronmk
+--
 --
-            aaronmk
+-- Name: map_pkey; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
-            aaronmk
+--
 ALTER TABLE ONLY map
     ADD CONSTRAINT map_pkey PRIMARY KEY ("from");
 --
-            aaronmk
+-- PostgreSQL database dump complete
 --

Project

General

Profile