JSONタイプを返すjOOQでPL / pgSQLユーザー定義関数のSELECTクエリを実行する方法

PL / pgSQLのユーザー定義関数をjOOQコードにキャストする正しい方法を見つけることに問題があります。PL / pgSQLのユーザー定義関数はJSON型を返し、どういうわけかjOOQで調整/キャストする必要があります。例をGoogleで検索しましたが、見つかりませんでした。

念のため、ここにPL / pgSQLのユーザー定義関数を示します。

create or replace function public.get_order_by_order_id8(o_id bigint) returns json as
$BODY$
DECLARE
    total_oi_price double precision;
    book_price double precision;
    total_price double precision;
    oi_amount integer;
    order_items json;
    item_recs RECORD;
    book_json json;
    single_order_item json;
    found_order "vertx-jooq-cr".public.orders;
    found_user json;
    _item_id bigint;
    item_array json[];
BEGIN
    select * into found_order
    from "vertx-jooq-cr".public.orders
    where order_id = o_id;

    select json_build_object('user_id', "vertx-jooq-cr".public.users.user_id, 'username', "vertx-jooq-cr".public.users.username)
    into found_user
    from "vertx-jooq-cr".public.users
    INNER JOIN "vertx-jooq-cr".public.orders as o USING (user_id)
    WHERE o.order_id = o_id;

    total_price = 0.00;

    FOR item_recs IN SELECT *
        FROM public.order_item AS oi WHERE oi.order_id = o_id
    LOOP
        select public.get_book_by_book_id(item_recs.book_id) into book_json
        from public.order_item
        where public.order_item.order_item_id IN (item_recs.order_item_id);

        select price INTO book_price FROM book AS b WHERE b.book_id = item_recs.book_id;
        select amount INTO oi_amount FROM order_item AS oi WHERE oi.amount = item_recs.amount;
        
        total_oi_price = book_price * oi_amount;

        SELECT json_build_object('order_item_id', item_recs.order_item_id,
        'amount', item_recs.amount,
        'book', book_json,
        'order_id', item_recs.order_id,
        'total_order_item_price', trunc(total_oi_price::double precision::text::numeric, 2)) INTO single_order_item;
        total_price := total_price + total_oi_price;
        item_array = array_append(item_array, single_order_item);
    END LOOP;
    order_items = array_to_json(item_array);

    return (select json_build_object(
        'order_id', found_order.order_id,
        'total_price', trunc(total_price::double precision::text::numeric, 2),
        'order_date', found_order.order_date,
        'user', found_user,
        'order_items', order_items
    ));

end;
$BODY$
LANGUAGE 'plpgsql';

...そして上記の関数を使用している別のもの。

CREATE OR REPLACE FUNCTION get_all_orders() RETURNS JSON AS
$BODY$
DECLARE
    single_order RECORD;
    single_order_json json;
    orders_array json[];
BEGIN

    FOR single_order IN SELECT * FROM public.orders ORDER BY order_id
    LOOP
        SELECT get_order_by_order_id(single_order.order_id) INTO single_order_json;
        orders_array = array_append(orders_array, single_order_json);
    END LOOP;

    return (select json_build_object(
        'orders', orders_array
    ));
END;
$BODY$
LANGUAGE 'plpgsql';

両方の関数は私のMavenプロジェクトで正常にコード生成されており、最後の関数はそれget_all_orders() に対してSELECT操作を実行してJSONオブジェクトをjOOQコードで返すために必要です。

以下は、他のクラスRoutines.java間Keys.javaで生成される**。jooqパッケージDefaultCatalog.javaのクラスです。

/**
 * Convenience access to all stored procedures and functions in public
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {

    /**
     * Call <code>public.get_all_orders</code>
     */
    public static JSON getAllOrders(Configuration configuration) {
        GetAllOrders f = new GetAllOrders();

        f.execute(configuration);
        return f.getReturnValue();
    }

    /**
     * Get <code>public.get_all_orders</code> as a field.
     */
    public static Field<JSON> getAllOrders() {
        GetAllOrders f = new GetAllOrders();

        return f.asField();
    }

// other methods left out for code brevity

/**
     * Call <code>public.get_order_by_order_id</code>
     */
    public static JSON getOrderByOrderId(Configuration configuration, Long oId) {
        GetOrderByOrderId f = new GetOrderByOrderId();
        f.setOId(oId);

        f.execute(configuration);
        return f.getReturnValue();
    }

    /**
     * Get <code>public.get_order_by_order_id</code> as a field.
     */
    public static Field<JSON> getOrderByOrderId(Long oId) {
        GetOrderByOrderId f = new GetOrderByOrderId();
        f.setOId(oId);

        return f.asField();
    }

    /**
     * Get <code>public.get_order_by_order_id</code> as a field.
     */
    public static Field<JSON> getOrderByOrderId(Field<Long> oId) {
        GetOrderByOrderId f = new GetOrderByOrderId();
        f.setOId(oId);

        return f.asField();
    }
}

