Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?

[Joe Conway]

Tom Lane wrote:

Joe Conway <[EMAIL PROTECTED]> writes:

Someone asked earlier about how to change a bunch of existing tables int the PUBLIC schema to some other schema. For grins I tried:
regression=# update pg_class set relnamespace=556829 where relname = 'foo' and relnamespace=2200;
UPDATE 1

and it seemed to work fine (i.e. moved foo from schema public to schema bar).

But it didn't fix the pg_depend entries linking the table to its schema :-(

Yeah, I knew there was something I was forgetting. That's why I didn't actually offer it up as a solution to anyone.

But it made me wonder if we shouldn't have:
  ALTER TABLE table SCHEMA TO new_schema

I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y

I don't see anything in the SQL spec about this; anyone know what
precedent is in Oracle or other DBMSes?

Good question. I can't find anything in the Oracle docs indicating it is even possible. We should probably just go with your suggestion. Anything else beyond the relnamespace and pg_depend entries that need to be dealt with?

Joe



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org