Using an INSERT RETURNING as a TableLike?

[Daniel Einspanjer]

I'm trying to build the following query in the DSL:

SELECT id
FROM (
  INSERT INTO foo(natural_key)
  VALUES ('aKey')
  ON CONFLICT DO NOTHING
  RETURNING id
) ins
WHERE id IS NOT NULL
UNION DISTINCT
SELECT id
FROM foo
WHERE natural_key = 'aKey';

I'm having trouble getting a TableLike object out of the insert, with or without the ON CONFLICT.  Is this doable?

[Lukas Eder]

Hi Daniel,

This is, very unfortunately, not doable yet. The relevant issue is this one here:

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

There is currently no simple workaround, short of resorting to constructing the outer query with plain SQL, and wrapping the nested queries in templates.

Note, I think that your suggested syntax isn't possible in PostgreSQL either. You'd have to put your "ins" query in a common table expression:

WITH ins AS (

  INSERT INTO foo(natural_key)

  VALUES ('aKey')

  ON CONFLICT DO NOTHING

  RETURNING id

)

SELECT id

FROM ins

WHERE id IS NOT NULL

UNION DISTINCT

SELECT id

FROM foo

WHERE natural_key = 'aKey';

Thanks,

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Daniel Einspanjer]

Okay, thank you for the update.  And yes, absolutely right, that insert does have to be in a CTE.  I just flubbed the example.

-Daniel

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.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/QsjcRa8NnnY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.