Re: Drop all databases objects except the 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(at)Yahoo(dot)com #

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