そしてGetAllOrders.java、これは**.jooq.routinesパッケージにある私のルーチンクラスです

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<JSON> {

    private static final long serialVersionUID = 917599810;

    /**
     * The parameter <code>public.get_all_orders.RETURN_VALUE</code>.
     */
    public static final Parameter<JSON> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false);

    /**
     * Create a new routine call instance
     */
    public GetAllOrders() {
        super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON);

        setReturnParameter(RETURN_VALUE);
    }
}

最後に、これはjOOQでSELECTクエリを実行するための私のjOOQコードです。

Future<JsonObject> ordersFuture = queryExecutor.transaction(qe -> qe
                .query(dsl -> dsl
                    .select(new Routines().getAllOrders())
            )); 

... qe.query（）メソッドの定義を以下に示します。

@Override
    public <R extends Record> Future<QueryResult> query(Function<DSLContext, ? extends ResultQuery<R>> queryFunction) {
        return executeAny(queryFunction).map(ReactiveQueryResult::new);
    }

発生する問題：

"型の不一致：Future <オブジェクト>からFuture <JsonObject>に変換できません"

"型の不一致：Future <QueryResult>からFuture <Object>に変換できません"

ところで、私はこれがjOOQ 3.13.1を使用するvertx -jooq実装であることを言及する必要があります。
どんな助け/提案でも大歓迎です。

更新：
ここでリクエストされたように、型とシグネチャがtransaction()メソッドの最初にありません（詳細はこちら）

/**
     * Convenience method to perform multiple calls on a transactional QueryExecutor, committing the transaction and
     * returning a result.
     * @param transaction your code using a transactional QueryExecutor.
     *                    <pre>
     *                    {@code
     *                    ReactiveClassicGenericQueryExecutor nonTransactionalQueryExecutor...;
     *                    Future<QueryResult> resultOfTransaction = nonTransactionalQueryExecutor.transaction(transactionalQueryExecutor ->
     *                      {
     *                          //make all calls on the provided QueryExecutor that runs all code in a transaction
     *                          return transactionalQueryExecutor.execute(dslContext -> dslContext.insertInto(Tables.XYZ)...)
     *                              .compose(i -> transactionalQueryExecutor.query(dslContext -> dslContext.selectFrom(Tables.XYZ).where(Tables.XYZ.SOME_VALUE.eq("FOO")));
     *                      }
     *                    );
     *                    }
     *                    </pre>
     * @param <U> the return type.
     * @return the result of the transaction.
     */
    public <U> Future<U> transaction(Function<ReactiveClassicGenericQueryExecutor, Future<U>> transaction){
        return beginTransaction()
                .compose(queryExecutor -> transaction.apply(queryExecutor) //perform user tasks
                        .compose(res -> queryExecutor.commit() //commit the transaction
                                .map(v -> res))); //and return the result
    }

...そしてexecuteAny()（詳細はこちらから入手可能）：

 /**
     * Executes the given queryFunction and returns a <code>RowSet</code>
     * @param queryFunction the query to execute
     * @return the results, never null
     */
    public Future<RowSet<Row>> executeAny(Function<DSLContext, ? extends Query> queryFunction) {
        Query query = createQuery(queryFunction);
        log(query);
        Promise<RowSet<Row>> rowPromise = Promise.promise();
        delegate.preparedQuery(toPreparedQuery(query)).execute(getBindValues(query),rowPromise);
        return rowPromise.future();
    }

...そしてReactiveQueryResultについてはこちら

UPDATE2：
これは、get_all_orders()JSONタイプのPL / pgSQLで作成された関数の結果です。

{
  "orders": [
    {
      "order_id": 1,
      "total_price": 29.99,
      "order_date": "2019-08-22T10:06:33",
      "user": {
        "user_id": 1,
        "username": "test"
      },
      "order_items": [
        {
          "order_item_id": 1,
          "amount": 1,
          "book": {
            "book_id": 1,
            "title": "Harry Potter and the Philosopher's Stone",
            "price": 29.99,
            "amount": 400,
            "is_deleted": false,
            "authors": [
              {
                "author_id": 4,
                "first_name": "JK",
                "last_name": "Rowling"
              }
            ],
            "categories": [
              {
                "category_id": 2,
                "name": "Lyric",
                "is_deleted": false
              }
            ]
          },
          "order_id": 1,
          "total_order_item_price": 29.99
        }
      ]
    },
    {
      "order_id": 2,
      "total_price": 29.99,
      "order_date": "2019-08-22T10:10:13",
      "user": {
        "user_id": 1,
        "username": "test"
      },
      "order_items": [
        {
          "order_item_id": 2,
          "amount": 1,
          "book": {
            "book_id": 1,
            "title": "Harry Potter and the Philosopher's Stone",
            "price": 29.99,
            "amount": 400,
            "is_deleted": false,
            "authors": [
              {
                "author_id": 4,
                "first_name": "JK",
                "last_name": "Rowling"
              }
            ],
            "categories": [
              {
                "category_id": 2,
                "name": "Lyric",
                "is_deleted": false
              }
            ]
          },
          "order_id": 2,
          "total_order_item_price": 29.99
        }
      ]
    }
  ]
}