Re: Invoking a function within a batch statement

Evan>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.

Evan,

Can you please try the latest pgjdbc 9.4.1210-SNAPSHOT + @GetGeneratedKeys
near your @SqlBatch("select ...") kind of statement?

The idea is as follows:
1) jDBI would issue prepareStatement(..., Statement.RETURN_GENERATED_KEYS);
2) pgjdbc has recently learned to handle "return generated keys" better, so
that "return_generated_keys" would hint pgjdbc that it should expect some
response (including empty rowset), so it won't fail with "none was
expected".

Technically speaking, the question "if pgjdbc should fail when unexpected
row data comes in a response to a query" was raised (see
https://github.com/pgjdbc/pgjdbc/issues/488#issuecomment-237908650 ),
however historical behavior was just fail with "A result was returned when
none was expected"

The solution is to use proper API when executing statements that return
something. For instance: executeQuery, or use "generated keys" API.

Vladimir

>