Re: transfering tables into other schema

On Wed, Feb 18, 2009 at 10:44:27AM +0100, Ivan Sergio Borgonovo wrote:
> It looks like I may put myself in a situation where I may get
> trapped by insidious bugs related to the order in which the schema
> path is read.
>
> I think I prefer to spend some time qualifying the schema in the
> functions so if I'm going to run in any bug I'll find it earlier
> than later.

Because of PG's somewhat arbitrary use of lexical/dynamic scoping this is
recommended practice. For example:

CREATE TABLE foo ( i INT );
CREATE VIEW testview AS SELECT i FROM foo;
CREATE FUNCTION testfn() RETURNS SETOF INT LANGUAGE SQL AS $$
SELECT i FROM foo $$;

The view is lexically bound and the function dynamically bound. This
means that running the function later will cause it to look for what
"foo" means at-the-moment, rather than when it was defined, but the view
always refers back to the same relation as when it was defined. For
example:

ALTER TABLE foo RENAME TO bar;
SELECT * from testview;
SELECT * FROM testfn();

The view will continue to work, but the function now fails.

> Before I try to see what happens to every object, relation, etc...
> etc... is there anything else that is going to change
> automatically/should be done manually once I move a table in a new
> schema?

The answer to that gets complicated pretty quickly; is it possible to
back the database up and restore it to a development database? Once
there you can make the change and see what happens inside the database,
and more importantly how your code deals with the change.

Sam