为 Postgres 编写自定义聚合函数

Question

我想编写一个聚合函数，返回格式如下的文本： min ->整数列的最大值，其中 min 和 max 是该列的最小值和最大值。 例如：

SELECT min_to_max(val) FROM (VALUES(5),(3),(6),(7),(9),(10),(7)) t(val);

会返回

min_to_max
-----------
3 -> 10

我尝试使用游标编写，但我认为使用自定义聚合会更好，但我不知道如何编写自定义聚合。

我所做的是编写一个正常的函数：

create or replace function min_to_max()
returns varchar
language plpgsql
as
$$
declare
   answer varchar;
begin
   select MIN(val) || '->' ||  max(val)
   into answer
   from abc;

return answer;
end;
$$;

Answer 1

为什么需要聚合函数？ MIN() 和 MAX() 工作正常：

SELECT MIN(val) || '->' ||  max(val) FROM (VALUES(5),(3),(6),(7),(9),(10),(7)) t(val);

Answer 2

正如您所怀疑的，您尝试了一个标准函数，但这只是定义聚合的一部分。用户定义的聚合基本上需要定义（创建聚合）和2个函数，一个定义状态到状态转换的函数和一个处理最终结果生成的函数。 （查看演示）

-- function to manage the state to state transition 
create or replace 
 function uagg_m2m (cagg bigint[], cval int)
   returns bigint[]
  language plpgsql
    strict
as $
-- SFUNC for min_to_max AGGREGATE
declare nagg bigint[]; 
begin
    nagg[1] = least(cval::bigint, cagg[1]);
    nagg[2] = greatest(cval::bigint, cagg[2]);
    return nagg; 
end;
$; 

-- function to manaage the final output
create or replace 
 function uagg_m2m_fin (cagg bigint[])
   returns text
  language plpgsql
as $
--  FINALFUNC for min_to_max AGGREGATE 
declare agg int[] = '{null,null}';
begin
     -- if the low value is = to initialization value 
     -- it means that all values for a state translation were null
     -- in that case then 
     if cagg[1] <> 2147483648 then 
        agg[1] = cagg[1]::int;
        agg[2] = cagg[2]::int;
     end if;
    
     return agg[1] || ' -> ' || agg[2]; 
end;
$;

-- define user aggregate
create or replace  aggregate  min_to_max (p_val integer)
    (
      sfunc = uagg_m2m                       -- state-to-state transition function  
    , stype = bigint[]                       -- array with 2 elements representing min, max.
    , finalfunc = uagg_m2m_fin               -- final result handling function   
    , initcond = '{2147483648, -2147483649}' -- initial state, array of (min, max) 
    );