在 Navicat 17 中创建自定义 PostgreSQL 聚合 2024 年 12 月 6 日，由 Robert Gravelle 撰写

PostgreSQL 的突出特点之一是广泛支持用户定义的函数和数据类型。这允许开发人员创建自定义转换、运算符和聚合函数。聚合函数是对数据执行复杂计算和转换的有效方式，超越了 SUM、AVG 和 COUNT 等标准 SQL 聚合函数。Navicat for PostgreSQL 和 Navicat Premium 都有专门的图形用户界面（GUI），可以轻松编写与数据库无缝集成的自定义函数和聚合。我们只需提供一些细节，Navicat 就会为我们生成 pgSQL 语句！在今天的博客中，我们将创建一个在 DVD Rental database 中使用的聚合，按类别连接电影标题。

关于聚合

聚合是 SQL 的一项基本功能，允许对一组记录执行计算或转换，并返回单一结果。最常见的聚合函数是 SUM、AVG、COUNT、MIN 和 MAX，它们允许你分别通过计算总数、平均值、计数、最小值和最大值来快速汇总数据。

然而，SQL 提供的内置聚合函数并不总能满足应用程序的特定需求。这时，创建自定义聚合的功能就变得非常有用。自定义聚合允许你按照自己的逻辑来汇总和转换数据，超越了标准 SQL 聚合的范围。这一过程通常包括定义一个状态转换函数，每一行都要调用该函数来更新累加器，以及定义一个可选的最终函数，调用该函数来生成最终的聚合结果。

生成转换函数和最终函数

转换函数 array_append_state() 将为每一行调用，以更新聚合状态。

访问 Navicat 的函数编辑器，请单击主按钮栏中的函数按钮，然后单击对象工具栏中的“新建函数”：

Navicat 将从主函数定义开始。在这里，我们将提供函数名称、输入参数和主体：

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)
  RETURNS text[] AS $BODY$
BEGIN
  RETURN array_append(current_state, new_value);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

当我们完成函数定义后，我们可以点击“保存”来创建函数。

现在我们将返回到对象选项卡，并点击“新建函数”来创建最终函数。

array_to_comma_string() 函数将获取一个电影题目数组，并在每个元素之间插入逗号：

CREATE FUNCTION "public"."array_to_comma_string" (state text[])
  RETURNS text AS $BODY$
BEGIN
  RETURN array_append(state, ', ');
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

创建 comma_concat() 聚合函数

现在我们可以将两个函数插入 Navicat 的聚合编辑器。我们可以通过单击主按钮栏中的“其它”按钮，然后从弹出的菜单中选择“聚合”来访问编辑器：

在表单中，我们将输入类型设置为“文本”，输入状态类型为“text[]”，并提供状态和最终函数。同时，确保初始条件是一个空数组（“{}”）：

点击“预览”选项卡，我们就能看到生成的 SQL：

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
  SFUNC = "public"."array_append_state",
  STYPE = "pg_catalog"."text[]",
  FINALFUNC = "public"."array_to_comma_string",
  INITCOND = "{}",
  PARALLEL = UNSAFE
);

ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

请注意，聚合体的名称是“无标题”。当我们点击“保存”按钮时，Navicat 将提示我们输入名称，并使用我们提供的名称执行命令。

使用自定义聚合

现在，我们可以像调用其他函数一样调用聚合函数。下面是一个按类别获取电影列表的查询：

SELECT 
    c.name AS category,
    comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;