Re: DROP TABLE IF EXISTS

[Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>]

Jaime,

Thanks for the reply.

On Oct 26, 2004, at 4:51 PM, Jaime Casanova wrote:
> http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php
>
>
> what about the Ron Johnson solution?
>  if exists (select 1 from pg_tables where tablename =
> "thetable")
> > drop table thetable

Actually, Ron gave some other possibilities, but the query above does 
NOT work (and was the original source of the question).  Just for 
information, here is a function that I had come up with that works.  It 
returns 1 or 0 just as a sanity check.

create or replace function drop_if_exists (text) returns INTEGER AS '
DECLARE
	tbl_name ALIAS FOR $1;
BEGIN
	IF (select count(*) from pg_tables where tablename=$1) THEN		EXECUTE 
''DROP TABLE '' || $1;
		RETURN 1;
	END IF;
	RETURN 0;
END;
'
language 'plpgsql';

Sean