From: Hans Peter Wiedau <hpw-nl@quelltext.com>
To: pgsql-admin@postgresql.org
Subject: Re: Destroying all exisitng tables in database
Date: Tue, 19 Apr 2005 15:28:05 +0200
Message-ID: <20050419132805.GA5860@entoron.wiedau.de> (raw)
In-Reply-To: <C1A114E7AE7D5D48A1FC49EB73D47FA005FEE0@icex2.ic.ac.uk>
References: <C1A114E7AE7D5D48A1FC49EB73D47FA005FEE0@icex2.ic.ac.uk>
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 <hpw-nl@quelltext.com>, CEO
// // Ostenhellweg 31, 44135 Dortmund, Germany
// \\/ fon +49 231 9503750, fax +49 231 9503751
////\\ Web http://www.quelltext.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: hpw-nl@quelltext.com
Subject: Re: Destroying all exisitng tables in database
In-Reply-To: <20050419132805.GA5860@entoron.wiedau.de>
References: <C1A114E7AE7D5D48A1FC49EB73D47FA005FEE0@icex2.ic.ac.uk> <20050419132805.GA5860@entoron.wiedau.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox