/* *****************************************************************************************************
 * Copyright 2020 VMware, Inc.   All rights reserved. -- VMware Confidential
 * ****************************************************************************************************/

/*====================================================================================================*/
/* VCDB_NDU_CNT_DROP_INDEX.sql                                                                        */
/*====================================================================================================*/
/* Execution Instructions & Examples:                                                                 */
/* To exetuce the DROP procedure:                                                                     */
/*                                                                                                    */
/* call vcdb_ndu_cnt_drop_index('table_name', 'index_name');                                          */
/*                                                                                                    */
/* For additional detail visit confluence page:                                                       */
/* https://confluence.eng.vmware.com/display/VCUSOF/Operation+DROP+INDEX+template                     */
/*====================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_cnt_drop_index
(
tab_name     VARCHAR(50),
index_name   VARCHAR(50)
)
 LANGUAGE plpgsql
AS $vcdb_ndu_cnt_drop_index$

DECLARE
    l_drop_index    TEXT;
    column_names    TEXT;
BEGIN

   SET LOCAL statement_timeout = 2000;
   SET LOCAL lock_timeout = '2s';

   tab_name   := lower(tab_name);
   index_name := lower(index_name);

   /* Prepare function variables */
   l_drop_index := FORMAT('DROP INDEX IF EXISTS %I CASCADE', index_name);

   /* Provide index column/s */
   SELECT array_to_string(array_agg(a.attname), ', ') INTO column_names
     FROM pg_class t,
          pg_class i,
          pg_index ix,
          pg_attribute a
    WHERE t.oid = ix.indrelid
      AND i.oid = ix.indexrelid
      AND a.attrelid = t.oid
      AND a.attnum = ANY(ix.indkey)
      AND t.relkind = 'r'
      AND lower(t.relname) = lower(tab_name)
      AND lower(i.relname) = lower(index_name);

   /* Drop the index */
   BEGIN
      IF (column_names != '')
        THEN EXECUTE l_drop_index;
          RAISE LOG 'Index % on column/s [%] in table % successfully dropped', index_name, column_names, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to drop index % on column/s % in table % - sqlstate: %, sqlerrm: %', index_name, column_names, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_cnt_drop_index$;

----------------------------------------------------
-- END OF FUNCTION
----------------------------------------------------