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)?
Add a comment
|
3 Answers
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 ...
answered Feb 2, 2018 at 3:49
Erwin Brandstetter
669k160 gold badges1.2k silver badges1.3k bronze badges
Comments
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
3 Comments
pozs
Good start, but arguments should be something like
(SELECT string_agg(format_type(t, null), ', ') FROM unnest(p.proargtypes::oid[]) t) -- see sqlfiddle.com/#!12/d0e8a/2 -- also note that in PostgreSQL 9.4+ there are more options & types of custom aggregate functions.
qingbo
Thanks guys. I thought there might be something similar to pg_get_functiondef but looks like no.
Erwin Brandstetter
@pozs: Good point. There is also
pg_get_function_identity_arguments() for this. Or more elegant, yet: aggfnoid::regprocedure. I added an updated version.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;