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

Question

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

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:

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:

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 someuser can execute the same function?
Is there something wrong with my function or cast?

Answer 1

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.

Details:

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

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;

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

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 grants default privileges on some types of objects to PUBLIC. [...] EXECUTE privilege for functions;

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

Aside 1

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.

Aside 2

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