Calling a stored function that returns nothing

Question

I have this function which only inserts some data and returns nothing

CREATE OR REPLACE FUNCTION test(srcDc integer, targetDc integer)

RETURNS void AS

$BODY$

BEGIN

INSERT INTO configurable_code(key_column,customer_discovery_scope_id)
SELECT key_column, targetDc FROM configurable_code WHERE customer_discovery_scope_id=srcDc
AND KEY_Column NOT IN (SELECT KEY_Column FROM configurable_code where customer_discovery_scope_id in( targetDc));

INSERT INTO user_customer_scope ( user_id, customer_discovery_scope_id )
SELECT user_id,targetDc FROM user_customer_scope where customer_discovery_scope_id=srcDc
AND customer_discovery_scope_id NOT IN (SELECT customer_discovery_scope_id FROM user_customer_scope where customer_discovery_scope_id = targetDc );

INSERT INTO user_scope_license ( user_id, customer_scope_id, license )
SELECT user_id,targetDc, license FROM user_scope_license WHERE customer_scope_id=srcDc
AND customer_scope_id NOT IN (SELECT customer_scope_id FROM user_scope_license where customer_scope_id = targetDc );

END;

$BODY$

LANGUAGE 'plpgsql'

COST 100;

And since the Postgres version is 10, procedures are not supported. So I use JDBCTemplate to make a simple call

jdbcTemplate.update("select test(?,?)",srcDc,targetDc);

But I get this error when running it

2022-02-02 17:03:22,640 [http-nio-8090-exec-4] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 0100E
2022-02-02 17:03:22,641 [http-nio-8090-exec-4] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - A result was returned when none was expected.
org.hibernate.exception.GenericJDBCException: A result was returned when none was expected.

It seems to work with procedures but I can't use it because the version is 10. SimpleJDBCCall didn't work either. I can't use queryForObject as it requires a RowMapper. What would be the better approach?

Answer 1

I find this in the documentation:

The escapeSyntaxCallMode connection property controls how the driver transforms the call syntax to invoke functions or procedures.

The default mode, select, supports backwards compatibility for existing applications and supports function invocation only. This is required to invoke a void returning function.

So you would call it like a stored procedure. Here is a complete code sample using JDBC:

Class.forName("org.postgresql.Driver");

java.sql.Connection conn =
    java.sql.DriverManager.getConnection(
        "jdbc:postgresql://127.0.0.1/test?user=laurenz&escapeSyntaxCallMode=select"
    );

java.sql.CallableStatement stmt = conn.prepareCall("{ call mumble(?) }");
stmt.setInt(1, 42);
stmt.execute();
stmt.close();

conn.close();