PostgreSQL递归CTE实现树形节点深拷贝及多表关联插入问题

我需要复制层级树形结构的节点，并将修改同步到关联表中。已从其他问题中找到部分解决方案：比如通过递归查询实现树形节点复制（仅复制子节点而非根节点），通过多表插入语法实现多表同时插入，但无法将两者结合。

我的核心需求如下：

• 从fields表生成新节点ID
• 将新字段ID插入data_versions表
• 将带有data_versions表中新生成data_id的节点插入fields表

注意：fields表与data_versions表存在循环引用。

现有可运行的浅拷贝查询 #

当前有一个能运行的查询，但仅实现了浅拷贝（保留原data_id，未插入data_versions表）：

WITH created_data AS (
    WITH RECURSIVE cte AS (
        SELECT *, nextval('fields_id_seq') new_id FROM fields WHERE parent_id = :source_field_id
        UNION ALL
        SELECT fields.*, nextval('fields_id_seq') new_id FROM cte JOIN fields ON cte.id = fields.parent_id
    )
    SELECT C1.new_id, C1.name, C1.field_type, C1.data_id, C2.new_id new_parent_id
    FROM cte C1 LEFT JOIN cte C2 ON C1.parent_id = C2.id
)

INSERT INTO fields (id, name, parent_id, field_type, data_id)
SELECT new_id, name, COALESCE(new_parent_id, :target_field_id), field_type, data_id FROM created_data
RETURNING id, name, parent_id, field_type, data_id;

出错的深拷贝草稿查询 #

我尝试编写了实现深拷贝的查询，但触发错误：WITH clause containing a data-modifying statement must be at the top level，代码如下：

WITH created_data AS (
    WITH cloned_fields AS (
        WITH RECURSIVE cte AS (
            SELECT *, nextval('fields_id_seq') new_id FROM fields WHERE parent_id = :source_field_id
            UNION ALL
            SELECT fields.*, nextval('fields_id_seq') new_id FROM cte JOIN fields ON cte.id = fields.parent_id
        )
        SELECT C1.new_id, C1.name, C1.field_type, C1.data_id, C2.new_id new_parent_id
        FROM cte C1 LEFT JOIN cte C2 ON C1.parent_id = C2.id
    ),
    cloned_data AS (
        INSERT INTO data_versions (value, author, field_id)
        SELECT d.value, d.author, c.new_id
        FROM cloned_fields c
        INNER JOIN data_versions d ON c.data_id = d.id
        RETURNING id data_id
    )

    SELECT cloned_fields.new_id, cloned_fields.name, cloned_fields.field_type, cloned_fields.new_parent_id, cloned_data.data_id
    FROM cloned_fields
    INNER JOIN cloned_data ON cloned_fields.data_id = cloned_data.id
)

INSERT INTO fields (id, name, parent_id, field_type, data_id)
SELECT new_id, name, COALESCE(new_parent_id, :target_field_id), field_type, data_id FROM created_data
RETURNING id, name, parent_id, field_type, data_id, value data;

内容的提问来源于stack exchange，提问作者KillianKemps