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

/*===================================================================================================*/
/* VCDB_NDU_EXP_ADD_COL_DEF_VAL.sql                                                                  */
/*===================================================================================================*/
/* Execution Instructions & Examples:                                                                */
/* To add a new varchar/text/character column with default value:                                    */
/*                                                                                                   */
/* call vcdb_ndu_add_col_with_def_val('vcdb_team',                                                   */
/*                                    701,                                                           */
/*                                    'vc',                                                          */
/*                                    'vpx_vcdb_test',                                               */
/*                                    'my_text_col_with_def_value',                                  */
/*                                    'varchar(50)',                                                 */
/*                                    '''vpxd''',                                                    */
/*                                    0);--new column                                                */
/*                                                                                                   */
/* To add a new int, number column with default value:                                               */
/*                                                                                                   */
/* call vcdb_ndu_add_col_with_def_val('vcdb_team',                                                   */
/*                                    701,                                                           */
/*                                    'vc',                                                          */
/*                                    'vpx_vcdb_test',                                               */
/*                                    'second_column',                                               */
/*                                    'int',                                                         */
/*                                    '30',                                                          */
/*                                    0);--new column                                                */
/*                                                                                                   */
/* To modify an already existent column with default value:                                          */
/*                                                                                                   */
/* call vcdb_ndu_add_col_with_def_val('vcdb_team',                                                   */
/*                                    701,                                                           */
/*                                    'vc',                                                          */
/*                                    'vpx_vcdb_test',                                               */
/*                                    'my_text_col_with_def_value',                                  */
/*                                    '',                                                            */
/*                                    '''vcdb''',                                                    */
/*                                    1);--old column                                                */
/*                                                                                                   */
/* call vcdb_ndu_add_col_with_def_val('vcdb_team',                                                   */
/*                                    701,                                                           */
/*                                    'vc',                                                          */
/*                                    'vpx_vcdb_test',                                               */
/*                                    'second_column',                                               */
/*                                    '',                                                            */
/*                                    '5',                                                           */
/*                                    1);--new column                                                */
/*                                                                                                   */
/* NOTE: A separate procedure exist for removing of a default value that                             */
/* is executed in the CONTRACT phase. However this procedure should not be                           */
/* mistaken for rollback.                                                                            */
/*                                                                                                   */
/* For additional detail visit confluence page:                                                      */
/* https://confluence.eng.vmware.com/display/VCUSOF/Operation+ADD+COLUMN+WITH+DEFAULT+VALUE+template */
/*===================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_add_col_with_def_val
(
cln_id        int,
cln_owner     TEXT,
release       INT,
tab_schema    VARCHAR(50),
tab_name      VARCHAR(50),
col_name      VARCHAR(50),
col_data_type VARCHAR(50),
def_value     TEXT,
is_new        INT --are you adding a new column or modifying an existent one
)
 LANGUAGE plpgsql
AS $vcdb_ndu_add_col_with_def_val$

DECLARE
    col_check             INT := 0;
    l_add_col_statement   TEXT;
    l_alter_col_statement TEXT;
    timestamp             TIMESTAMP WITH TIME ZONE := NOW();
	cln_timestamp         TIMESTAMP;

BEGIN

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

   /* Prepare function variables */
   l_add_col_statement := FORMAT('ALTER TABLE %I.%I ADD COLUMN %I %I DEFAULT %s', tab_schema, tab_name, col_name, col_data_type, def_value);
   l_alter_col_statement := FORMAT('ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT %I', tab_schema, tab_name, col_name, def_value);

   /* Create a CLN_ID for the change */
      INSERT INTO vc.VPX_VCDB_PROC_TRK (cln_id, cln_owner, proc, release, CREATE_DATE)
        SELECT cln_id,
               cln_owner,
               'vc.vcdb_ndu_add_col_def_val('''||tab_schema||''', '''||tab_name||''','''||col_name||''')',
               release,
               cln_timestamp;

   /* Generate respective revert statement */
   BEGIN
      INSERT INTO vc.VPX_VCDB_NDU_RVT (cln_id, release, CREATE_DATE, revert_proc)
        SELECT cln_id,
               release,
               cln_timestamp,
               'vc.vcdb_ndu_RVT_add_col_def_val('''||tab_schema||''', '''||tab_name||''','''||col_name||''')';
   END;

   /* Perform check if column already exist */
   BEGIN
      SELECT COUNT(column_name) INTO col_check
        FROM information_schema.columns
       WHERE table_schema = lower(tab_schema)
         AND table_name = lower(tab_name)
         AND column_name = lower(col_name);
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Addin a new non-existent column with default value */
   BEGIN
      IF (col_check = 0 AND is_new = 0 )
        THEN EXECUTE l_add_col_statement;
        RAISE LOG 'Adding a new column % to table %.% with default value %', col_name, tab_schema, tab_name, def_value;
        EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
        EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s',cln_id);
        RAISE LOG 'Failed to add column % on table %.% - sqlstate: %, sqlerrm: %', col_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

   /* Modify already existing column to add a default value */
   BEGIN
     IF (col_check = 1 AND is_new = 1)
       THEN EXECUTE l_alter_col_statement;
       RAISE LOG 'Modifying an existing column with the following: %', l_alter_col_statement;
       EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
     END IF;
   EXCEPTION
     WHEN OTHERS THEN
        EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
        RAISE LOG 'Failed to alter column % on table %.% - sqlstate: %, sqlerrm: %', col_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_add_col_with_def_val$;

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