Since Procedures were introduced in PG 11, the workaround to invoke them with JDBC is to send the native "CALL proc()" SQL and let it be treated as a SQL statement, not a specific stored routine invocation.
1) When using transaction control inside the stored proc, an exception is generated if autoCommit is false - see example code attached.
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid transaction termination
2) Output parameters are not mapped as parameters, and app code cannot use registerOutputParameter or getInt() style retrieval. Instead, outputs are left in the result set and app code must retrieve the result and pull, creating a big difference between how Procedures and Functions are invoked.
I propose improving support for procedures. Either:
(1) add support for "CALL proc()" to be treated as a routine invocation so that output parameters can be registered, no begin transaction is silently sent from driver, and calling a procedure and calling a function would be very similar (only differing in function still using the {call} escape syntax.
or
(2) change the {call} syntax to optionally support procedures. {? = call} would still be mapped to functions. Add a connection setting to control this change, and make default false, so that default stays backwards compatible with pre pg11 functionality.
