PostgreSQL递归查询

写法一：使用递归CTE

CREATE OR REPLACE FUNCTION get_all_child_nodes(pid integer)
RETURNS TABLE (child_id integer, parent_id integer) AS $$
BEGIN
  RETURN QUERY
  WITH RECURSIVE recursive_tree(child_id, parent_id) AS (
    SELECT pid, pid
    FROM your_table
    WHERE pid = $1
    UNION ALL
    SELECT t.pid, rt.parent_id
    FROM your_table t
    JOIN recursive_tree rt ON t.parent_id = rt.child_id
  )
  SELECT * FROM recursive_tree;
END;
$$ LANGUAGE 'plpgsql';

这个函数接受一个参数 pid，表示要查询其所有子节点的父节点 pid。它使用递归CTE（Common Table Expression）来向下遍历树形结构，并将查找到的所有子节点和对应的父节点 pid 作为输出结果返回。

写法二：使用递归函数

CREATE OR REPLACE FUNCTION get_child_nodes(pid integer)
RETURNS SETOF your_table AS $$
DECLARE
  child your_table;
BEGIN
  FOR child IN SELECT * FROM your_table WHERE parent_id = pid LOOP
    RETURN NEXT child;
    RETURN QUERY SELECT * FROM get_child_nodes(child.pid);
  END LOOP;
  RETURN;
END;
$$ LANGUAGE 'plpgsql';

这个函数采用递归函数的方式进行实现。它首先通过输入参数 pid 来查找第一级子节点，然后循环遍历每个查找到的子节点，并依次递归调用自身函数来查找下一级子节点。

写法三：使用 WITH 循环语句

CREATE OR REPLACE FUNCTION get_all_child_nodes(pid integer)
RETURNS TABLE (child_id integer, parent_id integer) AS $$
BEGIN
  RETURN QUERY
  WITH recursive_tree AS (
    SELECT pid, pid AS root_pid, 0 AS level
    FROM your_table
    WHERE pid = $1
    UNION ALL
    SELECT t.pid, rt.root_pid, rt.level + 1
    FROM your_table t
    JOIN recursive_tree rt ON t.parent_id = rt.pid
  )
  SELECT pid AS child_id, root_pid AS parent_id
  FROM recursive_tree
  ORDER BY level, child_id;
END;
$$ LANGUAGE 'plpgsql';

这个函数也是采用递归方式对树形结构进行遍历。它使用了 WITH 循环语句来遍历所有的子节点。通过记录每个节点的深度以及其根节点，这个函数可以按照先子后父节点的顺序输出所有子节点信息。