1.此时需要把所有数据表权限赋给ivms8700:,所以执行以下sql:
reate or replace function func_fq(dbuser varchar(64)) returns int as
$$
declare my_recd record;
declare num int;
declare parameter text;
begin
execute 'alter database '|| current_database() ||' owner to ' || dbuser;
execute 'alter schema public owner to ' || dbuser;
for my_recd in select * from pg_tables where schemaname in ('public') loop
execute 'alter table ' || my_recd.tablename || ' owner to ' || dbuser;
end loop;
for my_recd in select * from pg_views where schemaname in ('public') loop
execute 'alter view ' || my_recd.viewname || ' owner to ' || dbuser;
end loop;
for my_recd in select * from information_schema.sequences where sequence_schema in ('public') loop
execute 'alter sequence ' || my_recd.sequence_name || ' owner to ' || dbuser;
end loop;
for my_recd in select proc.oid, * from pg_proc proc inner join pg_namespace nps on proc.pronamespace=nps.oid where nspname in ('public') loop
execute 'alter function ' || my_recd.proname||'(' || pg_get_function_identity_arguments(my_recd.oid) || ') owner to ' || dbuser;
end loop;
RETURN 0;
end;
$$ language plpgsql;
select func_fq('ivms8700');
drop function func_fq(dbuser varchar(64));
2.查询某个数据库的用户信息
select * from pg_stat_activity where datid = (select oid from pg_database where datname='coms_louyu')
这是查询coms_louyu这个数据库的
3.查询数据库的用户
select * from pg_user