All:
Ideas for recursively changing the ownership of all objects in a database
to a new user?
- There is no way to specify recursion in "ALTER TABLE OWNER TO rolename"
- Globbing table names in "ALTER TABLE * OWNER TO rolename" does not work.
- To get a list of tables, you can do:
SELECT table_schema,table_name from information_schema.tables where
table_schema !~ '.*(catalog|info rmation_schema).*';
However to get a list of sequences, you have to:
"SELECT relname from pg_class where relkind='S';
And so on and so on a different approach for all other types of objects:
Procedures, Schemas, Database, Tablespaces, Languages, Views, Triggers,
Domains..
Then you have to loop those through a for loop with a different syntax to
ALTER, or (coming to mind just now) a sub-query instead of asterisk in
ALTER [Object].
Another option is to dump the schema and use regex to alter OWNER
statements.
Ideas on more efficient ways to do this?
l8*
-lava (Brian A. Seklecki - Pittsburgh, PA, USA)
http://www.spiritual-machines.org/