/* Example usage

--Adding comment on a given column from a given table
call vcdb_ndu_add_column_comment('vcdb_team',701,'vc','vpx_vcdb_test','my_columnA', $$'This is VCDB column my_columnA comment'$$);

*/
CREATE OR REPLACE PROCEDURE vcdb_ndu_add_column_comment(
cln_id            int,
cln_owner         text,
release           int,
tab_schema        varchar(50),
tab_name          varchar(50),
col_name          varchar(50),
text_comment text default ''
)
language plpgsql
as $add_column_comment$
DECLARE
 l_exec_statement_column   text;
 check_column_comment      int :=0;
 check_column_existance    int :=0;
 is_cln_prst               int :=0;
 cln_timestamp             timestamp;
BEGIN

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

  /* check if column has a comment already */

   SELECT count(pg_catalog.col_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid,isc.ordinal_position))
          as column_description INTO check_column_comment
     FROM information_schema.columns isc
    WHERE isc.table_schema = lower(tab_schema)
      AND isc.table_name = lower(tab_name)
      AND isc.column_name = lower(col_name);

  /* check if column on which comment is to be added exists */

  SELECT count(column_name) into check_column_existance
    FROM information_schema.columns
   WHERE table_name = lower(tab_name)
     AND table_schema = lower(tab_schema)
     AND column_name = lower(col_name);

  IF (check_column_existance = 0) THEN
    RAISE LOG 'Column % on table % does not exists', col_name, tab_name;
    RETURN;
  END IF;

/* if there is a comment, continue with next change */

  IF (check_column_comment > 0 )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 2 second to obtain lock */

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

  /* 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_comment('''||tab_schema||''','''||tab_name||''','''||col_name||''','''||text_comment||''')',
               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_col_cmt_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 2 second to obtain lock */

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

  /* build the ddl to be executed */

      l_exec_statement_column = 'COMMENT ON COLUMN ' || tab_schema || '.' || tab_name || '.' || col_name || ' IS ' || text_comment;

  /* show the DDL in stdout */

      RAISE LOG '%', l_exec_statement_column;

  /* execute the ddl */

      execute l_exec_statement_column;

  /* 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);
   END;
  END IF;

END;
$add_column_comment$;