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

I found this related answer useful:

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

sqlpostgresqlaggregate-functionspsqlddl


Solution 1:[1]

Something like this, but I'm not sure if this covers all possible ways of creating an aggregate (it definitely does not take the need for quoted identifiers into account)

SELECT 'create aggregate '||n.nspname||'.'||p.proname||'('||format_type(a.aggtranstype, null)||') (sfunc = '||a.aggtransfn
       ||', stype = '||format_type(a.aggtranstype, null)
       ||case when op.oprname is null then '' else ', sortop = '||op.oprname end 
       ||case when a.agginitval is null then '' else ', initcond = '||a.agginitval end
       ||')' as source
FROM pg_proc p 
  JOIN pg_namespace n ON p.pronamespace = n.oid 
  JOIN pg_aggregate a ON a.aggfnoid = p.oid 
  LEFT JOIN pg_operator op ON op.oid = a.aggsortop 
where p.proname = 'your_aggregate'
  and n.nspname = 'public' --- replace with your schema name

Solution 2:[2]

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 ...

Solution 3:[3]

My version using some system functions 

SELECT 
format(
   E'CREATE AGGREGATE %s (\n%s\n);'
   , (pg_identify_object('pg_proc'::regclass, aggfnoid, 0)).identity
   , array_to_string(
      ARRAY[
     format(E'\tSFUNC = %s', aggtransfn::regproc)
     , format(E'\tSTYPE = %s', format_type(aggtranstype, NULL))
     , CASE aggfinalfn WHEN '-'::regproc THEN NULL ELSE format(E'\tFINALFUNC = %s',aggfinalfn::text) END
     , CASE aggsortop WHEN 0 THEN NULL ELSE format(E'\tSORTOP = %s', oprname) END
     , CASE WHEN agginitval IS NULL THEN NULL ELSE format(E'\tINITCOND = %s', agginitval) END
      ]
      , E',\n'
   )
)

FROM pg_aggregate
LEFT JOIN pg_operator ON pg_operator.oid = aggsortop
WHERE aggfnoid = 'regr_r2'::regproc;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Erwin Brandstetter
Solution 2
Solution 3 Jelen

Related Questions