sql - 获取 SQL 树中的所有兄弟节点

Question

我必须处理为容纳产品树结构而创建的表 PRODUCTS。这样做是为了处理一个产品可以包含多个其他产品的情况(例如，一个包装产品拥有多个其他位置)。因此，我正在制作一个接受 OrderDetails 的函数，它必须遍历所有 PRODUCTS 并列出列出的每个产品的子产品。我面临一个问题，我必须遍历未知深度的树。请告诉我如何去做。

我在下表中实现了它，并列出了函数。但在该解决方案中，列表的深度限制为 1，而我想做的是获取树的所有深度。

代码如下:

CREATE OR REPLACE FUNCTION foo()RETURNS text AS 
$body$
DECLARE _row RECORD;
        _result text := '';
        _child_row RECORD;
        _count integer := -1;
         _marker integer := 1;
BEGIN
    FOR _row IN SELECT * FROM tree_products
    LOOP
        _result := _result || _marker || ' ' || _row.name;
        _count := (SELECT count(product_id) FROM tree_products WHERE parent_id = _row.product_id);
        IF _count > 0 THEN
             FOR _child_row IN SELECT * FROM tree_products WHERE parent_id = _row.product_id
            LOOP
                _result := _result || ' ' || _child_row.name;
            END LOOP;
         END IF;
         _marker := _marker =1;      
    END LOOP;
END;
$body$
    LANGUAGE plpgsql 

UPD 使用 CTE 完成此使用，但出现分组问题:

CREATE OR REPLACE FUNCTION public.__foo (
)
RETURNS SETOF refcursor AS
$body$
DECLARE _returnvalue refcursor;
        _q text;
 BEGIN
_q :='
        WITH RECURSIVE r_p (product_id, name, parent_id) AS    -- 1
    (SELECT t_p.product_id, t_p.name , t_p.parent_id -- 2
   FROM   tree_products t_p
   WHERE  t_p.product_id = 1
   UNION ALL
   SELECT t_c.product_id, t_c.name, t_c.parent_id    -- 3
   FROM   r_p t_p, tree_products t_c
   WHERE  t_c.parent_id = t_p.product_id)
SELECT product_id, name, parent_id                       -- 4
FROM   r_p;';
OPEN _returnvalue FOR EXECUTE (_q);
RETURN NEXT _returnvalue;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

我想兄弟产品在他们各自的 parent 之下，我想知道如何写分组声明...

UPD 抱歉，tree_products 的定义如下:

CREATE TABLE public.tree_products (
  product_id INTEGER DEFAULT nextval('ree_products_product_id_seq'::regclass) NOT NULL,
  name VARCHAR,
  parent_id INTEGER,
  CONSTRAINT ree_products_pkey PRIMARY KEY(product_id)
) 
WITH (oids = false);

UPD:样本输出:

product_id | name          | parent_id
---------------------------------------
1          | promo         | NULL
3          | fork          | 1
4          | spoon         | 1
6          | can           | 1
10         | big can       | 3
11         | small can     | 4
12         | large spoon   | 6
13         | mega fork     | 3
14         | super duper   | 6

DESIRED OUTPUT:

product_id | name          | parent_id
---------------------------------------
1          | promo         | NULL
3          | fork          | 1
10         | big can       | 3
13         | mega fork     | 3
4          | spoon         | 1
11         | small can     | 4
6          | can           | 1
12         | large spoon   | 6
14         | super duper   | 6

So, the fetched table has structure of the real tree, like the follwing:
 - promo
   - fork
     - big can
     - mega fork
   - spoon
     - small can
   - can
     - large can
     - super duper

Answer 1

This SQLFiddle自上而下遍历树，将父行号列表保存在一个数组中，本质上是一个“父行位置列表”。

然后它按父列表对结果进行排序。

WITH RECURSIVE tree(product_id, name, parentlist) AS (
  SELECT product_id, name, ARRAY[ row_number() OVER (ORDER BY product_id) ]
  FROM tree_products
  WHERE parent_id IS NULL
  UNION
  SELECT tp.product_id, tp.name, array_append(parentlist, row_number() OVER (ORDER BY tp.product_id))
  FROM tree_products tp
  INNER JOIN tree t
  ON (tp.parent_id = t.product_id)
)
SELECT *
FROM tree
ORDER BY parentlist;