Hi Emrul, I'm sorry I hadn't noticed that your other CTE were INSERT or UPDATE results. Yes, indeed, that doesn't work right now. Sorry about that
Lukas 2017-02-15 12:52 GMT+01:00 Emrul Islam <[email protected]>: > 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]>: >> >>> >>> 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]','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]. >>> 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. > -- 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.
