sql - 从列类型开始，如何找到Postgres中支持的聚合？

Question

我试图从列类型中找出它聚合的数据类型支持。类型有很多种，下面只是一个示例(当然，其中一些支持更多聚合):

uuid     count()
text     count(), min(), max()
integer  count(), min, max(),avg(),sum()

我一直在 system catalogs and views 中挣扎。 ，但还没有找到我想要的东西。 (请参阅“四处奔波”。)我研究过 pg_type、pg_aggregate、pg_operator 等。

有没有一种简单的方法可以从列类型开始并收集所有支持的聚合？

作为背景，我正在编写一个客户端交叉表代码生成器，当该工具自动阻止您选择不支持的聚合时，用户体验会更好。我现在已经修改了一些硬编码规则，但想改进系统。

我们使用的是 Postgres 11.4。

Answer 1

可用聚合函数的简单列表可以基于 pg_proc像这样:

SELECT oid::regprocedure::text AS agg_func_plus_args
FROM   pg_proc
WHERE  prokind = 'a'
ORDER  BY 1;

或者使用单独的函数名称和参数:

SELECT proname AS agg_func, pg_get_function_identity_arguments(oid) AS args
FROM   pg_proc
WHERE  prokind = 'a'
ORDER  BY 1, 2;

pg_proc.prokind 替换 Postgres 11 中的 proisagg。在 Postgres 10 或更早版本中使用:

...
WHERE proisagg
...

相关:

• How to drop all of my functions in PostgreSQL?
• How to get function parameter lists (so I can drop a function)

要获取每种数据类型的可用函数列表(您的问题)，请从以下内容开始:

SELECT type_id::regtype::text, array_agg(proname) AS agg_functions
FROM  (
   SELECT proname, unnest(proargtypes::regtype[])::text AS type_id
   FROM   pg_proc
   WHERE  proisagg
   ORDER  BY 2, 1
   ) sub
GROUP  BY type_id;

db<> fiddle here

只是一个开始。有些参数只是“直接”(非聚合)(这也是为什么某些函数被多次列出的原因 - 由于那些额外的非聚合列，例如 string_agg)。 “有序集”和“假设集”聚合也有特殊情况。请参阅附加系统目录 pg_aggregate 的 aggkind 和 aggnumdirectargs 列。 (对于初学者来说，您可能希望排除奇异的特殊情况......)

并且许多类型都隐式转换为查询列出的类型之一。著名的示例 string_agg() 也适用于 varchar，但它仅在上面的 text 中列出。您可以使用 pg_cast 中的信息扩展查询。以获得完整的图片。

另外，一些聚合适用于 pseudo types “any”、anyarray 等。您需要将这些因素考虑到每种适用的数据类型。

不过，相同数据类型名称的多个别名的复杂性可以轻松消除:转换为regtype以获得规范名称。或者使用返回标准名称的 pg_typeof() 。相关:

• Type conversion. What do I do with a PostgreSQL OID value in libpq in C?
• PostgreSQL syntax error in parameterized query on "date $1"
• How do I translate PostgreSQL OID using python