Formats input PostgreSQL int[] to a "human-readable" range representation. Originally meant for making up year-ranges from a comma-separated list of years.

array_format_as_range.sql

create or replace function array_format_as_range(
    arr int[], step int default 1) 
returns varchar 
as
$$
declare
    l int=array_upper(arr,1);
    hyphen varchar='-';
    comma varchar=', ';
    prv int;
    cur int;
    nxt int;
    outp varchar[];
begin
    if l is null then
        return null;
    end if;
    
    for i in 1 .. l
    loop
        cur := arr[i];
        if i = 1 then
	    outp := array_append(outp, cur::varchar);
	else
            nxt := arr[i + 1];
            prv := arr[i - 1];
            if (prv + step = cur) then
                /* current val is within reach of step from previous, 
                   append hyphen if not already there */
                if (outp[array_upper(outp, 1)] != hyphen) then
                    outp := array_append(outp, hyphen);
                end if;
            else
                /* current val is out of reach from previous, 
                   append comma if not already there */
                if (outp[array_upper(outp, 1)] != comma) then 
		    outp := array_append(outp, comma);
                end if;
                outp := array_append(outp, cur::varchar);
            end if;
            if (cur + step != nxt) or (i = l) then
                /* next value is not within reach of step 
                   or we're at the last value, append value */
                if (outp[array_upper(outp, 1)] != cur::varchar) then 
                    outp := array_append(outp, cur::varchar);
                end if;
            end if;
        end if;
    end loop;  
    return array_to_string(outp, '');
end;
$$
language plpgsql;
alter function array_format_as_range(int[], int) owner to postgres;
comment on function array_format_as_range(int[], int) is 'Formats input int[] to a "human-readable" range representation. Originally meant for making up year-ranges from a comma-separated list of years.';


/**

select array_format_as_range(array[2001,2002,2003,2004], 1);
--"2001-2004"

select array_format_as_range(null, 1);
--NULL

select array_format_as_range(array[2001], 1);
--"2001"

select array_format_as_range(array[2001,2003,2006,2007], 1);
--"2001, 2003, 2006-2007"

select array_format_as_range(array[2001,2002,2003,2006,2007], 1);
--"2001-2003, 2006-2007"

select array_format_as_range(array[2001,2003,2005,2007], 1);
--"2001, 2003, 2005, 2007"

select array_format_as_range(array[2001,2003,2005,2007], 2);
--"2001-2007"

select array_format_as_range(array[2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016], 1);
--"2001-2016"

select array_format_as_range(array[2001,2002,2003,2004,2005,2006,2007,2008,2010,2011,2012,2013,2014,2015,2016], 1);
--"2001-2008, 2010-2016"

*/