By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

change ownership

100+
P: 138
hi,
i have create one database with owner 'postgres' and i created tables with the same owner. but now i want to change the owner of the database as well as the tables. i changed the owner of database but i couldn't change the owner of the tables all at once. my postgres database is in linux server. and it's version is
"PostgreSQL 8.1.3 "
thanx.
May 11 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
hi,
i have create one database with owner 'postgres' and i created tables with the same owner. but now i want to change the owner of the database as well as the tables. i changed the owner of database but i couldn't change the owner of the tables all at once. my postgres database is in linux server. and it's version is
"PostgreSQL 8.1.3 "
thanx.
You can do it like that
1. create very usefull function
Expand|Select|Wrap|Line Numbers
  1. create or replace function exec(text) returns void as $$
  2. begin
  3. execute $1;
  4. end;
  5. $$ language plpgsql
  6.  
2. using this function you can do a lot of usefull queries for example
Expand|Select|Wrap|Line Numbers
  1. select exec('alter table '||table_name||' owner to new_owner') from information_schema.tables where table_schema='public'.
  2.  
changes owner of all tables in the current database in schema public.
Of course a where clause can be more comlicated.
Was it helpfull?
May 11 '08 #2

100+
P: 138
You can do it like that
1. create very usefull function
Expand|Select|Wrap|Line Numbers
  1. create or replace function exec(text) returns void as $$
  2. begin
  3. execute $1;
  4. end;
  5. $$ language plpgsql
  6.  
2. using this function you can do a lot of usefull queries for example
Expand|Select|Wrap|Line Numbers
  1. select exec('alter table '||table_name||' owner to new_owner') from information_schema.tables where table_schema='public'.
  2.  
changes owner of all tables in the current database in schema public.
Of course a where clause can be more comlicated.
Was it helpfull?
i 'm little bit confused with the second query....
i want to ask, is this function recursive, i mean , does it change the ownership of all the tables at once.

lets' say i have 3 tables in a database test_db
tbl_1
tbl_2
tbl_3
how can i change the ownership of these tables at once?
does the 2nd query help me to do so?
i couldn't get it. plz will u make me understand once again

with regards,
coolminded
May 11 '08 #3

Expert 100+
P: 700
i 'm little bit confused with the second query....
i want to ask, is this function recursive, i mean , does it change the ownership of all the tables at once.

lets' say i have 3 tables in a database test_db
tbl_1
tbl_2
tbl_3
how can i change the ownership of these tables at once?
does the 2nd query help me to do so?
i couldn't get it. plz will u make me understand once again

with regards,
coolminded
What do you mean at once, concurrently (in computer science mean of that word)? If so i'm not sure if it is possible.
The select query (sequentially) for each table that is in public schema executes a function i defined which executes dynamic query, for your table you can write
Expand|Select|Wrap|Line Numbers
  1. select exec('alter table '||table_name||' owner to new_owner') from information_schema.tables where table_name in('tbl_1','tbl_2','tbl_3');
  2.  
is that clear?
May 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.