Return fiields in INSERT .. SELECT query

[Dmitry Krivenko]

Hi there! 

In case when I use a SELECT statement as the source of values for the INSERT statement I don't have ability to return the inserted record.    

I need to implement a query like this:

INSERT INTO test
    (text)
    SELECT '1234567890123456789'
    WHERE
        NOT EXISTS (
            SELECT id FROM test WHERE text = '1234567890123456789'
        ) returning id;

And I use this approach ( by the way, it would be great, if we had an ability to do it via InsertQuery) :

List<Param<?>> params = new LinkedList<>();
params.add(DSL.val("1234567890123456789"));
 
List<Field<?>> fields = new LinkedList<>(); 
fields.add(TEST.TEXT);

SelectConditionStep<Record1<TEXT>> notExistsSelect = context.select(TEST.TEXT).from(TEST).where(TEST.TEXT.eq("1234567890123456789"));
SelectConditionStep<Record> insertIntoSelect = context.select(params).whereNotExists(notExistsSelect);

context.insertInto(TEST, fields).select(insertIntoSelect).execute();

But I can't return inserted value, because Insert interface, which is returned by select(insertIntoSelect), doesn't have returning() method.

Are there any reason why it hasn't been implemented? Or maybe there is another approach to execute my query?

[Lukas Eder]

For the record, this question has been cross-posted also to Stack Overflow:

http://stackoverflow.com/q/29434194/521799

This is a known API limitation and is currently being tracked here:

https://github.com/jOOQ/jOOQ/issues/3779

The simplest workaround right now is to resort to plain SQL:

DSL.using(configuration)

   .fetch("insert into {0} ({1}) {2} returning {3}",

       TEST, TEST.TEXT, select, TEST.ID);

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Dmitry Krivenko]

Thanks!

[Lukas Eder]

This has now been implemented for jOOQ 3.7:

https://github.com/jOOQ/jOOQ/issues/3779

Cheers,

Lukas

[Dmitry Krivenko]

great! thank you!