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

root/schemas/functions.sql @ 5688

-            aaronmk
+--
 -- PostgreSQL database dump
 --
 SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
 SET standard_conforming_strings = off;
 SET check_function_bodies = false;
 SET client_min_messages = warning;
 SET escape_string_warning = off;
 --
 -- Name: functions; Type: SCHEMA; Schema: -; Owner: -
 --
 CREATE SCHEMA functions;
-            aaronmk
+--
 -- Name: SCHEMA functions; Type: COMMENT; Schema: -; Owner: -
 --
 COMMENT ON SCHEMA functions 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
+SET search_path = functions, pg_catalog;
-            aaronmk
+--
-            aaronmk
+-- Name: datatype; Type: TYPE; Schema: functions; Owner: -
 --
 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: functions; 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: _cm_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _cm_to_m(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1/100.
 $_$;
 --
 -- Name: _cm_to_m(text); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _cm_to_m(value text) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT functions._cm_to_m($1::double precision)
 $_$;
 --
-            aaronmk
+-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT $1 = $2
 $_$;
 --
-            aaronmk
+-- Name: _fraction_to_percent(double precision); Type: FUNCTION; Schema: functions; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _fraction_to_percent(value double precision) RETURNS double precision
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1*100.
 $_$;
 --
-            aaronmk
+-- Name: _ft_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _ft_to_m(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT functions._in_to_m($1*12)
 $_$;
 --
 -- Name: _ft_to_m(text); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _ft_to_m(value text) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT functions._ft_to_m($1::double precision)
 $_$;
 --
-            aaronmk
+-- Name: _ha_to_m2(double precision); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _ha_to_m2(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1*10000.
 $_$;
 --
-            aaronmk
+-- Name: _ha_to_m2(text); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _ha_to_m2(value text) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT functions._ha_to_m2($1::double precision)
 $_$;
 --
-            aaronmk
+-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: functions; 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: functions; 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 functions._if($1 != '', $2, $3)
 $_$;
 --
-            aaronmk
+-- Name: _in_to_m(double precision); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _in_to_m(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1*2.54/100.
 $_$;
 --
 -- Name: _in_to_m(text); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _in_to_m(value text) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT functions._in_to_m($1::double precision)
 $_$;
 --
-            aaronmk
+-- Name: _join(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
 --
 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(array_to_string(ARRAY[$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: functions; Owner: -
 --
 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(array_to_string(ARRAY[$1, $2, $3, $4, $5, $6, $7, $8, $9, $10], ' '), '')
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: _label(anyelement, anyelement); Type: FUNCTION; Schema: functions; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _label(label anyelement, value anyelement) RETURNS anyelement
-            aaronmk
+    LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT coalesce($1 || ': ', '') || $2
 $_$;
             aaronmk
 --
-            aaronmk
+-- Name: _m2_to_ha(double precision); Type: FUNCTION; Schema: functions; Owner: -
 --
 CREATE FUNCTION _m2_to_ha(value double precision) RETURNS double precision
     LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1/10000.
 $_$;
 --
-            aaronmk
+-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; 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 max(value)
 FROM
 (VALUES
       ($1)
     , ($2)
     , ($3)
     , ($4)
     , ($5)
     , ($6)
     , ($7)
     , ($8)
     , ($9)
     , ($10)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; 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 functions.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: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: functions; 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 min(value)
 FROM
 (VALUES
       ($1)
     , ($2)
     , ($3)
     , ($4)
     , ($5)
     , ($6)
     , ($7)
     , ($8)
     , ($9)
     , ($10)
+)
 AS v (value)
 $_$;
 --
-            aaronmk
+-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: functions; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
-            aaronmk
+    LANGUAGE plpgsql IMMUTABLE
     AS $$
 DECLARE
     "null" text NOT NULL := "null"; -- add NOT NULL
     type functions.datatype NOT NULL := type; -- add NOT NULL
 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: functions; Owner: -
 --
 CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
     LANGUAGE sql IMMUTABLE
     AS $_$
 SELECT functions."_nullIf"($1, $2, $3::functions.datatype)
 $_$;
 --
-            aaronmk
+-- Name: _percent_to_fraction(double precision); Type: FUNCTION; Schema: functions; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _percent_to_fraction(value double precision) RETURNS double precision
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
     AS $_$
 SELECT $1/100.
 $_$;
 --
-            aaronmk
+-- Name: _percent_to_fraction(text); Type: FUNCTION; Schema: functions; Owner: -
-            aaronmk
+--
-            aaronmk
+CREATE FUNCTION _percent_to_fraction(value text) RETURNS double precision
-            aaronmk
+    LANGUAGE sql IMMUTABLE STRICT
     AS $_$
-            aaronmk
+SELECT functions._percent_to_fraction($1::double precision)
-            aaronmk
+$_$;
 --
-            aaronmk
+-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: functions; 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
+$_$;
 --
 -- Name: join_strs(text, text); Type: AGGREGATE; Schema: functions; Owner: -
 --
 CREATE AGGREGATE join_strs(text, text) (
-            aaronmk
+    SFUNC = join_strs_transform,
-            aaronmk
+    STYPE = text
-            aaronmk
+);
-            aaronmk
+--
-            aaronmk
+-- PostgreSQL database dump complete
 --

Project

General

Profile