/* Example usage:

call vcdb_ndu_add_table('vcdb_team',701,
'CREATE TABLE IF NOT EXISTS vc.VPX_FCD_SNAPSHOT_INFO (
   ID                            VARCHAR(50)   NOT NULL,
   SNAPSHOT_BACKING_OBJECT_ID    VARCHAR(60),
   SNAPSHOT_DESCRIPTION          VARCHAR(60),
   SNAPSHOT_ID                   VARCHAR(60),
   SNAPSHOT_DISK_PATH            VARCHAR(80),
   CONSTRAINT PK_VPX_FCD_SNAPSHOT PRIMARY KEY (ID, SNAPSHOT_BACKING_OBJECT_ID) )');

*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_add_table(
cln_id                 int,
cln_owner              text,
release                int,
table_create_statement text)
language plpgsql
as $add_table_proc$
DECLARE
  is_cln_prst        int :=0;
  l_input_table_name text;
  l_schema_name      text;
  l_tab_name         text;
  l_tbl              text;
  cln_timestamp      timestamp;
BEGIN

  /* get the 6th value of the input text parameter (must be schema.table name)
     NOTE THAT it must be CREATE TABLE IF NOT EXISTS schema_name. table_name */

  SELECT clock_timestamp() into cln_timestamp;
  SELECT rtrim(split_part(table_create_statement,' ',6), ' ') into l_input_table_name;

  select substring(l_input_table_name from '[^(]+'::text) into l_tbl;

  SELECT split_part(l_input_table_name,'.', 1) into l_schema_name;
  SELECT split_part(l_tbl,'.', 2) into l_tab_name;

  /* check if table is present if it is, change is already performed */

  SELECT COUNT(*) into is_cln_prst
    FROM information_schema.tables
   WHERE table_schema = lower(l_schema_name)
     AND table_name = lower(l_tab_name);

  /* if there is a comment, 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 2 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_table('''||l_tbl||''')',
               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_table_proc('''||l_tbl||''')';
     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';

       /* execute the DDL */

        EXECUTE table_create_statement;

       /* 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);
       RAISE LOG 'Failed creating table';
       RAISE LOG '% %', SQLERRM, SQLSTATE;
     END;
  END IF;
end;
$add_table_proc$
;