在PostgreSQL中编写自己的聚合函数

Question

我从未编写过自己的聚合函数，只编写过存储过程，我需要一些建议。 我想编写一个自定义聚合函数，它将返回整数行的最大值，并将其增加10。我该如何实现？我尝试了以下代码：

CREATE AGGREGATE incremented_max ( v ) (
    SFUNC = max,
    STYPE = integer,
    INITCOND = max + 10
)

但是它没有起作用。有人能帮我吗？ 我遇到了错误：

ERROR:  syntax error at or near "+"
LINE 4:     INITCOND = max + 10

说实话，我不明白它应该如何工作。

- user3663882

---

你能详细说明一下它为什么不起作用吗？ - Politank-Z

Answer 1

以下示例展示如何使用自定义函数创建聚合：

create function greaterint (int, int)
returns int language sql
as $$
    select case when $1 < $2 then $2 else $1 end
$$;

create function intplus10 (int)
returns int language sql
as $$
    select $1+ 10;
$$;

create aggregate incremented_max (int) (
    sfunc = greaterint,
    finalfunc = intplus10,
    stype = integer,
    initcond = 0
);

select incremented_max(v), max(V)
from (
    select 3 as v
    union select 10
    union select 12
    union select 5) alias

Sfunc是一个状态转换函数。在聚合中，它将根据行数执行多次。它的第一个参数是一个内部状态，即到目前为止累积的值。在第一次调用时，该值等于initcond（如果未定义，则为null）。第二个参数是next-data-value，即来自下一行的值。在上面的示例中，该函数计算非空正整数的最大值，并且被执行了四次（对于四行）：

call #      internal-state     next-data-value       result
1           0 (initcond)       3                     3 (because 3 > 0)
2           3                  10                    10 (10 > 3)
3           10                 12                    12 (12 > 10)
4           12                 5                     12 (12 > 5)

Finalfunc在聚合结束时执行一次。它有一个参数（到目前为止计算出的值），并返回聚合的最终（修改后）结果。在我们的示例中，它只是将10添加到聚合结果。

在文档中了解更多信息。

上面的示例只是一种练习。实际上，没有必要定义这样的聚合，因为select max(v+10)可以得到所需的结果。

- klin

---

这正是我正在寻找的。谢谢你。 - user3663882

一个问题是，为什么你指定了两个函数sfunc和finalfunc？ - user3663882

@klin，请问您可以帮忙解答一下这个问题吗？https://stackoverflow.com/questions/73136968/equivalent-of-parallel-enable-aggregate-using-in-postgresql - MAK

Answer 2

BEGIN;

CREATE AGGREGATE inc_sum(int) (
    sfunc = int4pl,
    stype = int,
    initcond = 10
);

CREATE TEMP TABLE tt (i int);
INSERT INTO tt VALUES (1),(2),(3),(4);

SELECT sum(i), inc_sum(i) FROM tt;

ROLLBACK;

 sum | inc_sum 
-----+---------
  10 |      20
(1 row)

在上面的代码中，int4pl是“+”的基础函数（通过列出以int开头的函数并猜测来找到）。

initcond是初始条件，也就是你从哪里开始。对于普通的求和操作，初始值应该是0。

- Richard Huxton