Search code examples
postgresqljooq

Unnest enum_range on Postgres with jOOQ

I'm trying to unnest an enum type on Postgres with jOOQ, so that I can use it for a cross-join in a with clause.

What I try to write is an expression like

WITH weekdays as (
  SELECT unnest(enum_range(NULL::week_days)) AS day
)
...
SELECT ...
FROM users
CROSS JOIN weekdays
...

The WITH statement and CROSS JOIN are just for context.

What I'm really struggling with is the SELECT unnest(enum_range(NULL:week_days)) AS day expression (week_days being an enum with values mon, tue, etc). I just can't figure out how to specify it in jOOQ.

Solution

  • SELECT unnest(...) is just a weird, PostgreSQL specific alternative syntax for SELECT * FROM unnest(...). The former isn't supported by jOOQ, the latter is. See this feature request for details:

    Now, enum_range(NULL::weekdays) is a PostgreSQL specific function, and the fact that it works is a bit weird, but why not. You can point the code generator the the pg_catalog schema to generate the enum_range() function, or you just use plain SQL templating for the unavailable parts, e.g.

    select(DSL.field(
    "unnest(enum_range(NULL::week_days))", 
    SQLDataType.VARCHAR.asEnumDataType(WeekDays.class)
))

    Assuming you've used the code generator to get a WeekDays class for your enum.