Re: How to change all owners on all objects in a schema

"Campbell, Lance" <lance(at)illinois(dot)edu> wrote:

> PostgreSQL: 9.0.x
>
> How do I change the ownership of all objects within a schema from
> X to Y? The objects within each schema would be tables and
> sequences.

I would use DO statements. What could be done as a one-liner to
handle the tables is shown below with line breaks for readability.

do $$
declare stmt text;
begin
for stmt in
select 'alter table oldschema.'
|| quote_ident(relname)
|| ' set schema newschema;'
from pg_class
where relkind = 'r'
and relnamespace =
(
select oid from pg_catalog.pg_namespace
where nspname = 'oldschema'
)
loop
raise notice '%', stmt;
end loop;
end;
$$;

Similar for sequences, only ALTER SEQUENCE and relkind = 'S'.

-Kevin