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
– Lukas Eder, Commented Dec 6, 2014 at 19:01
This is to sort version numbers directly in the DB. It's Postgres and it's awesome :-) — Axel Fontaine
– Axel Fontaine, Commented Dec 6, 2014 at 19:03
Yes, PostgreSQL seems to enhance the SQL:2011 standard, which specifies in 8.2 <comparison predicate>, 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 — Lukas Eder
– Lukas Eder, Commented Dec 6, 2014 at 19:15

Lukas Eder · Accepted Answer · 2014-12-06 19:06:02Z

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

Collectives™ on Stack Overflow

PostgreSQL function + cast in jOOQ

1 Answer 1

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

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related