如何在返回 JSON 类型的 jOOQ 中执行 PL/pgSQL 用户定义函数的 SELECT 查询?

我在寻找将 PL/pgSQL 中的用户定义函数转换为 jOOQ 代码的正确方法时遇到问题.我在 PL/pgSQL 中的用户定义函数返回 JSON 类型，我需要在 jOOQ 中以某种方式调整/转换它.我在 Google 上搜索过示例，但没有找到.

I'm having issue with finding right way to cast my user-defined function in PL/pgSQL into jOOQ code. My user-defined function in PL/pgSQL returns JSON type and I need to somehow adjust/cast it in jOOQ. I've Googled examples, but found none.

以防万一这是我在 PL/pgSQL 中的用户定义函数:

Just in case here is my user-defined function in PL/pgSQL:

create or replace function public.get_order_by_order_id(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';

...还有另一个使用上面列出的函数.

...and another one which is using function listed above.

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操作并在我的jOOQ中返回JSON对象代码.

Both functions has been successfully code-generated in my Maven project and last one get_all_orders() is needed to perform SELECT operation on it and return JSON object in my jOOQ code.

这里是在Keys.java、DefaultCatalog.java等类中生成的**.jooq包中的Routines.java类:

Here is Routines.java class in **.jooq package which is generated among Keys.java, DefaultCatalog.java and other classes:

/**
 * 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 包

And here is my GetAllOrders.java routine class located in **.jooq.routines package

/**
 * 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 代码:

Finally, this is my jOOQ code for performing SELECT query in jOOQ:

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

... qe.query() 方法的定义如下:

...definition of qe.query() method is given below:

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

产生的问题:

"类型不匹配:无法从 Future < 转换对象 >至未来JsonObject >"

"类型不匹配:无法从 Future < 转换查询结果 >至未来对象 >"

顺便说一句，我需要提一下这是vertx-jooq 实现，使用 jOOQ 3.13.1.
非常感谢任何帮助/建议.

BTW, I've need to mention this is vertx-jooq implementation which uses jOOQ 3.13.1.
Any help/suggestion is greatly appreciated.

更新:
根据这里的要求，缺少类型和签名 transaction() 方法的第一个(更多信息

UPDATE:
As requested here are missing types and signatures 1st for transaction() method (more info here )

/**
     * 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()(更多信息可用这里 ):

...and executeAny() (more info available here ):

 /**
     * 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

更新 2:
这是我在 JSON 类型的 PL/pgSQL 中创建的 get_all_orders() 函数的结果:

{
  "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
        }
      ]
    }
  ]
}