create or replace FUNCTION purge_stat2_proc()
RETURNS VOID
-- ==================================================================
/** 
  Purpose:

    Purpose of this procedure to to purge hist stats data in VPX_HIST_STAT2
    Table. Basically purge 30 mins(default value) interval data prior to 1 week 
    ago (or user defined value which store at VPX_INTERVAL_DEF.INTERVAL_LENGTH.
    For 30 mins interval, user can change the length from 604800(one week: default
    value) to other values. It will also purge the hist stats data 
    that do not have matching counter_id in VPX_STAT_COUNTER table when an entity is 
    permenantly removed from inventory which invokes delete_stats_proc_oracle.sql.
*/
--
-- ==================================================================
   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;

   -- make sure the time changes would not result in unexpected job schedule
   -- purge should be always performed at odd hour and 45 minutes(e.g. 01:45)
   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;

   -- commit
RETURN;
   exception
   when others then
    PERFORM 1
       FROM pg_cursors
      WHERE name = 'PI2_CUR';

    IF FOUND THEN
      CLOSE PI2_CUR; -- close the cursor no matter what happens
    END IF;

    RAISE EXCEPTION 'Raise_Error';
END; $procedure$
LANGUAGE plpgsql VOLATILE;
