jOOQ has a few PostgreSQL specific functions in the class PostgresDSL
(including arrayLength()) and also has the functions DSL#unnest() and
DSL#generateSeries(). However, these methods are not applicable in the
context of your query, as they return a Table type, which jOOQ doesn't
support as an input to select().
With jOOQ you can however typically solve problems like this by relying on
the SQL templating mechanisms. So this would be one way to do it:
String[] values = new String[] { "125d7988-14d7-4898-bf89-0b54eb8aa317",
"63028718-bf33-4e3f-a6bd-ef7c0e941205" };
ctx.with("cta1").as(
select(array(values).as("a"))
)
.with("cta2").as(
select(
function("unnest", String.class,
field("a")).cast(SQLDataType.UUID).as("id"),
function("generate_series", Integer.class, val(1),
function("array_length", Integer.class, field("a"), val(1))).as("idx")
)
.from("cta1")).
select(field("id"), field("idx")).from("cta2").fetch();
Note that I omitted the "public.deck" part of your query.
Hope this helps,
Knut
On Fri, Aug 9, 2019 at 4:11 AM Debapriya Patra <[email protected]>
wrote:
> The below values will be pass as the binding value to the query.
>
>
> '125d7988-14d7-4898-bf89-0b54eb8aa317',
>
> '63028718-bf33-4e3f-a6bd-ef7c0e941205',
>
> 'a1266416-4e7d-4426-9b9d-e5c66d15ffbc',
>
> '940b3c50-7a5e-49d5-8105-b3ddfdfde9c1',
>
> '86a1647f-a24d-45c0-ae04-0e19c2fd10e2'
>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/7bcd822f-1d43-4d1b-8cb3-351138d0e682%40googlegroups.com
> <https://groups.google.com/d/msgid/jooq-user/7bcd822f-1d43-4d1b-8cb3-351138d0e682%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAFx%3DKgekLeQKEJ6PKzv82tJ%2B4cAd4CiaMKwjoiOAHC07az0rLQ%40mail.gmail.com.
