pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Destroying all exisitng tables in database
2+ messages / 2 participants
[nested] [flat]

* Destroying all exisitng tables in database
@ 2005-04-13 15:20 Lim, Terrin <terrin.lim@imperial.ac.uk>
  2005-04-19 13:28 ` Re: Destroying all exisitng tables in database Hans Peter Wiedau <hpw-nl@quelltext.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Lim, Terrin @ 2005-04-13 15:20 UTC (permalink / raw)
  To: pgsql-admin

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.
 
terz

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Destroying all exisitng tables in database
  2005-04-13 15:20 Destroying all exisitng tables in database Lim, Terrin <terrin.lim@imperial.ac.uk>
@ 2005-04-19 13:28 ` Hans Peter Wiedau <hpw-nl@quelltext.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Hans Peter Wiedau @ 2005-04-19 13:28 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-04-19 13:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-13 15:20 Destroying all exisitng tables in database Lim, Terrin <terrin.lim@imperial.ac.uk>
2005-04-19 13:28 ` Hans Peter Wiedau <hpw-nl@quelltext.com>

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