2

How can this statement be expressed with jOOQ?

SELECT version FROM abc ORDER BY string_to_array(version, '.', '')::int[] desc limit 1

I am struggling with the function and cast combination.

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • Very interesting ordering technique, btw... Finally, a use case for arrays in PostgreSQL :-) – Lukas Eder Dec 06 '14 at 19:01
  • 1
    This is to sort version numbers directly in the DB. It's Postgres and it's awesome :-) – Axel Fontaine Dec 06 '14 at 19:03
  • Yes, PostgreSQL seems to enhance the SQL:2011 standard, which specifies in `8.2 `, `General Rules`, `1) b) ii)` that the `<` comparison operation is not defined for arrays... I wonder if PostgreSQL makes such guarantees, though... [it seems to be the case](http://stackoverflow.com/a/15674585/521799) – Lukas Eder Dec 06 '14 at 19:15
  • [yep, it is](http://www.postgresql.org/docs/9.3/static/functions-array.html) – Lukas Eder Dec 06 '14 at 19:22

1 Answers1

6

You have various options.

Be lazy and wrap everything in a single plain SQL expression:

Field<Integer[]> f1 = 
    DSL.field("string_to_array(version, '.', '')::int[]", Integer[].class);

Create a re-usable function:

Field<Integer[]> stringToIntArray(Field<String> arg1, String arg2, String arg3) {
    return DSL.field("string_to_array({0}, {1}, {2})::int[]", Integer[].class,
        arg1, DSL.val(arg2), DSL.val(arg3));
}

// and then...
Field<Integer[]> f2 = stringToIntArray(ABC.VERSION, ".", "");

Use the code generator to generate the built-in function, and cast it explicitly:

Field<Integer[]> f3 = Routines.stringToArray(ABC.VERSION, DSL.val("."), DSL.val(""))
                              .cast(Integer[].class);

The built-in function is part of the pg_catalog schema in the postgres database.

Put it together

DSL.using(configuration)
   .select(ABC.VERSION)
   .from(ABC)
   .orderBy(fN.desc()) // place any of f1, f2, f3 here
   .limit(1)
   .fetch();
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509