Thread: Drop all databases objects except the database

HorizontenetHi Everyone,

    Does anyone know about a script or function to drop all databases
objects except the database? ( to empty a database)


Thanks!

Evandro wrote:

>HorizontenetHi Everyone,
>
>    Does anyone know about a script or function to drop all databases
>objects except the database? ( to empty a database)
>

If you really want to drop ALL the objects, would it not be the same as
dropping the database and creating a new one?

Probably the fastest way is to drop the database and then
recreate... But if this doesn't suit then consider the (horrid) script
fragment below:

 psql -U sprint -A -q -t -c "SELECT relname FROM pg_class WHERE relname NOT LIKE 'pg_%' AND reltype != 0;" $1 | awk
'BEGIN{print "BEGIN;"} {print "GRANT ALL PRIVILEGES ON "$1" TO PUBLIC;"} END {print "COMMIT;"}' | psql -q $1 

I use this in a script to GRANT PUBLIC access to all tables in a
database. You could use something similar to DROP objects. Obviously
you'd need to get all tables from pg_class and DROP TABLE then, then
get all triggers DROP TRIGGER, and so on... See the follow page for
info on the pg_class table:

 http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/catalog-pg-class.html

So in short... "dropdb db; createdb test"!!!

Regards, Lee Kindness.

Evandro writes:
 >     Does anyone know about a script or function to drop all databases
 > objects except the database? ( to empty a database)

Lee Kindness wrote:
> Probably the fastest way is to drop the database and then
> recreate... But if this doesn't suit then consider the (horrid) script
> fragment below:
>
>  psql -U sprint -A -q -t -c "SELECT relname FROM pg_class WHERE relname NOT LIKE 'pg_%' AND reltype != 0;" $1 | awk
'BEGIN{print "BEGIN;"} {print "GRANT ALL PRIVILEGES ON "$1" TO PUBLIC;"} END {print "COMMIT;"}' | psql -q $1 
>
> I use this in a script to GRANT PUBLIC access to all tables in a
> database. You could use something similar to DROP objects. Obviously
> you'd need to get all tables from pg_class and DROP TABLE then, then
> get all triggers DROP TRIGGER, and so on... See the follow page for
> info on the pg_class table:

    I wonder how many triggers will be left after all tables have
    been dropped ...

    No, to be serious, that cannot be the way to do it. How  does
    the  script  determine what objects have been created in that
    database and what objects the DBA had placed  in  template1/0
    to be guaranteed in every database on his server?

    The  only  safe  way  would  be  to  develop  a tool based on
    pg_dump's  way  to  analyze  the  schema,  that   drops   all
    differences between the database and template1/0.

    In  the meantime, I don't see why dropping and recreating the
    database is a problem either.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com