How to get definition/source code of an aggregate in PostgreSQL?

Question

I found this related answer useful:

• Export "Create Aggregate" functions from PostgreSQL

But how do I get the CREATE AGGREGATE statement without a GUI client (e.g. with psql command line)?

Answer 1

A modern version to generate the CREATE AGGREGATE statement - using format() and casts to object identifier types to make it simple and add double-quotes and schema-qualification to identifiers where required automatically:

SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)'
            , aggfnoid::regprocedure
            , aggtransfn
            , aggtranstype::regtype
            , ', SORTOP = '    || NULLIF(aggsortop, 0)::regoper
            , ', INITCOND = '  || agginitval
            , ', FINALFUNC = ' || NULLIF(aggfinalfn, 0)
            , CASE WHEN aggfinalextra THEN ', FINALFUNC_EXTRA' END
            --  add more to cover special cases like moving-aggregate etc.
              ) AS ddl_agg
FROM   pg_aggregate
WHERE  aggfnoid = 'my_agg_func'::regproc;  -- name of agg func here

You can schema-qualify the aggregate function's name where necessary:

'public.my_agg_func'::regproc

And / or add function parameters to disambiguate in case of overloaded aggregate functions:

'array_agg(anyarray)'::regprocedure

This does not cover special cases like moving-aggregate functions etc. Can easily be extended to cover all options for your current Postgres version. But the next major version may bring new options.

A function pg_get_aggregatedef() similar to the existing pg_get_functiondef() would be great to eliminate the need for this custom query - which may have to be adapted with every new major Postgres version ...