PostgreSQL模拟Oracle dba_objects

PostgreSQL模拟Oracle dba_objects查询出schema下所有的用户自定义对象

创建测试数据

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

创建对象

export PGPASSWORD=test
psql -U test -d testdb

-- 创建域
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)
    ) ;

create table tbp(id int,date timestamp(6),col2 text) partition by range(date);
create table tbp_2020 partition of tbp for values from ('2020-01-01') to ('2021-01-01');
create table tbp_2021 partition of tbp for values from ('2021-01-01') to ('2022-01-01');
create table tbp_2022 partition of tbp for values from ('2022-01-01') to ('2023-01-01');
create table tbp_2023 partition of tbp for values from ('2023-01-01') to ('2024-01-01');
create table tbp_default partition of tbp default;

-- 创建主分区表
CREATE TABLE sales (
    id int,
    region VARCHAR(50),
    sales_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sales_date);
-- 创建一级分区
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') PARTITION BY LIST (region);
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') PARTITION BY LIST (region);
-- 创建二级分区
CREATE TABLE sales_2023_north PARTITION OF sales_2023 FOR VALUES IN ('North');
CREATE TABLE sales_2023_south PARTITION OF sales_2023 FOR VALUES IN ('South');
CREATE TABLE sales_2023_east PARTITION OF sales_2023 FOR VALUES IN ('East');
CREATE TABLE sales_2023_west PARTITION OF sales_2023 FOR VALUES IN ('West');
CREATE TABLE sales_2024_north PARTITION OF sales_2024 FOR VALUES IN ('North');
CREATE TABLE sales_2024_south PARTITION OF sales_2024 FOR VALUES IN ('South');
CREATE TABLE sales_2024_east PARTITION OF sales_2024 FOR VALUES IN ('East');
CREATE TABLE sales_2024_west PARTITION OF sales_2024 FOR VALUES IN ('West');

-- 插入数据
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 index idx_id on tbp(id);
create index idx_id2 on sales(id);
-- 创建约束
ALTER TABLE employees ADD CONSTRAINT check_salary CHECK (salary>0);

视图

-- 普通视图
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 relname as object_name,case when relkind='r' and relispartition='f' and relhassubclass='f' then '普通表'
                    when relkind='p' and relispartition='f' and relhassubclass='t' then '分区表主表'
                    when relkind='p' and relispartition='t' and relhassubclass='t' then '分区表一级分区'
                    when relkind='r' and relispartition='t' and relhassubclass='f' then '分区表子表'
										when relkind='i' and relispartition='f' and relhassubclass='f' then '普通索引'
										when relkind='I' and relispartition='f' and relhassubclass='t' then '分区表主表索引'
										when relkind='i' and relispartition='t' and relhassubclass='f' then '分区表子表索引'
										when relkind='I' and relispartition='t' and relhassubclass='t' then '分区表一级分区索引'
										when relkind='S' then '序列'
										when relkind='c' then '组合类型'
										when relkind='m' then '物化视图'
										when relkind='v' then '普通视图'
										end as object_type
from pg_class
where relnamespace::REGNAMESPACE::text='test'
order by 2 nulls last;

数据类型

select typname as object_name,'数据类型' as object_type from pg_type t
left join pg_class c ON c.reltype = t.oid
where t.typnamespace::REGNAMESPACE::text='test'
AND c.relkind IS NULL
and t.typname !~ '^_';

约束

select conname as object_name,'约束' as object_type from pg_constraint where connamespace::REGNAMESPACE::text='test';

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,
       concat(t2.relname,'.',tgname) as object_name,
			 '普通触发器' as object_type
  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 concat(event_object_table,'.',trigger_name) as object_name,'trigger' as object_type from information_schema.triggers where trigger_schema='test';

-- 事件触发器
select evtname as object_name,'事件触发器' as object_type from pg_event_trigger;

存储过程/函数

select 
translate(concat(proname,(select array(select format_type(val,null) from unnest(proargtypes) as val))::text),'{}','()') as object_name,
case when prokind='f' then '函数' when prokind='p' then '存储过程' end as object_type
from pg_proc
where pronamespace::REGNAMESPACE::text='test';

合并查询总数量

with schema_info as (
select unnest(ARRAY['test']) as schema_name    -- 模式名
)
select object_type,count(*) from (
-- 表（分区表和普通表）、索引（分区索引和普通索引）、视图、物化视图、序列
select relname as object_name,
               case when relkind='r' and relispartition='f' and relhassubclass='f' then '普通表'
                    when relkind='p' and relispartition='f' and relhassubclass='t' then '分区表主表'
                    -- when relkind='p' and relispartition='t' and relhassubclass='t' then '分区表一级分区'
                    -- when relkind='r' and relispartition='t' and relhassubclass='f' then '分区表子表'
										when relkind='i' and relispartition='f' and relhassubclass='f' then '普通索引'
										-- when relkind='I' and relispartition='f' and relhassubclass='t' then '分区表主表索引'
										-- when relkind='i' and relispartition='t' and relhassubclass='f' then '分区表子表索引'
										-- when relkind='I' and relispartition='t' and relhassubclass='t' then '分区表一级分区索引'
										when relkind='S' then '序列'
										when relkind='c' then '组合类型'
										when relkind='m' then '物化视图'
										when relkind='v' then '普通视图'
										end as object_type
from pg_class join schema_info on (pg_class.relnamespace::REGNAMESPACE::text=schema_info.schema_name)
union all
-- 数据类型
select typname as object_name,'数据类型' as object_type from pg_type t
left join pg_class c ON c.reltype = t.oid
join schema_info on (t.typnamespace::REGNAMESPACE::text=schema_info.schema_name)
AND c.relkind IS NULL
and t.typname !~ '^_'
union all
-- 约束
select conname as object_name,'约束' as object_type from pg_constraint where connamespace::REGNAMESPACE::text=(select * from schema_info)
union all
-- 触发器
select -- t3.nspname as schema,
       concat(t2.relname,'.',tgname) as object_name,
			 '普通触发器' as object_type
  from pg_trigger t1
  join pg_class t2 on t1.tgrelid=t2.oid
  join pg_namespace t3 on t2.relnamespace=t3.oid
  join schema_info on (t3.nspname=schema_info.schema_name)
union all
select evtname as object_name,'事件触发器' as object_type from pg_event_trigger
union all
-- 存储过程/函数
select 
translate(concat(proname,(select array(select format_type(val,null) from unnest(proargtypes) as val))::text),'{}','()') as object_name,
case when prokind='f' then '函数' when prokind='p' then '存储过程' end as object_type
from pg_proc
join schema_info on (pg_proc.pronamespace::REGNAMESPACE::text=schema_info.schema_name)) t
where t.object_type is not null
group by 1
order by 2 desc;