Starting from a column type, how to find supported aggregations in Postgres?

I’m trying to figure out from a column type, which aggregates the data type supports. There’s a lot of variety amongst types, just a sample below (some of these support more aggregates, of course):

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

I’ve been thrashing around in the system catalogs and views, but haven’t found what I’m after. (See “thrashing around.”) I’ve poked at pg_type, pg_aggregate, pg_operator, and a few more.

Is there a straightforward way to start from a column type and gather all supported aggregates?

For background, I’m writing a client-side cross-tab code generator, and the UX is better when the tool automatically prevents you from selecting an aggregation that’s not supported. I’ve hacked in some hard-coded rules for now, but would like to improve the system.

We’re on Postgres 11.4.

Answer

A plain list of available aggregate functions can be based on pg_proc like this:

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

Or with separate function name and arguments:

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 replaces proisagg in Postgres 11. In Postgres 10 or older use:

...
WHERE proisagg
...

Related:

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

To get a list of available functions for every data type (your question), start with:

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

Just a start. Some of the arguments are just “direct” (non-aggregated) (That’s also why some functions are listed multiple times – due to those additional non-aggregate columns, example string_agg). And there are special cases for “ordered-set” and “hypothetical-set” aggregates. See the columns aggkind and aggnumdirectargs of the additional system catalog pg_aggregate. (You may want to exclude the exotic special cases for starters …)

And many types have an implicit cast to one of the types listed by the query. Prominent example string_agg() works with varchar, too, but it’s only listed for text above. You can extend the query with information from pg_cast to get the full picture.

Plus, some aggregates work for pseudo types "any", anyarray etc. You’ll want to factor those in for every applicable data type.

The complication of multiple aliases for the same data type names can be eliminated easily, though: cast to regtype to get canonical names. Or use pg_typeof() which returns standard names. Related:

• 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