postgresqljooqcomposite-key

How to update an individual subfield for on a composite column on PostgreSQL using jOOQ?

On the PostgreSQL documentation there's an example about how to update a subfield of a composite type:

We can update an individual subfield of a composite column:

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

Assuming we are using jOOQ and code generation, how would that translate to jOOQ code?

Solution

  • As of jOOQ 3.15, there's no API to allow for accessing members of UDTs yet: https://github.com/jOOQ/jOOQ/issues/228

    As always, you can easily work around this missing functionality by using plain SQL templating:

    Field<Integer> r = field("({0}).{1}", COMPLEX_TYPE.R.getDataType(), 
    MYTAB.COMPLEX_COL.getUnqualifiedName(), 
    COMPLEX_TYPE.R.getUnqualifiedName()
);

ctx.update(MYTAB)
   .set(r, r.plus(1))
   .where(...)
   .execute();