====== Задание 2 ======
Реализовать иерархический запрос

<code plsql>
create or replace type t_region_type is object (id integer, name varchar2(32000), pid integer);
create or replace type t_region_tab is table of t_region_type;
  
create or replace procedure MAIN_INS (pTab t_region_tab, pReg_name varchar2) is  
   my_code NUMBER;
   my_errm VARCHAR2(32000);
   my_path VARCHAR2(100);
  begin

    select ltrim(SYS_CONNECT_BY_PATH(name,'/'), '/') 
      into my_path
      from table(pTab) t
     where upper(t.name) = upper(pReg_name)
      start with pid is null
      connect by prior id = pid;
      
   dbms_output.put_line(my_path);    
                
exception 
   when others then
     my_code := SQLCODE;
     my_errm := SQLERRM;
     dbms_output.put_line('Ошибка');    
end;

-- вызов
declare
 t_reg_tab t_region_tab:= t_region_tab();
begin
  
 t_reg_tab.extend(6);
 t_reg_tab(1):= t_region_type(1, 'Россия', null);
 t_reg_tab(2):= t_region_type(2, 'Москва', 1);
 t_reg_tab(3):= t_region_type(3, 'Санкт-Петербург', 1);
 t_reg_tab(4):= t_region_type(4, 'Краснодарский край', 1);
 t_reg_tab(5):= t_region_type(5, 'Краснодар', 4);
 t_reg_tab(6):= t_region_type(6, 'Воронежская область', 1);

 MAIN_INS(t_reg_tab, 'Краснодар');
 MAIN_INS(t_reg_tab, 'Стамбул');
end;
</code>

----


__**Решение**__
<code postgresql>
-- -----------------------------------------------------------------------
DO $$
BEGIN
	CREATE TYPE t_region_type AS (id INTEGER, name VARCHAR(32000), pid INTEGER);
	EXCEPTION WHEN duplicate_object THEN return;
END
$$;

-- -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_region_tab() RETURNS VARCHAR AS $fun$
DECLARE t_name VARCHAR = 't_region_tab';
BEGIN
	EXECUTE('CREATE TEMPORARY TABLE ' || t_name || '( p1 t_region_type );');
EXCEPTION WHEN OTHERS THEN
	EXECUTE('TRUNCATE ' || t_name || ';');
RETURN t_name;
END
$fun$ LANGUAGE plpgsql;

-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE new_region (tt VARCHAR, d t_region_type) AS $proc$
BEGIN
	EXECUTE('INSERT INTO ' || tt || ' VALUES ($$' || d || '$$);');
END
$proc$ LANGUAGE plpgsql;

-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE main_ins (tt VARCHAR, pReg_name VARCHAR) AS $proc$
DECLARE
	my_path VARCHAR(32000);
BEGIN
	EXECUTE('WITH RECURSIVE temp1(id, name, pid, path) AS (
			SELECT 
				(t1.t_reg_tab).id AS id,
				(t1.t_reg_tab).name AS name,
				(t1.t_reg_tab).pid AS pid,
				CAST((t1.t_reg_tab).name AS VARCHAR(32000)) AS path
			FROM ' || tt || ' AS t1
			WHERE UPPER((t1.t_reg_tab).name) = UPPER(''' || pReg_name || ''')
			UNION
			SELECT 
				(t2.t_reg_tab).id AS id,
				(t2.t_reg_tab).name AS name,
				(t2.t_reg_tab).pid AS pid,
				CAST(temp1.path || ''/'' || (t2.t_reg_tab).name AS VARCHAR(32000)) AS path
			FROM t_region_tab AS t2 JOIN temp1 ON temp1.pid = (t2.t_reg_tab).id
		)
		SELECT path my_path FROM temp1 WHERE pid IS NULL;
	') INTO my_path;

	RAISE INFO '%',my_path;

EXCEPTION WHEN OTHERS THEN 
	RAISE INFO 'Ошибка';
END
$proc$ LANGUAGE plpgsql;

-- -----------------------------------------------------------------------
DO $$
DECLARE 
	tt VARCHAR = get_region_tab();
BEGIN
	CALL new_region(tt,(1, 'Россия', NULL));
	CALL new_region(tt,(2, 'Москва', 1));
	CALL new_region(tt,(3, 'Санкт-Петербург', 1));
	CALL new_region(tt,(4, 'Краснодарский край', 1));
	CALL new_region(tt,(5, 'Краснодар', 4));
	CALL new_region(tt,(6, 'Воронежская область', 1));

	CALL main_ins(tt, 'Краснодар');
	CALL main_ins(tt, 'Стамбул');
END
$$;
</code>

<code postgresql>
-- -----------------------------------------------------------------------
DO $$
BEGIN
	CREATE TYPE t_region_type AS (id INTEGER, name VARCHAR(32000), pid INTEGER);
	EXCEPTION WHEN duplicate_object THEN return;
END
$$;

-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE main_ins (tt t_region_type[], pReg_name VARCHAR(32000)) AS $proc$
DECLARE
	my_path VARCHAR;
BEGIN
	WITH RECURSIVE temp1(id, name, pid, path) AS (
		SELECT
			(t1).id AS id,
			(t1).name AS name,
			(t1).pid AS pid,
			CAST((t1).name AS VARCHAR) AS path
		FROM unnest(tt) AS t1
		WHERE UPPER((t1).name) = UPPER(pReg_name)
		UNION
		SELECT 
			(t2).id AS id,
			(t2).name AS name,
			(t2).pid AS pid,
			CAST(temp1.path || '/' || (t2).name AS VARCHAR) AS path
		FROM unnest(tt) AS t2 JOIN temp1 ON temp1.pid = (t2).id
	)
	SELECT path INTO my_path FROM temp1 WHERE pid IS NULL;
	
	RAISE INFO '%',my_path;

EXCEPTION WHEN OTHERS THEN 
	RAISE INFO 'Ошибка';
END
$proc$ LANGUAGE plpgsql;

-- -----------------------------------------------------------------------
DO $$
DECLARE 
	t_reg_tab t_region_type[];
	
BEGIN
	t_reg_tab[1] = (1, 'Россия', NULL);
	t_reg_tab[2] = (2, 'Москва', 1);
	t_reg_tab[3] = (3, 'Санкт-Петербург', 1);
	t_reg_tab[4] = (4, 'Краснодарский край', 1);
	t_reg_tab[5] = (5, 'Краснодар', 4);
	t_reg_tab[6] = (6, 'Воронежская область', 1);

	CALL main_ins2(t_reg_tab, 'Краснодар');
	CALL main_ins2(t_reg_tab, 'Стамбул');
END
$$;
</code>