>> 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
