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; 
     END IF;
 
     PERFORM 1
        FROM pg_cursors
       WHERE name = 'PI4_CUR';
 
     IF FOUND THEN
       CLOSE PI4_CUR; 
     END IF;
 
     RAISE EXCEPTION 'Raise_Error';
 END; $procedure$
 LANGUAGE plpgsql VOLATILE;
