Re: transfering tables into other schema

On Tue, Feb 17, 2009 at 3:31 PM, Ivan Sergio Borgonovo
<mail(at)webthatworks(dot)it> wrote:
> 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.

You are wrong. The problem is that the function plan is cached in
your current session. Try this. alter your table to another schema.
then \q. then psql back in. Then SET YOUR SEARCH PATH to match the
schemas

set search_path='public','new_schema';

and then run the function and it should work.

> Do constraints survive?

Yes