Strongly-typed Record<R> RETURNING statement

[jacekgajek]

Use case

I'm using PostgreSQL Currently

        return dsl.insertInto(MYTABLE)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .returning(MYTABLE)
            .fetchOne()

Runs two statements: first insert, then select.

returningResult uses RETURNING, but there is no way to get a full record.

        return dsl.insertInto(MYTABLE)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .set(...)
            .returningResult(MYTABLE) // returns a generic Record with a field of type "MYTABLE"
            .fetchOne()

Possible solution

1. a regular .returning() might also use RETURNING statement.
2. add an asterisk overload for .returningResult()

Possible workarounds

1. )Call .returning() inside a transaction.

            .returningResult(*MY_TABLE.fields())
            .fetchOne { it.into(MY_TABLE) }

jOOQ Version

Jooq 3.19.7

Database product and version

PostgreSQL 16.1

Java Version

openjdk 21.0.2

JDBC / R2DBC driver name and version (include name if unofficial driver)

org.postgresql:postgresql:42.7.3

[lukaseder]

Runs two statements: first insert, then select.

I don't think jOOQ will do this with proper SQLDialect.POSTGRES configuration. Can you please show what you're doing with an MCVE? We have a template here: https://github.com/jOOQ/jOOQ-mcve

1. a regular .returning() might also use RETURNING statement.

That's what should be done. The only difference between returning() and returningResult() is the shape of the resulting record(s). There's no difference in generated SQL

2. add an asterisk overload for .returningResult()

Why would you need it?

1. )Call .returning() inside a transaction.

I'm not sure how a transaction would change anything, but I'm sure your MCVE will be able to help clarify.