On Tue, Feb 17, 2009 at 01:09:10AM -0700, Scott Marlowe wrote: > On Tue, Feb 17, 2009 at 12:34 AM, Ivan Sergio Borgonovo > <mail@xxxxxxxxxxxxxxx> wrote: > > I've around 150-200 tables in the same schema. > > Some of them have pk/fk relationships and are referenced into > > functions (~20). > > > > I'd like to move all the 200 tables to a new schema and leave that > > one in the public schema. > > ALTER TABLE name SET SCHEMA new_schema; Make sure your functions don't contain any hard coded references to the old schema name though! As Raymond says, you can do this all in a transaction and roll back if any of the functions don't do the correct thing. Be aware that you're probably going to lock other users out when doing this so it may be worth having a script (so it's locked for as small a time as possible and doesn't wait for human amounts of time) that renames the schema and runs a few of the functions with representative arguments and a rollback at the end. Once you've run this a few times with different functions and arguments and generally convinced yourself that all is OK, change the rollback to commit and all will be done. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
