假设我有一个自引用层次结构表，以这种方式构建经典方式：

CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test);

insert into test (name,id,parent_id) values
('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root
2sub1',5,2),('root2sub2',6,2);

testdb=# select * from test;

   name    | id | parent_id
-----------+----+-----------
 root1     |  1 |  
 root2     |  2 |  
 root1sub1 |  3 |         1
 root1sub2 |  4 |         1
 root2sub1 |  5 |         2
 root2sub2 |  6 |         2

我现在需要的是一个函数(最好是在普通sql中)，该函数将使用测试记录的ID并
克隆所有添加记录(包括给定的记录)。当然，克隆的记录需要具有新的ID。理想的结果
例如：

Select * from cloningfunction(2);

   name    | id | parent_id    
-----------+----+-----------
 root2     |  7 |  
 root2sub1 |  8 |         7
 root2sub2 |  9 |         7

有指针吗？我正在使用PostgreSQL 8.3.

解决方案

递归地提取此结果很棘手(尽管可能)。但是，它通常效率不高，并且有更好的方法来解决此问题。

基本上，我们在表格上增加了一个额外的列，该列将树追踪到顶部，我将其称为"上链"。它只是一个长字符串，看起来像这样：

name | id | parent_id | upchain
root1 | 1 | NULL | 1:
root2 | 2 | NULL | 2:
root1sub1 | 3 | 1 | 1:3:
root1sub2 | 4 | 1 | 1:4:
root2sub1 | 5 | 2 | 2:5:
root2sub2 | 6 | 2 | 2:6:
root1sub1sub1 | 7 | 3 | 1:3:7:

通过使用表格上的触发器来保持此字段的更新非常容易。 (对术语表示歉意，但我一直使用SQL Server来做到这一点)。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。每次添加或者删除记录，或者更新parent_id字段时，我们只需要更新树的该部分上的upchain字段即可。这是一件微不足道的工作，因为我们只需要获取父记录的上链并添加当前记录的ID。使用LIKE可以轻松地识别所有子记录，以检查其上行链中具有起始字符串的记录。

我们要做的是有效地进行一些额外的写活动，以节省读取数据时的开销。

当我们想在树中选择一个完整的分支时，它是微不足道的。假设我们想要在节点1下的分支。节点1的上行链为" 1："，因此我们知道该树下该树的分支中的任何节点都必须从" 1：..."开始。因此，我们只需执行以下操作：

SELECT *
FROM table
WHERE upchain LIKE '1:%'

这是非常快的(当然可以索引上行链领域)。另外，它还使许多活动变得非常简单，例如查找局部树，树中的水平等。

我在跟踪大型员工报告层次结构的应用程序中使用了此方法，但是我们几乎可以将其用于任何树结构(零件分解等)。

注意事项(适用于任何有兴趣的人)：

• 我没有逐步介绍SQL代码，但是一旦我们掌握了原理，就很容易实现。我不是一个优秀的程序员，所以我从经验上讲。
• 如果表中已经有数据，则需要进行一次更新以使上行链最初同步。同样，这并不困难，因为代码与触发器中的UPDATE代码非常相似。
• 这项技术也是识别循环引用的好方法，否则可能很难发现。

这听起来像乔·塞尔科(Joel Celko)的" SQL For Smarties"中的练习。

我没有复印件，但是如果我们需要解决这类问题，我认为这本书对我们有很大帮助。

Joe Celko的方法与njreed的答案类似，但更通用，可以在这里找到：

• 树的嵌套集模型(在本文的中间)
• 树的嵌套集模型，第2部分
• SQL中的树-第三部分

@Maximilian：没错，我们忘记了实际要求。递归存储过程如何？我不确定在PostgreSQL中是否可行，但是这是一个有效的SQL Server版本：

CREATE PROCEDURE CloneNode
    @to_clone_id int, @parent_id int
AS
    SET NOCOUNT ON
    DECLARE @new_node_id int, @child_id int

    INSERT INTO test (name, parent_id) 
        SELECT name, @parent_id FROM test WHERE id = @to_clone_id
    SET @new_node_id = @@IDENTITY

    DECLARE @children_cursor CURSOR
    SET @children_cursor = CURSOR FOR 
        SELECT id FROM test WHERE parent_id = @to_clone_id

    OPEN @children_cursor
    FETCH NEXT FROM @children_cursor INTO @child_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE CloneNode @child_id, @new_node_id
        FETCH NEXT FROM @children_cursor INTO @child_id
    END
    CLOSE @children_cursor
    DEALLOCATE @children_cursor

示例由" EXECUTE CloneNode 2，null"(第二个参数是新的父节点)完成。