There are some custom functions in Central Biotics that are likely useful to many member programs. When we have capacity,

we hope to add them as standard functions, which means you'll receive them (and any updates) with Biotics releases. In the meantime, you can run the scripts provided below to create them in your own database. (We will add to this list as folks request functions or we think of new ones to add.)


The scripts need to be run in pgAdmin or another third-party database tool using a Power User database account. The "biotics_ext_admin_role" needs to be used, so each script begins with assigning that role and then removes the role at the end.


Information about each function is included in the script within /* */ after $BODY$.


Note: In general, custom object scripts should be recorded in your repository. But these could be an exception since they'll eventually be maintained only Centrally. If you do store these in your repository, don't include the "set role" statements at beginning and end.


TABLE OF CONTENTS


listDistGrid: Distribution Grid summary for a species EST or ENT.

set role biotics_ext_admin_role;  --switch to role for creating/editing objects


CREATE OR REPLACE FUNCTION ext.listdistgrid(

    id bigint,

    level character DEFAULT 'S'::bpchar,

    delim text DEFAULT ' xx '::text)

    RETURNS text

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE PARALLEL UNSAFE

AS $BODY$


/*

List summary of Distribution Grid for a species EST or ENT (EST is default):

    Origin, Regularity, Distribution Confidence, Current Presence/Absence, Population

Enter the EST or ENT ID as first parameter

          'S' (default) or 'N' for Subnational or National as 2nd parameter

      and a delimiter (up to 6 characters including spaces, default=' xx ') as 3rd parameter.

-Examples: SELECT element_subnational_id, ListDistGrid(element_subnational_id) as subnatl_dist_data FROM element_subnational WHERE rownum < 21

           SELECT element_national_id, ListDistGrid(element_national_id,'N',' | ') as natl_dist_data FROM element_national WHERE rownum < 21

*/


DECLARE

    ThisList TEXT;

BEGIN

    IF id IS NULL THEN

        RETURN NULL;

    END IF;


    IF UPPER(level) = 'N' THEN

        SELECT string_agg(dist_item, delim ORDER BY dist_item)

        INTO ThisList

        FROM (

            SELECT

                substr(orig.origin_desc, 1, 1) || '. ' ||

                substr(reg.regularity_desc, 1, 1) || '. ' ||

                substr(conf.dist_confidence_cd, 1, 3) || '. ' ||

                substr(pres.curr_presence_absence_desc, 1, 1) ||

                CASE WHEN dist.d_population_id IS NOT NULL THEN '. ' || substr(pop.population_desc, 1, 1) ELSE '' END

                AS dist_item

            FROM core.taxon_natl_dist dist

            LEFT JOIN domain.d_curr_presence_absence pres ON dist.d_curr_presence_absence_id = pres.d_curr_presence_absence_id

            LEFT JOIN domain.d_dist_confidence conf ON dist.d_dist_confidence_id = conf.d_dist_confidence_id

            LEFT JOIN domain.d_origin orig ON dist.d_origin_id = orig.d_origin_id

            LEFT JOIN domain.d_population pop ON dist.d_population_id = pop.d_population_id

            LEFT JOIN domain.d_regularity reg ON dist.d_regularity_id = reg.d_regularity_id

            WHERE dist.element_national_id = id

        ) sub;

    ELSE

        SELECT string_agg(dist_item, delim ORDER BY dist_item)

        INTO ThisList

        FROM (

            SELECT

                substr(orig.origin_desc, 1, 1) || '. ' ||

                substr(reg.regularity_desc, 1, 1) || '. ' ||

                substr(conf.dist_confidence_cd, 1, 3) || '. ' ||

                substr(pres.curr_presence_absence_desc, 1, 1) ||

                CASE WHEN dist.d_population_id IS NOT NULL THEN '. ' || substr(pop.population_desc, 1, 1) ELSE '' END

                AS dist_item

            FROM core.taxon_subnatl_dist dist

            LEFT JOIN domain.d_curr_presence_absence pres ON dist.d_curr_presence_absence_id = pres.d_curr_presence_absence_id

            LEFT JOIN domain.d_dist_confidence conf ON dist.d_dist_confidence_id = conf.d_dist_confidence_id

            LEFT JOIN domain.d_origin orig ON dist.d_origin_id = orig.d_origin_id

            LEFT JOIN domain.d_population pop ON dist.d_population_id = pop.d_population_id

            LEFT JOIN domain.d_regularity reg ON dist.d_regularity_id = reg.d_regularity_id

            WHERE dist.element_subnational_id = id

        ) sub;

    END IF;


    RETURN ThisList;

END;

$BODY$;


ALTER FUNCTION ext.listdistgrid(bigint, character, text)

    OWNER TO biotics_ext_admin_role;


set role none;                    --revert back to your default role