CREATE OR REPLACE TEMP VIEW my_data 
AS
	WITH RECURSIVE t AS (SELECT 1 AS id, 0 AS parent_id 
			UNION
			SELECT id+1 AS id, id As parent_id
			FROM t
			WHERE id<800
			)
	SELECT * FROM t;

DO $$
DECLARE result JSONB[];
	childs JSONB[];
	tempp JSONB[];
BEGIN
	-- Закидываем все записи во временный массив JSONB
	SELECT 
		INTO tempp 
		array_agg(jsonb_build_object('id', id, 'parent_id', parent_id, 'childs', ARRAY[]::JSONB[])) 
	FROM my_data; 

	-- Цикл, пока временный массив не пустой
	WHILE array_length(tempp, 1)>0 LOOP

		-- Выбираем крайние дочерние записи (не имеющие других дочерних) 
		SELECT INTO childs array_agg(el) 
		FROM unnest(tempp) AS a(el)
		WHERE el->'id' NOT IN (SELECT el->'parent_id' FROM unnest(tempp) AS a(el))
		;

		RAISE NOTICE '%', cast(tempp As varchar);


		-- Вкладываем крайние дочерние элементы в родительские
		SELECT INTO tempp array_agg(jsonb_build_object(
				'id', el->'id'
				, 'parent_id', el->'parent_id'
				, 'childs', coalesce((SELECT array_agg(el) FROM (SELECT child.el FROM jsonb_array_elements(el->'childs') AS child(el) UNION SELECT child.el FROM unnest(childs) AS child(el) WHERE parent.el->'id'=child.el->'parent_id') AS a),ARRAY[]::JSONB[])
				))  
		FROM unnest(tempp) AS parent(el)
		;

		-- Добавляем в результирующий массив одинокие элементы (не имеющие родителей и потомков)
		SELECT INTO result array_agg(el) 
		FROM (	SELECT el FROM unnest(result) AS a(el)

			UNION 

			SELECT el FROM unnest(tempp) AS a(el)
			WHERE el->'id' NOT IN (SELECT el->'parent_id' FROM unnest(tempp) AS a(el))
				AND el->'parent_id' NOT IN (SELECT el->'id' FROM unnest(tempp) AS a(el))
		) AS a
		;

		-- Удаляем из временного массива элементы не попавшие в дочерние и результат
		SELECT INTO tempp array_agg(el) 
		FROM unnest(tempp) AS a(el)
		WHERE el->'id' NOT IN (SELECT el->'id' FROM unnest(childs) AS a(el))
			AND el->'id' NOT IN (SELECT el->'id' FROM unnest(result) AS a(el))
		;
		

	END LOOP ;


	BEGIN
		EXECUTE 'DEALLOCATE result_sql';
	EXCEPTION
	WHEN invalid_sql_statement_name THEN
	END;
	EXECUTE 'PREPARE result_sql AS SELECT el FROM unnest('''||cast(coalesce(result, ARRAY[]::JSONB[]) as varchar)||'''::JSONB[]) AS a(el)';
END$$;
EXECUTE result_sql;

CREATE OR REPLACE FUNCTION get_tree_json(p_id bigint DEFAULT 1)
  RETURNS jsonb AS
$BODY$
declare
node record;
res jsonb;
childs jsonb;
begin
/* получение данных для определенного(current) узла*/
select row_to_json(t) into res from view_abn_tree t where t.id = p_id;
childs:= '[]'::jsonb;
/*перебираем дочерние узлы*/
for node in select * from view_abn_tree t where t.parent_id = p_id loop
/*рекурсивный вызов нашей функции для дочерних узлов*/
select jsonb_set(childs, format('{%s}', node.id)::text[], get_tree_json(node.id), true) into childs;
end loop;
/* полученный на выходе из рекурсивного вызова json-массив с дочерними узлами добавляем в данные current узла*/
select jsonb_set(res, '{childs}', childs, true) into res;
return res;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  ;

CREATE OR REPLACE TEMP VIEW view_abn_tree AS
	WITH RECURSIVE t AS (SELECT 1 AS id, 0 AS parent_id 
			UNION
			SELECT id+1 AS id, id As parent_id
			FROM t
			WHERE id<800
			)
	SELECT * FROM t;


SELECT get_tree_json()

-- 0. Эмуляция таблицы с данными
CREATE OR REPLACE TEMP VIEW view_abn_tree 
AS
	WITH RECURSIVE t AS (SELECT 1 AS id, 0 AS parent_id 
			UNION
			SELECT id+1 AS id, id As parent_id
			FROM t
			WHERE id<1000
			)
	SELECT * FROM t;

DO $$
DECLARE 
	max_level integer;
	tempp JSONB[];
	result JSONB[];
BEGIN
	-- 1. Закидываем все записи во временный массив JSONB с указанием уровня вложенности
	WiTH RECURSIVE t AS (
		SELECT *, 0 as level_id FROM view_abn_tree WHERE parent_id=0
		UNION
		SELECT child.*, parent.level_id+1 AS level_id
		FROM t AS parent
		INNER JOIN view_abn_tree AS child ON child.parent_id=parent.id 

		)
	SELECT 
		INTO 
			tempp
			, max_level 

			array_agg(jsonb_build_object('id', id, 'parent_id', parent_id, 'level_id', level_id, 'childs', ARRAY[]::JSONB[])) 
			, max(level_id)
	FROM t; 
	max_level=coalesce(max_level,0);

	-- 2. Цикл по уровням вложенности от MAX до 0
	result=ARRAY[]::JSONB[];
	FOR current_level IN REVERSE max_level..0 LOOP

		--RAISE NOTICE '%', cast(current_level As varchar);
		--RAISE NOTICE '%', cast((SELECT array_agg(el) AS els FROM unnest(result) AS child(el) ) As varchar);

		-- 2.1. Вкладываем крайние дочерние элементы в родительские (текущего уровня)
		SELECT INTO result array_agg(jsonb_build_object(
				'id', parent.el->'id'
				, 'parent_id', parent.el->'parent_id'
				, 'level_id', parent.el->'level_id'
				, 'childs', coalesce(child.els,ARRAY[]::JSONB[])
				))  
		FROM unnest(tempp) AS parent(el)
		
		LEFT JOIN (SELECT el->'parent_id' AS parent_id, array_agg(el) AS els 
			FROM unnest(result) AS child(el) 
			GROUP BY el->'parent_id'
			) AS child ON child.parent_id=parent.el->'id'
			 
		WHERE cast(parent.el->>'level_id' AS integer)=current_level
		;
		
		--RAISE NOTICE '%', cast(result As varchar);

	END LOOP;

	-- 3. Подготавливаем запрос для выполнения после DO
	BEGIN
		EXECUTE 'DEALLOCATE result_sql';
	EXCEPTION
	WHEN invalid_sql_statement_name THEN
	END;
	EXECUTE 'PREPARE result_sql AS SELECT el FROM unnest('''||cast(coalesce(result, ARRAY[]::JSONB[]) as varchar)||'''::JSONB[]) AS a(el)';
END$$;
-- 4. Выполняем подготовленный запрос
EXECUTE result_sql;