create or replace FUNCTION calc_topn3_proc(p_last_run TIMESTAMP,
    p_last_stat TIMESTAMP)
 RETURNS VOID
 LANGUAGE plpgsql
 VOLATILE
 AS
 $BODY$
  
 
 
 
 
 
 
 
 
 
   
 
  
 
 
 
 
 
 
 
    DECLARE
    v_time_level  vpx_topn_state.time_level%type DEFAULT 3;
    v_partitions  VARCHAR(1000) DEFAULT ' ';
    v_sql  VARCHAR(2000);
    v_partition  VPX_SAMPLE_TIME3.partition_index%type;
    v_cnt  INTEGER;
 
    PI_CUR cursor FOR select distinct partition_index
    from VPX_SAMPLE_TIME3
    where sample_time > p_last_run and sample_time <= p_last_stat;
 BEGIN
    select count(1) into v_cnt
    from vpx_job_log
    where job_id = v_time_level;
 
    if v_cnt = 0 then
       return;
    end if;
 
    v_cnt := 0;
    open PI_CUR;
    loop
       fetch PI_CUR into v_partition;
       exit when NOT FOUND;
       v_partitions := v_partitions || case when v_cnt > 0 then ' union all '
                                            else ' '
                                        end
       || 'select counter_id, time_id, stat_val from VPX_HIST_STAT3_' || CAST(v_partition AS TEXT);
       v_cnt := v_cnt+1;
    end loop;
    close PI_CUR;
 
 
 
    if v_cnt = 0 then
       return;
    end if;
 
    v_sql := 'INSERT INTO vpx_topn_past_month(counter_id, sample_time, integral_value)
  WITH stats AS
  (SELECT s.counter_id, t.sample_time, s.stat_val
     FROM (' || v_partitions || ') s,
           VPX_SAMPLE_TIME3 t,
           vpx_stat_counter c,
           vpx_topn_statistics tos
    WHERE s.counter_id = c.counter_id
      AND s.time_id = t.time_id
      AND c.stat_id = tos.stat_id
      AND t.sample_time > $1 and t.sample_time <= $2)
  SELECT a.counter_id as counter_id,
         a.sample_time as sample_time,
        ((a.stat_val + coalesce(a.stat_val, 0))* 120) as integral_value
    FROM stats a LEFT OUTER JOIN stats b
                 ON a.sample_time = b.sample_time + ''2 hour''::interval
   WHERE a.counter_id = b.counter_id';
 
    EXECUTE v_sql using p_last_run, p_last_stat;
 
    GET DIAGNOSTICS v_cnt = ROW_COUNT;
    IF v_cnt < 1 THEN
       RETURN;
    END IF;
 
    v_cnt := 0;
  
    select 1 into v_cnt
    from vpx_topn_state
    where time_level = v_time_level;
 
    IF v_cnt = 1 THEN
       UPDATE vpx_topn_state
       SET sample_time = p_last_stat
       WHERE TIME_LEVEL = v_time_level;
    ELSE
       INSERT INTO vpx_topn_state(TIME_LEVEL,SAMPLE_TIME)
       VALUES(v_time_level,p_last_stat);
    END IF;
 
 
 
 ANALYZE VPX_TOPN_PAST_MONTH;
 END;
 $BODY$
