Why am I getting the error that the “result was not expected” when executing stored procedures on PostgreSQL with Java in the package?

Question

I have this procedure in the database:

CREATE OR REPLACE FUNCTION replacePageRelevance(id INT, value REAL) RETURNS VOID AS $$ BEGIN INSERT INTO pageRelevance VALUES (id,value); EXCEPTION WHEN unique_violation THEN UPDATE pageRelevance SET relevance = value WHERE pageId = id; END $$ LANGUAGE plpgsql; 

And this code that calls this function:

 private final String PAGE_RELEVANCE_SQL = "SELECT replacePageRelevance(?,?::REAL)"; try (CallableStatement cstm = conn.prepareCall(PAGE_RELEVANCE_SQL)) { for (Map.Entry<Integer, Double> entry : weightMap.entrySet()) { cstm.setInt(1, entry.getKey()); cstm.setDouble(2, entry.getValue()); cstm.addBatch(); } cstm.executeBatch(); } catch (SQLException e) { LOGGER.error("Error discovering pages relevance: " + e.getNextException()); } } 

When I execute the package, the values ​​are inserted or replaced in the table, but after that I get an exception informing that A result was returned when none was expected.

I do not know what is wrong if I call the procedure or the procedure itself. What could be the problem and how to solve it?

Is calling a procedure with SELECT the correct / only way?

Answer 1

From what I can tell, you are using SELECT when you need to use call .

An example from the PostgreSQL documentation on the JDBC interface :

 // Turn transactions off. con.setAutoCommit(false); // Procedure call. CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }"); upperProc.registerOutParameter(1, Types.VARCHAR); upperProc.setString(2, "lowercase to uppercase"); upperProc.execute(); String upperCased = upperProc.getString(1); upperProc.close(); 

Note the syntax ? = call ? = call for the result is not needed in your case - you want to use only call replacePageRelevance(?,?::REAL)

The reason this syntax differs from real PostgreSQL is because it is part of the specification.