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

/*=================================================================================================*/
/* VCDB_NDU_CNT_DROP_CONSTRAINT.sql                                                                */
/*=================================================================================================*/
/* Execution Instructions & Examples:                                                              */
/* To exetuce the DROP procedure:                                                                  */
/*                                                                                                 */
/* call vcdb_ndu_cnt_drop_constraint('tab_schema', 'table_name', 'constraint_name');               */
/*                                                                                                 */
/* For additional detail visit confluence page:                                                    */
/* https://confluence.eng.vmware.com/display/VCUSOF/Operation+ALTER+TABLE+DROP+CONSTRAINT+template */
/*=================================================================================================*/

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

DECLARE
    const_check    INT := 0;
    l_drop_const     TEXT;
BEGIN

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

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

   /* Prepare function variables */
   l_drop_const := FORMAT('ALTER TABLE %I.%I DROP CONSTRAINT IF EXISTS %I CASCADE', tab_schema, tab_name, const_name);

   /* Perform check if constraint already exist */
   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;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Dropping the constraint */
   BEGIN
      IF (const_check = 1)
        THEN EXECUTE l_drop_const;
        RAISE LOG 'Constraint % on table %.% successfully dropped', const_name, tab_schema, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE LOG 'Failed to drop constraint % on table %.% - sqlstate: %, sqlerrm: %', const_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_cnt_drop_constraint$;

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