I need to find the shortest string in postgres. Searched around could not find any answers. My use case started from a aggregate function in SQL:
select key_col, strarr_shortest(text_col) as text_col
from (
select key_col, array_agg(tex_col::text) as text_col
from mytable group by key_col
) foo;
It turn out I have to write a pl/pgsql to solve this problem.
There is no need to aggregate the rows and search in arrays. Use distinct on, example:
with mytable (key_col, text_col) as (
values
(1, 'asd'),
(1, 'a'),
(2, 'asd'),
(2, 'asdfg')
)
select distinct on (key_col) key_col, text_col as shortest
from mytable
order by key_col, length(text_col)
key_col | shortest
---------+----------
1 | a
2 | asd
(2 rows)
If you really need the function (to use in other circumstances), it may be a simple query wrapped in an SQL function:
create or replace function shortest(text[])
returns text language sql as $$
select elem
from unnest($1) as elem
order by length(elem)
limit 1;
$$;
My solution is to create a new function; not sure this is the best answer or not. There might be solution in pure SQL.
CREATE OR REPLACE FUNCTION strarr_shortest(arr text[]) RETURNS text AS $$
DECLARE
res text;
minlen int := 2147483647; -- largest singed int in postgres
el text;
BEGIN
FOREACH el IN ARRAY arr
LOOP
if char_length(el) < minlen then
res=el;
minlen=char_length(el);
end if;
END LOOP;
RETURN res;
END;
$$ LANGUAGE plpgsql;