【openGauss/PostgreSQL】openGauss/PostgreSQL批量修改对象owner

---

PostgreSQL批量修改对象owner

创建测试数据

创建数据库

psql -U postgres
create user test password 'test';
create database testdb;
\c testdb
CREATE SCHEMA test AUTHORIZATION postgres;

创建对象

export PGPASSWORD=test
psql -U postgres -d testdb
set searc_path to test;
-- 创建域
create domain phone_type as text constraint phone_check check (VALUE ~ '^\d{11}$');
-- 创建数据类型
CREATE TYPE human_sex AS ENUM ('male', 'female');
CREATE TYPE test_type AS (f1 int, f2 text);
-- 创建序列
create sequence employees_s;
create sequence employees_s1;
-- 创建测试表
create table test_t1(id int);
create table test_t2(id int);
create table test_t3(id int);
create table test_t4(id int);
create table employees
    ( employee_id    int8    primary key
    , first_name     varchar(20)
    , last_name      varchar(25)
    , sex            human_sex
    , email          varchar(25)
    , phone_number   phone_type
    , salary         numeric(8,2)
    , last_update_date timestamp
    , constraint     emp_salary_min check (salary > 0) 
    , constraint     emp_email_uk unique (email)
    ) ;
-- 插入数据
insert into employees values(nextval('employees_s'),'King','Johnn','male','johnn@163.com','15145264084',10000,now());
-- 以下两条应该报错
insert into employees values(nextval('employees_s'),'Job','Lucy','female','lucy@163.com','151452640841',10000,now());
insert into employees values(nextval('employees_s'),'Job','Lucy','females','lucy@163.com','15145264084',10000,now());
-- 创建索引
create index idx_name on employees(first_name,last_name);

视图

-- 普通视图
create view emp as select * from employees;
-- 物化视图
create materialized view emp_v as select * from employees;
refresh materialized view emp_v;

创建触发器

-- 普通触发器
CREATE OR REPLACE FUNCTION update_time_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_update_date := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_time_trigger BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION update_time_column();

-- 事件触发器
CREATE OR REPLACE FUNCTION DISABLE_DROP_TABLE()
RETURNS event_trigger AS $$
BEGIN
    if tg_tag = 'DROP TABLE'  THEN
        RAISE EXCEPTION 'Command % is disabled.', tg_tag;
    END if;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER DISABLE_DROP_TABLE on ddl_command_start EXECUTE FUNCTION DISABLE_DROP_TABLE();

函数和存储过程

-- 函数
CREATE FUNCTION inc(val integer) RETURNS integer AS $$
BEGIN
  RETURN val + 1;
END;
$$ LANGUAGE PLPGSQL;

CREATE FUNCTION inc(val1 integer,val2 integer) RETURNS integer AS $$
BEGIN
  RETURN val1 + val2;
END;
$$ LANGUAGE PLPGSQL;


-- 存储过程
CREATE OR REPLACE PROCEDURE test_sum(a NUMERIC,b NUMERIC,C NUMERIC)
AS $$
DECLARE
  val int;
BEGIN
  val := a+b+c;
  RAISE NOTICE 'Total is : % !',val;
END;
$$ language plpgsql;

对象信息查询

-- 数据库
select d.datname as "database",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
		d.datacl AS "Access privileges"
FROM pg_catalog.pg_database d where datname = 'testdb';
-- 模式
SELECT n.nspname AS "schema",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  n.nspacl AS "Access privileges"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
-- 域
select t2.nspname,t1.typname,t3.usename as owner from pg_type t1
  join pg_namespace t2 on t1.typnamespace=t2.oid
  join pg_user t3 on t1.typowner=t3.usesysid
where t1.typtype ='d'
    and t2.nspname='test';
-- 数据类型
SELECT t.typname AS type_name,
       t.typtype AS type_category,
       t.typowner AS owner_id,
       t.typnamespace::regnamespace AS schema_name,
       t.typacl AS access_privileges
FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
LEFT JOIN pg_class c ON c.reltype = t.oid
WHERE t.typtype IN ('c', 'e', 'd', 'b')  -- 仅筛选用户自定义类型
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- 排除系统命名空间
  AND c.relkind IS NULL  -- 排除表、视图和序列
