Re: transfering tables into other schema

On Tue, Feb 17, 2009 at 8:42 PM, Schwaighofer Clemens
<clemens(dot)schwaighofer(at)tequila(dot)jp> wrote:
> On Wed, Feb 18, 2009 at 07:31, Ivan Sergio Borgonovo
> <mail(at)webthatworks(dot)it> wrote:
>> 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 just moved one table to the new schema. 'x' is still in the old,
> where 'y' is in the new schema.
>
> If you want to keep one table in "public" and the other in
> "new_schema" then you have to rewrite all functions. I am not sure
> about constraints, they might do that automatically.

This is incorrect. As long as both tables are in your search path it
will work just fine. But you do have to re-connect to flush your
cached plans for the functions.

> Advertising Age Global Agency of the Year 2008
> Adweek Global Agency of the Year 2008
>
> This e-mail is intended only for the named person or entity to which
19 lines snipped.
> agencies or affiliates.

Wow, could you have a longer mandatory but legally worthless
signature? :) I know, lots of companies have them. Interestingly,
including them in ALL emails is what makes them basically worthless
for the emails you would really need them for.