CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionspsqlddl
qingbo
qingbo

Reputation: 2160

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)?

Upvotes: 6

Views: 2901

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658142

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

Upvotes: 5

user330315
user330315

Reputation:

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

Upvotes: 3

Jelen
Jelen

Reputation: 633

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;

Upvotes: 1

Related Questions