/* Revert procedure for already commited change
   REMOVING TABLE FROM SCHEMA */
CREATE OR REPLACE PROCEDURE vcdb_ndu_RVT_drop_column_proc(
schema_name varchar(50),
tab_name    varchar(50),
col_name    varchar(50))
language plpgsql
AS $vcdb_ndu_RVT_drop_column_proc$
DECLARE
BEGIN
 /* do we need to track this as this is not a change it is a tool provided
    to services to remove a change that was already !!!SUCCESSFUL!!!
    performed but client wants it removed. Caution if column to be dropped has
    constraints or is referenced, these constraints will be also removed */

  IF EXISTS (SELECT column_name
               FROM information_schema.columns
              WHERE table_name = lower(tab_name)
                AND table_schema = lower(schema_name)
                AND column_name = lower(col_name) ) THEN
     BEGIN
     SET LOCAL statement_timeout = 2000;
     SET LOCAL lock_timeout = '2s';
        EXECUTE 'ALTER TABLE ' || schema_name || '.' || tab_name || ' DROP COLUMN ' || col_name || ' CASCADE';
     EXCEPTION WHEN OTHERS THEN RAISE LOG 'Removing column % from table % failed', col_name, tab_name;
                                RAISE LOG '%,%',SQLSTATE,SQLERRM;
     END;
  ELSE
     RAISE LOG 'Column does not exists';
  END IF;

END;
$vcdb_ndu_RVT_drop_column_proc$;

/* USAGE

call vcdb_ndu_RVT_drop_column_proc('test_gd_t1', 'col1');

*/
