Re: Invoking a function within a batch statement

Hello,

I'm having trouble invoking a PostgreSQL function within a prepared batch statement, and I'm wondering if it's possible that I'm encountering a limitation in the JDBC driver.

For context, my application uses jDBI v2.63.1 with version 9.4-1204-jdbc42 of the Postgres driver. I've implemented a feature which requires insertion into certain tables using a custom PL/pgSQL function. In order to issue batches of these queries within a single transaction, I'd like to use jDBI's `@SqlBatch` annotation. However, because Postgres functions are invoked using SELECT, they return a table-like result, so even though my PL/pgSQL function returns VOID, the queries fail in the JDBC driver because it's expecting a null result.

The jDBI-annotated DAO method in question:

    @SqlBatch(

      transactional=true,

      value="SELECT append_to_time_series(<arguments_redacted>)")

    public void insertV2(

      ...

    );

This results in a BatchUpdateException wrapping a PSQLException which indicates a root cause of an unexpected null result: https://gist.github.com/evnm/17a370c6ead2a2dfeaab0285e067897a

This issue is being tracked in a jDBI issue on GitHub, which links to some relevant discussion that took place on the jDBI mailing list. Any insight from the JDBC community would be greatly appreciated.

--

Evan Meagher