-- Function: get_clssfctn_by_node_id(bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)

-- DROP FUNCTION get_clssfctn_by_node_id(bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION get_clssfctn_by_node_id(
    pn_node_id bigint,
    ps_if character varying,
    ps_equal character varying,
    ps_endif character varying,
    ps_closeif character varying,
    ps_comment character varying,
    ps_assign_close character varying,
    ps_assign character varying,
    ps_quote character varying,
    ps_indent character varying,
    ps_cur_indent character varying,
    ps_result_name character varying,
    ps_entr character varying,
    v_is_null_str character varying)
  RETURNS text AS
$BODY$
-- по номеру ноды находим ее дочерние и строим для каждой из них рекурсивно текст классификатора
DECLARE
 tmp_txt text;
 tmp_txt2 text;
 result_name varchar default 'result';
 vn_id bigint;
 vs_default_value varchar;
 vn_count integer;
 vn_entropy numeric;
 vs_column_name varchar;
 vs_c_ststement varchar;
 vs_last_str varchar;
 vs_first_str varchar;
 pn_main_class_count integer;
BEGIN
  tmp_txt:='';

  for vn_id, vs_default_value, vn_count, vn_entropy, vs_column_name, vs_c_ststement, vs_last_str, vs_first_str, pn_main_class_count in
    select id, tn.default_value, rcount, rentropy, column_name, column_statement,
    lead(id,1,'-1') over(order by tn.column_statement),
    lag(id,1,'-1') over(order by tn.column_statement),
    main_class_count
    from tree_nodes tn
    where tn.parent_node_id=pn_node_id
    order by tn.column_statement
  loop
    if vs_first_str='-1' then tmp_txt:=tmp_txt||ps_cur_indent||ps_entr; end if; -- сделаем пробел перед if-ами. так код легче читается

    -- как нужно описывать равенство при выводе текста кода. добавлена обработка null значений
    if v_is_null_str is null then -- если никакой явной обработки null значений не предусмотрено, считаем их раыными пустой строке
      if vs_c_ststement is null then vs_c_ststement:=''; end if;
      tmp_txt2:=vs_column_name||ps_equal||ps_quote||vs_c_ststement||ps_quote;
    else -- иначе вставляем строку обозначающую эти самые null значения
      if vs_c_ststement is null then
        -- если случился null
        tmp_txt2:=vs_column_name||' '||v_is_null_str;
      else
        -- если null не случился (самая обычная строка)
        tmp_txt2:=vs_column_name||ps_equal||ps_quote||vs_c_ststement||ps_quote;
      end if;
    end if;

    tmp_txt:=tmp_txt||ps_cur_indent||ps_if||' '||tmp_txt2||' '||ps_endif||ps_entr;
    -- откажемся от энтропии в пользу числачастиц
    tmp_txt:=tmp_txt||ps_cur_indent||ps_indent||result_name||ps_assign||ps_quote||vs_default_value||ps_quote||ps_assign_close||ps_comment||'count='||vn_count||'; main class '||cast(cast(pn_main_class_count as numeric)/vn_count*100 as numeric(10,1))||'%'||ps_entr;


    -- сделаем рекурсивный вызов для дочерних нод
    tmp_txt:=tmp_txt||get_clssfctn_by_node_id (vn_id,
                                               ps_if,
                                               ps_equal,
                                               ps_endif,
                                               ps_closeif,
                                               ps_comment,
                                               ps_assign_close,
                                               ps_assign,
                                               ps_quote,
                                               ps_indent,
                                               ps_cur_indent||ps_indent,
                                               ps_result_name,
                                               ps_entr,
                                               v_is_null_str);

    tmp_txt:=tmp_txt||ps_cur_indent||ps_closeif||ps_entr;
    if vs_last_str<>'-1' then tmp_txt:=tmp_txt||ps_entr; end if;-- если строка не последняя - добавим разделение между if-ами
  end loop;

  RETURN tmp_txt;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_clssfctn_by_node_id(bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
  OWNER TO postgres;