Recursive/Wildcard Object Ownership Change

["Brian A. Seklecki" <lavalamp(at)spiritual-machines(dot)org>]

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/