Thread: alter table schema in 8.0

Hi!

I am migrating a relativly huge database from postgres 7.2 to 8.0. I
need to alter schema
for sth about 500 tables and twice as many other objects. Do I need to
sth other on a new database than:

create schema ... ;
update pg_class set relnamespace =$oid_of_new_schema where
relnamespace=$oid_of_old_schema;
update pg_proc set pronamespace =$oid_of_new_schema where
pronamespace=$oid_of_old_schema and not proname in(select a.proname from
pg_proc a, pg_proc b where a.pronamespace=$oid_of_new_schema and
b.pronamespace=$oid_of_old_schema and a.proname=b.proname);

where $oid_of_old_schema is the oid of the public namespace.

I didn't found any other reference to pg_catalog.pg_namespace.oid.
Did anybody try something like this?

I examined the sources of  AlterTableNamespace() defined in tablecmds.c
but it wasn't realy helpful.
I will appreciate any help.

Best regards,

--
Marcin Inkielman

Marcin Inkielman <marn@wsisiz.edu.pl> writes:
> I am migrating a relativly huge database from postgres 7.2 to 8.0. I
> need to alter schema
> for sth about 500 tables and twice as many other objects. Do I need to
> sth other on a new database than:

I'd suggest updating to 8.1 (where there's ALTER SET SCHEMA commands)
instead of trying to mess with the system catalogs by hand.  Your sketch
fails to account for updating pg_depend, and I'm not sure offhand if you
missed anything else.

            regards, tom lane