Re: How to change all owners on all objects in a schema - Mailing list pgsql-admin

"Campbell, Lance" <lance@illinois.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