返回节点所有子表的函数 [英] Function to return a table of all children of a node

问题描述

假设我具有以下表结构:

Let's say I've got the following table structure:

| ID | ParentID | Name |

我想编写一个递归的PostgreSQL函数，以获取节点ID的所有子节点作为参数传递给它.

I'd like to write a recursive PostgreSQL function for getting all child nodes of a node ID passed to it as a parameter.

到目前为止，这是我的代码(我只有一部分函数可以获取传递的ID的所有子元素，现在我需要递归部分):

Here's my code so far (I only have one part of the function which gets all the children of the passed ID, and now I need the recursive part):

CREATE OR REPLACE FUNCTION GetAllChildren(IN NodeID INTEGER) RETURNS INTEGER AS $$
DECLARE
    Crs CURSOR FOR SELECT ID, ParentID, Name FROM Tree WHERE ParentID=NodeID;
    VarRow Tree%ROWTYPE;
BEGIN
    OPEN Crs;

    CREATE TEMPORARY TABLE TBL(
        ID SERIAL,
        ParentID INTEGER,
        Name CHARACTER(100)
    );

    LOOP
        FETCH Crs INTO VarRow;
        IF VarRow IS NULL THEN
            EXIT;
        END IF;
        INSERT INTO TBL(ID, ParentID, Name) VALUES(VarRow.ID, VarRow.ParentID, VarRow.Name);
    END LOOP;

    CLOSE Crs;

    RETURN 0;
END;
$$ LANGUAGE plpgsql;

也许最大的问题是我不知道在递归调用之间保存输出.

Perhaps the biggest problem is that I don't know where to save the output between the calls of the recursion.

如果到目前为止您还没有弄清楚，那就是邻接表，获取节点的所有子节点并将它们打印到表中.

If you haven't figured out so far, it's about the adjacency list, getting all the children of a node and printing them out to a table.

有人可以解决吗?

推荐答案

• PostgreSQL不知道本地(过程)受限的临时表-您的临时表在所有被调用函数的实例中都是可见的，并且在您的函数外也将可见-它具有会话可见性.

  • PostgreSQL doesn't know local (procedure) limited temporary tables - your temp table is visible in all instances of called function, and it will be visible outside your function too - it has session visibility.

    但是PostgreSQL函数(PostgreSQL没有过程)可以直接返回表-因此您不需要使用辅助表来存储数据

    But PostgreSQL functions (PostgreSQL has no procedures) can returns table directly - so you don't need use auxiliary table for storing data

    
    CREATE OR REPLACE FUNCTION children_by_parent(_parent_id int)
    RETURNS SETOF children AS $$ -- children is table name
    DECLARE r children;
    BEGIN
      FOR r IN 
        SELECT * FROM children
           WHERE parent_id = _parent_id
      LOOP
        RETURN NEXT r; -- return node
        RETURN QUERY SELECT * FROM children_by_parent(r.id); -- return children
      END LOOP;
      RETURN;
    END;
    $$ LANGUAGE plpgsql STRICT;
    

    此表单速度更快，因为您无需填写任何表(尽管临时表通常仅在RAM中).

    This form is faster, because you don't fill any table (although temp table is usually in RAM only).

    您不需要在PostgreSQL中使用显式游标-语句FOR可以完成所有任务，它更短且更友好.

    You don't need use a explicit cursor in PostgreSQL - statement FOR does all, it is shorter and more user friendly.

    • 最好的解决方案是Denis的想法-使用CTE-递归SQL.

    这篇关于返回节点所有子表的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！