postgresql “函数不存在”，但我真的认为它存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9679418/
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
"function does not exist," but I really think it does
提问by echtish
Am I crazy or just plain dumb?
我是疯了还是只是愚蠢？
dev=# \df abuse_resolve
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------
Schema | public
Name | abuse_resolve
Result data type | record
Argument data types | INOUT __abuse_id bigint, OUT __msg character varying
Type | normal
dev=# select abuse_resolve('30'::bigint);
ERROR: function abuse_resolve(bigint) does not exist
LINE 1: select abuse_resolve('30'::bigint);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Here's the CREATE FUNCTION, I've omitted the meat of the code, but that should be irrelevant:
这是CREATE FUNCTION，我省略了代码的内容，但这应该无关紧要：
CREATE OR REPLACE FUNCTION abuse_resolve(INOUT __abuse_id bigint, OUT __msg character varying) RETURNS record AS $_$
DECLARE
__abuse_status VARCHAR;
BEGIN
...snip...
UPDATE abuse SET abuse_status = __abuse_status,
edate = now(),
closed_on = now()
WHERE abuse_id = __abuse_id;
__msg = 'SUCCESS';
END;
$_$ LANGUAGE plpgsql SECURITY DEFINER;
And just for giggles:
只是为了咯咯笑：
GRANT ALL ON FUNCTION abuse_resolve(INOUT __abuse_id, OUT __msg character varying) TO PUBLIC;
GRANT ALL ON FUNCTION abuse_resolve(INOUT __abuse_id, OUT __msg character varying) TO myuser;
That function seems like it exists. What could I be missing?
该功能似乎存在。我可能会错过什么？
This is resolved, the answer is: I'm dumb. I had improperly defined the arguments originally, but my code was using the correct ones. There was an extra bigintthat had no business being there.
这个解决了，答案是：我傻。我最初没有正确定义参数，但我的代码使用了正确的参数。有一个多余的bigint人在那里没有生意。
采纳答案by araqnid
Well, something is odd. I did:
嗯，有些奇怪。我做了：
steve@steve@[local] =# create function abuse_resolve(inout __abuse_id bigint,
out __msg text) returns record language plpgsql as
$$ begin __msg = 'ok'; end; $$;
CREATE FUNCTION
steve@steve@[local] =# \df abuse_resolve
List of functions
-[ RECORD 1 ]-------+----------------------------------------
Schema | so9679418
Name | abuse_resolve
Result data type | record
Argument data types | INOUT __abuse_id bigint, OUT __msg text
Type | normal
steve@steve@[local] =# select abuse_resolve('30'::bigint);
-[ RECORD 1 ]-+--------
abuse_resolve | (30,ok)
Have you had any other issues with this database? Can you copy it with dump/restore and try this on the new copy? Does explicitly qualifying the function name with the "public" schema help? Which version of PostgreSQL are you using?
您对这个数据库有任何其他问题吗？你能用转储/恢复复制它并在新副本上试试这个吗？使用“公共”模式明确限定函数名称是否有帮助？您使用的是哪个版本的 PostgreSQL？
update: sql functionIt also worked fine for me using:
更新：sql 函数它也适用于我使用：
create function abuse_resolve(inout __abuse_id bigint, out __msg text)
language sql as $$ select , 'ok'::text $$;
回答by Perlos
If you can and if is that problem. I recommend to use
如果可以，如果是那个问题。我建议使用
"set search_path = mainSchemaName, secondOnes"
to set correct schema where function is created or in a place where you call it directly specify the schema name
在创建函数的地方或在直接调用它的地方设置正确的模式指定模式名称
select schemaName.abuse_resolve('30'::bigint);
回答by John P
Try this syntax:
试试这个语法：
SELECT * FROM abuse_resolve('30'::bigint);
