更新时间:2022-05-23 23:47:02
类似这样的事情,但是我不确定是否这涵盖了创建集合的所有可能方式(它绝对不会考虑使用引号引起的标识符)
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