postgresql SQL 状态：42883，没有函数匹配给定的名称和参数类型。但是那个函数确实存在

SQL state: 42883, No function matches the given name and argument types. But that function actually exists

提问by mmontes

I have a server with PostgreSQL 8.1.23, with a function that works perfectly when it runs with postgresuser, but with another user shows the SQL STATE:

我有一台带有 PostgreSQL 8.1.23 的服务器，它的功能在与postgres用户一起运行时运行良好，但与另一个用户一起运行时会显示 SQL 状态：

SQL state: 42883

SQL state: 42883

This is my function:

这是我的功能：

CREATE OR REPLACE FUNCTION fun_validatepost(integer, integer)
  RETURNS integer AS
$BODY$
...
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION fun_validatepost(integer, integer)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION fun_validatepost(integer, integer) TO public;
GRANT EXECUTE ON FUNCTION fun_validatepost(integer, integer) TO postgres;
GRANT EXECUTE ON FUNCTION fun_validatepost(integer, integer) TO someuser;

If I run this with postgres user like this:

如果我像这样使用 postgres 用户运行它：

select fun_validatepost(1,230465);

The result is like this:

结果是这样的：

-[ RECORD 1 ]-----------+--
fun_validatepost        | 1

But if I execute the same query as someuser, shows me this message:

但是，如果我与 someuser 执行相同的查询，则会显示以下消息：

ERROR:  function fun_validatepost(integer, integer) does not exist
SQL state: 42883
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

ERROR:  function fun_validatepost(integer, integer) does not exist
SQL state: 42883
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

Even if a do a explicit cast I get the same result:

即使执行显式转换，我也会得到相同的结果：

select fun_validatepost from fun_validatepost(1::integer,230465::integer);

Same error message.

同样的错误信息。

What can I do so someusercan execute the same function?
Is there something wrong with my function or cast?

我可以做什么才能someuser执行相同的功能？
我的函数或演员有什么问题吗？

采纳答案by Erwin Brandstetter

Most probably a matter of schema vs. schema search_path. The function is created in the default schema of the creating user. If that's not in the search_pathof the current user, it's not visible.

很可能是 schema 与 schema 的问题search_path。该函数是在创建用户的默认架构中创建的。如果它不在search_path当前用户的 中，则它不可见。

Details:

细节：

• How does the search_path influence identifier resolution and the "current schema"

• search_path 如何影响标识符解析和“当前模式”

Typically, you would create public functions in the schema publicand have that schema in everbody's search_path.

通常，您会在架构中创建公共函数public并在每个人的search_path.

CREATE OR REPLACE FUNCTION public.fun_validatepost(integer, integer)
  RETURNS integer AS
$BODY$
...
$BODY$ LANGUAGE plpgsql;
ALTER FUNCTION public.fun_validatepost(integer, integer) OWNER TO postgres;

Schema-qualification is only needed if publicisn't the default schema anyway.

仅当public不是默认模式时才需要模式限定。

Also, your GRANTcommands make no sense. The EXECUTEprivilege for functions is granted to publicby default. And once you grant to public, there is no need to grant to other users. Especially not to postgres, which is the OWNERanyway and a superuser, too. The manual:

此外，您的GRANT命令毫无意义。默认情况下EXECUTE授予函数的权限public。一旦您授予public，就无需授予其他用户。尤其是 not to postgres，这也是OWNER一个超级用户。手册：

PostgreSQL grants default privileges on some types of objects to PUBLIC. [...] EXECUTEprivilege for functions;

PostgreSQL 将某些类型对象的默认权限授予 PUBLIC. [...]EXECUTE功能特权；

You do need to grant USAGEon the SCHEMAwhere the function is created. The publicschema grants USAGEto public(everyone) by default.

你需要授予USAGE在SCHEMA其中创建功能。该public模式赋予USAGE到public默认（每个人）。

旁白 1

Casting to integerdoes not change anything here because a numeric literal without decimal point is coerced to integer automatically. Details about constants in the manual.

转换到integer这里不会改变任何东西，因为没有小数点的数字文字会自动强制为整数。手册中有关常量的详细信息。

旁白 2

Urgently consider updating to a current version of Postgres. Your software is completely outdated.

紧急考虑更新到当前版本的 Postgres。你的软件已经完全过时了。