Change owner of multiple tables of a PostgreSQL database
I use the following tiny SQL script to change the owner of multiple tables at once. Here, the owner of all tables of the public schema is changed:
create or replace function exec(text) returns void as $$
begin
execute $1;
end;
$$ language plpgsql;
select table_name || exec('alter table '||table_name||' owner to newuser')
as "Changed ownership"
from information_schema.tables where table_schema='public';
This works also for other database structures such as sequences (PostgreSQL 8.3):
select sequence_name || exec('alter table '||sequence_name||' owner to newuser')
as "Changed ownership"
from information_schema.sequences where sequence_schema='public';
Written by Lars Behnke
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Sql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#