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.
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';
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.
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();
}
}
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);
}
}
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())
));
...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);
}
Produced problems:
"Type mismatch: cannot convert from Future < Object > to Future < JsonObject > "
"Type mismatch: cannot convert from Future < QueryResult > to Future < Object > "
BTW, I've need to mention this is vertx-jooq implementation which uses jOOQ 3.13.1.
Any help/suggestion is greatly appreciated.
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
}
...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();
}
...and here for ReactiveQueryResult
UPDATE2:
This is my result of get_all_orders() function created in PL/pgSQL of JSON type:
{
"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
}
]
}
]
}
