Exception "function does not exist" when executing PostgreSQL stored function via JDBC

Question

I have problems calling a PostgreSQL function from JDBC with circumstances as follows. Stored function endpoint_organizations is defined like this:

postgres=# \df public.endpoint_organizations   
                                             List of functions
 Schema |          Name          |             Result data type             | Argument data types |  Type  
--------+------------------------+------------------------------------------+---------------------+--------
 public | endpoint_organizations | TABLE(organizationid integer, name text) | staffid1 integer    | normal
(1 row)

I am calling it from Java like this:

int staffId = 1
PreparedStatement endpointOrganizations = connection.prepareStatement("SELECT * FROM endpoint_organizations (?)");
endpointOrganizations.setInt(1, staffId);
ResultSet resultSet = endpointOrganizations.executeQuery();

And I receive this exception:

org.postgresql.util.PSQLException: 
ERROR: function endpoint_organizations(integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15

What could be the reason why? If I am not mistaken, this has worked before. I've double-checked and triple-checked now, but don't see what might cause the problem.

Do you have permission to execute on that function with the jdbc user? If not grant execute on function endpoint_organizations; — d1ll1nger
– d1ll1nger, Commented Oct 2, 2016 at 16:27
@d1ll1nger Yes, I have. If I psql -U <user> into the database and select * from endpoint_organizations(1) there, things work fine. — Drux
– Drux, Commented Oct 2, 2016 at 17:04

Drux · Accepted Answer · 2016-10-02 17:33:54Z

4

This was (of course) a stupid mistake on my part. I had made recent updates also to the stored function after connecting to PostgreSQL with psql -U <user> instead of psql -U <user> <database>, i.e. updates affected database postgres instead of <database>, whereas JDBC connected to <database>.

After performing updates on the right database, things are now back to normal.

answered Oct 2, 2016 at 17:33

Drux

12.8k13 gold badges71 silver badges123 bronze badges

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

Collectives™ on Stack Overflow

Exception "function does not exist" when executing PostgreSQL stored function via JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related