CODEWITHSUNDEEP
postgresqljooq
Burg
Burg

Reputation: 2048

How to use an insert...returning statement as a "table-like" query in jooq?

I am writing a SQL query in the form of INSERT INTO ... RETURNING, and then want to aggregate the results. This is possible with postgres:

DROP TABLE IF EXISTS __test;
CREATE TEMPORARY TABLE __test
(
    id    int  not null,
    value text not null
);

WITH things AS (
    INSERT INTO __test
        VALUES (1, 'foo'),
               (2, 'bar'),
               (2, 'baz')
        RETURNING *
)
SELECT id,
       array_agg(value)
FROM things
GROUP BY id;

Though I am struggling to find a way to do this with jooq, as an InsertResultStep does not implement Select, nor does it implement Table or TableLike interfaces. Is there a canonical way to achieve this, or should I be looking for a workaround?

val insertQuery = DSL
     .insertInto(otherTable)
     .select(
         DSL.select(recordPkColumns)
         .from(recordTable)
         .crossJoin(permValues)
         .where(filter(command.command))
     )
     .onConflictDoNothing()
     .returning()

DSL.select()
    .from(insertQuery) // problem is here!

Upvotes: 2

Views: 369

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

As of jOOQ 3.11 (and soon 3.12), this is not yet possible in jOOQ without resorting to plain SQL templating.

The relevant feature request is here: https://github.com/jOOQ/jOOQ/issues/4474

Upvotes: 1

Related Questions