postgresql – 如何获取函数参数列表(所以我可以放一个函数)

pg_get_function_identity_arguments(func_oid) … get argument list to identify a function (without default values)
…

pg_get_function_identity_arguments returns the argument list
necessary to identify a function,in the form it would need to appear
in within ALTER FUNCTION,for instance. This form omits default values.

使用该方法,以下查询生成DDL语句以删除与搜索词匹配的函数：

SELECT 'DROP '
    || CASE WHEN p.proisagg THEN 'AGGREGATE ' ELSE 'FUNCTION ' END
    || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' 
    || pg_catalog.pg_get_function_identity_arguments(p.oid) || ');' AS stmt
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public'                     -- schema name (optional)
AND    p.proname ILIKE 'crosstab%'              -- function name
-- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user
ORDER  BY 1;

返回：

stmt
---------------------------------------------------
 DROP FUNCTION public.dblink(text);
 DROP FUNCTION public.dblink(text,boolean);
 DROP FUNCTION public.dblink(text,text);
 DROP FUNCTION public.dblink(text,text,boolean);

在该示例中找到四个匹配,因为dblink使用overloaded functions.有选择地运行DDL语句！