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@Yahoo.com #



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