Skip site navigation (1) Skip section navigation (2)

Re: DROP TABLE IF EXISTS

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Jaime Casanova <systemguards(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: DROP TABLE IF EXISTS
Date: 2004-10-26 21:35:55
Message-ID: 04A38A8D-2797-11D9-8C43-000A95D7BA10@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: ishaameDate: 2004-10-27 06:46:54
Subject: Cannot Create tables - on Windows XP
Previous:From: Jaime CasanovaDate: 2004-10-26 20:51:34
Subject: Re: DROP TABLE IF EXISTS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group