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.
