Thread

pg_get_functiondef and aggregate functions

Erki Eessaar <erki.eessaar@taltech.ee> — 2021-11-03T15:44:50Z

Hello

In my opinion it is confusing that although in terms of the system catalog an aggregate function is a function
the function pg_get_functiondef does not work in case of aggregate functions and produces an error.

The following code was tested in PostgreSQL 14.

SELECT pg_get_functiondef(oid) AS aggregate_function
FROM pg_proc
WHERE prokind='a';

ERROR:  "array_agg" is an aggregate function

The same happens in PostgreSQL 10.

SELECT pg_get_functiondef(oid) AS aggregate_function
FROM pg_proc
WHERE proisagg=TRUE;

At the same time the routine for returning information about parameters works with aggregate functions.

SELECT pg_get_function_arguments(oid) AS aggregate_function
FROM pg_proc
WHERE prokind='a';

I suggest that it should be at least mentioned in the documentation.

Best regards
Erki Eessaar