“功能不存在”但我真的认为 [英] "function does not exist," but I really think it does

问题描述

我是发疯还是愚蠢？

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 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;

仅用于咯咯笑：

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?

此问题已解决，答案是：我很傻。我最初对参数的定义不正确，但是我的代码使用了正确的参数。还有一个额外的 bigint 那里没有生意。

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 bigint that had no business being there.

推荐答案

好吧，有些奇怪。我做到了：

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)

此数据库是否还有其他问题？您可以通过转储/还原来复制它，然后在新副本上尝试一下吗？使用公共模式明确限定功能名称是否有帮助？您正在使用哪个版本的PostgreSQL？

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?

更新：sql函数
使用以下命令对我也很好：

update: sql function It also worked fine for me using:

create function abuse_resolve(inout __abuse_id bigint, out __msg text)
  language sql as $$ select $1, 'ok'::text $$;

这篇关于“功能不存在”但我真的认为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！