如何获取函数参数列表(这样我可以删除一个函数) [英] How to get function parameter lists (so I can drop a function)
问题描述
我想让SQL在PostgreSQL中删除一个函数。我从 pg_proc
写入 DROP FUNCTION
和一个获取函数名称。这不是问题。但是,如果我留下空白参数,它不会放弃该功能。
我检查了手册,然后写入,然后我必须用它的参数来标识该函数,例如 DROP FUNCTION some_func(text,整数)
不只是 DROP FUNCTION some_func
。
哪里可以找到参数?在 pg_proc
表中的函数行中没有参数。那么我怎样才能让SQL去掉这个函数?
Postgres有一个专门的功能。 Postgres引入8.4。 手册:
pg_get_function_identity_arguments(func_oid)
...获取参数列表来识别一个函数(没有默认值)...
$ b pre $ c $ pg_get_function_identity_arguments 返回参数列表
例如,在> ALTER FUNCTION
中需要以
的形式标识一个函数。这个表格省略了默认值。
使用(和 c> format() ),以下查询生成DDL语句以删除与您的搜索条件匹配的函数:
SELECT格式('DROP%s%I.%I (%s)'
,CASE WHEN p.proisagg THEN'AGGREGATE'ELSE'FUNCTION'END
,n.nspname
,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 p.proname ='dblink' - 函数名称
- AND n.nspname ='public' - 模式名称(可选)
- AND pg_catalog.pg_function_is_visible(p.oid) - 函数可见用户
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使用重载函数。
运行 DROP
语句有选择性!
或者 ,您可以使用方便的转换为对象标识符类型 regprocedure $ c
$ b
- SET LOCAL search_path返回包含参数类型的完整函数签名=''; - 可选,获得所有名称模式限定
SELECT格式('DROP%s%s;'
,CASE当proisagg THEN'AGGREGATE'ELSE'FUNCTION'END
,oid: :regprocedure
)AS stmt
FROM pg_catalog.pg_proc
WHERE proname ='dblink' - 函数名称
ORDER BY 1;
I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTION
and a get function name from pg_proc
. That is not problem. However if I leave blank parameters it will not drop the function.
I checked the manual and there is written then I have to identify the function with its parameters to drop it, eg DROP FUNCTION some_func(text,integer)
not just DROP FUNCTION some_func
.
Where can I find the parameters? In the function's row on in the pg_proc
table there is no parameters. So how can I get the SQL to drop the function?
Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:
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 withinALTER FUNCTION
, for instance. This form omits default values.
Using that (and format()
, introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:
SELECT format('DROP %s %I.%I(%s)'
, CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
, n.nspname
, 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 p.proname = 'dblink' -- function name
-- AND n.nspname = 'public' -- schema name (optional)
-- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user
ORDER BY 1;
Returns:
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);
Found four matches in the example because dblink uses overloaded functions.
Run DROP
statements selectively!
Alternatively, you can use the convenient cast to the object identifier type regprocedure
which returns a complete function signature including argument types:
-- SET LOCAL search_path = ''; -- optional, to get all names schema-qualified
SELECT format('DROP %s %s;'
, CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
, oid::regprocedure
) AS stmt
FROM pg_catalog.pg_proc
WHERE proname = 'dblink' -- function name
ORDER BY 1;
这篇关于如何获取函数参数列表(这样我可以删除一个函数)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!