Re: transfering tables into other schema

On Tue, 17 Feb 2009 12:19:14 +0000
Sam Mason <sam@samason.me.uk> 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