Получить всех братьев и сестер в дереве SQL

Я должен обрабатывать таблицу PRODUCT, которая создана для размещения древовидной структуры продуктов. Это делается для обработки ситуаций, когда один продукт может содержать несколько других (например, один пакетный продукт содержит несколько других позиций). Итак, я выполняю функцию, которая принимает OrderDetails, и она должна проходить через все ПРОДУКТЫ и перечислять дочерние продукты для каждого перечисленного продукта. Я столкнулся с проблемой, которую я должен повторить через дерево неизвестной глубины. Пожалуйста, дайте мне представление, как это сделать.

Я реализовал его в приведенной ниже таблице с указанной функцией вместе с ней. Но в этом решении глубина листинга ограничена 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;

Я хочу, чтобы материалы для братьев были под их соответствующими родителями, мне интересно, как написать выражение Grouping...

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
1 ответ

Этот 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;

licensed under cc by-sa 3.0 with attribution.