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

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

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

DECLARE
    l_drop_column    TEXT;
    col_ext          TEXT;
BEGIN

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

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

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

   /* Drop the column */
   BEGIN
     IF EXISTS ( SELECT TRUE
                   FROM pg_attribute
                  WHERE attrelid = (
                      SELECT c.oid
                        FROM pg_class c
                        JOIN pg_namespace n ON n.oid = c.relnamespace
                       WHERE n.nspname = tab_schema
                         AND c.relname = tab_name
                      )
                  AND attname = col_name
                  AND NOT attisdropped
                  AND attnum > 0 )
      THEN EXECUTE l_drop_column;
        RAISE LOG 'Column % in table %.% successfully dropped', col_name, tab_schema, tab_name;
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE LOG 'Failed to drop column % in table %.% - sqlstate: %, sqlerrm: %', col_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_cnt_drop_column$;

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