Jooq update return value

Question

I try to update rows in table and get results after. If I do

dsl.update(TABLE)
   .set(TABLE.ROW, newRow)
   .where(TABLE.ROW_2.eq(
            dsl.select(ANOTHER_TABLE.ID)
               .from(ANOTHER_TABLE)
               .where(ANOTHER_TABLE.GUID.eq(guid))
   )).execute()

it returns 1. But if I do

dsl.update(TABLE)
   .set(TABLE.ROW, newRow)
   .where(TABLE.ROW_2.eq(
            dsl.select(ANOTHER_TABLE.ID)
               .from(ANOTHER_TABLE)
               .where(ANOTHER_TABLE.GUID.eq(guid))
   )).returningResult(TABLE.ROW_3).fetchOne()

it returns empty result. But I want to get TABLE.ROW_3 after update. Whats the problem?

Answer 1

Vertica doesn't support UPDATE .. RETURNING like PostgreSQL, as can be seen in the Vertica docs: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/UPDATE.htm

The jOOQ documentation of UpdateReturningStep::returningResult reflects this by not listing SQLDialect.VERTICA in its @Support annotation:

@Support({AURORA_POSTGRES,COCKROACHDB,DB2,FIREBIRD,ORACLE,POSTGRES,SQLSERVER})

There's currently no workaround for this. If you want to avoid using such API, you could use the jOOQ-checker module in your build to produce compilation errors whenever you use API that is not supported by VERTICA