如何使用JOOQ批量调用存储的函数？(How do you call a stored function in batch with JOOQ?)

如何使用JOOQ批量调用存储的函数？(How do you call a stored function in batch with JOOQ?)

我想批量调用多次存储函数。 我正在使用JOOQ 3.7.3和PostgreSQL 9.5。 我已尝试在批处理中使用select [function call]语句，但它会抛出以下异常PSQLException: A result was returned when none was expected 。

// exemplary 'select [function call]' context.batch(context.select(Routines.foo(someParam))).execute();

我发现没有其他方法可以批量调用JOOQ存储的函数。 我知道使用带有CallableStatement的原始JDBC是可能的，所以我假设它也应该可以使用JOOQ。

JOOQ可以批量调用存储的函数吗？ 如果是的话，该怎么做？

存储功能签名：

create function foo(param1 int, param2 int) returns boolean as $$ ... $$ language plpgsql

I want to call a stored function multiple times in batch. I'm using JOOQ 3.7.3 and PostgreSQL 9.5. I have tried using select [function call] statement in batch but it throws the following exception PSQLException: A result was returned when none was expected.

// exemplary 'select [function call]' context.batch(context.select(Routines.foo(someParam))).execute();

I have found no other way to call stored functions with JOOQ in batch. I know it is possible with raw JDBC with CallableStatement so I'm assuming it should also be possible with JOOQ.

Is it possible with JOOQ to call stored functions in batch? If yes, how to do it?

Stored function signature:

create function foo(param1 int, param2 int) returns boolean as $$ ... $$ language plpgsql

最满意答案

最新的9.4.1208 postgres jdbc驱动程序支持select [function call]语法。 我使用的是9.4.1205版本。

如果使用最新的（9.4.1208）jdbc驱动程序，如果存储的函数返回一些值，则可以使用以下语法：

Query query1 = context.select(Routines.foo(someParam)); Query query2 = context.select(Routines.foo(someParam)); context.batch(query1, query2).execute();

如果存储的函数返回void ，则可以使用：

Foo foo1 = new Foo(); foo1.setParam(someParam); Foo foo2 = new Foo(); foo2.setParam(someParam); context.batch(context.select(foo1.asField()), context.select(foo2.asField())).execute();

The select [function call] syntax is supported by the latest 9.4.1208 postgres jdbc driver. I was using 9.4.1205 version.

If you use the latest (9.4.1208) jdbc driver you can use the following syntax if the stored function returns some value:

Query query1 = context.select(Routines.foo(someParam)); Query query2 = context.select(Routines.foo(someParam)); context.batch(query1, query2).execute();

If the stored function returns void you can use:

Foo foo1 = new Foo(); foo1.setParam(someParam); Foo foo2 = new Foo(); foo2.setParam(someParam); context.batch(context.select(foo1.asField()), context.select(foo2.asField())).execute();