Drop all databases objects except the database - Mailing list pgsql-general

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)