Re: transfering tables into other schema

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(at)webthatworks(dot)it> 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/