Re: Invoking a function within a batch statement

Correct syntax is {call procedure(params)}. I think you forgot "call".

Пн, 22 серп. 2016 23:38 користувач Evan Meagher <evan.meagher@gmail.com> пише:

> Let us know how the call syntax works out for you

Using `{append_to_time_series(...)}` results in a o.p.u.PSQLException with message 'ERROR: syntax error at or near "{"'

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

That works! In fact, on 9.4.1210-SNAPSHOT, it works with and without the @GetGeneratedKeys annotation.

I guess I'll just stay tuned for a stable 9.4.1210 release and make do with the snapshots in the meantime. Thanks to all for the responses, and thanks Vladimir for the workaround!

On Fri, Aug 19, 2016 at 8:00 AM, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:

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

--

Evan Meagher