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?

Answer 1

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"