Can I call a postgres "Procedure" (not "function") from java using the postgres JDBC driver?

Question

I'm new to postgres but am attempting to call a procedure in Postgres 11 (new "procedure" not a "function"), calling from java as a spring SimpleJDBCCall (using Postgresql-42.2.5 jdbc driver). However, when I execute the procedure I am encountering the following exception:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call pa_test_schema.pr_dosomething(?)}]; nested exception is org.postgresql.util.PSQLException: ERROR: pa_test_schema.pr_dosomething(bigint) is a procedure Hint: To call a procedure, use CALL. Position: 15 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1065) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1104) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:414) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:397) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:193)

My procedure code:

CREATE PROCEDURE pa_test_schema.pr_DoSomething
( P_input_ID IN inputs.input_ID%TYPE
) AS $$
BEGIN
    -- do something   
END; 
$$ LANGUAGE plpgsql;

My java code:

SimpleJdbcCallOperations pr_DoSomething =  new SimpleJdbcCall(jdbcTemplate)
        .withSchemaName("pa_test_schema")
        .withProcedureName("pr_DoSomething");
Map<String, Object> inputs = Maps.newHashMap();
inputs.put("p_input_id", 123456);

pr_DoSomething.execute(inputs);    

When I step through the code I can see the driver is modifying the sql of the callable statement to the syntax required for calling a postgres function:

select * from pa_test_schema.pr_dosomething(?) as result

This is the method in the driver that is doing this conversion: https://github.com/pgjdbc/pgjdbc/blob/faab499853c56f67cb70fb242f75b918452f2a6f/pgjdbc/src/main/java/org/postgresql/core/Parser.java#L766

I understand procedures were only introduced in Postgres 11 (previously one would have used void-returning functions) and have read through the postgres driver documentation but don't see any reference to calling procedures rather than functions.

Does this mean that the current postgres driver does not yet support this or is there another approach I should be using? Should I just be using postgres functions instead?

Answer 1

Currently (as of Postgres 11.1 and driver version 42.2.5) the standard JDBC approach using a CallableStatement cannot be used to call a stored procedure.

I don't really use Spring JDBC Template, but the following code works in plain JDBC and should be adaptable to Spring JDBC Tempalte:

Connection con = DriverManager.getConnection(...);
PreparedStatement pstmt = con.prepareStatement("call pa_test_schema.pr_DoSomething(?)");
pstmt.setInt(1, 42);
pstmt.execute();

Note that this uses Postgres' call command. Do not confuse this with the "{call ...}" syntax for a CallableStatement.

---

Some more details on why currently a CallableStatement does not work can be found in the JDBC mailing list here and here