Re: Some useful plpgsql
От | Berend Tober |
---|---|
Тема | Re: Some useful plpgsql |
Дата | |
Msg-id | 65451.66.212.203.144.1046467423.squirrel@$HOSTNAME обсуждение исходный текст |
Ответ на | Re: Some useful plpgsl (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
>> 1) To change the schema associated with given tables. > > I think this is likely to break things, particularly if it's used to > move individual tables and not the entire contents of a schema. I > don't see anything here that will rename a table's rowtype into the new > schema, and I don't see any guarantee that a table's indexes will move You're absolutely correct! The problem was that I (successfully) used a SIMILAR technique which involved more manual inspection of intermediate steps to accomplish this, and then I lost some important details in the translation trying to make a more generic solution that others could use. I think the function is still potentially useful, but you'ld have to run a more general query, starting with something like SELECT relname, alter_object_namespace('paid', relname) FROM pg_catalog.pg_class and then play with it a bit in order to better identify the objects you want to effect, such as SELECT relname, relowner, ( SELECT usename FROM pg_catalog.pg_user WHERE usesysid=relowner) as usename, alter_object_namespace('paid', relname) FROM pg_catalog.pg_class followed by SELECT relname, relowner, ( SELECT usename FROM pg_catalog.pg_user WHERE usesysid=relowner) as usename, alter_object_namespace('paid', relname) FROM pg_catalog.pg_class WHERE relowner = [oid_of_relation_owner] Then output the results to a text file and delete entries by hand that were not of interest. Seems like a hell of a process, but I didn't see an alternative besides re-creating the database one table at a time with the new schema names in place. ~Berend Tober
В списке pgsql-general по дате отправления: