Drop all databases objects except the database

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:

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

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?