X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7D0DB5338A for ; Tue, 19 Apr 2005 10:28:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75976-10 for ; Tue, 19 Apr 2005 13:28:05 +0000 (GMT) Received: from mailgate.quelltext.com (mailgate.quelltext.com [212.46.119.68]) by svr1.postgresql.org (Postfix) with SMTP id E6C73533ED for ; Tue, 19 Apr 2005 10:28:03 -0300 (ADT) Received: (qmail 6057 invoked from network); 19 Apr 2005 13:28:58 -0000 Received: from unknown (HELO mail1.quelltext.com) (212.46.119.157) by mailgate.quelltext.com with SMTP; 19 Apr 2005 13:28:58 -0000 Received: (qmail 12546 invoked from network); 19 Apr 2005 13:28:06 -0000 Received: from unknown (HELO entoron.wiedau.de) (212.46.119.210) by digda.intern.quelltext.com with SMTP; 19 Apr 2005 13:28:06 -0000 Received: (qmail 16918 invoked by uid 10013); 19 Apr 2005 13:28:05 -0000 Date: Tue, 19 Apr 2005 15:28:05 +0200 From: Hans Peter Wiedau To: pgsql-admin@postgresql.org Subject: Re: Destroying all exisitng tables in database Message-ID: <20050419132805.GA5860@entoron.wiedau.de> References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-15 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/258 X-Sequence-Number: 17271 On Wed, Apr 13, 2005 at 04:20:40PM +0100, Lim, Terrin wrote: > I'm trying to drop all tables in a database but I can't use DROP dbname > as I don't have permission rights to do so. I also can't manually delete > all the tables cause there are about 200 tables. How do i go about this? > Thanks. Here is a little PL/pgSQL sniplet, I used to delete all tables from schema public: ----- snip-snap ----- CREATE OR REPLACE FUNCTION dropit() RETURNS integer AS' DECLARE numberoftables integer := 0; tabletodrop RECORD; BEGIN FOR tabletodrop IN SELECT tablename from pg_tables where schemaname=''public'' LOOP numberoftables := numberoftables + 1; RAISE NOTICE ''Droping table %'', tabletodrop.tablename; EXECUTE ''DROP TABLE public.'' || tabletodrop.tablename; END LOOP; RETURN numberoftables; END; ' LANGUAGE plpgsql; ----- snip-snap ----- You execute it by "select dropit();" on the psql commandline. But be warned, it will drop _all_ tables in the used schema. cu, Hans Peter -- ///// Quelltext AG -- Professional Software Services // // Hans Peter Wiedau , CEO // // Ostenhellweg 31, 44135 Dortmund, Germany // \\/ fon +49 231 9503750, fax +49 231 9503751 ////\\ Web http://www.quelltext.com