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

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

CREATE OR REPLACE PROCEDURE vcdb_ndu_cnt_drop_def_val
(
tab_schema VARCHAR(50),
tab_name   VARCHAR(50),
col_name   VARCHAR(50)
)
 LANGUAGE plpgsql
AS $vcdb_ndu_cnt_drop_def_val$

DECLARE
    l_drop_def    TEXT;
    def_val_ext    INT :=0;
    def_val       TEXT;
BEGIN

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

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

   /* Prepare function variables */
   l_drop_def := FORMAT('ALTER TABLE %I.%I ALTER COLUMN %I DROP DEFAULT', tab_schema, tab_name, col_name);

   /* Check if default value exists */
   SELECT COUNT(pg_get_expr(d.adbin, d.adrelid)) INTO def_val_ext
     FROM pg_catalog.pg_attribute a
     LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
     INNER JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
     INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE NOT a.attisdropped
      AND n.nspname = tab_schema
      AND a.attnum > 0
      AND a.attrelid = tab_name::regclass
      AND a.attname  = col_name;

   /* Get actual default value */
   SELECT pg_get_expr(d.adbin, d.adrelid) INTO def_val
     FROM pg_catalog.pg_attribute a
     LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
     INNER JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
     INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE NOT a.attisdropped
      AND n.nspname = tab_schema
      AND a.attnum > 0
      AND a.attrelid = tab_name::regclass
      AND a.attname  = col_name;

   /* Drop the default value */
   BEGIN
      IF (def_val_ext = 1)
         THEN EXECUTE l_drop_def;
           RAISE LOG 'Default value % on column % in table %.% successfully dropped', def_val, col_name, tab_schema, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG 'Failed to drop default value % on column % in table %.% - sqlstate: %, sqlerrm: %', def_val, col_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;
END;
$vcdb_ndu_cnt_drop_def_val$;

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