In Java, calling a PostgreSQL function, why I'm getting an error informing that the function doesn't exists?

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:

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.getVal开发者_如何学编程ue());
            cstm.addBatch();
        }
        cstm.executeBatch();
    } catch (SQLException e) {
        LOGGER.error("Error discovering pages relevance: " + e.getNextException());
    }
}

This is not working, I'm getting an error informing that function replacepagerelevance(integer, double precision) doesn't exists.. Why?

The generated call is this: SELECT replacePageRelevance('882','8.0'). If I execute this in pgAdmin, it works, but not from Java.

---

It's because your PL/pgSQL function is defined as:

replacePageRelevance(id INT, value REAL)

REAL is a single precision floating point, but on Java side you're trying to use it as double precision and there is no implicit casting for such situation.

You should rather use CallableStatement.setFloat for single precision REAL datatype, however it's not co clear, becuase reading Java API:

Sets the designated parameter to the given Java float value. The driver converts this to an SQL FLOAT value when it sends it to the database.

Since FLOAT is a synonym for DOUBLE PRECISION in Postgres it runs with same effect as CallableStatement.setDouble producing error message (that's weird).

Of course you could just change your function to replacePageRelevance(id INT, value DOUBLE PRECISION) and as I checked it works well with your code.

Nevertheless, If you want to keep REAL in your function), then use explicit cast like this:

String PAGE_RELEVANCE_SQL = "{call replacePageRelevance(?, ?::real)}";