/* *************************************************************************************************************************
 * Copyright 2020 VMware, Inc.   All rights reserved. -- VMware Confidential
 * *************************************************************************************************************************/

/*=========================================================================================================================*/
/* VCDB_NDU_CNT_ADD_UNIQUE_KEY_CONST.sql                                                                                   */
/*=========================================================================================================================*/
/* Execution Instructions & Examples:                                                                                      */
/* To executed the ADD CONSTRAINT procedure:                                                                               */
/*                                                                                                                         */
/* Example structure:                                                                                                      */
/* call vcdb_ndu_add_unique_key_const('tab_schema', 'table_name', 'index_name', 'constraint_name')                         */
/*                                                                                                                         */
/* For additional detail visit confluence page:                                                                            */
/* https://confluence.eng.vmware.com/pages/viewpage.action?pageId=683920401                                                */
/*=========================================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_add_unique_key_const
(
tab_schema    VARCHAR(50),
tab_name      VARCHAR(50),
index_name    VARCHAR(50),
const_name    VARCHAR(50)
)
 LANGUAGE plpgsql
AS $vcdb_ndu_add_unique_key_const$

DECLARE
   const_check   INT := 0;
   idx_check     INT := 0;
   l_add_const   TEXT;
BEGIN

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

   tab_schema := lower(tab_schema);
   tab_name   := lower(tab_name);
   index_name := lower(index_name);
   const_name := lower(const_name);

   /* Prepare function variables */
   l_add_const := FORMAT('ALTER TABLE %I.%I ADD CONSTRAINT %I UNIQUE USING INDEX %I', tab_schema, tab_name, const_name, index_name);

   /* STEP 1 */
   /* Perform check to verify INDEX EXISTS */
   BEGIN
      SELECT COUNT(i.relname) INTO idx_check
        FROM pg_class t,
             pg_class i,
             pg_index ix,
             pg_attribute a
       WHERE t.oid = ix.indrelid
         AND i.oid = ix.indexrelid
         AND a.attrelid = t.oid
         AND a.attnum = ANY(ix.indkey)
         AND t.relkind = 'r'
         AND lower(t.relname) = lower(tab_name)
         AND lower(i.relname) = lower(index_name);
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* STEP 2 */
   /* Perform check to verify constraint does NOT EXISTS*/
   BEGIN
      SELECT COUNT(con.conname) INTO const_check
        FROM pg_catalog.pg_constraint con
             INNER JOIN pg_catalog.pg_class rel
                        ON rel.oid = con.conrelid
             INNER JOIN pg_catalog.pg_namespace nsp
                        ON nsp.oid = connamespace
        WHERE nsp.nspname = tab_schema
          AND rel.relname = tab_name
          AND con.conname = const_name
          AND con.convalidated IS TRUE
          AND con.contype = 'u';
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* STEP 3 */
   /* Add unique constraint */
   BEGIN
      IF ( idx_check = 1 AND const_check = 0 )
         THEN EXECUTE l_add_const;
           RAISE LOG 'Constraint % added on table %.%', const_name, tab_schema, tab_name;
      ELSIF ( idx_check = 0 AND const_check = 0 )
         THEN RAISE LOG 'Constraint not added, missing unique index %', index_name;
         RETURN;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE LOG 'Failed to validate constraint % on table %.% - sqlstate: %, sqlerrm: %', const_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_add_unique_key_const$;

----------------------------------------------------
-- END OF FUNCTION
----------------------------------------------------