Re: transfering tables into other schema

On Tue, 17 Feb 2009 17:36:32 +0000
Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> 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:

So... somehow everything is going to happen by magic if everything
was in public schema... mmm

create language 'plpgsql';

create schema new_schema;

create table x(
xid int primary key,
a varchar(2)
);
create table y(
xid int references x(xid),
b varchar(2)
);

create or replace function x(out varchar(2), out varchar(2)) returns
setof record
as
$$
begin
return query select a,b from x join y on x.xid=y.xid;
end;
$$ language plpgsql;

test=# select * from x();
column1 | column2
---------+---------
(0 rows)

alter table y set schema new_schema;

test=# SELECT * from x();
ERROR: relation "y" does not exist
CONTEXT: SQL statement " select a,b from x join y on x.xid=y.xid"
PL/pgSQL function "x" line 2 at RETURN QUERY
test=# SELECT a,b from x join new_schema.y on y.xid=x.xid;
a | b
---+---
(0 rows)

I did a similar test changing x schema with similar results.
So, pk/fk relationships survive. Function don't survive.

Do constraints survive?

I'm running 8.3.6

--
Ivan Sergio Borgonovo
http://www.webthatworks.it