Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

функция содержащая иерархические запрос в PostgreSQL

Posted Сентябрь 8th, 2011 by torento1
in

Всем привет!
Помогите пожалуйста решить задачу. Существует , база со структурой бинарного дерева. Даны данные о всех элементах (ID ) и данные о их родителей (parentID). Задача сделать функцию, которая покажет всех детей - конкретного элементов и укажет на каком уровне находятся дети от данного элемента. И данная функция пройдет по всем элементам.

Дана таблица domains
ID integer, - Ид элемента
parentID integer - ИД элемента предка

Входные данные
Dannie s tablici Domains

ID; ParentID
1;Null
101;1
111;101
112;101
166;1
216;166
209;166
300;216
301;216
410;300
411;300

Результате -

1;101; 1
1;166;1
1;111;2
1;112;2
101;111;1
101;112;1
1;216;2
1;209;2
1;300;3
1;301;3
1;410;4
1;411;4
166;216;1
166;209;1
166;300;2
166;301;2
166;410;3
166;411;3
216;300;1
216;301;1
216;410;2
216;411;2
300;410;1
300;411;1

ID integer - Ид элемента ,
relativeID integer - Ид - ребенка
level integer - уровень - ( на каком уровне находится ребенок (relativeID ) от родителя (ID) вниз

Заранее спасибо.

Написал функцию

CREATE OR REPLACE FUNCTION get_domain_chl(pid integer)
  RETURNS SETOF mt AS
$BODY$
DECLARE 
rec mt%rowtype;
 
BEGIN
 
	FOR rec IN 
		SELECT id, parentid FROM domains_test WHERE parentid = pid
	LOOP
		RAISE NOTICE 'BBBBB %', rec;
		RETURN NEXT rec;
		RETURN query SELECT id, parentid FROM get_domain_chl( rec.id );
	END LOOP;
 
	RETURN;
 
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Пробовал по разному добавить счетчик уровней - не получается

CREATE OR REPLACE FUNCTION get_domain_chl(pid integer)
  RETURNS SETOF mt AS
$BODY$
DECLARE 
rec mt%rowtype;
lvl integer;
 
BEGIN
lvl :=1 
	FOR rec IN 
		SELECT id, parentid FROM domains_test WHERE parentid = pid
	LOOP
                rec.lvl:=lvl + 1
		RAISE NOTICE 'BBBBB %', rec;
		RETURN NEXT rec;
		RETURN query SELECT id, parentid FROM get_domain_chl( rec.id );
	END LOOP;
 
	RETURN;
 
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Помогите пожалуйста.

‹ sql+postgresql Нужна помощь - переписать процедуру написанную на Firebird - на postgresql ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Откровенно лень вникать. Могу

On Сентябрь 14th, 2011 admin says:

Откровенно лень вникать. Могу лишь направить в сторону вот этой страницы мануала, где как раз рассматриваются рекурсивные запросы:
http://postgresql.ru.net/manual/queries-with.html
На мой взгляд без рекурсивного запроса вам всё это не развернуть.

Спасибо, я написал

On Сентябрь 14th, 2011 torento1 says:

Спасибо за подсказку , я написал функцию

CREATE OR REPLACE FUNCTION proc_domains_relations_test_up(start_id integer)
  RETURNS SETOF domains_relations_test AS
$BODY$
 DECLARE      
      i_rec   domains_relations_test%ROWTYPE;
   BEGIN
      FOR i_rec IN 
		WITH RECURSIVE temp1 ( ID,PARENTID,LEVEL ) AS (
		SELECT T1.ID,T1.PARENTID,  1
		FROM Domains_test T1 WHERE T1.PARENTID = start_id
		UNION
		SELECT T2.ID, T2.PARENTID, LEVEL + 1
		FROM Domains_test T2 INNER JOIN temp1 ON( temp1.ID= T2.PARENTID)   )
		SELECT * FROM temp1 
      LOOP
           RETURN NEXT i_rec; 
           -- INSERT INTO domains_relations_test (id, parentid, level) values (i_rec.id, i_rec.parentid, i_rec.level);
      END LOOP;
      RETURN ;
 
   END
   $BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;

Я сделал функцию иерархическую с вводимой вершиной - с которой функция начнет спускаться вниз.
Но у меня не получается - менять каждый раз вершину с которой снова побежим по дереву. (вершина которая находится на один уровень ниже).

Я делал так:

On Сентябрь 16th, 2011 Grey says:

Текст написанный ниже воспроизведен по памяти без синтаксической проверки, так, как Постгресса у меня на данном компе нету...

Нам потребуются:
временные таблицы- 3 штуки и переменная

u inetger; /*уровень*/
create temporary table body_ /*Результирующая выборка*/
(
id integer,
pid inetger,
sort text,
urov integer
);

create temporary table c_ur( /*Временные для цикла */
id integer,
pid inetger,
sort text,
urov integer
);
create temporary table c_ur1(
id integer,
pid inetger,
sort text,
urov integer
);

заполняем таблицы вершиной дерева:

insert into c_ur(id,pid,urov)
select k.id,k.parentid,1 from domain.test as k
where k.parentid is null;

insert into body_ (id,pid,urov) select k.id,k.parentid,1 from domain.test as k;

u:=1

/*собственно цикл*/
while exists (select l.id from domain.test as l join c_ur on l.parentid=c_ur.id where c_ur.urov=u) loop
insert into body_ (id,pid,urov) select k.id,k.parentid,u+1 from domain.test as k join c_ur as c on k.parentid=c.id where c.urov=u;
delete from c_ur1;
insert into c_ur1 (id,pid,urov) select k.id,k.parentid,u+1 from domain.test as k join c_ur as c on k.parentid=c.id where c.urov=u;
delete from c_ur;
insert into c_ur select * from c_ur1;
u=u+1;
end loop;
;

Далее выбираем все из body и кормим return.

Как-то так. Еще раз оговорюсь, что в запросе наверное много ошибок, но чтобы уловить суть, я думаю, этого хватит.

Спасибо - попробую

On Сентябрь 19th, 2011 torento1 says:

Спасибо - попробую

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023