DROP_TABLE_IF_EXISTS

In this post, there is a nice explanation about executing conditional DDL. Here are my two functions which I use when I have to recreate a database schema.

create or replace function table_drop_if_exist(tbl varchar(255)) returns void as
$$
begin
	if (select count(relname) from pg_class where relname=tbl) <> 0 then
		raise notice 'dropping [%] with cascade',tbl;
		execute 'drop table ' || tbl || ' cascade';
	end if;
end;
$$
language plpgsql;

and of course

create or replace function type_drop_if_exist(typ varchar(255)) returns void as
$$
begin
	if (select count(relname) from pg_class where relname=typ and relkind='c') <> 0 then
		raise notice 'dropping [%] with cascade',typ;
		execute 'drop type ' || typ || ' cascade';
	end if;
end;
$$
language plpgsql;

Usage:

select table_drop_if_exist('tblregion');
create table tblregion
(
	regionid integer primary key not null,
	region varchar(50),
	code varchar(2)
);