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
– Craig Ringer, Commented Dec 16, 2014 at 5: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
– Roy Justin, Commented Dec 16, 2014 at 5: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
– Erwin Brandstetter, Commented Dec 16, 2014 at 13:17

Community · Accepted Answer · 2017-05-23 12:05:52Z

4

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"

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Dec 16, 2014 at 13:24

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL function does not exist

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related