问题描述
假设我具有以下表结构:
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.
这篇关于返回节点所有子表的函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!