-- Function: audio_s(IN p_date1 "timestamp", IN p_date2 "timestamp", IN step int4, OUT channel_no int4, OUT column_no int4, OUT value int4)

-- DROP FUNCTION audio_s(IN p_date1 "timestamp", IN p_date2 "timestamp", IN step int4, OUT channel_no int4, OUT column_no int4, OUT value int4);

CREATE OR REPLACE FUNCTION audio_s(IN p_date1 "timestamp", IN p_date2 "timestamp", IN step int4, OUT channel_no int4, OUT column_no int4, OUT value int4) AS
$BODY$declare
  rec        record;
  --l_step     interval;
  l_step_sec int4;
  l_deth     int4;
  l_chl_c    int4;
  l_x1       int4;
  l_x2       int4;
  l_time_beg timestamp;
  l_time_end timestamp;
  i          int4;
  i2         int4;
  l_value    int4;
  l_length   int4;
  sql        text;
  l_col_1    int4;
  l_col_2    int4;
  l_pos      int4;
begin

    l_deth     :=  16 ; -- кол-во линий в ячейке
    l_chl_c    :=  32 ; -- кол-во каналов

    l_step_sec := step *  60 ;
    l_length   := interval_to_sec(p_date2 - p_date1);

-- временая таблица для промежуточного результата
sql := 'CREATE temporary TABLE res
(  
  t_channel    int4,
  t_column_no  int4,
  t_value      int4
) 
WITHOUT OIDS;
CREATE INDEX k_res ON res (t_channel, t_column_no);';
  execute sql;

-- инициализация временной таблицы
  for i IN  1  .. l_chl_c loop
    for i2 IN  1  .. (l_length/l_step_sec) loop
      sql := 'insert into res(t_channel, t_column_no, t_value)
      values (' || i || ',' || i2 || ',0);';
      execute sql;
    end loop;
  end loop;

    for rec in 
        select *
        from audio
        where (time_beg>p_date1 and time_beg<p_date2) 
           or (time_end>p_date1 and time_end<p_date2) 
           or (time_beg<p_date1 and time_end>p_date2)
        order by id_channel, time_beg
    loop
        l_time_beg   := rec.time_beg;

        column_no := (interval_to_sec(l_time_beg - p_date1))/l_step_sec +  1 ; 

        l_col_1   := l_step_sec * (column_no -  1 );
        l_col_2   := l_step_sec * column_no;
        l_pos     := interval_to_sec(l_time_beg - p_date1);

        l_x1      := (l_deth * (l_pos - l_col_1))/l_step_sec;

        l_time_end:= rec.time_end;

        if (rec.time_end is NULL or rec.time_end > (p_date1+sec_to_interval(l_step_sec*column_no))) then 
          l_time_end := p_date1+sec_to_interval(l_step_sec*column_no);
        end if;
        l_pos     := interval_to_sec(l_time_end - p_date1);
        l_x2      := (l_deth * (l_pos - l_col_1))/l_step_sec;

        l_value :=  0 ;

        for i IN  1  .. l_deth loop
          if ((i=l_x1 or i>l_x1) and (i=l_x2 or i<l_x2)) then
            l_value := l_value | ( 1  << (i- 1 ));
          end if;
        end loop;

-- запись результата по строке во временную таблицу
        if (l_value >  0 ) then
          sql := 'update res
                   set t_value = t_value | ' || l_value ||
                 ' where t_channel = ' || rec.id_channel || ' and t_column_no = ' || column_no || ';';
          execute sql;
        end if;

    end loop; -- for rec in

-- возврат результата
  sql := 'select * from res where t_value >0;';
  for rec in
    execute sql
  loop
    channel_no := rec.t_channel;
    column_no  := rec.t_column_no;
    value      := rec.t_value;
    return next;  
  end loop;


    sql := 'drop table res;';
    execute sql;

    return;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION audio_s(IN p_date1 "timestamp", IN p_date2 "timestamp", IN step int4, OUT channel_no int4, OUT column_no int4, OUT value int4) OWNER TO serik;