Search code examples
javapostgresqljooq

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

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()
Solution

  • 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: