Re: Invoking a function within a batch statement - Mailing list pgsql-jdbc

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