ORDER BY type_name;

-- 对象
select t2.nspname as "schema",
       t1.relname as "object_name",
       case when t1.relkind='r' then '普通表'
            when t1.relkind='i' then '索引'
            when t1.relkind='S' then '序列'
            when t1.relkind='v' then '视图'
            when t1.relkind='m' then '物化视图' end
        as "object_type",
			 t3.usename as "owner",
			 t1.relacl
from pg_class t1,pg_namespace t2,pg_user t3
where t1.relnamespace=t2.oid
and t2.nspname = 'test'
and t1.relowner=t3.usesysid;
-- 约束
select t2.nspname as schema,t3.relname as table,t1.conname,
       case when t1.contype='c' then '检查约束'
            when t1.contype='p' then '主键约束'
            when t1.contype='u' then '唯一约束' end as contype,
       t4.relname as index
from pg_constraint t1 join pg_namespace t2 on t1.connamespace = t2.oid
     join pg_class t3 on t1.conrelid = t3.oid
     left join pg_class t4 on t1.conindid = t4.oid
where t2.nspname = 'test';
-- 普通触发器
select t3.nspname as schema,t2.relname,tgname from pg_trigger t1
  join pg_class t2 on t1.tgrelid=t2.oid
  join pg_namespace t3 on t2.relnamespace=t3.oid
 where t3.nspname = 'test';
-- 事件触发器
select t1.evtname,t2.usename from pg_event_trigger t1
  join pg_user t2 on t1.evtowner = t2.usesysid;
-- 函数/存储过程
select t2.nspname as schema,t1.proname,t3.usename as owner,
       case when t1.prokind='f' then '函数'
            when t1.prokind='p' then '存储过程' end as prokind
from pg_proc t1
  join pg_namespace t2 on t1.pronamespace=t2.oid
  join pg_user t3 on t3.usesysid = t1.proowner
where t2.nspname = 'test';

注意：约束、索引、表上的触发器（普通触发器）是和表关联的，只用修改表的属主即可

批量修改对象owner

针对数据库和模式

alter database testdb owner to test;
alter schema test owner to test;

针对其他对象：

DO $$ << change_owner >>
DECLARE
    new_owner  text := 'test';   -- 变更后的对象owner
    table_names text;
    sequence_names text;
    view_names text;
    mview_names text;
    function_names text;
    procedure_names text;
    domain_names text;
BEGIN
    -- 修改表的owner
    FOR table_names IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_type = 'BASE TABLE' LOOP
        EXECUTE 'ALTER TABLE test.' || table_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改序列的owner
    FOR sequence_names IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'test' LOOP
        EXECUTE 'ALTER SEQUENCE test.' || sequence_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改视图的owner
    FOR view_names IN SELECT table_name FROM information_schema.views WHERE table_schema = 'test' LOOP
        EXECUTE 'ALTER VIEW test.' || view_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改物化视图的owner
    FOR mview_names IN select matviewname from pg_matviews where schemaname = 'test' LOOP
        EXECUTE 'ALTER MATERIALIZED VIEW test.' || mview_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改函数的owner
    FOR function_names IN SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'test' AND routine_type = 'FUNCTION' LOOP
        EXECUTE 'ALTER FUNCTION test.' || function_names || '() OWNER TO '|| new_owner;
    END LOOP;
    -- 修改存储过程的owner
    FOR procedure_names IN SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'test' AND routine_type = 'PROCEDURE' LOOP
        EXECUTE 'ALTER PROCEDURE test.' || procedure_names || '() OWNER TO '|| new_owner;
    END LOOP;
    -- 修改域的owner
    FOR domain_names in select t1.typname from pg_type t1 join pg_namespace t2 on t1.typnamespace=t2.oid join pg_user t3 on t1.typowner=t3.usesysidwhere t1.typtype ='d' and t2.nspname='test' loop
        EXECUTE 'ALTER DOMAIN test.' || domain_names || ' OWNER TO '|| new_owner;
END change_owner$$;