PostgreSQL树表中复制数据

Question

我有一个具有树形结构的表格，列名为id，category和parent_id。

现在我需要将一个节点及其子节点复制到另一个节点，复制时，类别必须相同，但是具有新的id和parent_id。

我的输入将是要复制的节点和目标节点。

我已经在图片文件中解释了树形结构。

我需要一个函数来实现这个功能。 PostgreSQL版本为9.1.2。

  Column   |  Type   |                    Modifiers                    
-----------+---------+-------------------------------------------------
 id        | integer | not null default nextval('t1_id_seq'::regclass)
 category  | text    | 
 parent_id | integer | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_t1_1" FOREIGN KEY (parent_id) REFERENCES t1(id)
Referenced by:
    TABLE "t1" CONSTRAINT "fk_t1_1" FOREIGN KEY (parent_id) REFERENCES t1(id)

- MAHI

---

帮我写一个函数，它可以接收两个输入并完成任务... - MAHI

所以基本上你想要克隆一个子树？这很困难。我正在考虑。 - wildplasser

是的，这很难...我已经尝试了许多函数，但都没有用。 - MAHI

我认为这是可以做到的，但是你需要在表格中至少有一个“状态”位。你有多余的列吗？（也可以在另一个表格中完成） - wildplasser

有人能帮助我解决这个问题吗？链接 - MAHI

Answer 1

（在PostgreSQL 8.4.3下测试过）

以下查询为节点4下的子树分配新的ID（查看nextval），然后找到相应的新父ID （查看LEFT JOIN）。

WITH RECURSIVE CTE AS (
    SELECT *, nextval('t1_id_seq') new_id FROM t1 WHERE id = 4
    UNION ALL
    SELECT t1.*, nextval('t1_id_seq') new_id FROM CTE JOIN t1 ON CTE.id = t1.parent_id
)
SELECT C1.new_id, C1.category, C2.new_id new_parent_id
FROM CTE C1 LEFT JOIN CTE C2 ON C1.parent_id = C2.id

测试数据的结果：

new_id  category    new_parent_id
------  --------    -------------
9       C4          
10      C5          9
11      C6          9
12      C7          10

一旦您拥有了这个，将其插入回表格就很容易，只需小心地将子树根与适当的父级重新连接即可（在本例中为8，请参见COALESCE(new_parent_id, 8)）：

INSERT INTO t1
SELECT new_id, category, COALESCE(new_parent_id, 8) FROM (
    WITH RECURSIVE CTE AS (
        SELECT *, nextval('t1_id_seq') new_id FROM t1 WHERE id = 4
        UNION ALL
        SELECT t1.*, nextval('t1_id_seq') new_id FROM CTE JOIN t1 ON CTE.id = t1.parent_id
    )
    SELECT C1.new_id, C1.category, C2.new_id new_parent_id
    FROM CTE C1 LEFT JOIN CTE C2 ON C1.parent_id = C2.id
) Q1

之后，表格包含以下数据：

new_id  category    new_parent_id
------  --------    -------------
1       C1
2       C2          1
3       C3          1
4       C4          2
5       C5          4
6       C6          4
7       C7          5
8       C8          3
9       C4          8
10      C5          9
11      C6          9
12      C7          10

- Branko Dimitrijevic

---

太棒了！我没想到递归+next组合可以在不需要额外状态变量的情况下完成任务。向你致敬！ - wildplasser

有没有人能帮我处理这个问题？ - MAHI