On Tue, 17 Feb 2009 12:19:14 +0000 Sam Mason <sam@xxxxxxxxxxxxx> wrote: > > > 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. I can't get how this really work. You're saying that constraint, fk/pk relationships will be preserved automatically... what else? OK BEFORE: create table x ( xid primary key, ... ); create table y ( xid int referencex x (xid), ... ); -- following in application select x.a, y.b from x join y on x.xid=y.xid; -- following in the DB create or replace function xy() as $$ begin select x.a, y.b from x join y on x.xid=y.xid; ... end; $$ ... ALTER TABLE y SET SCHEMA new_schema; What should I change by hand? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
