How to write non-returning postgresql CTEs in Jooq

Question

In Jooq, I can use the DSL#name(...) method to write CTEs, including INSERT / UPDATE / DELETE queries that have a RETURNING clause

e.g.

name("my_cte").as(
    update(MY_TABLE)
        // ...
    .returning(MY_TABLE.ID)
)

But I get a type mismatch at compile time if I remove the .returning(...) part

Is there a way around that ?

Answer 1

This isn't possible in jOOQ. You must supply a RETURNING clause for a DML statement to be usable in a CTE. You could try creating a feature request for this, but I don't think this will be prioritised. The utility of this approach seems negligible, compared to a much cleaner approach where you'd use a stored procedure for the same thing, either written in native PL/pgSQL or with jOOQ's procedural language support.