create or replace FUNCTION purge_stat2_proc()
 RETURNS VOID
 
 
 
 
    AS $procedure$
    DECLARE
    l_purge_interval     interval;
    l_purge_sample_time  vpx_sample_time2.sample_time%type;
    l_partition          vpx_sample_time2.partition_index%type;
    l_time               vpx_sample_time2.sample_time%type;
    l_now                timestamp with time zone;
    l_utc                vpx_sample_time2.sample_time%type;
 
    PI2_CUR cursor(purge_time_in vpx_sample_time2.sample_time%type) FOR
    select partition_index, max(sample_time) as st
      from vpx_sample_time2 st2
     where sample_time <= purge_time_in
       and (rollup_counter is not null or exists(select 1
                                                 from vpx_stat_interval_def
                                                 where interval_seq_num = 3
                                                 and rollup_enabled_flg = 0))
       and not exists(select 1
                        from vpx_sample_time2 st21
                       where st21.partition_index = st2.partition_index
                         and st21.sample_time > purge_time_in)
     group by partition_index
     order by 2 asc;
 
 BEGIN
 
    select (interval_length  || ' sec')::interval
      into l_purge_interval
      from vpx_stat_interval_def
     where interval_seq_num = 2;
 
    
    
    l_now := now();
    l_utc := timezone('UTC', l_now);
    l_purge_sample_time := l_utc - (abs(extract(hour from (l_utc - l_now))::int) % 2 || ' hour')::interval;
    l_purge_sample_time := l_purge_sample_time - l_purge_interval;
 
    open PI2_CUR(l_purge_sample_time);
    loop
       fetch PI2_CUR into l_partition, l_time;
       exit when NOT FOUND;
       execute 'truncate table vpx_hist_stat2_' || CAST(l_partition AS TEXT);
       execute 'analyze vpx_hist_stat2_' || CAST(l_partition AS TEXT);
    end loop;
 
    close PI2_CUR;
 
    delete from vpx_sample_time2
     where sample_time < l_purge_sample_time;
 
    
 RETURN;
    exception
    when others then
     PERFORM 1
        FROM pg_cursors
       WHERE name = 'PI2_CUR';
 
     IF FOUND THEN
       CLOSE PI2_CUR; 
     END IF;
 
     RAISE EXCEPTION 'Raise_Error';
 END; $procedure$
 LANGUAGE plpgsql VOLATILE;
