/* USAGE

call vcdb_ndu_generate_and_exec_rvt_statement();

*/

create or replace procedure vcdb_ndu_generate_and_exec_rvt_statement()
language plpgsql
as $vcdb_ndu_generate_and_exec_rvt_statement$
DECLARE
  ids_to_revert int :=0;
  l_statements  text;
  lr_statements text;
  rel           int :=0;
  t             timestamptz := clock_timestamp();
BEGIN
  /* get ids for revert procs */
  FOR ids_to_revert, l_statements in
      SELECT revert.cln_id,
             revert_proc,
             revert.CREATE_DATE
  FROM vc.VPX_VCDB_NDU_RVT revert,
       vc.VPX_VCDB_NDU_EXP current_clns
 WHERE current_clns.cln_id = revert.cln_id
ORDER BY revert.CREATE_DATE desc

  LOOP
    BEGIN
      lr_statements = 'call '||l_statements;
      RAISE LOG '%',lr_statements;
      EXECUTE lr_statements;
      EXECUTE 'UPDATE vc.VPX_VCDB_NDU_RVT SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = '||ids_to_revert;
    EXCEPTION WHEN OTHERS then RAISE LOG 'Failed to revert, open bug with VCDB team';
    END;
  END LOOP;
  RAISE LOG 'VCDB expand rollback took % seconds', (SELECT EXTRACT(EPOCH FROM clock_timestamp() - t));
END;
$vcdb_ndu_generate_and_exec_rvt_statement$;