CREATE OR REPLACE FUNCTION purge_stat3_proc()
RETURNS VOID
   AS $procedure$
   DECLARE
   l_purge_interval3     interval;
   l_purge_interval4     interval;
   l_purge_sample_time3  vpx_sample_time3.sample_time%type;
   l_purge_sample_time4  vpx_sample_time4.sample_time%type;
   l_partition          vpx_sample_time3.partition_index%type;
   l_time               vpx_sample_time3.sample_time%type;

   PI3_CUR cursor(purge_time_in vpx_sample_time3.sample_time%type) FOR
   select partition_index, max(sample_time)
     from vpx_sample_time3 st3
    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 = 4
                                                and rollup_enabled_flg = 0))
      and not exists(select 1
                       from vpx_sample_time3 st31
                      where st31.partition_index = st3.partition_index
                        and st31.sample_time > purge_time_in)
    group by partition_index;

   PI4_CUR cursor(purge_time_in vpx_sample_time4.sample_time%type) FOR
   select partition_index, max(sample_time)
     from vpx_sample_time4 st4
    where sample_time <= purge_time_in
      and not exists(select 1
                       from vpx_sample_time4 st41
                      where st41.partition_index = st4.partition_index
                        and st41.sample_time > purge_time_in)
    group by partition_index;

BEGIN

   select (interval_length  || ' sec')::interval
     into l_purge_interval3
     from vpx_stat_interval_def
    where interval_seq_num = 3;

   l_purge_sample_time3 := timezone('UTC', now()) - l_purge_interval3;

   open PI3_CUR(l_purge_sample_time3);
   loop
      fetch PI3_CUR into l_partition, l_time;
      exit when NOT FOUND;
      execute 'truncate table vpx_hist_stat3_' || CAST(l_partition AS TEXT);
      execute 'analyze vpx_hist_stat3_' || CAST(l_partition AS TEXT);
   end loop;

   close PI3_CUR;

   delete from vpx_sample_time3
    where sample_time < l_purge_sample_time3;

   select (interval_length  || ' sec')::interval
     into l_purge_interval4
     from vpx_stat_interval_def
    where interval_seq_num = 4;

   l_purge_sample_time4 := timezone('UTC', now()) - l_purge_interval4;

   open PI4_CUR(l_purge_sample_time4);
   loop
      fetch PI4_CUR into l_partition, l_time;
      exit when NOT FOUND;
      execute 'truncate table vpx_hist_stat4_' || CAST(l_partition AS TEXT);
      execute 'analyze vpx_hist_stat4_' || CAST(l_partition AS TEXT);
   end loop;

   close PI4_CUR;

   delete from vpx_sample_time4
    where sample_time < l_purge_sample_time4;

exception
   when others then
    PERFORM 1
       FROM pg_cursors
      WHERE name = 'PI3_CUR';

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

    PERFORM 1
       FROM pg_cursors
      WHERE name = 'PI4_CUR';

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

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