Using "lower" and "upper" aggregate functions on JOOQ Postgres range types

user_10815141 0
Viewed 35

I'm using the Postgres numrange type with JOOQ as defined here and want to call the lower/upper aggregate function on the selected ranges. My understanding is that these functions are not actually implemented in the jooq-postgres-extensions module and that I somehow have to implement this myself. Reading through this blog post, the author mentions that these functions have to be implemented yourself and he gives some examples:

static <T extends Comparable<T>> Condition 
    rangeContainsElem(Field<Range<T>> f1, T e) {
    return DSL.condition("range_contains_elem({0}, {1})", f1, val(e));
}
 
static <T extends Comparable<T>> Condition 
    rangeOverlaps(Field<Range<T>> f1, Range<T> f2) {
    return DSL.condition("range_overlaps({0}, {1})", f1, val(f2, f1.getDataType()));
}

However, he does not show any implementation of the lower/upper functions. How are these functions implemented?

Ideally, the end-goal would be to be able to do something like this, where the lower and upper bound of a column of ranges is retrieved:

val rangeMetadata = create.select(
         BigDecimalRange(
             max(upper(RANGE_PARAMETER.VALUE)),
             true,
             min(lower(RANGE_PARAMETER.VALUE)),
             true
         )
     )
     .from(RANGE_PARAMETER)
     .fetch()
jooq postgresql java
1 Answers

There isn't a big difference between defining your rangeOverlaps() and a lower() or upper() aggregate function, you can do it exactly the same way. In order to map that data directly into the BigDecimalRange type, you can use:

create.select(
          row(
              max(upper(RANGE_PARAMETER.VALUE)),
              min(lower(RANGE_PARAMETER.VALUE))
          ).mapping { u, l -> bigDecimalRange(u, true, l, true) }
      )
      .from(RANGE_PARAMETER)
      .fetch()

A note regarding:

My understanding is that these functions are not actually implemented in the jooq-postgres-extensions module and that I somehow have to implement this myself

There isn't a big reason why this shouldn't be done. It's just a task that hasn't been implemented yet. I've created an issue for this. Could be useful to support this out of the box: #13828

How to create a plain SQL templating Field<T>

Apparently, there seems to be a difficulty to going from the plain SQL templating Condition (which you already have) to a Field<T>, but it's just the same thing:

public static Field<BigDecimal> upper(Field<? extends BigDecimalRange> f) {
    return DSL.field("upper({0})", SQLDataType.NUMERIC, f);
}

See also:

user_5217990
Related