The custom DELIMLIST function was converted to PostgreSQL and can still be used, but it doesn't work for all SQL. (One fixable issue is that there must be a space before ORDER BY in the delimlist SQL, as in: ' ORDER BY s_dist.d_origin_id'.)


It is preferable to use the built-in STRING_AGG function. This article explains how to use it in general and how to use it to replace DELIMLIST.


STRING_AGG general usage


string_agg ( value1, delimiter ORDER BY value2)

Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter.

-value is required and must be a text field, or you must cast it to text using value1::text

-delimiter is required* and must be text; use Null or '' if you don't want a delimiter.

-ORDER BY value2 is optional (and can be the same as value1).

*Function will not be recognized if delimiter is not entered.


Simple examples that work, for illustration

select string_agg(subnation_code, ', ') from subnation where nation_id=38

select string_agg(subnation_code, ', ' order by subnation_code) from subnation where nation_id=38

select string_agg(subnation_id::text, ', ' order by subnation_id) from subnation where nation_id=38


Analagous simple examples that DON'T work, for illustration, with reason they don't work

select string_agg(subnation_code) from subnation where nation_id=38  --delimiter is missing
select string_agg(subnation_code order by subnation_code) from subnation where nation_id=38   --delimiter is missing

select string_agg(subnation_id, ', ' order by subnation_id) from subnation where nation_id=38   --must cast subnation_id to text like this: "subnation_id::text"



HOW TO replace DELIMLIST with STRING_AGG

string_agg works on "multi-valued" tables by themselves, not within the context of a parent table.

delimlist is usually used to pull values from a "multi-valued" table into a query of a parent table. I.e., it serves 2 purposes: 1) brings values from the  "multi-valued" table as a subquery, essentially, and 2) lists the values


So to replace delimlist, you need to use string_agg within a subquery.


Simple example for illustration:

select element_global_id

  , elcode_bcd

  , (select string_agg(reference_id::text,', ' order by reference_id) 

     from ELEMENT_GLOBAL_REF 

     where element_global_id=eg.element_global_id)

from ELEMENT_GLOBAL eg limit 6


But we normally want a value from a lookup table rather than an ID from the multi-valued table, so we need to join to the lookup table (usually a domain table). (Alternatively, you can use a view that has the lookup values.)


Simple example for illustration:

select element_global_id

  , elcode_bcd

  , (select string_agg(reference_code::text,', ' order by reference_code) 

     from ELEMENT_GLOBAL_REF er 

     join REFERENCE r on er.reference_id = r.reference_id 

     where element_global_id=eg.element_global_id)

from ELEMENT_GLOBAL eg limit 6



Real-world examples


DISTRIBUTION DATA CONDENSED using string_agg

A complicated example that programs may want to use:

SELECT est.element_subnational_id,

   --the string_agg clause is 3 lines and the subquery an additional 7 lines:

   (SELECT string_agg(concat_ws('. ', substr(d_origin.origin_desc,1,1), substr(d_reg.regularity_desc,1,1), 

    substr(d_conf.dist_confidence_cd,1,3), substr(d_pres.curr_presence_absence_desc,1,1), 

    substr(d_pop.population_desc,1,1)), ' xx ') 

    FROM taxon_subnatl_dist s_dist

    left join d_curr_presence_absence d_pres on s_dist.d_curr_presence_absence_id = d_pres.d_curr_presence_absence_id

    left join d_dist_confidence d_conf on s_dist.d_dist_confidence_id = d_conf.d_dist_confidence_id

    left join d_origin on s_dist.d_origin_id = d_origin.d_origin_id

    left join d_population d_pop on s_dist.d_population_id = d_pop.d_population_id

    left join d_regularity d_reg on s_dist.d_regularity_id = d_reg.d_regularity_id

    WHERE s_dist.element_subnational_id = est.element_subnational_id) as dist_data_cond_sa


FROM element_subnational est

join element_national ent on  est.element_national_id = ent.element_national_id

join element_global egt on ent.element_global_id = egt.element_global_id

join scientific_name gname  on egt.gname_id = gname.scientific_name_id

join d_name_category name_cat on gname.d_name_category_id = name_cat.d_name_category_id


WHERE

--this exists clause is to limit to elements with more than 1 distribution row so you can see how the 'xx ' delimiter appears:

 exists(select element_subnational_id 

                       from taxon_subnatl_dist 

                       where element_subnational_id= est.element_subnational_id 

                       group by element_subnational_id 

                       having count(1)>1)

    and egt.inactive_ind = 'N'

    and name_cat.name_type_cd in ('A','P')

limit 50