/* Example usage

CREATE TABLE vpx_vcdb_test (col1 int);
call vcdb_ndu_add_column(1, 'vcdb_team',701,'vc','vpx_vcdb_test', 'my_columnA', 'varchar(32)');
call vcdb_ndu_add_column('vcdb_team',701,'vc','vpx_vcdb_test', 'my_columnB', 'varchar(32)', 'NOT NULL');
call vcdb_ndu_add_column('vcdb_team',701,'vc','vpx_vcdb_test', 'my_columnC', 'varchar(32)', 'NULL');

*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_add_column(
cln_id      int,
cln_owner   text,
release     int,
tab_schema  varchar(50),
tab_name    varchar(50),
col_name    varchar(50),
col_type    varchar(10),
is_null     text default '')
language plpgsql
as $add_STD_col_proc$
DECLARE
  l_exec_statement text;
  is_cln_prst      int :=0;
  cln_timestamp    timestamp;
BEGIN

   /* log in time when proc is executed */
   SELECT clock_timestamp() into cln_timestamp;

  /* check if this column hasnt been added already */

    SELECT 1 into is_cln_prst
      FROM information_schema.columns
     WHERE table_schema = lower(tab_schema)
       AND table_name = lower(tab_name)
       AND column_name = lower(col_name);
       --AND data_type = lower(col_type);
  /* if column is present, continue with next change */

  IF is_cln_prst = 1 THEN
     RAISE LOG 'Change already applied';
     EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Already applied'' WHERE CLN_ID = %s', cln_id);
     RETURN;
  ELSE

   BEGIN
  /* abort if statement waits for more than 2 seconds to complete
     abort if statement waits for more than 1 second to obtain lock */

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

  /* generate CLN id */

      INSERT INTO vc.VPX_VCDB_PROC_TRK (cln_id, cln_owner, proc, release, CREATE_DATE)
        SELECT cln_id,
               cln_owner,
               'vc.vcdb_ndu_add_column('''||tab_schema||''','''||tab_name||''','''||col_name||''')',
               release,
               cln_timestamp;

  /* generate respective revert statement */

      INSERT INTO vc.VPX_VCDB_NDU_RVT (cln_id, release, CREATE_DATE, revert_proc)
        SELECT cln_id,
               release,
               cln_timestamp,
               'vc.vcdb_ndu_RVT_drop_column_proc('''||tab_schema||''','''||tab_name||''','''||col_name||''')';
   END;

   BEGIN

  /* abort if statement waits for more than 2 seconds to complete
     abort if statement waits for more than 1 second to obtain lock */

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

  /* build the DDL statement based on the input params */

      l_exec_statement = 'ALTER TABLE ' || tab_schema || '.' || tab_name || ' ADD COLUMN ' || col_name || ' ' || col_type || ' ' || is_null;

  /* show the DDL in stdout */

      RAISE LOG 'Processing stetement: %', l_exec_statement;

  /* execute the DDL */

      EXECUTE l_exec_statement;

  /* update the cln record if it was OK or NOK*/

      EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);

  /* Add error handling (NOT GRACEFUL) we need it to rollback automatically */

   EXCEPTION  WHEN others THEN
      EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_EXP SET CLN_STATUS = ''Failed and Rolledback'' WHERE CLN_ID = %s', cln_id);
      EXECUTE FORMAT('UPDATE vc.VPX_VCDB_NDU_RVT SET CLN_STATUS = ''SUCCESS'' WHERE CLN_ID = %s', cln_id);
      RAISE LOG 'Failed creating column % %', SQLERRM, SQLSTATE;

   END;
  END IF;
end;
$add_STD_col_proc$;