Sort a postgresql text array by length

Using postgresql's with expressions, we:

  1. unnest the array into a table
  2. create a table with the string and lengths and use that to order the table
  3. output the ordered table without the lengths; and finally
  4. use array to pack that up into an array again.
with 
  unnested as (select unnest(arr) as str),
  sorted_w_len as (select str, length(str) as str_len
    from unnested order by str_len desc),
  sorted as (select str from sorted_w_len)
select array(select * from sorted);

Putting that into a function is a good idea:

create or replace function _array_str_length_sort(arr text[])
  returns text[]
  language plpgsql
as $$
declare
  ret text[];
begin
  with 
    unnested as (select unnest(arr) as str),
    sorted_w_len as (select str, length(str) as str_len
	  from unnestedorder by str_len desc),
    sorted as (select str from sorted_w_len)
  select array(select * from sorted) into ret;
  return ret;
end;
$$;
postgresql

Edit on github
comments powered by Disqus
Click me