/* USAGE


For non-UNIQUE INDEX creation:
call vcdb_ndu_crt_idx('vcdb_team',701,'schema_name', index_name', 'table_name', 'index_context');

call vcdb_ndu_crt_idx('vcdb_team',701, 'schema_name', 'vpx_test_index','vpx_vcdb_test', '(first_column)');
call vcdb_ndu_crt_idx('vcdb_team',701, 'schema_name', 'vpx_test_index','vpx_vcdb_test', '(column1,column2)');
call vcdb_ndu_crt_idx('vcdb_team',701, 'schema_name', 'vpx_test_index','vpx_vcdb_test', '(column1,column2)
TABLESPACE vpx_test_tablespace WHERE COLUMN1 IS NOT NULL ');

For UNIQUE INDEX creation:
call vcdb_ndu_crt_idx('vcdb_team', 701, 'index_name', 'table_name', 'index_context', 'UNIQUE');

*/
CREATE OR REPLACE PROCEDURE vcdb_ndu_crt_idx
(
cln_id        int,
cln_owner     text,
release       int,
schema_name   varchar(50),
index_name    varchar(50),
index_table   varchar(50),
index_context text,
is_unique     text default ''
)
language plpgsql
AS $$
DECLARE
 is_present       varchar(50);
 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 index with that name on that table exists */

  SELECT 1 into is_cln_prst
    FROM pg_indexes
    WHERE tablename = lower(index_table)
      AND indexname = lower(index_name)
      AND schemaname = lower(schema_name);

  /* if index exists 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 = '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_crt_idx('''||index_table||''','''||index_name||''','''||index_context||''')',
                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_idx_proc('''||index_table||''','''||index_name||''','''||schema_name||''')';
   END;

  /* SQL securities */

   IF (index_context = ';') THEN
      RAISE EXCEPTION 'Invalid input parameter';
   ELSIF index_table =';' THEN 
      RAISE EXCEPTION 'Invalid input parameter';
   ELSIF index_name = ';' THEN
      RAISE EXCEPTION 'Invalid input parameter';
   END if;

  /* prepare the statement to be executed */

   l_exec_statement = FORMAT('CREATE %s INDEX %s ON %s.%s(%s)',is_unique, index_name, schema_name, index_table, index_context);

  /* show the DDL in stdout */

   RAISE LOG 'Processing stetement: %', l_exec_statement;

   BEGIN

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

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

  /* execute the statement */

      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 index, %,%', SQLERRM, SQLSTATE;
   END;
  END IF;
END;
$$;