Re: Invoking a function within a batch statement

I think you should be able to use {call proc(params)} form, see
https://jdbc.postgresql.org/documentation/80/callproc.html

Best regards, Vitalii Tymchyshyn

Ср, 17 серп. 2016 10:24 користувач Evan Meagher <evan(dot)meagher(at)gmail(dot)com>
пише:

> Hello,
>
> I'm having trouble invoking a PostgreSQL function
> <https://www.postgresql.org/docs/9.4/static/sql-syntax-calling-funcs.html>
> 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 `(at)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
> <https://github.com/jdbi/jdbi/issues/447>, 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
>