Re: transfering tables into other schema

On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo wrote:
> 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?

Sorry, I could have been clearer...  Nothing in function xy() needs to
change because you don't explicitly refer to any schema anywhere.  If
your tables had been created in the "public" schema, as per default, and
your code was:

  CREATE FUNCTION foo() AS $$
    SELECT x.a, y.b FROM public.x, public.y WHERE x.xid = y.xid $$;

Then you'd have to change the function to be:

  CREATE FUNCTION foo() AS $$
    SELECT x.a, y.b FROM newschema.x, newschema.y WHERE x.xid = y.xid $$;

does that make any more sense?

I actually mis-read the original suggestion from Scott as being:

  ALTER SCHEMA foo RENAME TO bar;

I.e. just rename the whole schema across with everything inside it.

The same caveats would apply.

--
  Sam  http://samason.me.uk/