在postgresql-9.2数据库中,存储了125个视图。其中75个视图的所有者是sa。那么,有没有一种方法可以将拥有者为sa的视图更改为postgres?
查找与所有者 sa
相关联的视图
select
viewname
from
pg_catalog.pg_views
where
schemaname NOT IN ('pg_catalog', 'information_schema')
and
viewowner = 'sa'
要更改视图的所有者,我们可以使用:ALTER VIEW <view_name> OWNER TO <owner_name>
根据文档:
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )
最后,要查找与所有者sa
和ALTER
相关联的视图,请使用以下命令:
do $$
declare
myrow record;
begin
for myrow in
select
'ALTER VIEW '||quote_ident(v.viewname)||' OWNER TO "postgres";' as viewq
from
(select
viewname
from
pg_catalog.pg_views
where
schemaname NOT IN ('pg_catalog', 'information_schema')
and
viewowner = 'sa'
) v
loop
execute myrow.viewq;
end loop;
end;
$$;
v
是我给子选择器select viewname from pg_catalog.pg_views where schemaname NOT IN ('pg_catalog', 'information_schema') and viewowner = 'sa'
指定的别名。 - Vivek S.