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

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

CREATE OR REPLACE PROCEDURE vcdb_ndu_cnt_drop_table
(
tab_schema VARCHAR(50),
tab_name   VARCHAR(50)
)
 LANGUAGE plpgsql
AS $vcdb_ndu_cnt_drop_table$

DECLARE
    l_drop_table    TEXT;
BEGIN

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

   tab_schema := lower(tab_schema);
   tab_name := lower(tab_name);

   /* Prepare function variables */
   l_drop_table := FORMAT('DROP TABLE IF EXISTS %I.%I CASCADE', tab_schema, tab_name);

   /* Drop the table */
   BEGIN
      IF EXISTS ( SELECT 1
                    FROM pg_tables
                   WHERE schemaname = tab_schema
                     AND tablename  = tab_name )
        THEN EXECUTE l_drop_table;
          RAISE LOG 'Table %.% successfully dropped', tab_schema, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to drop table %.% - sqlstate: %, sqlerrm: %', tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_cnt_drop_table$;

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