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

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?

CodePudding user response：

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