[coolminded]

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.

[rski]

Originally Posted by coolminded

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?

[coolminded]

Originally Posted by rski

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

[rski]

Originally Posted by coolminded

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?