CODEWITHSUNDEEP
sqlpostgresql
Kemin Zhou
Kemin Zhou

Reputation: 6891

In postgres how to find the shortest string an array

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.

Upvotes: 2

Views: 807

Answers (2)

klin
klin

Reputation: 121644

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;
$$;

Upvotes: 2

Kemin Zhou
Kemin Zhou

Reputation: 6891

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;

Upvotes: 0

Related Questions