CREATE OR REPLACE FUNCTION "up_data"."catalog_test" () RETURNS text AS
$body$
/* New function body */
DECLARE
       all_cat CURSOR FOR select distinct(c.item_category_code), c.group_by from up_data.catalog c;
       sub_cat_man refcursor; -- CURSOR FOR select c.item_category_code, c.manufacturer_code from up_data.catalog c ORDER BY c.item_category_code, c.manufacturer_code;
       sub_cat_prod CURSOR FOR select c.item_category_code, c.product_group_code from up_data.catalog c ORDER BY c.item_category_code, c.product_group_code;

       item_cat varchar( 100 );
       group_by integer;
       sub_cat varchar( 100 );
       sub_cat_ic varchar( 100 );
       
       counter_sub_cat integer;

       tmp_sub_cat varchar( 100 );
       html text;

BEGIN
     html := '';
     counter_sub_cat :=  0 ;
     OPEN all_cat;
     OPEN sub_cat_man FOR select c.item_category_code, c.manufacturer_code from up_data.catalog c ORDER BY c.item_category_code, c.manufacturer_code;
     OPEN sub_cat_prod;
     /**/
     LOOP
       --------------------------
       -------!!!неработает!!!-------------------
       MOVE  100  IN all_cat;
       --------------------------
       --------------------------
       FETCH all_cat INTO item_cat, group_by;IF FOUND = false THEN EXIT; END IF;
       html := html || '[a parent]' || item_cat;
       ---------------------------------------------
       IF group_by =  1  THEN
         MOVE FORWARD  1  IN sub_cat_man;
         counter_sub_cat :=  0 ;
         LOOP
           FETCH sub_cat_man INTO sub_cat_ic, sub_cat;IF sub_cat_ic != item_cat THEN EXIT; END IF;
           IF sub_cat IS NOT NULL THEN
             html := html || '[a child' || group_by || ']' || sub_cat;
           END IF;
           counter_sub_cat := counter_sub_cat + 1 ;
         END LOOP;
         sub_cat = NULL;
       END IF;
       ---------------------------------------------
       IF group_by =  2  THEN
         MOVE FORWARD counter_sub_cat IN sub_cat_prod;
         counter_sub_cat :=  0 ;
         LOOP
           FETCH sub_cat_prod INTO sub_cat_ic, sub_cat;IF sub_cat_ic != item_cat THEN EXIT; END IF;
           IF sub_cat IS NOT NULL THEN
             html := html || '[a child' || group_by || ']' || sub_cat;
           END IF;
           counter_sub_cat := counter_sub_cat + 1 ;
         END LOOP;
         sub_cat = NULL;
       END IF;
       ---------------------------------------------
     END LOOP;
     
     CLOSE all_cat;
     CLOSE sub_cat_man;
     CLOSE sub_cat_prod;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

DECLARE
  cur CURSOR FOR
      SELECT
        DISTINCT c.item_category_code,
        CASE
          WHEN c.group_by =  1  THEN c.product_group_code
          WHEN c.group_by =  2  THEN c.manufacturer_code
          ELSE c.item_category_code
         END AS sub_cat
       FROM up_data.catalog c INNER JOIN up_data.catalogue c1 ON c.item_category_code = c1.item_code
       WHERE (c.group_by =  1  AND c.product_group_code IS NOT NULL) OR (c.group_by =  2  AND c.manufacturer_code IS NOT NULL)
       ORDER BY c.item_category_code, sub_cat;
  ------------
  html_sub text;
  html_main text;
  html text;
  tmp_main_cat varchar( 100 );
  main_cat varchar( 100 );
  sub_cat varchar( 100 );
  
BEGIN
  html = '';
  html_main = '';
  html_sub = '';
  
  OPEN cur;
  
  LOOP
    FETCH cur INTO main_cat, sub_cat;
    IF NOT FOUND THEN EXIT; END IF;
    IF tmp_main_cat != main_cat THEN
      html_main := '[a parent]' || tmp_main_cat || '\n';
      html :=  html || html_main || html_sub;
      html_main := '';
      html_sub := '';
    END IF;
    html_sub := html_sub || '[a child]' || sub_cat || '\n';
    tmp_main_cat = main_cat;
  END LOOP;
  
  RETURN html;
END;