我正在搜索如何复制层次树的节点并将更改应用到连接表中，我在其他问题中找到了部分答案，如树复制的Postgresql copy data within the tree table（在我的例子中，我只复制子节点而不复制根节点）和同时在多个表中插入数据的PostgreSQL - Insert data into multiple tables simultaneously，但我没有设法将它们混合使用。
我想：
1.从fields表生成新节点ID
1.在data_versions表中插入新字段ID
1.使用data_versions表中的data_id在fields表中插入新节点
注：fields和data_versions表之间存在循环引用。
见下图：

下面是一个有效的查询，但是没有在data_versions表中插入。它只是一个浅副本（保持相同的data_id），而我想要一个深副本：

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;

下面是我正在处理的查询草稿，用于在data_versions表中插入数据，结果是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;