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.
