/* Revert procedure for already commited change
   REMOVING TABLE FROM SCHEMA */
CREATE OR REPLACE PROCEDURE vcdb_ndu_RVT_drop_idx_proc(
tab_name    varchar(50),
idxname     varchar(50),
schema      varchar(50)
)
language plpgsql
AS $vcdb_ndu_RVT_drop_idx_proc$
DECLARE
l_drop_idx_stmt text;
BEGIN
 /* do we need to track this as this is not a change it is a tool provided
    to services to remove a change that was already !!!SUCCESSFUL!!!
    performed but client wants it removed. Caution if column to be dropped has
    constraints or is referenced, these constraints will be also removed */
  IF EXISTS (SELECT indexname
               FROM pg_indexes
              WHERE tablename = lower(tab_name)
                AND indexname = lower(idxname)
                AND schemaname = lower(schema)
            ) THEN
     BEGIN
     SET LOCAL statement_timeout = 2000;
     SET LOCAL lock_timeout = '2s';
     l_drop_idx_stmt = FORMAT('DROP INDEX %s.%s', schema, idxname);
        EXECUTE l_drop_idx_stmt;
     EXCEPTION WHEN OTHERS THEN RAISE LOG 'Removing index % from table % failed', idxname, tab_name;
                                RAISE LOG '%,%',SQLSTATE,SQLERRM;
     END;
  ELSE
     RAISE LOG 'Index does not exists';
  END IF;
  /* in case of revert should we think in retry loop drop attempt ?*/
END;
$vcdb_ndu_RVT_drop_idx_proc$;
