how to alter all view's owner in postgresql

Question

In postgresql-9.2 database there are 125 views stored.Among them 75 views owner is sa.

So,is there any method can I apply to alter the view's having owner sa to postgres.?

Solution 1

To find out the view associated with the owner sa

select 
      viewname 
from 
      pg_catalog.pg_views
where
      schemaname NOT IN ('pg_catalog', 'information_schema') 
and 
      viewowner = 'sa'

---

To ALTER view's owner we can use :ALTER VIEW <view_name> OWNER TO <owner_name>
As per documentation:

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 [, ... ] )

---

finally, to find out the view associated with the owner sa and ALTER use the following

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;
$$;

Solution 2

To change all the objects owned by sa, you can use Reassign Owned.

To change individual views, you can use Alter View.

You should be able to script the procedure in the second link

Comments

[Admin]

actually this is not what I want

[Admin]

Hi, I am totally new to pl/sql. After a quick search I can't find the type "record" in pl/sql. And also the "v" written after the parentheses seems to be an abbreviation. What's this syntax called? I would like to search for it and learn how to use it in other situations. Thanks!

[Vivek S.]

@kumom When you refer PostgreSQL search pl/pgsql. Refer declaration from here. v is the alias which I had given to the subselect select viewname from pg_catalog.pg_views where schemaname NOT IN ('pg_catalog', 'information_schema') and viewowner = 'sa'