PostgreSQL function does not exist

Question

I'm using the SQL query which uses a custom written function similar to this

CREATE OR REPLACE FUNCTION per_cont(myarray integer[], percentile real)

This works perfectly in the pgAdmin tool, but when I use this query in my java application it gives me an error:

function per_cont(integer[], real) does not exist

I'm using JDBI library to interact with the database. Why doesn't it find the function when running it from a java application? How can I fix it?

You're not connecting to the same database you think you are, most likely. — Craig Ringer, Dec 16 '14 at 05:39
I also thought whether I'm making that mistake, but checked it several times. In JDBI do we have to import the postgresql function externally, or does this library automatically recognizes the functions? — Roy Justin, Dec 16 '14 at 05:45
***How*** do you "use" the function exactly? Obviously, you must provide the command that triggered the error message. And be sure to provide the complete error message. And your version of Postgres (as *always*). — Erwin Brandstetter, Dec 16 '14 at 13:17

score 3 · Answer 1 · edited May 23 '17 at 12:05

The error message doesn't make sense at all for the CREATE OR REPLACE FUNCTION command you show. Assuming you are actually calling the function in a DML statement like

SELECT per_cont('{1,2,3}', 1);

Obviously you have to be using the same database, but you say that has been established.

Your search_path also has to match. Since you are not providing a schema explicitly, the function is created in the "current" schema when created. The same schema has to show up in the search_path of the other session or the function is not visible.

Detailed instructions in the linked answer:

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

PostgreSQL function does not exist

1 Answers1

Linked