Hello Max,
Unfortunately, this is currently not possible as the CTE syntax requires an
org.jooq.Select type, instead of an org.jooq.ResultQuery type. There's a
pending feature request to improve this:
https://github.com/jOOQ/jOOQ/issues/4474
In particular, some databases like PostgreSQL also allow DML statements as
CTE bodies:
WITH cte AS (
INSERT INTO table (a, b)
VALUES (1, 2)
RETURNING *
)
SELECT * FROM cte
So, there's a whole set of API changes that we'd like to implement to
enable these features.
One workaround I can think of right now is for you to implement a
reflection proxy that implements org.jooq.Select and delegates all calls to
a backing plain SQL ResultQuery. It doesn't sound like a very robust
workaround, though.
Another option is not to use a CTE, but a derived table, where plain SQL is
perfectly possible.
Finally, if you externalise the CTE into a view, you can re-use the SQL
string even more easily.
I hope this helps,
Lukas
2015-11-06 1:15 GMT+01:00 Max Kremer <mkre...@trialfire.com>:
> Hello fellow JOOQers
>
> I have a need to mix raw SQL strings with SQL generated by the DSL.
>
> I have a CTE which is far too cumbersome to express in jooq - for the sake
> of simplicity however lets say my cte is
> SELECT foo FROM bar
>
> I would like to do something like this
>
> String myCte = "SELECT foo FROM bar";
>
> DSL.with("mycte").as(myCte)
> .select( ... )
> .from(... )
> .where( ... )
> .fetch().steam().
> //etc..
>
>
>
> So basically what I want to do is mix SQL strings and the DSL. Doesn't
> seem like this is possible because with("mycte").as() takes Select<?> as a
> param and not a String.
>
> Is what I want to do possible or just too crazy?
>
> Thanks,
>
> Max
>
> --
> 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+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
