PostgreSQL function + cast in jOOQ

Question

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.

Very interesting ordering technique, btw... Finally, a use case for arrays in PostgreSQL :-) — Lukas Eder, Dec 06 '14 at 19:01
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 ` — 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

Lukas Eder · Accepted Answer · 2014-12-06T19:06:02.420

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

PostgreSQL function + cast in jOOQ

1 Answers1

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

Create a re-usable function:

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

Put it together