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

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:

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());
    }
}

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.

Grzegorz Szpetkowski · Accepted Answer · 2011-09-01 18:30:17Z

6

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)}";

edited Sep 1, 2011 at 18:30

answered Sep 1, 2011 at 17:48

Grzegorz Szpetkowski

38.2k6 gold badges94 silver badges140 bronze badges

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

3 Comments

Renato Dinhani Over a year ago

What PostgreSQL type is compatible with double precision?

Grzegorz Szpetkowski Over a year ago

@Renato: Just double precision

Renato Dinhani Over a year ago

Thank you for the answer and explanation. I'm using the conversion now because I don't need much precision

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related