Create a custom aggregate function in postgresql-CodePudding

I need an aggregate function in postgresql that returns the maximum value of a text column, where the maximum is calculated not by alphabetical order but by the length of the string.

Can anyone please help me out?

CodePudding user response：

A custom aggregate consist of two parts: a function that does the work and the definition of the aggregate function.

So we first need a function that returns the longer of two strings:

create function greater_by_length(p_one text, p_other text)
  returns text
as
$$ 
  select case 
           when length(p_one) >= length(p_other) then p_one
           else p_other
         end
$$
language sql
immutable;

Then we can define an aggregate using that function:

create aggregate max_by_length(text)
(
  sfunc = greater_by_length,
  stype = text
);

And using it:

select max_by_length(s)
from (
  values ('one'), ('onetwo'), ('three'), ('threefourfive')
) as x(s);

returns threefourfive