postgresql - 编写自定义聚合函数

Question

最近我一直在练习和研究PL/pgSQL。我坚持创建自定义聚合函数。

下面的代码工作得很好，但我无法编写这种类型的聚合函数:

SELECT my_aggregate_function(column) from table;

这是“部分工作的自定义聚合”的代码:

CREATE OR REPLACE FUNCTION searchMinValue (numeric[]) RETURNS numeric   AS $$
  DECLARE 
     i numeric;
     minVal numeric;
  BEGIN
    minVal := $1[1];
    IF ARRAY_LENGTH($1,1) > 0 THEN --Checking whether the array is empty or not
  <<confrontoMinimo>>
   FOREACH i IN ARRAY $1 LOOP --Looping through the entire array, passed as parameter
       IF minVal >= i THEN
           minVal := i;
       END IF;
   END LOOP confrontoMinimo;
   ELSE
    RAISE NOTICE 'Invalid parameter % passed to the aggregate function',$1;
   --Raising exception if the parameter passed as argument points to null.
   RAISE EXCEPTION 'Cannot find Max value. Parameter % is null', $1
   USING HINT = 'You cannot pass a null array! Check the passed parameter';
END IF;
RETURN minVal;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE searchMinValueArray (numeric)
(
sfunc = array_append,
stype = numeric[],
finalfunc = searchMinValue,
initCond = '{}'
); 

 with w(v) as (select 5 union all select 2 union all select 3)
 select min(v) "Normal Aggregate", searchMinValueArray(v) "My Customed Aggregate" from w;

正如我之前所说，我想以这种方式调用我的自定义聚合函数:

SELECT my_aggregate_function(column) from table;

其中表是 Customers，列是 salary，类型为 numeric。

Answer 1

从根本上说，你做的是对的，但你的实现仍然存在一些问题:

1. 如果表为空，您的聚合将出错。相反，它应该返回 NULL。

2. 如果您聚合的第一个值是NULL，您的聚合将做错事。如果

   minVal := $1[1];
   

   将 minVal 设置为 NULL，那么所有 future 的比较 minVal >= i 将不是 TRUE，最终结果将是NULL，这不是你想要的。

3. 您将所有值收集在一个数组中，如果您聚合许多行，该数组将变得非常大。首先，这会使您面临数组内存耗尽的危险，然后聚合的性能将不会达到应有的水平。

   在 SFUNC 中执行比较要好得多:从 INITCOND = NULL 开始，使用 STYPE = numeric 并在任何时候执行聚合a 你处理一个新值。 SFUNC 的相关部分如下所示:

   IF $1 IS NULL OR $1 > $2
       RETURN $2;
   ELSE
       RETURN $1;
   END IF;
   

   这样你就根本不需要 FINALFUNC。