Thanks Lucas, I was able to use selectFrom as you suggested but then I got stuck on this issue: https://github.com/jOOQ/jOOQ/issues/4474
I think that means I'm at a dead-end for now as I can't use jOOQ for data-modifying CTEs. On Tuesday, February 14, 2017 at 12:09:37 PM UTC, Lukas Eder wrote: > > Hi Emrul, > > Indeed, jOOQ currently doesn't support the VALUES() constructor to be used > as a Select<?> reference. Maybe, we should. I've created a feature request > for this: > https://github.com/jOOQ/jOOQ/issues/5871 > > Of course, you can always simply select from the values constructor > explicitly, e.g.: > > selectFrom(values(...)) > > > and put that in your CTE. > Hope this helps, > Lukas > > 2017-02-14 12:59 GMT+01:00 Emrul Islam <[email protected] <javascript:>>: > >> >> Hi, >> >> I'm trying to construct the following PostgreSQL query in jOOQ & Kotlin: >> >> >> WITH "data/0_d" ("emailAddress","displayName","name","_id") AS ( VALUES >> ('[email protected] <javascript:>','a','b',id_generator()) >> ), >> >> "data/0/0_d" ("_id","_in_members") AS ( VALUES >> (decode('EtNtO3iAAAM=', 'base64')::BIGINT,ARRAY(SELECT _id FROM >> "data/0_d")), >> (decode('EtNt1NqAAAQ=', 'base64')::BIGINT,ARRAY(SELECT _id FROM >> "data/0_d")) >> ), >> "data/0/0_update" AS (UPDATE "Role" SET >> _id="data/0/0_d"._id,_in_members="data/0/0_d"._in_members FROM "data/0/0_d" >> WHERE "data/0/0_d"._id = "Role"._id >> RETURNING "Role"._id), >> "data/0_insert" AS (INSERT INTO "User" AS >> "data/0_t"("emailAddress","displayName","name","_id", _out_roles) SELECT >> "emailAddress","displayName","name","_id", (SELECT array_agg(_id) FROM >> "data/0/0_update") AS _out_roles FROM "data/0_d" >> RETURNING "data/0_t"._id) >> SELECT "data/0_insert"._id from "data/0_insert" >> >> >> But I'm not able to construct the first 2 lines (*(AS (VALUES...*) >> >> I am following the example for CTEs here: >> https://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/with-clause/ >> and when I do name("t1").fields("f1", "f2").as(*values(...)*) I get an >> Syntax error because 'as' requires either a Select or WindowSpecification. >> >> Is there a way forward? >> >> -- >> 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] <javascript:>. >> 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
