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

/*==============================================================*/
/* VCDB_nukePostgreSQL.sql                                      */
/* DBMS name:                                                   */
/* Created on:     4/17/2009                                    */
/*==============================================================*/

create or replace function nuke
(
)
RETURNS void AS $$

declare
cur_type RECORD;

begin

FOR cur_type in select tablename from pg_catalog.pg_tables where tablename like 'vpx%'
LOOP
    EXECUTE 'drop table ' || quote_ident(cur_type.tablename) || ' cascade';
END LOOP;

FOR cur_type in select viewname from pg_catalog.pg_views where viewname like 'vpx%'
LOOP
    EXECUTE 'drop view ' || quote_ident(cur_type.viewname);
END LOOP;

FOR cur_type in select relname from  pg_catalog.pg_statio_all_sequences where relname like 'vpx%'
LOOP
    EXECUTE 'drop sequence ' || quote_ident(cur_type.relname);
END LOOP;

End;
$$ LANGUAGE plpgsql;
----------------------------------------------------
-- END FUNCTION
----------------------------------------------------

DO $$
BEGIN
  IF NOT EXISTS ( select 1
                  from pg_locks l, pg_stat_all_tables t
                  where l.relation=t.relid
                  and UPPER(t.relname) like 'VPX%')
    THEN
       PERFORM  nuke();
    ELSE
       RAISE EXCEPTION 'ERROR !%','There are locked tables';
  END IF;

END$$;




