postgresql 如何获取函数参数列表（这样我就可以删除一个函数）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12127274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get function parameter lists (so I can drop a function)
提问by John
I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTIONand a get function name from pg_proc. That is not problem. However if I leave blank parameters it will not drop the function.
我想让 SQL 在 PostgreSQL 中删除一个函数。我DROP FUNCTION从pg_proc. 那不是问题。但是，如果我将参数留空，则不会删除该功能。
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.
我检查了手册并写了然后我必须用它的参数来识别函数以删除它，例如DROP FUNCTION some_func(text,integer)不仅仅是DROP FUNCTION some_func.
Where can I find the parameters? In the function's row on in the pg_proctable there is no parameters. So how can I get the SQL to drop the function?
我在哪里可以找到参数？在表中的函数行中pg_proc没有参数。那么如何让 SQL 删除该函数呢？
回答by Erwin Brandstetter
Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:
Postgres 有一个专门用于此目的的功能。随 Postgres 8.4 引入。手册：
pg_get_function_identity_arguments(func_oid)... get argument list to identify a function (without default values) ...
pg_get_function_identity_argumentsreturns 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.
pg_get_function_identity_arguments(func_oid)...获取参数列表以标识函数（没有默认值）...
pg_get_function_identity_arguments返回标识函数所需的参数列表，例如，以它需要出现在 中的形式ALTER FUNCTION。这种形式省略了默认值。
Using that (and format(), introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:
使用它（以及format()Postgres 9.1 引入的），以下查询生成 DDL 语句以删除与您的搜索词匹配的函数：
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;
The system catalog pg_procchanged in Postgres 11. proisaggwas replaced by prokind, true stored procedures were added. You need to adapt. See:
系统目录pg_proc在Postgres 11 中发生了变化。proisagg替换为prokind，添加了真正的存储过程。你需要适应。看：
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 DROPstatements selectively!
在示例中找到四个匹配项，因为 dblink 使用了重载函数。有选择地
运行DROP语句！
Alternatively, you can use the convenient cast to the object identifier type regprocedurewhich returns a complete function signature including argument types:
或者，您可以使用方便的转换为对象标识符类型regprocedure，该类型返回一个完整的函数签名，包括参数类型：
-- 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;
回答by SamGoody
In Postgres 10, you can delete a function without knowing the list of parameters, as long as it is unique in its schema.
在 Postgres 10 中，您可以在不知道参数列表的情况下删除函数，只要它在其架构中是唯一的。
drop function if exists some_func;
Of course, if you have overloaded the function (or are trying to delete over multiple schemas), you will still need the above answers.
当然，如果您重载了该函数（或尝试删除多个架构），您仍然需要上述答案。
回答by Ruthe
use pgadminIII and direct access to function list and right click it then select delete
使用 pgadminIII 并直接访问功能列表并右键单击它然后选择删除
回答by Robin
If you are working on an old previous version of postgres, for which pg_get_function_identity_arguments(func_oid) doesn't exist, I create my own function get the parameters from the function, you only need to pass the oid for the function, you need to deploy the function below to your postgres db.
如果您正在使用旧版本的 postgres，其中 pg_get_function_identity_arguments(func_oid) 不存在，我创建自己的函数从函数中获取参数，您只需要为函数传递 oid，您需要部署下面的函数到你的 postgres 数据库。
CREATE OR REPLACE FUNCTION public.getFunctionParameter(functionOid oid)
RETURNS text AS
$BODY$
declare
t_paras text;
paras oid[];
res text :='(';
begin
select proargtypes into t_paras from pg_proc where oid=functionOid;
if t_paras is null or t_paras='' then
return '()';
else
paras:=string_to_array(t_paras,' ');
for i in array_lower(paras,1) .. array_upper(paras,1)
loop
raise notice 'para is %',paras[i];
select format_type(paras[i]::oid,NULL) into t_paras;
res:=res||t_paras||',';
end loop;
res:=substring(res from 1 for char_length(res)-1);
res:=res||')';
return res;
end if;
end
$BODY$
LANGUAGE plpgsql ;
The function below will list the function name and parameters, change the schema name if you want to get function under some other schema, I am using public for example
下面的函数将列出函数名称和参数，如果要在其他模式下获取函数，请更改模式名称，例如我使用的是 public
SELECT n.nspname||'.'||p.proname||public.getFunctionParameter(p.oid)
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname='public'
You will the result like below
你会得到如下结果
1 "public.getfunctionparameter(integer,text)"
2 "public.getfunctionparameter(oid)"
