/* Example usage

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

*/
CREATE OR REPLACE PROCEDURE vcdb_ndu_add_table_comment(
cln_id       int,
cln_owner    text,
release      int,
tab_schema   varchar(50),
tab_name     varchar(50),
text_comment text default ''

)
language plpgsql
as $add_table_comment$
DECLARE
 exec_statement_table  text;
 check_table_comment   int :=0;
 check_column_comment  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 comment in case there is -> exit out
   (by default we can override it) or we can delete the comment then re-apply
   depends on what we want, current implementation proc exits and leaves the old comment
   I can suggest a simple function which sets the comment to NULL and
   then re-execute the current one yet to be decided */

   SELECT count(obj_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid, 'pg_class'))
          as table_description INTO check_table_comment
     FROM information_schema.columns isc
    WHERE isc.table_schema = lower(tab_schema)
      AND isc.table_name = lower(tab_name);

  /* if column has comment, continue with next change */

  IF (check_table_comment = 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 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_table_comment('''||tab_schema||''', '''||tab_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_tab_cmt_proc('''||tab_schema||''', '''||tab_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 statement */

      exec_statement_table = 'COMMENT ON TABLE ' || tab_schema || '.' || tab_name || ' IS ' || text_comment;
  /* log the statement in stdout */

      RAISE LOG '%', exec_statement_table;

  /* execute the statement */

      execute exec_statement_table;

  /* reflect CLN status */
      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_table_comment$;