SQL状态：42883，没有函数与给定的名称和参数类型匹配。但是那个功能确实存在 [英] SQL state: 42883, No function matches the given name and argument types. But that function actually exists

问题描述

我有一台装有PostgreSQL 8.1.23的服务器，该服务器在与 postgres 用户一起运行时可以完美地运行，但是与另一个用户一起显示SQL STATE：

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

SQL state: 42883

这是我的功能：

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;

如果我是这样与postgres用户一起运行的：

If I run this with postgres user like this:

select fun_validatepost(1,230465);

结果是这样的：

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

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

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

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

即使a显式强制转换得到相同的结果：

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

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

相同的错误消息。

可以做什么我这样做 someuser 可以执行相同的功能吗？

我的函数或演员表出问题了吗？

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

推荐答案

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

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_path of the current user, it's not visible.

详细信息：

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

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

通常，您将在架构 public 中创建公共功能，并将该架构包含在所有人的 search_path 中。

Typically, you would create public functions in the schema public and have that schema in everbody's 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;

仅在以下情况下才需要模式限定 public 仍然不是默认模式。

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

此外，您的 GRANT 命令没有任何意义。默认情况下，将功能的 EXECUTE 特权授予 public 。而且，一旦您授予 public ，就无需授予其他用户了。尤其不要使用 postgres ，它也是 OWNER 也是超级用户。 手册：

Also, your GRANT commands make no sense. The EXECUTE privilege for functions is granted to public by default. And once you grant to public, there is no need to grant to other users. Especially not to postgres, which is the OWNER anyway and a superuser, too. The manual:

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

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

您确实需要在创建函数的 SCHEMA 上授予用法。 public 模式将 USAGE 授予 public （每个人）

You do need to grant USAGE on the SCHEMA where the function is created. The public schema grants USAGE to public (everyone) by default.

向 integer 进行浇铸不会更改任何内容，因为不带小数点的数字文字会自动强制为整数。 手册中有关常量的详细信息。

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

紧急考虑更新到最新版本的Postgres。您的软件已完全过时。

这篇关于SQL状态：42883，没有函数与给定的名称和参数类型匹配。但是那个功能确实存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！