代码拉取完成，页面将自动刷新
-- This scripts contains following view/synonym's definition:
-- =============================================================================
-- [DBA,ALL,USER]_CATALOG
-- [DBA,ALL,USER]_CONS_COLUMNS
-- [DBA,ALL,USER]_CONSTRAINTS
-- [DBA,ALL,USER]_DATA_FILES
-- [DBA,ALL,USER]_DEPENDENCIES
-- [DBA,ALL,USER]_IND_COLUMNS
-- [DBA,ALL,USER]_IND_EXPRESSIONS
-- [DBA,ALL,USER]_IND_PARTITIONS
-- [DBA,ALL,USER]_IND_STATISTICS
-- [DBA,ALL,USER]_INDEX_USAGE
-- [DBA,ALL,USER]_INDEXES
-- [DBA,ALL,USER]_OBJECTS
-- [DBA,ALL,USER]_PART_INDEXES
-- [DBA,ALL,USER]_PART_TABLES
-- [DBA,ALL,USER]_PROCEDURES
-- [DBA,ALL,USER]_SEGMENTS
-- [DBA,ALL,USER]_SEQUENCES
-- [DBA,ALL,USER]_SOURCE
-- [DBA,ALL,USER]_SYNONYMS
-- [DBA,ALL,USER]_TAB_COL_STATISTICS
-- [DBA,ALL,USER]_TAB_COLS
-- [DBA,ALL,USER]_TAB_COLUMNS
-- [DBA,ALL,USER]_TAB_COMMENTS
-- [DBA,ALL,USER]_COL_COMMENTS
-- [DBA,ALL,USER]_TAB_MODIFICATIONS
-- [DBA,ALL,USER]_TAB_PARTITIONS
-- [DBA,ALL,USER]_TAB_SUBPARTITIONS
-- [DBA,ALL,USER]_PART_KEY_COLUMNS
-- [DBA,ALL,USER]_SUBPART_KEY_COLUMNS
-- [DBA,ALL,USER]_TAB_STATISTICS
-- [DBA,ALL,USER]_TABLES
-- [DBA,ALL,USER]_TABLESPACES
-- [DBA,ALL,USER]_TRIGGER_COLS
-- [DBA,ALL,USER]_TRIGGERS
-- [DBA,ALL,USER]_TYPES
-- [DBA,ALL,USER]_VIEWS
-- [DBA,ALL,USER]_JOBS
-- [DBA,ALL,USER]_JOBS_RUNNING
-- [DBA,ALL,USER]_MVIEWS
-- [DBA,ALL,USER]_MVIEW_LOGS
-- [DBA,ALL,USER]_MVIEW_COMMENTS
-- [DBA,ALL,USER]_USERS
-- [DBA,ALL]_DIRECTORIES
-- [DBA,ALL,USER]_ARGUMENTS
-- DBA_ROLES
-- [DBA,USER]_ROLE_PRIVS
-- PRODUCT_COMPONENT_VERSION
-- PLAN_TABLE
-- DBA_SOURCE_ALL
-- NLS_DATABASE_PARAMETERS
-- NLS_INSTANCE_PARAMETERS
-- NLS_SESSION_PARAMETERS
-- COLS
-- DICT
-- DICTIONARY
-- DUAL (For 2.0 and above)
-- IND
-- OBJ
-- TAB
-- [GV,V]$DATABASE
-- [GV,V]$INSTANCE
-- [GV,V]$DATAFILE
-- [GV,V]$LOCK
-- [GV,V]$PARAMETER
-- [GV,V]$PARAMETER_VALID_VALUES
-- [GV,V]$SESSION
-- [GV,V]$SESSTAT
-- [GV,V]$SPPARAMETER
-- [GV,V]$SYSSTAT
-- [GV,V]$TABLESPACE
-- [GV,V]$VERSION
-- [GV,V]$NLS_PARAMETERS
-- [GV,V]$NLS_VALID_VALUES
-- [GV,V]$SYSTEM_WAIT_CLASS
-- [GV,V]$SQL
-- [GV,V]$OPTION
-- [GV,V]$LICENSE
-- [GV,V]$STATNAME
-- [GV,V]$MYSTAT
-- DBA_DETAIL_PRIVILEGES
-- DBA_ALL_PRIVILEGES
-- DBA_ALL_PRIVILEGES_SQL
-- =============================================================================
set client_min_messages='warning';
set statement_timeout = 60000;
set behavior_compat_options = '';
-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
l_cnt bigint;
l_version varchar(10);
begin
select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'compat_tools';
if l_cnt = 0
then
create schema compat_tools;
end if;
end;
$$ language plpgsql;
-- =============================================================================
-- Version Table
-- =============================================================================
create table if not exists compat_tools.compat_version
(
compat_type varchar(10), -- VIEW, FUNCTION, PROCEDURE
object_name varchar(128), -- Compat object name
object_version varchar(10), -- Compat object version
constraint pk_compat_version primary key(compat_type, object_name)
);
-- =============================================================================
-- Table: compat_tools.pg_function_list
-- =============================================================================
drop table if exists compat_tools.pg_function_list;
create table compat_tools.pg_function_list as
select p.oid
, l.lanname as language
, n.nspname as schema_name
, p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as function_name
from (select oid
, pronamespace
, proname
, prolang
, case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
, generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
, unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
from pg_catalog.pg_proc
) as p
join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
join pg_catalog.pg_language as l on p.prolang = l.oid
join pg_catalog.pg_type as t on p.protype = t.oid
left join pg_catalog.pg_type as a on t.typcategory = 'A' and t.typelem = a.oid -- for array type
group by p.proname, p.proallargtypes, l.lanname, p.oid, n.nspname
union all
select p.oid, l.lanname, n.nspname, p.proname||'()'
from pg_catalog.pg_proc as p
join pg_catalog.pg_namespace as n on p.pronamespace = n.oid
join pg_catalog.pg_language as l on p.prolang = l.oid
where (oidvectortypes(p.proargtypes) is null or oidvectortypes(p.proargtypes) = '');
-- =============================================================================
-- Table: compat_tools.pg_depend_list
-- =============================================================================
drop table if exists compat_tools.pg_depend_list;
create table compat_tools.pg_depend_list as
select distinct dep.classid::regclass::text as object_type
, coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) as schema_name
, coalesce(typ.typname, coalesce(cls.relname, dep.objid::text)) as object_name
, dep.refclassid::regclass::text as ref_object_type
, cpt.object_name as ref_object_name
, cpt.compat_type
, case dep.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else dep.deptype::text end as DEPENDENCY_TYPE
from pg_depend as dep
join (select v.compat_type, v.object_name, l.language, coalesce(l.oid, c.oid) as oid
from compat_tools.compat_version as v
left join compat_tools.pg_function_list as l on v.object_name = l.schema_name||'.'||l.function_name
left join (select cls.oid, nsp.nspname||'.'||cls.relname object_name
from pg_catalog.pg_class as cls
join pg_catalog.pg_namespace as nsp on cls.relnamespace = nsp.oid
) as c on v.object_name = c.object_name
where v.compat_type in ('aggregate', 'procedure', 'function', 'view')) as cpt on dep.refobjid = cpt.oid
left join pg_type as typ on dep.classid = 'pg_type'::regclass and dep.objid = typ.oid
left join pg_namespace as typ_n on typ_n.oid = typ.typnamespace
left join pg_rewrite as rwt on dep.classid = 'pg_rewrite'::regclass and dep.objid = rwt.oid
left join pg_class as cls on rwt.ev_class = cls.oid
left join pg_namespace as cls_n on cls_n.oid = cls.relnamespace
where coalesce(typ_n.nspname, coalesce(cls_n.nspname, dep.objid::text)) != 'compat_tools';
-- =============================================================================
-- Version Function
-- =============================================================================
drop function if exists compat_tools.drop_compat_object(varchar, varchar, varchar, varchar);
create or replace function compat_tools.drop_compat_object( p_object_type varchar(10)
, p_object_name varchar(128)
, p_object_version varchar(10)
, p_object_schema varchar(128) default 'compat_tools')
returns boolean
as $$
declare
l_version varchar(10);
l_depend_cnt bigint;
l_app_name varchar(128) := current_setting('application_name');
l_result boolean := 'true';
l_operation varchar(128);
begin
CREATE TEMPORARY TABLE if not exists temp_result
(
object_type varchar(10),
object_name varchar(128),
local_version varchar(10),
script_version varchar(10),
local_language varchar(10),
script_language varchar(10),
operation varchar(128)
) ON COMMIT PRESERVE ROWS;
-- 字符串参数统一转小写
p_object_name := lower(p_object_name);
p_object_type := lower(p_object_type);
p_object_schema := lower(p_object_schema);
-- 获取当前目标库中的对象版本
select max(object_version) into l_version
from compat_tools.compat_version
where object_name = p_object_schema||'.'||p_object_name
and compat_type = p_object_type;
-- 获取非 Compat Tools 依赖对象数量
select count(*) into l_depend_cnt
from compat_tools.pg_depend_list
where ref_object_name = p_object_schema||'.'||p_object_name;
if l_version >= p_object_version
then
l_operation := 'Skip';
l_result := 'false';
if l_app_name != 'checkMe'
then
insert into compat_tools.compat_version
values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
ON DUPLICATE KEY UPDATE NOTHING;
end if;
else
l_operation := case when l_version is null then 'Initial creation' else 'Upgrade' end;
l_result := 'true';
if l_app_name != 'checkMe'
then
begin
-- 若系统中存在非 compat_tools 对象依赖本对象，无法删除，可尝试直接创建
if l_depend_cnt = 0
then
if instr(p_object_name, '(') > 0
then
execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||replace(p_object_name, '(', '"(')||' cascade';
else
execute 'drop '||p_object_type||' if exists "'||p_object_schema||'"."'||p_object_name||'" cascade';
end if;
insert into compat_tools.compat_version
values (p_object_type, p_object_schema||'.'||p_object_name, p_object_version)
ON DUPLICATE KEY UPDATE object_version = p_object_version;
else
l_operation := l_operation||' - dependence';
end if;
exception
when others then
l_result := 'false';
get stacked diagnostics l_operation = message_text;
l_operation = substr(l_operation, 1, 128);
end;
end if;
end if;
-- 插入本次临时结果表
insert into temp_result (object_type, object_name, local_version, script_version, operation)
values (p_object_type, p_object_schema||'.'||p_object_name, l_version, p_object_version, l_operation);
-- 返回函数结果
if l_app_name = 'checkMe'
then
return 'false';
else
return l_result;
end if;
end;
$$ language plpgsql;
-- =============================================================================
-- Version Comparison Function
-- Result:
-- version_a > version_b => 1
-- version_a = version_b => 0
-- version_a < version_b => -1
-- =============================================================================
create or replace function pg_catalog.f_version_compare ( version_a text
, version_b text)
returns int IMMUTABLE strict as $$
declare
l_rec record;
begin
if version_a !~ '^\d+(\.\d+)*$' or version_b !~ '^\d+(\.\d+)*$'
then
return null;
end if;
for l_rec in select coalesce(t1.ver, -1) as ver_1, coalesce(t2.ver, -1) as ver_2
from (select rownum as id, t1::int as ver from regexp_split_to_table(version_a, '\.') as t1) as t1
full outer join (select rownum as id, t2::int as ver from regexp_split_to_table(version_b, '\.') as t2) as t2
on t1.id = t2.id
loop
if l_rec.ver_1 > l_rec.ver_2
then
return 1;
elsif l_rec.ver_1 < l_rec.ver_2
then
return -1;
end if;
end loop;
return 0;
end;
$$ language plpgsql;
-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
l_cnt bigint;
begin
-- =========================================================================
-- FUNCTION: F_UPPER_NAME(text,text)
-- =========================================================================
begin
if compat_tools.drop_compat_object('FUNCTION', 'F_UPPER_NAME(text,text)', '2.0')
then
create or replace function compat_tools.f_upper_name(name text,convert_ind text default 'Y')
returns text immutable as
$$ select case when convert_ind='Y' then upper(name) else name end;
$$ language sql;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in F_UPPER_NAME(text,text): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- FUNCTION: MG_SEQUENCE()
-- 20231124 2.2版本，指定owner字段的数据类型，确保输出类型稳定
-- =========================================================================
begin
if compat_tools.drop_compat_object('FUNCTION', 'MG_SEQUENCE()', '2.2')
then
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.9.9') = 1 then
create or replace function compat_tools.mg_sequence()
returns setof record
language plpgsql
as $$
declare
l_seq_item record;
begin
for l_seq_item in SELECT nc.nspname as owner,
c.relname AS sequence_name
FROM pg_catalog.pg_namespace nc
join pg_catalog.pg_class c on c.relnamespace = nc.oid
WHERE c.relkind in ( 'S'::"char",'L'::"char")
AND NOT pg_is_other_temp_schema(nc.oid)
AND has_sequence_privilege((case when c.relkind in ('S'::"char",'L'::"char") then c.oid end),'SELECT'::text)
loop
return query execute 'select tableoid, '''||l_seq_item.owner||'''::text as owner, sequence_name, last_value::int16, start_value::int16, increment_by::int16, max_value::int16, min_value::int16, cache_value::int16, is_cycled from '||l_seq_item.owner||'.'||l_seq_item.sequence_name;
end loop;
end;
$$;
else
create or replace function compat_tools.mg_sequence()
returns setof record
language plpgsql
as $$
declare
l_seq_item record;
begin
for l_seq_item in SELECT nc.nspname as owner,
c.relname AS sequence_name
FROM pg_catalog.pg_namespace nc
join pg_catalog.pg_class c on c.relnamespace = nc.oid
WHERE c.relkind in ( 'S'::"char",'L'::"char")
AND NOT pg_is_other_temp_schema(nc.oid)
AND has_sequence_privilege((case when c.relkind='S'::"char" then c.oid end),'SELECT'::text)
loop
return query execute 'select tableoid, '''||l_seq_item.owner||''' as owner, sequence_name, last_value, start_value, increment_by, max_value, min_value, cache_value, is_cycled from '||l_seq_item.owner||'.'||l_seq_item.sequence_name;
end loop;
end;
$$;
end if;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in MG_SEQUENCE(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_SEQUENCES
-- ALL_SEQUENCES
-- USER_SEQUENCES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_SEQUENCES', '4.1')
then
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.9.9') = 1 then
CREATE OR REPLACE VIEW compat_tools.DBA_SEQUENCES
AS
select sequence_oid as sequence_oid
, case when owner::text = lower(owner::text) then compat_tools.f_upper_name(owner::text) else owner::text end AS SEQUENCE_OWNER
, case when sequence_name::text = lower(sequence_name::text) then compat_tools.f_upper_name(sequence_name::text) else sequence_name::text end AS SEQUENCE_NAME
, min_value
, max_value
, increment_by
, case when is_cycled then 'Y' else 'N' end AS CYCLE_FLAG
, cache_value as CACHE_SIZE
, last_value AS LAST_NUMBER
, start_value
from compat_tools.mg_sequence()
as ( sequence_oid oid
, owner text
, sequence_name name
, last_value int16
, start_value int16
, increment_by int16
, max_value int16
, min_value int16
, cache_value int16
, is_cycled boolean);
else
CREATE OR REPLACE VIEW compat_tools.DBA_SEQUENCES
AS
select sequence_oid as sequence_oid
, case when owner::text = lower(owner::text) then compat_tools.f_upper_name(owner::text) else owner::text end AS SEQUENCE_OWNER
, case when sequence_name::text = lower(sequence_name::text) then compat_tools.f_upper_name(sequence_name::text) else sequence_name::text end AS SEQUENCE_NAME
, min_value
, max_value
, increment_by
, case when is_cycled then 'Y' else 'N' end AS CYCLE_FLAG
, cache_value as CACHE_SIZE
, last_value AS LAST_NUMBER
, start_value
from compat_tools.mg_sequence()
as ( sequence_oid oid
, owner text
, sequence_name name
, last_value bigint
, start_value bigint
, increment_by bigint
, max_value bigint
, min_value bigint
, cache_value bigint
, is_cycled boolean);
end if;
CREATE OR REPLACE VIEW compat_tools.USER_SEQUENCES AS SELECT sequence_oid, SEQUENCE_NAME , min_value, max_value, increment_by, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER, start_value
FROM compat_tools.DBA_SEQUENCES WHERE SEQUENCE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_SEQUENCES for compat_tools.DBA_SEQUENCES;
CREATE OR REPLACE SYNONYM public.ALL_SEQUENCES for compat_tools.DBA_SEQUENCES;
CREATE OR REPLACE SYNONYM public.USER_SEQUENCES for compat_tools.USER_SEQUENCES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_SEQUENCES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_SYNONYMS
-- ALL_SYNONYMS
-- USER_SYNONYMS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_SYNONYMS', '2.0')
then
create or replace view compat_tools.DBA_SYNONYMS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when s.synname::text = lower(s.synname::text) then compat_tools.f_upper_name(s.synname::text) else s.synname::text end AS SYNONYM_NAME
, case when s.synobjschema::text = lower(s.synobjschema::text) then compat_tools.f_upper_name(s.synobjschema::text) else s.synobjschema::text end AS TABLE_OWNER
, case when s.synobjname::text = lower(s.synobjname::text) then compat_tools.f_upper_name(s.synobjname::text) else s.synobjname::text end AS TABLE_NAME
from pg_catalog.pg_synonym as s
join pg_catalog.pg_namespace as n on s.synnamespace = n.oid;
CREATE OR REPLACE VIEW compat_tools.USER_SYNONYMS AS SELECT SYNONYM_NAME, TABLE_OWNER , TABLE_NAME FROM compat_tools.DBA_SYNONYMS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_SYNONYMS for compat_tools.DBA_SYNONYMS;
CREATE OR REPLACE SYNONYM public.ALL_SYNONYMS for compat_tools.DBA_SYNONYMS;
CREATE OR REPLACE SYNONYM public.USER_SYNONYMS for compat_tools.USER_SYNONYMS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_SYNONYMS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_COLS
-- ALL_TAB_COLS
-- USER_TAB_COLS
-- COLS
-- DBA_TAB_COLUMNS
-- ALL_TAB_COLUMNS
-- USER_TAB_COLUMNS
-- =========================================================================
-- 变更历史：
-- 2023-09-11 修复A模式下varchar类型data_length为null的问题
-- 2023-12-09 重写获取data_length的逻辑
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COLS', '3.0')
then
create or replace function compat_tools.f_get_type_max_length(pg_attribute,pg_type)returns int4 immutable as
$$
select (case
--bpchar(n) varchar(n) nvarchar(n)
when ($1.atttypid in (1042,1043,3969) or $2.typbasetype in (1042,1043,3969)) and $1.atttypmod>0 then $1.atttypmod -4
--bpchar varchar nvarchar
when ($1.atttypid in (1042,1043,3969) or $2.typbasetype in (1042,1043,3969)) and $1.atttypmod<0 then 10*2^10 -- 10M
--numeric
when $1.atttypid in (1700) or $2.typbasetype in (1700) then 2^16 -- actually,extra + 9 bytes ,64K
--text clob json xml unknown hstore
when $1.atttypid in (25,90,114,142,705,15601) or $2.typbasetype in (25,90,114,142,705,15601) then 2^30-1 -- 1G-1
--bytea raw blob jsonb
when $1.atttypid in (17,86,88,3802) or $2.typbasetype in (17,86,88,3802) then 2^30-8203 -- 1G-8203
--byteawithoutorderwithequalcol byteawithoutordercol
when $1.atttypid in (4402,4403) or $2.typbasetype in (4402,4403) then 2^30-53 -- 1G-53
--bit varbit
when $1.atttypid in (1560,1562) or $2.typbasetype in (1560,1562) then $1.atttypmod
--inet cidr
when $1.atttypid in (869,650) or $2.typbasetype in (869,650) then 2^4+3 -- 16+3
--hll
when $1.atttypid in (4301) or $2.TYPBASETYPE in (4301) then 2^14 -- 16K
--dolphin bin type
when $2.typname in ('binary','tinyblob') then 2^8-1 -- 255B
when $2.typname in ('mediumblob') then 2^14-1 -- 16K-1
when $2.typname in ('varbinary') then 2^16-1 -- 64K-1
when $2.typname in ('longblob') then 2^32-1 -- 4G-1
--others
else $1.attlen end)::int4;
$$language sql;
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COLS
AS
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
, case when cls.relname::text = lower(cls.relname::text) then compat_tools.f_upper_name(cls.relname::text) else cls.relname::text end AS TABLE_NAME
, case when att.attname::text = lower(att.attname::text) then compat_tools.f_upper_name(att.attname::text) else att.attname::text end AS COLUMN_NAME
, case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end AS DATA_TYPE
, case when tnsp.nspname::text = lower(tnsp.nspname::text) then compat_tools.f_upper_name(tnsp.nspname::text) else tnsp.nspname::text end AS DATA_TYPE_OWNER
, compat_tools.f_get_type_max_length(att.*,typ.*) as DATA_LENGTH
, information_schema._pg_char_octet_length(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_LENGTH_OCTET
, information_schema._pg_numeric_precision(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_PRECISION
, information_schema._pg_numeric_scale(information_schema._pg_truetypid(att.*, typ.*), information_schema._pg_truetypmod(att.*, typ.*)) as DATA_SCALE
, CASE when att.attnotnull THEN 'N'::text ELSE 'Y'::text END AS NULLABLE
, att.attnum as COLUMN_ID
, length(pg_get_expr(ad.adbin, ad.adrelid)) as DEFAULT_LENGTH
, pg_get_expr(ad.adbin, ad.adrelid) as DATA_DEFAULT
, CASE WHEN stat.n_distinct >= 0 THEN stat.n_distinct ELSE ROUND(ABS(stat.n_distinct * cls.RELTUPLES)) END as NUM_DISTINCT
, stat.correlation
, stat.NULL_FRAC * cls.RELTUPLES AS NUM_NULLS
, stat.avg_width as AVG_COL_LEN
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type typ on att.atttypid = typ.oid
JOIN pg_catalog.pg_namespace tnsp on typ.typnamespace = tnsp.oid
JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid
JOIN pg_catalog.pg_namespace nsp on cls.relnamespace = nsp.oid
LEFT JOIN pg_catalog.PG_STATS stat ON nsp.nspname = stat.schemaname AND cls.relname = stat.tablename AND att.attname = stat.attname
LEFT JOIN pg_catalog.pg_attrdef ad ON att.attrelid = ad.adrelid AND att.attnum = ad.adnum
WHERE nsp.nspname NOT LIKE 'pg_toast%'
AND cls.relkind in ('r', 'v', 't', 'f'); -- 限制表和视图
CREATE OR REPLACE VIEW compat_tools.USER_TAB_COLS
AS SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_OWNER, DATA_LENGTH, DATA_LENGTH_OCTET, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, CORRELATION, NUM_NULLS, AVG_COL_LEN
FROM compat_tools.DBA_TAB_COLS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COLUMNS AS SELECT * FROM compat_tools.DBA_TAB_COLS WHERE COLUMN_ID >= 1;
CREATE OR REPLACE VIEW compat_tools.USER_TAB_COLUMNS AS SELECT * FROM compat_tools.USER_TAB_COLS WHERE COLUMN_ID >= 1;
CREATE OR REPLACE SYNONYM public.DBA_TAB_COLS for compat_tools.DBA_TAB_COLS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_COLS for compat_tools.DBA_TAB_COLS;
CREATE OR REPLACE SYNONYM public.USER_TAB_COLS for compat_tools.USER_TAB_COLS;
CREATE OR REPLACE SYNONYM public.COLS for compat_tools.DBA_TAB_COLS;
CREATE OR REPLACE SYNONYM public.DBA_TAB_COLUMNS for compat_tools.DBA_TAB_COLUMNS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_COLUMNS for compat_tools.DBA_TAB_COLUMNS;
CREATE OR REPLACE SYNONYM public.USER_TAB_COLUMNS for compat_tools.USER_TAB_COLUMNS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_COLS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_COL_STATISTICS
-- ALL_TAB_COL_STATISTICS
-- USER_TAB_COL_STATISTICS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COL_STATISTICS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COL_STATISTICS
AS
SELECT OWNER
, TABLE_NAME
, COLUMN_NAME
, NUM_DISTINCT
, CORRELATION
, NUM_NULLS
, AVG_COL_LEN
FROM compat_tools.DBA_TAB_COLUMNS;
CREATE OR REPLACE VIEW compat_tools.USER_TAB_COL_STATISTICS AS SELECT TABLE_NAME , COLUMN_NAME, NUM_DISTINCT, CORRELATION, NUM_NULLS, AVG_COL_LEN FROM compat_tools.DBA_TAB_COL_STATISTICS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_COL_STATISTICS for compat_tools.DBA_TAB_COL_STATISTICS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_COL_STATISTICS for compat_tools.DBA_TAB_COL_STATISTICS;
CREATE OR REPLACE SYNONYM public.USER_TAB_COL_STATISTICS for compat_tools.USER_TAB_COL_STATISTICS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_COL_STATISTICS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_OBJECTS
-- ALL_OBJECTS
-- USER_OBJECTS
-- OBJ
-- =========================================================================
-- 2022-01-25 [1.1 -> 2.0] 添加 TABLE/INDEX SUBPARTITION 对象类型的识别
-- 2022-04-20 [2.0 -> 3.0] 针对 openGauss 2.1 版本，增加 Package 相关对象
-- 2023-12016 [4.0 -> 5.0] 针对plsql编译依赖功能及type-object功能重写dba_objects视图，
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_OBJECTS', '5.0')
then
select count(1) into l_cnt from pg_attribute
where (attrelid=(SELECT pc.oid FROM pg_class pc ,pg_namespace pn WHERE pc.relnamespace=pn.oid AND relname='gs_package' AND nspname='pg_catalog') and attname='isobject')
or (attrelid='pg_object'::regclass and attname='valid');
if l_cnt=2 then
CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS AS
select
case when "owner"::regnamespace::text = lower("owner"::regnamespace::text) then compat_tools.f_upper_name("owner"::regnamespace::text) else "owner"::regnamespace::text end AS owner
, case when object_name::text = lower(object_name::text) then compat_tools.f_upper_name(object_name::text) else object_name::text end AS object_name
, case when subobject_name::text = lower(subobject_name::text) then compat_tools.f_upper_name(subobject_name::text) else subobject_name::text end AS SUBOBJECT_NAME
,
object_id,data_object_id,object_type,created,last_ddl_time,"timestamp",
status,"temporary","generated",secondary,"namespace",edition_name,
compat_tools.f_upper_name(creator::regrole::text) as creator
from (
--pg_class 1. 表、视图、物化视图、索引、序列
select c.relnamespace as owner,
c.relname as object_name,
null ::text as subobject_name,
c.oid as object_id,
c.relfilenode as data_object_id,
CASE relkind
WHEN 'r' ::"char" THEN 'TABLE' ::text
WHEN 'v' ::"char" THEN 'VIEW' ::text
WHEN 'm' ::"char" THEN 'MATERIALIZED VIEW' ::text
WHEN 'i' ::"char" THEN 'INDEX' ::text
WHEN 'I' ::"char" THEN 'GLOBAL INDEX' ::text
WHEN 'S' ::"char" THEN 'SEQUENCE' ::text
WHEN 'f' ::"char" THEN 'FOREIGN TABLE' ::text
WHEN 'c' ::"char" THEN 'COMPOSITE TYPE' ::text
WHEN 't' ::"char" THEN 'TOAST' ::text
when 'L' ::"char" then 'LARGE SEQUENCE' ::text
ELSE relkind ::text
END AS object_type,
obj.ctime::timestamp(0) without time zone as created,
obj.mtime::timestamp(0) without time zone as last_ddl_time,
to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as TIMESTAMP,
case when c.relkind in ('v','m') then (case rr.ev_enabled when 'D' then 'INVALID' else 'VALID' END) else 'VALID' end as STATUS,
case when relpersistence in ('t', 'g') THEN 'Y' else 'N' end as temporary,
null ::text as generated,
null ::text as SECONDARY,
1 ::int1 as namespace,
null ::text as EDITION_NAME,
relowner ::regrole as creator
from pg_class c
left join pg_object obj
on obj.object_oid = c.oid
left join pg_rewrite rr
on c.oid=rr.ev_class
union all
--pg_partition 2. 分区/子分区对象
SELECT CASE p.parttype
WHEN 's' THEN st.relnamespace ELSE pp.relnamespace END AS owner,
CASE p.parttype WHEN 's' THEN st.relname ELSE pp.relname END AS object_name,
p.relname as SUBOBJECT_NAME,
p.oid AS OBJECT_ID,
p.relfilenode as DATA_OBJECT_ID,
CASE p.parttype
WHEN 'p' ::"char" THEN 'TABLE PARTITION' ::text
WHEN 'x' ::"char" THEN (CASE it.parttype WHEN 'p' THEN 'INDEX PARTITION'
ELSE 'INDEX SUBPARTITION' end) ::text
WHEN 't' ::"char" THEN 'TOAST PARTITION' ::text
WHEN 's' ::"char" THEN 'TABLE SUBPARTITION' ::text
ELSE p.parttype ::text
END AS object_type,
null ::timestamp(0) without time zone as created,
null ::timestamp(0) without time zone as last_ddl_time,
null ::text as timestamp,
'VALID' as status,
'N' as temporary,
null ::text as generated,
null ::text as SECONDARY,
1 ::int1 as namespace,
null ::text as EDITION_NAME,
CASE p.parttype
WHEN 's' THEN st.relowner ELSE pp.relowner
END as creator
FROM pg_catalog.pg_partition as p -- 分区/子分区
left join pg_catalog.pg_class as pp
on p.parentid = pp.oid -- 分区的 parent
left join pg_catalog.pg_partition as sp
on p.parentid = sp.oid -- 子分区的分区
left join pg_catalog.pg_class as st
on sp.parentid = st.oid -- 子分区的分区的 parent
left join pg_catalog.pg_partition as it
on p.indextblid = it.oid -- 分区索引的 table (分区/子分区)
where p.parttype in ('p', 'x', 't', 's')
union all
--pg_proc 3.函数、过程、触发器
SELECT pronamespace AS owner,
proname AS object_name,
null as sub_object_name,
p.OID as OBJECT_ID,
null as DATA_OBJECT_ID,
CASE
WHEN prorettype = 'trigger' ::regtype ::oid THEN 'TRIGGER' ::text
ELSE
(case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END AS object_type,
null ::timestamp(0) without time zone as created,
null ::timestamp(0) without time zone as last_ddl_time,
null ::text as timestamp,
case
when obj."valid" then 'VALID' else 'INVALID'
end as status,
'N' as temporary,
null ::text as generated,
null ::text as SECONDARY,
1 ::int1 as namespace,
null ::text as EDITION_NAME,
proowner as creator
FROM pg_catalog.pg_proc p
left join pg_object obj
on obj.object_oid = p.oid
WHERE propackageid = 0
union all
--pg_synonym 4.同义词
SELECT synnamespace as owner,
synname as object_name,
null as sub_object_name,
oid as object_Id,
null as DATA_OBJECT_ID,
'SYNONYM' as object_type,
null ::timestamp(0) without time zone as created,
null ::timestamp(0) without time zone as last_ddl_time,
null ::text as timestamp,
'VALID' as status,
'N' as temporary,
null ::text as generated,
null ::text as SECONDARY,
1 ::int1 as namespace,
null ::text as EDITION_NAME,
synowner as creator
from pg_synonym
union all
--gs_package 5.plsql包、plsql类型
select pkg.pkgnamespace,
pkg.pkgname,
null as sub_object_name,
case
when pkg.isobject then tp.oid else pkg.oid
end as object_Id,
null as DATA_OBJECT_ID,
case
when pkg.isobject then 'TYPE'
else 'PACKAGE'
end ||
case OBJ.object_type when 'B' then ' BODY'
end as object_type,
obj.ctime::timestamp(0) without time zone as created,
obj.mtime::timestamp(0) without time zone as last_ddl_time,
to_char(obj.ctime, 'YYYY-MM-DD:HH24:MI:SS') as timestamp,
case
when obj."valid" then 'VALID' else 'INVALID'
end as status,
'N' as temporary,
null ::text as generated,
null ::text as SECONDARY,
(case OBJ.object_type when 'S' then 1 else 2 end) ::int1 as namespace,
null ::text as EDITION_NAME,
pkg.pkgowner as creator
from gs_package PKG
left join pg_type tp
on (case
when pkg.isobject then
(pkg.pkgnamespace ::regnamespace ::text || '.' || pkg.pkgname) ::regtype ::oid
end) = tp.oid
left join pg_object obj
on pkg.oid = obj.object_oid
union all
--pg_type 6.数据类型、自定义类型、集合类型
select tp.typnamespace,
tp.typname,
null ::text as subobject_name,
tp.oid,
null as data_object_id,
'TYPE' as object_type,
null::timestamp(0) without time zone as created,
null::timestamp(0) without time zone as last_ddl_time,
null as timestamp,
'VALID' as status,
'N' as temporary,
null ::text as generated,
null ::text as SECONDARY,
1 ::int1 as namespace,
null ::text as EDITION_NAME,
tp.typowner as creator
from pg_type tp left join pg_class c
on tp.typrelid =c.oid
where instantiable is null
and nvl(relkind,'o') not in ('r','v','m','t','S','L')
and (tp.typelem = 0 or typtype = 'o' )
and instr(tp.typname,'.')=0
);
CREATE OR REPLACE VIEW compat_tools.USER_OBJECTS AS
SELECT object_name,
subobject_name,
object_id,data_object_id,
object_type,
created,
last_ddl_time,
"timestamp",
status,
"temporary",
"generated",
secondary,
"namespace",
edition_name,
creator
FROM compat_tools.DBA_OBJECTS
WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text)
then compat_tools.f_upper_name(current_schema()::text)
else current_schema()::text end);
else
-- 不含 Package 版本的视图
CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS_LIST_WITHOUT_PKG
AS
SELECT relowner as creator -- 1. 常规对象
, relnamespace AS owner
, relname AS object_name
, NULL as SUBOBJECT_NAME
, oid AS OBJECT_ID
, relfilenode as DATA_OBJECT_ID
, CASE relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
WHEN 'i'::"char" THEN 'INDEX'::text
WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
WHEN 'S'::"char" THEN 'SEQUENCE'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
WHEN 't'::"char" THEN 'TOAST'::text
ELSE relkind::text
END AS object_type
, case when relpersistence = 't' THEN 'Y' else 'N' end as TEMPORARY
FROM pg_catalog.pg_class
UNION ALL
SELECT CASE p.parttype WHEN 's' THEN st.relowner ELSE pp.relowner END as creator -- 2. 分区/子分区对象
, CASE p.parttype WHEN 's' THEN st.relnamespace ELSE pp.relnamespace END AS owner
, CASE p.parttype WHEN 's' THEN st.relname ELSE pp.relname END AS object_name
, p.relname as SUBOBJECT_NAME
, p.oid AS OBJECT_ID
, p.relfilenode as DATA_OBJECT_ID
, CASE p.parttype
WHEN 'p'::"char" THEN 'TABLE PARTITION'::text
WHEN 'x'::"char" THEN (CASE it.parttype WHEN 'p' THEN 'INDEX PARTITION' ELSE 'INDEX SUBPARTITION' end)::text
WHEN 't'::"char" THEN 'TOAST PARTITION'::text
WHEN 's'::"char" THEN 'TABLE SUBPARTITION'::text
ELSE p.parttype::text
END AS object_type
, 'N' as TEMPORARY
FROM pg_catalog.pg_partition as p -- 分区/子分区
left join pg_catalog.pg_class as pp on p.parentid = pp.oid -- 分区的 parent
left join pg_catalog.pg_partition as sp on p.parentid = sp.oid -- 子分区的分区
left join pg_catalog.pg_class as st on sp.parentid = st.oid -- 子分区的分区的 parent
left join pg_catalog.pg_partition as it on p.indextblid = it.oid -- 分区索引的 table (分区/子分区)
where p.parttype in ('p', 'x', 't', 's')
UNION ALL
SELECT proowner as creator -- 3. 自定义函数（存储过程，函数，触发器）
, pronamespace AS owner
, proname AS object_name
, null as sub_object_name
, min(OID) as OBJECT_ID
, null as DATA_OBJECT_ID
, CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END AS object_type
, 'N' as temporary
FROM pg_catalog.pg_proc
WHERE pg_function_is_visible(oid)
group by proowner, pronamespace, proname
, CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END
UNION ALL
SELECT synowner as creator -- 4. 同义词
, synnamespace as owner
, synname as object_name
, null as sub_object_name
, oid as object_Id
, null as DATA_OBJECT_ID
, 'SYNONYM' as object_type
, 'N' as temporary
from pg_catalog.pg_synonym;
-- 根据是否有 gs_package 进行二次包装
select count(*) into l_cnt
from pg_class
where relname = 'gs_package'
and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog');
if l_cnt = 0 -- 2.0 以及以下版本中，没有 gs_package 表
then
CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS owner
, case when o1.object_name::text = lower(o1.object_name::text) then compat_tools.f_upper_name(o1.object_name::text) else o1.object_name::text end AS object_name
, case when o1.SUBOBJECT_NAME::text = lower(o1.SUBOBJECT_NAME::text) then compat_tools.f_upper_name(o1.SUBOBJECT_NAME::text) else o1.SUBOBJECT_NAME::text end AS SUBOBJECT_NAME
, o1.OBJECT_ID
, o1.DATA_OBJECT_ID
, o1.object_type
, o2.ctime as CREATED
, o2.mtime as LAST_DDL_TIME
, o1.TEMPORARY
, compat_tools.f_upper_name(u.usename) as creator
FROM compat_tools.dba_objects_list_without_pkg as o1
JOIN pg_catalog.pg_namespace as n on o1.owner = n.oid
JOIN pg_catalog.pg_user as u on o1.creator = u.usesysid
left JOIN pg_catalog.pg_object as o2 on o1.object_id = o2.object_oid
where n.nspname NOT LIKE 'pg_toast%';
else -- 2.1 以及以上版本中，还需要在 dba_objects_list_without_pkg 基础上，增加 package/package body 相关数据
CREATE OR REPLACE VIEW compat_tools.DBA_OBJECTS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS owner
, case when o1.object_name::text = lower(o1.object_name::text) then compat_tools.f_upper_name(o1.object_name::text) else o1.object_name::text end AS object_name
, case when o1.SUBOBJECT_NAME::text = lower(o1.SUBOBJECT_NAME::text) then compat_tools.f_upper_name(o1.SUBOBJECT_NAME::text) else o1.SUBOBJECT_NAME::text end AS SUBOBJECT_NAME
, o1.OBJECT_ID
, o1.DATA_OBJECT_ID
, o1.object_type
, o2.ctime as CREATED
, o2.mtime as LAST_DDL_TIME
, o1.TEMPORARY
, compat_tools.f_upper_name(u.usename) as creator
FROM (/*SELECT creator -- 常规对象，分区，子分区，PLSQL，同义词
, owner
, object_name
, subobject_name
, object_id
, data_object_id
, object_type
, temporary
FROM compat_tools.dba_objects_list_without_pkg*/
SELECT relowner as creator -- 1. 常规对象
, relnamespace AS owner
, relname AS object_name
, NULL as SUBOBJECT_NAME
, oid AS OBJECT_ID
, relfilenode as DATA_OBJECT_ID
, CASE relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
WHEN 'i'::"char" THEN 'INDEX'::text
WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
WHEN 'S'::"char" THEN 'SEQUENCE'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
WHEN 't'::"char" THEN 'TOAST'::text
ELSE relkind::text
END AS object_type
, case when relpersistence = 't' THEN 'Y' else 'N' end as TEMPORARY
FROM pg_catalog.pg_class
UNION ALL
SELECT CASE p.parttype WHEN 's' THEN st.relowner ELSE pp.relowner END as creator -- 2. 分区/子分区对象
, CASE p.parttype WHEN 's' THEN st.relnamespace ELSE pp.relnamespace END AS owner
, CASE p.parttype WHEN 's' THEN st.relname ELSE pp.relname END AS object_name
, p.relname as SUBOBJECT_NAME
, p.oid AS OBJECT_ID
, p.relfilenode as DATA_OBJECT_ID
, CASE p.parttype
WHEN 'p'::"char" THEN 'TABLE PARTITION'::text
WHEN 'x'::"char" THEN (CASE it.parttype WHEN 'p' THEN 'INDEX PARTITION' ELSE 'INDEX SUBPARTITION' end)::text
WHEN 't'::"char" THEN 'TOAST PARTITION'::text
WHEN 's'::"char" THEN 'TABLE SUBPARTITION'::text
ELSE p.parttype::text
END AS object_type
, 'N' as TEMPORARY
FROM pg_catalog.pg_partition as p -- 分区/子分区
left join pg_catalog.pg_class as pp on p.parentid = pp.oid -- 分区的 parent
left join pg_catalog.pg_partition as sp on p.parentid = sp.oid -- 子分区的分区
left join pg_catalog.pg_class as st on sp.parentid = st.oid -- 子分区的分区的 parent
left join pg_catalog.pg_partition as it on p.indextblid = it.oid -- 分区索引的 table (分区/子分区)
where p.parttype in ('p', 'x', 't', 's')
UNION ALL
SELECT proowner as creator -- 3. 自定义函数（存储过程，函数，触发器）
, pronamespace AS owner
, proname AS object_name
, null as sub_object_name
, min(OID) as OBJECT_ID
, null as DATA_OBJECT_ID
, CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END AS object_type
, 'N' as temporary
FROM pg_catalog.pg_proc
WHERE pg_function_is_visible(oid)
group by proowner, pronamespace, proname
, CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END
UNION ALL
SELECT synowner as creator -- 4. 同义词
, synnamespace as owner
, synname as object_name
, null as sub_object_name
, oid as object_Id
, null as DATA_OBJECT_ID
, 'SYNONYM' as object_type
, 'N' as temporary
from pg_catalog.pg_synonym
UNION ALL
SELECT pkgowner -- PACKAGE
, pkgnamespace
, pkgname
, null as SUBOBJECT_NAME
, oid AS OBJECT_ID
, null as DATA_OBJECT_ID
, 'PACKAGE'
, 'N' as TEMPORARY
FROM pg_catalog.gs_package
UNION ALL
SELECT pkgowner -- PACKAGE BODY
, pkgnamespace
, pkgname
, null as SUBOBJECT_NAME
, oid AS OBJECT_ID
, null as DATA_OBJECT_ID
, 'PACKAGE BODY'
, 'N' as TEMPORARY
FROM pg_catalog.gs_package
WHERE pkgbodydeclsrc is not null
UNION ALL
select typowner
, typnamespace
, typname
, null as SUBOBJECT_NAME
, OID as OBJECT_ID
, null as DATA_OBJECT_ID
, 'TABLE TYPE'
, 'N' as TEMPORARY
from pg_type PT where typtype='o'
) as o1
JOIN pg_catalog.pg_namespace as n on o1.owner = n.oid
JOIN pg_catalog.pg_user as u on o1.creator = u.usesysid
left JOIN pg_catalog.pg_object as o2 on o1.object_id = o2.object_oid
where n.nspname NOT LIKE 'pg_toast%';
end if;
CREATE OR REPLACE VIEW compat_tools.USER_OBJECTS AS SELECT object_name, subobject_name, object_id, data_object_id, object_type, temporary FROM compat_tools.DBA_OBJECTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
end if;
CREATE OR REPLACE SYNONYM public.DBA_OBJECTS for compat_tools.DBA_OBJECTS;
CREATE OR REPLACE SYNONYM public.ALL_OBJECTS for compat_tools.DBA_OBJECTS;
CREATE OR REPLACE SYNONYM public.USER_OBJECTS for compat_tools.USER_OBJECTS;
CREATE OR REPLACE SYNONYM public.OBJ for compat_tools.USER_OBJECTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_OBJECTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_CATALOG
-- ALL_CATALOG
-- USER_CATALOG
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_CATALOG', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_CATALOG
AS
SELECT owner
, object_name AS TABLE_NAME
, object_type as TABLE_TYPE
FROM compat_tools.dba_objects c
WHERE object_type in ('TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM');
CREATE OR REPLACE VIEW compat_tools.USER_CATALOG AS SELECT TABLE_NAME, TABLE_TYPE FROM compat_tools.DBA_CATALOG WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_CATALOG for compat_tools.DBA_CATALOG;
CREATE OR REPLACE SYNONYM public.ALL_CATALOG for compat_tools.DBA_CATALOG;
CREATE OR REPLACE SYNONYM public.USER_CATALOG for compat_tools.USER_CATALOG;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_CATALOG: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DICTIONARY COMMENTS 字段均为空
-- DICT
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DICTIONARY', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DICTIONARY
AS
SELECT OWNER
, OBJECT_NAME AS TABLE_NAME
, d.description as COMMENTS
FROM compat_tools.dba_objects as o
LEFT JOIN pg_catalog.pg_description d on o.object_id = d.objoid and d.objsubid = 0
WHERE o.object_type in ('TABLE', 'VIEW')
and o.owner in (
compat_tools.f_upper_name('pg_catalog'),
compat_tools.f_upper_name('information_schema'),
compat_tools.f_upper_name('dbe_perf'),
compat_tools.f_upper_name('pkg_service'),
compat_tools.f_upper_name('snapshot'),
compat_tools.f_upper_name('cstore'));
CREATE OR REPLACE SYNONYM public.DICTIONARY for compat_tools.DICTIONARY;
CREATE OR REPLACE SYNONYM public.DICT for compat_tools.DICTIONARY;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DICTIONARY: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_DEPENDENCIES
-- ALL_DEPENDENCIES
-- USER_DEPENDENCIES
-- =========================================================================
-- 假设： A 依赖 B
-- ===========================
-- DEPENDENCY_NORMAL (n): A 删除不影响 B, B cascade 删除同步删除 A
-- DEPENDENCY_AUTO (a): 删除 B 自动删除 A (不管是 restrict 或 cascade)。例子: 约束对表的依赖
-- DEPENDENCY_INTERNAL (i): A 是 B 的一部分，不能单独删除 A, 删除 B 自动删除 A (无需 cascade)
-- DEPENDENCY_EXTENSION (e): A 是 B extension 的一个成员。A 只能在 drop extension 的时候删除
-- DEPENDENCY_PIN (p): 系统本身依赖于被引用对象，决不能被删除。只在 initdb 时创建，A 是零
-- =========================================================================
-- 变更历史
-- 2022-06-29 1.1 修复不同对象拥有相同 oid 导致的查询数据不正确的问题
-- 2022-07-08 1.2 修复 gs_package 在低于 3.0 版本的数据库中不存在导致的报错
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_DEPENDENCIES', '2.0')
then
-- 根据是否有 gs_package 进行不同的视图创建
select count(*) into l_cnt
from pg_class
where relname = 'gs_package'
and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog');
if l_cnt = 0 -- 2.0 以及以下版本中，没有 gs_package 表
then
CREATE OR REPLACE VIEW compat_tools.DBA_DEPENDENCIES
AS
with obj as (select obj.oid, obj.pg_type, obj.object_type, obj.object_name, nsp.nspname as schema_name
from (select 'pg_synonym' as pg_type, 'SYNONYM' as object_type, synname as object_name, synnamespace as object_nsp, oid from pg_synonym
union all
select 'pg_class', CASE relkind WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
WHEN 'i'::"char" THEN 'INDEX'::text
WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
WHEN 'S'::"char" THEN 'SEQUENCE'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
WHEN 't'::"char" THEN 'TOAST'::text
ELSE relkind::text
END, relname, relnamespace, oid from pg_class
union all
select 'pg_ts_parser', 'TS_PARSER', prsname, prsnamespace, oid from pg_ts_parser
union all
select 'pg_collation', 'COLLATION', collname, collnamespace, oid from pg_collation
union all
select 'pg_constraint', 'CONSTRAINT', conname, connamespace, oid from pg_constraint
union all
select 'pg_proc', CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END, proname, pronamespace, oid from pg_proc
union all
select 'pg_opfamily', 'OP_FAMILY', opfname, opfnamespace, oid from pg_opfamily
union all
select 'pg_type', 'TYPE', typname, typnamespace, oid from pg_type
union all
select 'pg_opclass', 'OP_CLASS', opcname, opcnamespace, oid from pg_opclass
union all
select 'pg_conversion', 'CONVERSION', conname, connamespace, oid from pg_conversion
union all
select 'pg_ts_template', 'TS_TEMPLATE', tmplname, tmplnamespace, oid from pg_ts_template
union all
select 'pg_ts_config', 'TS_CONFIG', cfgname, cfgnamespace, oid from pg_ts_config
union all
select 'pg_extension', 'EXTENSION', extname, extnamespace, oid from pg_extension
union all
select 'pg_operator', 'OPERATOR', oprname, oprnamespace, oid from pg_operator
union all
select 'pg_namespace', 'SCHEMA', nspname, oid, oid from pg_namespace
union all
select 'pg_ts_dict', 'TS_DICT', dictname, dictnamespace, oid from pg_ts_dict
union all
select 'pg_rewrite', 'REWRITE', cls.relname, cls.relnamespace, rwt.oid
from pg_rewrite as rwt
join pg_class as cls on rwt.ev_class = cls.oid
) as obj
join pg_namespace as nsp on obj.object_nsp = nsp.oid
union all
select obj.oid, obj.pg_type, obj.object_type, obj.object_name, own.rolname as schema_name
from (select 'pg_language' as pg_type, 'LANGUAGE' as object_type, lanname as object_name, lanowner as owner_oid, oid from pg_language
union all
select 'pg_foreign_server', 'FOREIGN SERVER', srvname, srvowner, oid from pg_foreign_server
union all
select 'pg_foreign_data_wrapper', 'FOREIGN DATA WRAPPER', fdwname, fdwowner, oid from pg_foreign_data_wrapper
) as obj
join pg_authid as own on obj.owner_oid = own.oid)
SELECT case when owner = lower(owner) then compat_tools.f_upper_name(owner) else owner end AS owner
, case when NAME = lower(NAME) then compat_tools.f_upper_name(NAME) else NAME end AS NAME
, TYPE
, case when referenced_owner = lower(referenced_owner) then compat_tools.f_upper_name(referenced_owner) else referenced_owner end AS referenced_owner
, case when referenced_name = lower(referenced_name) then compat_tools.f_upper_name(referenced_name) else referenced_name end AS referenced_name
, REFERENCED_TYPE
, DEPENDENCY_TYPE
FROM (select distinct obj.schema_name as owner
, coalesce(obj.object_name, d.objid::text) as NAME
, coalesce(obj.object_type, cls.relname::text) as TYPE
, ref_obj.schema_name as REFERENCED_OWNER
, coalesce(ref_obj.object_name, d.refobjid::text) as REFERENCED_NAME
, coalesce(ref_obj.object_type, ref_cls.relname::text) as REFERENCED_TYPE
, case d.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else compat_tools.f_upper_name(d.deptype::text) end as DEPENDENCY_TYPE
FROM pg_catalog.pg_depend d
join pg_catalog.pg_class as cls on d.classid = cls.oid
join pg_catalog.pg_class as ref_cls on d.refclassid = ref_cls.oid
left join obj on d.objid = obj.oid and cls.relname = obj.pg_type
left join obj as ref_obj on d.refobjid = ref_obj.oid and ref_cls.relname = ref_obj.pg_type
WHERE d.objsubid = 0
) as o
WHERE owner||'.'||name != referenced_owner||'.'||referenced_name;
else
CREATE OR REPLACE VIEW compat_tools.DBA_DEPENDENCIES
AS
with obj as (select obj.oid, obj.pg_type, obj.object_type, obj.object_name, nsp.nspname as schema_name
from (select 'pg_synonym' as pg_type, 'SYNONYM' as object_type, synname as object_name, synnamespace as object_nsp, oid from pg_synonym
union all
select 'pg_class', CASE relkind WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'm'::"char" THEN 'MATERIALIZED VIEW'::text
WHEN 'i'::"char" THEN 'INDEX'::text
WHEN 'I'::"char" THEN 'GLOBAL INDEX'::text
WHEN 'S'::"char" THEN 'SEQUENCE'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
WHEN 't'::"char" THEN 'TOAST'::text
ELSE relkind::text
END, relname, relnamespace, oid from pg_class
union all
select 'pg_ts_parser', 'TS_PARSER', prsname, prsnamespace, oid from pg_ts_parser
union all
select 'pg_collation', 'COLLATION', collname, collnamespace, oid from pg_collation
union all
select 'pg_constraint', 'CONSTRAINT', conname, connamespace, oid from pg_constraint
union all
select 'pg_proc', CASE WHEN prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END, proname, pronamespace, oid from pg_proc
union all
select 'pg_opfamily', 'OP_FAMILY', opfname, opfnamespace, oid from pg_opfamily
union all
select 'pg_type', 'TYPE', typname, typnamespace, oid from pg_type
union all
select 'pg_opclass', 'OP_CLASS', opcname, opcnamespace, oid from pg_opclass
union all
select 'pg_conversion', 'CONVERSION', conname, connamespace, oid from pg_conversion
union all
select 'pg_ts_template', 'TS_TEMPLATE', tmplname, tmplnamespace, oid from pg_ts_template
union all
select 'pg_ts_config', 'TS_CONFIG', cfgname, cfgnamespace, oid from pg_ts_config
union all
select 'pg_extension', 'EXTENSION', extname, extnamespace, oid from pg_extension
union all
select 'pg_operator', 'OPERATOR', oprname, oprnamespace, oid from pg_operator
union all
select 'pg_namespace', 'SCHEMA', nspname, oid, oid from pg_namespace
union all
select 'pg_ts_dict', 'TS_DICT', dictname, dictnamespace, oid from pg_ts_dict
union all
select 'pg_rewrite', 'REWRITE', cls.relname, cls.relnamespace, rwt.oid
from pg_rewrite as rwt
join pg_class as cls on rwt.ev_class = cls.oid
union all
select 'gs_package', 'PACKAGE', pkgname, pkgnamespace, oid from gs_package
) as obj
join pg_namespace as nsp on obj.object_nsp = nsp.oid
union all
select obj.oid, obj.pg_type, obj.object_type, obj.object_name, own.rolname as schema_name
from (select 'pg_language' as pg_type, 'LANGUAGE' as object_type, lanname as object_name, lanowner as owner_oid, oid from pg_language
union all
select 'pg_foreign_server', 'FOREIGN SERVER', srvname, srvowner, oid from pg_foreign_server
union all
select 'pg_foreign_data_wrapper', 'FOREIGN DATA WRAPPER', fdwname, fdwowner, oid from pg_foreign_data_wrapper
) as obj
join pg_authid as own on obj.owner_oid = own.oid)
SELECT case when owner = lower(owner) then compat_tools.f_upper_name(owner) else owner end AS owner
, case when NAME = lower(NAME) then compat_tools.f_upper_name(NAME) else NAME end AS NAME
, TYPE
, case when referenced_owner = lower(referenced_owner) then compat_tools.f_upper_name(referenced_owner) else referenced_owner end AS referenced_owner
, case when referenced_name = lower(referenced_name) then compat_tools.f_upper_name(referenced_name) else referenced_name end AS referenced_name
, REFERENCED_TYPE
, DEPENDENCY_TYPE
FROM (select distinct obj.schema_name as owner
, coalesce(obj.object_name, d.objid::text) as NAME
, coalesce(obj.object_type, cls.relname::text) as TYPE
, ref_obj.schema_name as REFERENCED_OWNER
, coalesce(ref_obj.object_name, d.refobjid::text) as REFERENCED_NAME
, coalesce(ref_obj.object_type, ref_cls.relname::text) as REFERENCED_TYPE
, case d.deptype when 'n' then 'NORMAL' when 'a' then 'AUTO' when 'i' then 'INTERNAL' when 'e' then 'EXTENSION' when 'p' then 'PIN' when 'x' then 'AUTO_EXTENSION' when 'I' then 'INTERNAL_AUTO' else compat_tools.f_upper_name(d.deptype::text) end as DEPENDENCY_TYPE
FROM pg_catalog.pg_depend d
join pg_catalog.pg_class as cls on d.classid = cls.oid
join pg_catalog.pg_class as ref_cls on d.refclassid = ref_cls.oid
left join obj on d.objid = obj.oid and cls.relname = obj.pg_type
left join obj as ref_obj on d.refobjid = ref_obj.oid and ref_cls.relname = ref_obj.pg_type
WHERE d.objsubid = 0
) as o
WHERE owner||'.'||name != referenced_owner||'.'||referenced_name;
end if;
CREATE OR REPLACE VIEW compat_tools.USER_DEPENDENCIES AS SELECT NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, DEPENDENCY_TYPE FROM compat_tools.DBA_DEPENDENCIES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_DEPENDENCIES for compat_tools.DBA_DEPENDENCIES;
CREATE OR REPLACE SYNONYM public.ALL_DEPENDENCIES for compat_tools.DBA_DEPENDENCIES;
CREATE OR REPLACE SYNONYM public.USER_DEPENDENCIES for compat_tools.USER_DEPENDENCIES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_DEPENDENCIES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_SEGMENTS
-- ALL_SEGMENTS
-- USER_SEGMENTS
-- =========================================================================
-- 2022-01-25 [1.0 -> 1.1] 调整表分区/子分区的大小计算，删除索引分区大小的计算
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_SEGMENTS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_SEGMENTS
AS
SELECT o.owner
, o.object_name as SEGMENT_NAME
, o.subobject_name as PARTITION_NAME
, o.object_type as SEGMENT_TYPE
, compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
, (case o.object_type
when 'TABLE' then pg_table_size(o.object_id)
when 'INDEX' then pg_table_size(o.object_id)
when 'TABLE PARTITION' then pg_partition_size(p.parentid, p.oid)
-- when 'INDEX PARTITION' then pg_partition_indexes_size(ip.parentid, ip.oid)
when 'TABLE SUBPARTITION' then pg_partition_size(sp.parentid, p.oid)
-- when 'INDEX SUBPARTITION' then pg_partition_indexes_size(isp.parentid, ip.oid)
else pg_relation_size(o.data_object_id)
end) as bytes
, (case o.object_type
when 'TABLE' then pg_table_size(o.object_id)
when 'INDEX' then pg_table_size(o.object_id)
when 'TABLE PARTITION' then pg_partition_size(p.parentid, p.oid)
-- when 'INDEX PARTITION' then pg_partition_indexes_size(ip.parentid, ip.oid)
when 'TABLE SUBPARTITION' then pg_partition_size(sp.parentid, p.oid)
-- when 'INDEX SUBPARTITION' then pg_partition_indexes_size(isp.parentid, isp.oid)
else pg_relation_size(o.data_object_id)
end / b.block_size)::bigint as blocks
FROM compat_tools.DBA_OBJECTS o
join (select setting::bigint as block_size FROM pg_catalog.pg_settings WHERE name = 'block_size') b on 1=1
left join pg_catalog.pg_class c on o.object_id = c.oid
left join pg_catalog.pg_partition p on o.object_id = p.oid -- 分区表/索引对应的分区信息
left join pg_catalog.pg_partition sp on sp.oid = p.parentid -- 分区表/索引对应的子分区的上层分区
left join pg_catalog.pg_tablespace t on c.reltablespace = t.oid or p.reltablespace = t.oid
left join pg_catalog.pg_partition ip on p.indextblid = ip.oid -- 索引对应的表的分区/子分区
left join pg_catalog.pg_partition isp on ip.parentid = isp.oid -- 索引对应的表的子分区的父表
WHERE o.object_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION');
CREATE OR REPLACE VIEW compat_tools.USER_SEGMENTS AS SELECT segment_name, partition_name, segment_type, tablespace_name, bytes, blocks FROM compat_tools.DBA_SEGMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_SEGMENTS for compat_tools.DBA_SEGMENTS;
CREATE OR REPLACE SYNONYM public.ALL_SEGMENTS for compat_tools.DBA_SEGMENTS;
CREATE OR REPLACE SYNONYM public.USER_SEGMENTS for compat_tools.USER_SEGMENTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_SEGMENTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_SOURCE_ALL
-- DBA_SOURCE
-- ALL_SOURCE
-- USER_SOURCE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_SOURCE_ALL', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_SOURCE_ALL
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when p.proname::text = lower(p.proname::text) then compat_tools.f_upper_name(p.proname::text) else p.proname::text end AS NAME
, CASE WHEN p.prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end)
END AS TYPE
, length(p.prosrc) - length(replace(p.prosrc, chr(10), '')) + 1 as lines
, p.prosrc as TEXT
FROM pg_catalog.pg_proc as p
JOIN pg_catalog.pg_namespace as n on p.pronamespace = n.oid
WHERE p.prolang not in (select oid FROM pg_catalog.pg_language WHERE lanname in ('internal', 'c'));
CREATE OR REPLACE VIEW compat_tools.DBA_SOURCE
AS
with recursive t_line as (select owner, name, type, text, 1 as line_id, lines FROM compat_tools.DBA_SOURCE_ALL
union all
select owner, name, type, text, 1 + line_id as line_id, lines FROM t_line WHERE 1 + line_id <= lines)
select owner, name, type, line_id as line, substr(text, p1 + 1, p2-p1-1) as text
FROM (select owner, name, type, line_id, lines, text
, case line_id when 1 then 0 else instr(text, chr(10), 1, line_id - 1) end as p1
, case instr(text, chr(10), 1, line_id) when 0 then length(text) else instr(text, chr(10), 1, line_id) end as p2
FROM t_line) as x
order by owner, name, line_id;
CREATE OR REPLACE VIEW compat_tools.USER_SOURCE AS SELECT name, type, line, text FROM compat_tools.DBA_SOURCE WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_SOURCE_ALL for compat_tools.DBA_SOURCE_ALL;
CREATE OR REPLACE SYNONYM public.DBA_SOURCE for compat_tools.DBA_SOURCE;
CREATE OR REPLACE SYNONYM public.ALL_SOURCE for compat_tools.DBA_SOURCE;
CREATE OR REPLACE SYNONYM public.USER_SOURCE for compat_tools.USER_SOURCE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_SOURCE_ALL: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_PROCEDURES
-- ALL_PROCEDURES
-- USER_PROCEDURES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_PROCEDURES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_PROCEDURES
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when p.proname::text = lower(p.proname::text) then compat_tools.f_upper_name(p.proname::text) else p.proname::text end AS OBJECT_NAME
, p.oid as OBJECT_ID
, CASE WHEN p.prorettype = 'trigger'::regtype::oid THEN 'TRIGGER'::text
ELSE (case p.prokind when 'p' then 'PROCEDURE' when 'f' then 'FUNCTION' else NULL end)
END AS OBJECT_TYPE
, case p.prokind when 'a' then 'YES' else 'NO' end as AGGREGATE
, case p.provolatile when 'v' then 'NO' else 'YES 'end as DETERMINISTIC
FROM pg_catalog.pg_proc as p
JOIN pg_catalog.pg_namespace as n on p.pronamespace = n.oid
WHERE p.prolang not in (select oid FROM pg_catalog.pg_language WHERE lanname in ('internal', 'c'));
CREATE OR REPLACE VIEW compat_tools.USER_PROCEDURES AS SELECT object_name, object_id, object_type, aggregate, deterministic FROM compat_tools.DBA_PROCEDURES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_PROCEDURES for compat_tools.DBA_PROCEDURES;
CREATE OR REPLACE SYNONYM public.ALL_PROCEDURES for compat_tools.DBA_PROCEDURES;
CREATE OR REPLACE SYNONYM public.USER_PROCEDURES for compat_tools.USER_PROCEDURES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_PROCEDURES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TRIGGERS
-- ALL_TRIGGERS
-- USER_TRIGGERS
-- =========================================================================
-- 2023-11-09 [2.0 -> 2.1] 修复不同schema下有同名trigger导致有重复数据的情况
-- 2023-11-11 [2.0 -> 3.0] 重写dba_triggers视图，合并event为一行,增加支持truncate事件的显示，支持识别基表类型为view
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TRIGGERS', '2.2')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TRIGGERS
AS
SELECT
CASE
WHEN it.trigger_schema::text = lower(it.trigger_schema::text) THEN compat_tools.f_upper_name(it.trigger_schema::text)
ELSE it.trigger_schema::text
END AS owner,
CASE
WHEN it.trigger_name::text = lower(it.trigger_name::text) THEN compat_tools.f_upper_name(it.trigger_name::text)
ELSE it.trigger_name::text
END AS trigger_name,
compat_tools.f_upper_name((it.action_timing::text || ' '::text) || it.action_orientation::text) AS trigger_type,
compat_tools.f_upper_name( event_manipulation ) as triggering_event,
CASE
WHEN it.event_object_schema::text = lower(it.event_object_schema::text) THEN compat_tools.f_upper_name(it.event_object_schema::text)
ELSE it.event_object_schema::text
END AS table_owner,
it.base_object_type,
CASE
WHEN it.event_object_table::text = lower(it.event_object_table::text) THEN compat_tools.f_upper_name(it.event_object_table::text)
ELSE it.event_object_table::text
END AS table_name,
'REFERENCING NEW AS NEW OLD AS OLD'::text AS referencing_names,
it.action_condition AS when_clause,
it.status,
'PL/SQL'::text AS action_type,
it.action_statement AS trigger_body,
it.before_statement,
it.before_row,
it.after_row,
it.after_statement,
it.instead_of_row
FROM (
SELECT
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
n.nspname AS event_object_schema,
c.relname AS event_object_table,
CASE
WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN ( SELECT rm.m[1] AS m
FROM regexp_matches(pg_get_triggerdef(t.oid), '.{35,} WHEN \((.+)\) EXECUTE PROCEDURE'::text) rm(m)
LIMIT 1)
ELSE NULL::text
END AS action_condition,
case substring(tgtype::int4::bit(8) from 3 for 5)
when B'01110' then 'INSERT OR UPDATE OR DELETE'
when B'01011' then 'INSERT OR UPDATE'
when B'00011' then 'INSERT'
when B'01100' then 'UPDATE OR DELETE'
when B'00100' then 'DELETE'
when B'00111' then 'INSERT OR DELETE'
when B'10001' then 'TRUNCATE'
end event_manipulation,
"substring"(pg_get_triggerdef(t.oid), "position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 47) AS action_statement,
CASE when t.tgtype::integer & 1 <> 1 and t.tgtype::integer & 66 = 2 THEN 'YES' end ::text before_statement,
CASE when t.tgtype::integer & 1 = 1 and t.tgtype::integer & 66 = 2 THEN 'YES' end ::text before_row,
CASE when t.tgtype::integer & 1 = 1 and t.tgtype::integer & 66 not in (2,64) THEN 'YES' end ::text after_row,
CASE when t.tgtype::integer & 1 <> 1 and t.tgtype::integer & 66 not in (2,64) THEN 'YES' end ::text after_statement,
CASE when t.tgtype::integer & 1 = 1 and t.tgtype::integer & 66 = 64 THEN 'YES' end ::text instead_of_row,
CASE t.tgtype & 1 WHEN 1 THEN 'ROW'::text ELSE 'STATEMENT'::text END AS action_orientation,
CASE t.tgtype::integer & 66 WHEN 2 THEN 'BEFORE'::text WHEN 64 THEN 'INSTEAD OF'::text ELSE 'AFTER'::text END AS action_timing,
CASE t.tgenabled WHEN 'D'::"char" THEN 'DISABLED'::text ELSE 'ENABLED'::text END AS status,
case when C.relkind ='v' then 'VIEW' when C.relkind in ('r','f') then 'TABLE' else 'UNDEFINED' end base_object_type
FROM pg_namespace n, pg_class c, pg_trigger t
WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid
AND NOT t.tgisinternal AND NOT pg_is_other_temp_schema(n.oid)
AND (pg_has_role(c.relowner, 'USAGE'::text)
OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES'::text))) it;
CREATE OR REPLACE VIEW compat_tools.USER_TRIGGERS AS SELECT trigger_name, trigger_type, triggering_event, table_owner, base_object_type, table_name, referencing_names, when_clause, status, action_type, trigger_body, before_statement, before_row, after_row, after_statement, instead_of_row FROM compat_tools.DBA_TRIGGERS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TRIGGERS for compat_tools.DBA_TRIGGERS;
CREATE OR REPLACE SYNONYM public.ALL_TRIGGERS for compat_tools.DBA_TRIGGERS;
CREATE OR REPLACE SYNONYM public.USER_TRIGGERS for compat_tools.USER_TRIGGERS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TRIGGERS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TRIGGER_COLS
-- ALL_TRIGGER_COLS
-- USER_TRIGGER_COLS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TRIGGER_COLS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TRIGGER_COLS
AS
SELECT case when trigger_schema::text = lower(trigger_schema::text) then compat_tools.f_upper_name(trigger_schema::text) else trigger_schema::text end AS TRIGGER_OWNER
, case when trigger_name::text = lower(trigger_name::text) then compat_tools.f_upper_name(trigger_name::text) else trigger_name::text end AS TRIGGER_NAME
, case when event_object_schema::text = lower(event_object_schema::text) then compat_tools.f_upper_name(event_object_schema::text) else event_object_schema::text end AS TABLE_OWNER
, case when event_object_table::text = lower(event_object_table::text) then compat_tools.f_upper_name(event_object_table::text) else event_object_table::text end AS TABLE_NAME
, case when event_object_column::text = lower(event_object_column::text) then compat_tools.f_upper_name(event_object_column::text) else event_object_column::text end AS COLUMN_NAME
FROM information_schema.triggered_update_columns
WHERE trigger_catalog = current_database();
CREATE OR REPLACE VIEW compat_tools.USER_TRIGGER_COLS AS SELECT TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, COLUMN_NAME FROM compat_tools.DBA_TRIGGER_COLS WHERE TRIGGER_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TRIGGER_COLS for compat_tools.DBA_TRIGGER_COLS;
CREATE OR REPLACE SYNONYM public.ALL_TRIGGER_COLS for compat_tools.DBA_TRIGGER_COLS;
CREATE OR REPLACE SYNONYM public.USER_TRIGGER_COLS for compat_tools.USER_TRIGGER_COLS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TRIGGER_COLS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TYPES
-- ALL_TYPES
-- USER_TYPES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TYPES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TYPES
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when t.typname::text = lower(t.typname::text) then compat_tools.f_upper_name(t.typname::text) else t.typname::text end AS TYPE_NAME
, t.OID as TYPE_OID
, case t.typtype when 'b' then 'BASE' when 'c' then 'COMPOSITE' when 'd' then 'DOMAIN' when 'e' then 'ENUM' when 'p' then 'PSEUDO' when 'r' then 'RANGE' else compat_tools.f_upper_name(t.typtype::text) end as TYPECODE
, case when t.typisdefined then 'YES' else 'NO' end as PREDEFINED
FROM pg_catalog.pg_type as t
JOIN pg_catalog.pg_namespace as n on t.typnamespace = n.oid
WHERE t.typtype != 'b'
and t.typname not in (select relname FROM pg_catalog.pg_class WHERE relkind in ('r', 't', 'S', 'v', 'f'));
CREATE OR REPLACE VIEW compat_tools.USER_TYPES AS SELECT type_name, type_oid, typecode, predefined FROM compat_tools.DBA_TYPES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TYPES for compat_tools.DBA_TYPES;
CREATE OR REPLACE SYNONYM public.ALL_TYPES for compat_tools.DBA_TYPES;
CREATE OR REPLACE SYNONYM public.USER_TYPES for compat_tools.USER_TYPES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TYPES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_CONSTRAINTS
-- ALL_CONSTRAINTS
-- USER_CONSTRAINTS
-- =========================================================================
-- 变更历史：
-- 2022-05-20 1.1 增加 SEARCH_CONDITION 字段，用于记录 Check 约束的表达式
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_CONSTRAINTS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_CONSTRAINTS
AS
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
, case when cons.conname::text = lower(cons.conname::text) then compat_tools.f_upper_name(cons.conname::text) else cons.conname::text end AS CONSTRAINT_NAME
, compat_tools.f_upper_name(cons.contype) as CONSTRAINT_TYPE
, case when cls_r.relname::text = lower(cls_r.relname::text) then compat_tools.f_upper_name(cls_r.relname::text) else cls_r.relname::text end AS TABLE_NAME
, consrc as SEARCH_CONDITION
, case when nsp_f.nspname::text = lower(nsp_f.nspname::text) then compat_tools.f_upper_name(nsp_f.nspname::text) else nsp_f.nspname::text end AS R_OWNER
, case when cls_f.relname::text = lower(cls_f.relname::text) then compat_tools.f_upper_name(cls_f.relname::text) else cls_f.relname::text end AS R_TABLE_NAME
, case cons.confdeltype when 'a' then 'NO ACTION' when 'r' then 'RESTRICT' when 'c' then 'CASCADE' when 'n' then 'SET NULL' when 'd' then 'SET DEFAULT' else compat_tools.f_upper_name(confdeltype::text) end as DELETE_RULE
, case when cons.convalidated then 'ENABLED' else 'DISABLED' end as STATUS
, case when cons.condeferrable then 'DEFERRABLE' else 'NOT DEFERRABLE' end as DEFERRABLE
, case when cons.condeferred then 'DEFERRED' else 'IMMEDIATE' end as DEFERRED
, case when cons.convalidated then 'VALIDATED' else 'NOT VALIDATED' end as VALIDATED
, case when nsp_i.nspname::text = lower(nsp_i.nspname::text) then compat_tools.f_upper_name(nsp_i.nspname::text) else nsp_i.nspname::text end AS INDEX_OWNER
, case when cls_i.relname::text = lower(cls_i.relname::text) then compat_tools.f_upper_name(cls_i.relname::text) else cls_i.relname::text end AS INDEX_NAME
, case when cons.convalidated then 'VALIDATED' else 'NOT VALIDATED' end as INVALID
FROM pg_catalog.pg_constraint cons
JOIN pg_catalog.pg_namespace nsp on nsp.oid = cons.connamespace
LEFT JOIN pg_catalog.pg_class cls_r on cons.conrelid = cls_r.oid
LEFT JOIN pg_catalog.pg_class cls_f on cons.confrelid = cls_f.oid
LEFT JOIN pg_catalog.pg_namespace nsp_f on nsp_f.oid = cls_f.relnamespace
LEFT JOIN pg_catalog.pg_class cls_i on cons.conindid = cls_i.oid
LEFT JOIN pg_catalog.pg_namespace nsp_i on nsp_i.oid = cls_i.relnamespace
WHERE nsp.nspname not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_CONSTRAINTS AS SELECT * FROM compat_tools.DBA_CONSTRAINTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_CONSTRAINTS for compat_tools.DBA_CONSTRAINTS;
CREATE OR REPLACE SYNONYM public.ALL_CONSTRAINTS for compat_tools.DBA_CONSTRAINTS;
CREATE OR REPLACE SYNONYM public.USER_CONSTRAINTS for compat_tools.USER_CONSTRAINTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_CONSTRAINTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_CONS_COLUMNS
-- ALL_CONS_COLUMNS
-- USER_CONS_COLUMNS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_CONS_COLUMNS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_CONS_COLUMNS
AS
SELECT case when c.nspname::text = lower(c.nspname::text) then compat_tools.f_upper_name(c.nspname::text) else c.nspname::text end AS OWNER
, case when c.conname::text = lower(c.conname::text) then compat_tools.f_upper_name(c.conname::text) else c.conname::text end AS CONSTRAINT_NAME
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
, case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
, c.ord as POSITION
FROM (select ns.nspname
, cn.conname
, cl.relname
, unnest(cn.conkey) as column_Id
, case cn.contype when 'c' then NULL else generate_series( 1, array_length(cn.conkey, 1)) end as ord
, cn.conrelid as table_oid
FROM pg_catalog.pg_constraint as cn
join pg_catalog.pg_class as cl on cn.conrelid = cl.oid
join pg_catalog.pg_namespace as ns on cn.connamespace = ns.oid
WHERE ns.nspname::text not like 'pg_toast%') as c
JOIN pg_catalog.pg_attribute a on c.table_oid = a.attrelid and c.column_Id = a.attnum;
CREATE OR REPLACE VIEW compat_tools.USER_CONS_COLUMNS AS SELECT * FROM compat_tools.DBA_CONS_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_CONS_COLUMNS for compat_tools.DBA_CONS_COLUMNS;
CREATE OR REPLACE SYNONYM public.ALL_CONS_COLUMNS for compat_tools.DBA_CONS_COLUMNS;
CREATE OR REPLACE SYNONYM public.USER_CONS_COLUMNS for compat_tools.USER_CONS_COLUMNS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_CONS_COLUMNS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_VIEWS
-- ALL_VIEWS
-- USER_VIEWS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_VIEWS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_VIEWS
AS
SELECT case when schemaname::text = lower(schemaname::text) then compat_tools.f_upper_name(schemaname::text) else schemaname::text end AS OWNER
, case when viewname::text = lower(viewname::text) then compat_tools.f_upper_name(viewname::text) else viewname::text end AS VIEW_NAME
, length(definition) as TEXT_LENGTH
, definition as TEXT
, definition as TEXT_VC
FROM pg_catalog.pg_views;
CREATE OR REPLACE VIEW compat_tools.USER_VIEWS AS SELECT VIEW_NAME, TEXT_LENGTH, "text", TEXT_VC FROM compat_tools.DBA_VIEWS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_VIEWS for compat_tools.DBA_VIEWS;
CREATE OR REPLACE SYNONYM public.ALL_VIEWS for compat_tools.DBA_VIEWS;
CREATE OR REPLACE SYNONYM public.USER_VIEWS for compat_tools.USER_VIEWS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_VIEWS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TABLES
-- DBA_ALL_TABLES
-- ALL_TABLES
-- ALL_ALL_TABLES
-- USER_TABLES
-- USER_ALL_TABLES
-- TAB
-- =========================================================================
-- 变更历史：
-- 2022-01-26 [1.0 -> 1.1] 增加段管理字段 SEGMENT_MANAGED
-- 2022-05-30 [1.1 -> 1.2] 增加同义词 ALL_ALL_TABLES, USER_ALL_TABLES
-- 2023-10-09 [2.0 -> 2.1] 修改全局临时表的临时表属性为YES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TABLES', '2.1')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TABLES
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
, compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
, case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
, c.reltuples::bigint as NUM_ROWS
, c.relpages::bigint as BLOCKS
, s.stawidth as AVG_ROW_LEN
, coalesce(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid)) as LAST_ANALYZED
, case c.parttype when 'n' then 'NO' else 'YES' end as PARTITIONED
, case c.relpersistence when 't' then 'YES' when 'g' then 'YES' else 'NO' end as TEMPORARY
, case when c.relrowmovement then 'ENABLE' else 'DISABLE' end as ROW_MOVEMENT
, case when arraycontains(reloptions, string_to_array('compression=no', ',')) then 'NO' else 'YES' end as COMPRESSION
, case when arraycontains(reloptions, string_to_array('compression=no', ',')) then NULL
when arraycontains(reloptions, string_to_array('compresslevel=3', ',')) then 3
when arraycontains(reloptions, string_to_array('compresslevel=2', ',')) then 2
when arraycontains(reloptions, string_to_array('compresslevel=1', ',')) then 1
ELSE 0
end as COMPRESSION_LEVEL
, case when arraycontains(reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
, case when arraycontains(reloptions, string_to_array('orientation=column', ',')) then 'COLUMN' else 'ROW' end as ORIENTATION
, case when arraycontains(reloptions, string_to_array('storage_type=ustore', ',')) then 'INPLACE' else 'APPEND' end as STORAGE_TYPE
FROM pg_catalog.pg_class c
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_catalog.pg_tablespace t on c.reltablespace = t.oid
left join (select starelid, sum(stawidth) as stawidth FROM pg_catalog.pg_statistic group by starelid) s on c.oid = s.starelid
WHERE c.relkind in ('r', 'f')
and n.nspname::text not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_TABLES AS SELECT table_name, tablespace_name, logging, num_rows, blocks, avg_row_len, last_analyzed, partitioned, temporary, row_movement, compression, compression_level, segment_managed, orientation, storage_type FROM compat_tools.DBA_TABLES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TABLES for compat_tools.DBA_TABLES;
CREATE OR REPLACE SYNONYM public.ALL_TABLES for compat_tools.DBA_TABLES;
CREATE OR REPLACE SYNONYM public.USER_TABLES for compat_tools.USER_TABLES;
CREATE OR REPLACE SYNONYM public.DBA_ALL_TABLES for compat_tools.DBA_TABLES;
CREATE OR REPLACE SYNONYM public.ALL_ALL_TABLES for compat_tools.DBA_TABLES;
CREATE OR REPLACE SYNONYM public.USER_ALL_TABLES for compat_tools.USER_TABLES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TABLES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- TAB
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'TAB', '2.0')
then
create view compat_tools.tab
as
with nsp_oid as (select oid from pg_namespace where nspname = current_schema())
select relname as TNAME
, case relkind when 'v' then 'VIEW' else 'TABLE' end as TABTYPE
, null as CLUSTERID
from pg_class
where relkind in ('t','r','f','v')
and relnamespace in (select oid from nsp_oid)
union
select synname
, 'SYNONYM'
, null as CLUSTERID
from pg_synonym
where synnamespace in (select oid from nsp_oid);
create or replace synonym public.tab for compat_tools.tab;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in TAB: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_PART_TABLES
-- ALL_PART_TABLES
-- USER_PART_TABLES
-- =========================================================================
-- 2022-01-26 [1.0 -> 2.0] 增加子分区相关字段，增加段管理字段 SEGMENT_MANAGED
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_PART_TABLES', '3.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_PART_TABLES
AS
SELECT OWNER
, TABLE_NAME
, max(PARTITIONING_TYPE) as PARTITIONING_TYPE
, max(SUBPARTITIONING_TYPE) as SUBPARTITIONING_TYPE
, count(distinct part_oid) as PARTITION_COUNT
, count(distinct sub_oid) as SUBPARTITION_COUNT
, max(PARTITIONING_KEY_COUNT) as PARTITIONING_KEY_COUNT
, max(SUBPARTITIONING_KEY_COUNT) as SUBPARTITIONING_KEY_COUNT
, max(DEF_TABLESPACE_NAME) as DEF_TABLESPACE_NAME
, MAX(DEF_LOGGING) as DEF_LOGGING
, max(INTERVAL) as INTERVAL
, max(SEGMENT_MANAGED) as SEGMENT_MANAGED
from (SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS TABLE_NAME
, case p.partstrategy
when 'r' then 'RANGE'
when 'v' then 'VALUE'
when 'i' then 'INTERVAL'
when 'l' then 'LIST'
when 'h' then 'HASH'
when 'n' then 'INVALID'
else compat_tools.f_upper_name(p.partstrategy::text)
end as PARTITIONING_TYPE
, case when pp.partkey is null
then null
else case pp.partstrategy
when 'r' then 'RANGE'
when 'v' then 'VALUE'
when 'i' then 'INTERVAL'
when 'l' then 'LIST'
when 'h' then 'HASH'
when 'n' then 'INVALID'
else compat_tools.f_upper_name(pp.partstrategy::text)
end
end as SUBPARTITIONING_TYPE
-- , count(p.parentid) over (partition by p.parentid) - 1 as PARTITION_COUNT
, pp.oid as part_oid
, ppp.oid as sub_oid
, array_length(p.partkey::int[], 1) as PARTITIONING_KEY_COUNT
, array_length(pp.partkey::int[], 1) as SUBPARTITIONING_KEY_COUNT
, compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as DEF_TABLESPACE_NAME
, case c.relpersistence when 'p' then 'YES' else 'NO' end as DEF_LOGGING
, case when arraycontains(p.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as DEF_COMPRESSION
, case when arraycontains(p.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
, p.interval
, p.parttype
FROM pg_catalog.pg_partition p -- 父表
join pg_catalog.pg_class c on p.parentid = c.oid -- 父表详细信息
join pg_catalog.pg_namespace n on c.relnamespace = n.oid -- 父表 schema
join pg_catalog.pg_partition pp on p.parentid = pp.parentid and pp.parttype = 'p' -- 分区
left join pg_catalog.pg_partition ppp on pp.oid = ppp.parentid and ppp.parttype = 's' -- 子分区
left join pg_catalog.pg_tablespace s on p.reltablespace = s.oid -- 父表表空间
where p.parttype = 'r'
) as p
group by owner, table_name;
CREATE OR REPLACE VIEW compat_tools.USER_PART_TABLES AS SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT, SUBPARTITIONING_KEY_COUNT, DEF_TABLESPACE_NAME, DEF_LOGGING, INTERVAL, SEGMENT_MANAGED FROM compat_tools.DBA_PART_TABLES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_PART_TABLES for compat_tools.DBA_PART_TABLES;
CREATE OR REPLACE SYNONYM public.ALL_PART_TABLES for compat_tools.DBA_PART_TABLES;
CREATE OR REPLACE SYNONYM public.USER_PART_TABLES for compat_tools.USER_PART_TABLES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_PART_TABLES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_PARTITIONS
-- ALL_TAB_PARTITIONS
-- USER_TAB_PARTITIONS
-- =========================================================================
-- 2022-01-26 [1.0 -> 1.1] 增加段管理字段 SEGMENT_MANAGED，移除 AVG_ROW_LEN (只能记录到父表，不能精确到分区)
-- 2024-03-29 [2.1] 增加 interval 字段
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_PARTITIONS', '2.1')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_PARTITIONS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS TABLE_OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
, case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS PARTITION_NAME
, coalesce(array_to_string(p.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
, length(array_to_string(p.boundaries, ',')) as HIGH_VALUE_LENGTH
, row_number() over (partition by p.parentid order by p.boundaries) as PARTITION_POSITION
, compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
, case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
, case when arraycontains(p.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as COMPRESSION
, case when arraycontains(p.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
, p.reltuples::bigint as NUM_ROWS
, p.relpages::bigint as BLOCKS
, coalesce(pg_stat_get_last_analyze_time(p.parentid), pg_stat_get_last_autoanalyze_time(p.parentid)) as LAST_ANALYZED
, case when pp.partstrategy = 'i' then 'YES' else 'NO' end as interval
FROM pg_catalog.pg_partition p
join pg_catalog.pg_class c on p.parentid = c.oid
join pg_catalog.pg_partition pp on p.parentid = pp.parentid and pp.parttype = 'r'
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid
WHERE p.parttype = 'p';
CREATE OR REPLACE VIEW compat_tools.USER_TAB_PARTITIONS AS SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name, logging, compression, segment_managed, num_rows, blocks, last_analyzed, interval FROM compat_tools.DBA_TAB_PARTITIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_PARTITIONS for compat_tools.DBA_TAB_PARTITIONS;
CREATE OR REPLACE SYNONYM public.USER_TAB_PARTITIONS for compat_tools.USER_TAB_PARTITIONS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_PARTITIONS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_SUBPARTITIONS
-- ALL_TAB_SUBPARTITIONS
-- USER_TAB_SUBPARTITIONS
-- =========================================================================
-- 2022-01-26 [1.0] 新增视图
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_SUBPARTITIONS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_SUBPARTITIONS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as TABLE_OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as TABLE_NAME
, case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end as PARTITION_NAME
, case when sp.relname::text = lower(sp.relname::text) then compat_tools.f_upper_name(sp.relname::text) else sp.relname::text end as SUBPARTITION_NAME
, coalesce(array_to_string(sp.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
, length(array_to_string(sp.boundaries, ',')) as HIGH_VALUE_LENGTH
, dense_rank() over (partition by p.parentid order by p.boundaries) as PARTITION_POSITION
, row_number() over (partition by sp.parentid order by sp.boundaries) as SUBPARTITION_POSITION
, compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
, case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
, case when arraycontains(sp.reloptions, string_to_array('compression=no',',')) then 'NO' else 'YES' end as COMPRESSION
, case when arraycontains(sp.reloptions, string_to_array('segment=on',',')) then 'YES' else 'NO' end as SEGMENT_MANAGED
, sp.reltuples::bigint as NUM_ROWS
, sp.relpages::bigint as BLOCKS
, coalesce(pg_stat_get_last_analyze_time(p.parentid), pg_stat_get_last_autoanalyze_time(p.parentid)) as LAST_ANALYZED
, sp.INTERVAL
, case when sp.indisusable then 'YES' else 'NO' end as INDEXING
FROM pg_catalog.pg_partition p -- 分区
join pg_catalog.pg_partition sp on p.oid = sp.parentid and sp.parttype = 's' -- 子分区
join pg_catalog.pg_class c on p.parentid = c.oid -- 父表详细信息
join pg_catalog.pg_namespace n on c.relnamespace = n.oid -- 父表 schema
left join pg_catalog.pg_tablespace t on p.reltablespace = t.oid -- 父表表空间
WHERE p.parttype = 'p';
CREATE OR REPLACE VIEW compat_tools.USER_TAB_SUBPARTITIONS AS SELECT table_name, partition_name, subpartition_name, high_value, high_value_length, partition_position, subpartition_position, tablespace_name, logging, compression, segment_managed, num_rows, blocks, last_analyzed, interval, indexing FROM compat_tools.DBA_TAB_SUBPARTITIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_SUBPARTITIONS for compat_tools.DBA_TAB_SUBPARTITIONS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_SUBPARTITIONS for compat_tools.DBA_TAB_SUBPARTITIONS;
CREATE OR REPLACE SYNONYM public.USER_TAB_SUBPARTITIONS for compat_tools.USER_TAB_SUBPARTITIONS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_SUBPARTITIONS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_PART_KEY_COLUMNS
-- ALL_PART_KEY_COLUMNS
-- USER_PART_KEY_COLUMNS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_PART_KEY_COLUMNS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_PART_KEY_COLUMNS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as NAME
, CASE c.relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'i'::"char" THEN 'INDEX'::text
ELSE relkind::text
END AS OBJECT_TYPE
, case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end as COLUMN_NAME
, p.ord as COLUMN_POSITION
FROM (select parentid
, unnest(partkey) as partkey
, generate_series( 1, array_length(partkey, 1)) as ord
from pg_catalog.pg_partition
where parttype = 'r') p -- 分区表主表
join pg_catalog.pg_class c on p.parentid = c.oid -- 父表详细信息
join pg_catalog.pg_namespace n on c.relnamespace = n.oid -- 父表 schema
join pg_catalog.pg_attribute a on p.parentid = a.attrelid and p.partkey = a.attnum; -- 分区键;
CREATE OR REPLACE VIEW compat_tools.USER_PART_KEY_COLUMNS AS SELECT NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION FROM compat_tools.DBA_PART_KEY_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_PART_KEY_COLUMNS for compat_tools.DBA_PART_KEY_COLUMNS;
CREATE OR REPLACE SYNONYM public.ALL_PART_KEY_COLUMNS for compat_tools.DBA_PART_KEY_COLUMNS;
CREATE OR REPLACE SYNONYM public.USER_PART_KEY_COLUMNS for compat_tools.USER_PART_KEY_COLUMNS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_PART_KEY_COLUMNS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_SUBPART_KEY_COLUMNS
-- ALL_SUBPART_KEY_COLUMNS
-- USER_SUBPART_KEY_COLUMNS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_SUBPART_KEY_COLUMNS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_SUBPART_KEY_COLUMNS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end as OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end as NAME
, CASE c.relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'i'::"char" THEN 'INDEX'::text
ELSE relkind::text
END AS OBJECT_TYPE
, case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end as COLUMN_NAME
, p.ord as COLUMN_POSITION
FROM (select distinct parentid
, unnest(partkey) as partkey
, generate_subscripts(partkey, 1) + 1 as ord
from pg_catalog.pg_partition
where parttype = 'p'
and array_length(partkey, 1) > 0) p -- 子分区表的分区： 类型为 p 且 partkey 不为空
join pg_catalog.pg_class c on p.parentid = c.oid -- 父表详细信息
join pg_catalog.pg_namespace n on c.relnamespace = n.oid -- 父表 schema
join pg_catalog.pg_attribute a on p.parentid = a.attrelid and p.partkey = a.attnum; -- 分区键;
CREATE OR REPLACE VIEW compat_tools.USER_SUBPART_KEY_COLUMNS AS SELECT NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION FROM compat_tools.DBA_SUBPART_KEY_COLUMNS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_SUBPART_KEY_COLUMNS for compat_tools.DBA_SUBPART_KEY_COLUMNS;
CREATE OR REPLACE SYNONYM public.ALL_SUBPART_KEY_COLUMNS for compat_tools.DBA_SUBPART_KEY_COLUMNS;
CREATE OR REPLACE SYNONYM public.USER_SUBPART_KEY_COLUMNS for compat_tools.USER_SUBPART_KEY_COLUMNS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_SUBPART_KEY_COLUMNS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_STATISTICS
-- ALL_TAB_STATISTICS
-- USER_TAB_STATISTICS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_STATISTICS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_STATISTICS
AS
SELECT OWNER
, TABLE_NAME
, Null as PARTITION_NAME
, null as PARTITION_POSITION
, 'TABLE' as OBJECT_TYPE
, NUM_ROWS
, BLOCKS
, AVG_ROW_LEN
, LAST_ANALYZED
FROM compat_tools.dba_tables
WHERE partitioned = 'NO'
union all
SELECT TABLE_OWNER as OWNER
, TABLE_NAME
, PARTITION_NAME
, PARTITION_POSITION
, 'PARTITION' as OBJECT_TYPE
, NUM_ROWS
, BLOCKS
, null as AVG_ROW_LEN
, LAST_ANALYZED
FROM compat_tools.DBA_TAB_PARTITIONS;
CREATE OR REPLACE VIEW compat_tools.USER_TAB_STATISTICS AS SELECT table_name, partition_name, partition_position, object_type, num_rows, blocks, avg_row_len, last_analyzed FROM compat_tools.DBA_TAB_STATISTICS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_STATISTICS for compat_tools.DBA_TAB_STATISTICS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_STATISTICS for compat_tools.DBA_TAB_STATISTICS;
CREATE OR REPLACE SYNONYM public.USER_TAB_STATISTICS for compat_tools.USER_TAB_STATISTICS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_STATISTICS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_COMMENTS
-- ALL_TAB_COMMENTS
-- USER_TAB_COMMENTS
-- =========================================================================
-- 2022-05-26 1.1 增加没有注释的表的展示，缩减 pg_class 对象类型
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_COMMENTS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_COMMENTS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
, CASE c.relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
ELSE c.relkind::text
END AS TABLE_TYPE
, d.description as COMMENTS
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n on c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_description d on c.oid = d.objoid and d.objsubid = 0 and d.classoid = 'pg_class'::regclass::oid
WHERE n.nspname not like 'pg_toast%'
AND c.relkind in ('r', 'v', 'f');
CREATE OR REPLACE VIEW compat_tools.USER_TAB_COMMENTS AS SELECT table_name, table_type, comments FROM compat_tools.DBA_TAB_COMMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_COMMENTS for compat_tools.DBA_TAB_COMMENTS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_COMMENTS for compat_tools.DBA_TAB_COMMENTS;
CREATE OR REPLACE SYNONYM public.USER_TAB_COMMENTS for compat_tools.USER_TAB_COMMENTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_COMMENTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_COL_COMMENTS
-- ALL_COL_COMMENTS
-- USER_COL_COMMENTS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_COL_COMMENTS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_COL_COMMENTS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS TABLE_NAME
, case when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
, d.description as COMMENTS
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c on a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n on c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_description d on a.attrelid = d.objoid and a.attnum = d.objsubid and d.classoid = 'pg_class'::regclass::oid
WHERE n.nspname not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_COL_COMMENTS AS SELECT table_name, column_name, comments FROM compat_tools.DBA_COL_COMMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_COL_COMMENTS for compat_tools.DBA_COL_COMMENTS;
CREATE OR REPLACE SYNONYM public.ALL_COL_COMMENTS for compat_tools.DBA_COL_COMMENTS;
CREATE OR REPLACE SYNONYM public.USER_COL_COMMENTS for compat_tools.USER_COL_COMMENTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_COL_COMMENTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TAB_MODIFICATIONS
-- ALL_TAB_MODIFICATIONS
-- USER_TAB_MODIFICATIONS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TAB_MODIFICATIONS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TAB_MODIFICATIONS
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS TABLE_OWNER
, case when p.relname::text = lower(p.relname::text) then compat_tools.f_upper_name(p.relname::text) else p.relname::text end AS TABLE_NAME
, s.n_tup_ins as INSERTS
, s.n_tup_upd as UPDATES
, s.n_tup_del as DELETES
, coalesce(s.last_analyze, s.last_autovacuum) as TIMESTAMP
FROM pg_catalog.pg_class p
join pg_catalog.pg_namespace n on p.relnamespace = n.oid
join pg_catalog.pg_stat_all_tables s on p.oid = s.relid
WHERE p.relkind = 'r';
CREATE OR REPLACE VIEW compat_tools.USER_TAB_MODIFICATIONS AS SELECT TABLE_NAME, INSERTS, UPDATES, DELETES, "timestamp" FROM compat_tools.DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_TAB_MODIFICATIONS for compat_tools.DBA_TAB_MODIFICATIONS;
CREATE OR REPLACE SYNONYM public.ALL_TAB_MODIFICATIONS for compat_tools.DBA_TAB_MODIFICATIONS;
CREATE OR REPLACE SYNONYM public.USER_TAB_MODIFICATIONS for compat_tools.USER_TAB_MODIFICATIONS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TAB_MODIFICATIONS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_INDEXES
-- ALL_INDEXES
-- USER_INDEXES
-- IND
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_INDEXES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_INDEXES
AS
SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
, case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
, case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
, CASE t.relkind
WHEN 'r'::"char" THEN 'TABLE'::text
WHEN 'v'::"char" THEN 'VIEW'::text
WHEN 'i'::"char" THEN 'INDEX'::text
WHEN 'I'::"char" THEN 'PARTITIONED INDEX'::text
WHEN 'S'::"char" THEN 'SEQUENCE'::text
WHEN 'f'::"char" THEN 'FOREIGN TABLE'::text
WHEN 'c'::"char" THEN 'COMPOSITE TYPE'::text
WHEN 't'::"char" THEN 'TOAST'::text
ELSE t.relkind::text
END AS TABLE_TYPE
, case when i.indisunique then 'UNIQUE' else 'NONUNIQUE' end as UNIQUENESS
, compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as TABLESPACE_NAME
, case c.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
, CASE WHEN st.stadistinct >= 0 THEN st.stadistinct ELSE ROUND(ABS(st.stadistinct * c.RELTUPLES)) END as DISTINCT_KEYS
, case when i.indisvalid then 'VALID' else 'UNUSABLE' end as STATUS
, c.reltuples as NUM_ROWS
, coalesce(pg_stat_get_last_analyze_time(t.oid), pg_stat_get_last_autoanalyze_time(t.oid)) as LAST_ANALYZED
, case when c.relkind = 'I' then 'YES' else 'NO' end as PARTITIONED
, case c.relpersistence when 't' then 'YES' else 'NO' end as TEMPORARY
FROM pg_catalog.pg_index i
join pg_catalog.pg_class c on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
left join pg_catalog.pg_tablespace s on c.reltablespace = s.oid
left join pg_catalog.pg_statistic st on i.indnatts = 1 and i.indkey[0] = st.staattnum and st.starelid = t.oid
WHERE nc.nspname::text not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_INDEXES AS SELECT index_name, table_owner, table_name, table_type, uniqueness, tablespace_name, logging, distinct_keys, status, num_rows, last_analyzed, partitioned, temporary FROM compat_tools.DBA_INDEXES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_INDEXES for compat_tools.DBA_INDEXES;
CREATE OR REPLACE SYNONYM public.ALL_INDEXES for compat_tools.DBA_INDEXES;
CREATE OR REPLACE SYNONYM public.USER_INDEXES for compat_tools.USER_INDEXES;
CREATE OR REPLACE SYNONYM public.IND for compat_tools.USER_INDEXES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_INDEXES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_INDEX_USAGE
-- ALL_INDEX_USAGE
-- USER_INDEX_USAGE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_INDEX_USAGE', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_INDEX_USAGE
AS
SELECT i.oid as object_id
, case when i.relname::text = lower(i.relname::text) then compat_tools.f_upper_name(i.relname::text) else i.relname::text end AS NAME
, case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, pg_stat_get_numscans(i.oid) as TOTAL_ACCESS_COUNT
, pg_stat_get_tuples_returned(i.oid) as TOTAL_ROWS_RETURNED
FROM pg_catalog.pg_class i
join pg_catalog.pg_namespace n on n.oid = i.relnamespace
WHERE i.relkind in ('i') and n.nspname::text not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_INDEX_USAGE AS SELECT object_id, name, total_access_count, total_rows_returned FROM compat_tools.DBA_INDEX_USAGE WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_INDEX_USAGE for compat_tools.DBA_INDEX_USAGE;
CREATE OR REPLACE SYNONYM public.ALL_INDEX_USAGE for compat_tools.DBA_INDEX_USAGE;
CREATE OR REPLACE SYNONYM public.USER_INDEX_USAGE for compat_tools.USER_INDEX_USAGE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_INDEX_USAGE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_IND_COLUMNS
-- ALL_IND_COLUMNS
-- USER_IND_COLUMNS
-- =========================================================================
-- 变更历史：
-- 2022-05-20 1.1 支持函数索引字段的展示，字段名称统一展示为 SYS_FUNCTION_EXPR，额外添加 COLUMN_EXPRESSION 可避免与 dba_ind_expressions 视图关联
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_IND_COLUMNS', '2.0')
then
-- 创建必要的函数，用于解析函数索引的字段表达式
create or replace function pg_get_expr_array (expr_list text)
returns text[] immutable strict as $$
declare
l_result text[] := '{}';
l_len_left int := 0;
l_len_right int := 0;
l_temp_elem text := '';
l_rec record;
begin
for l_rec in select t
, length(replace(regexp_replace(t, '''[^'']+''', ''), '(', '')) as len_left
, length(replace(regexp_replace(t, '''[^'']+''', ''), ')', '')) as len_right
from regexp_split_to_table(expr_list, ',') as t
loop
l_len_left := l_len_left + l_rec.len_left;
l_len_right := l_len_right + l_rec.len_right;
l_temp_elem := l_temp_elem || l_rec.t;
if l_len_left = l_len_right
then
l_result := l_result || trim(l_temp_elem);
l_len_left := 0;
l_len_right := 0;
l_temp_elem := '';
end if;
end loop;
if l_temp_elem != '' and l_temp_elem is not null
then
l_result := l_result || trim(l_temp_elem);
end if;
return l_result;
end;
$$ language plpgsql;
-- 创建视图
CREATE OR REPLACE VIEW compat_tools.DBA_IND_COLUMNS
AS
SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS INDEX_OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
, case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
, case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
, case when a.attname is null then 'SYS_FUNCTION_EXPR' when a.attname::text = lower(a.attname::text) then compat_tools.f_upper_name(a.attname::text) else a.attname::text end AS COLUMN_NAME
, case when i.indkey = 0 then indexprs[row_number() over (partition by i.indexrelid, i.indkey order by ord)] else null end AS COLUMN_EXPRESSION
, i.ord as COLUMN_POSITION
, a.attlen as COLUMN_LENGTH
, case i.indoption when 0 then 'ASC' when 3 then 'DESC' end as DESCEND
FROM (select indexrelid, indrelid, unnest(indkey) as indkey, unnest(indoption) as indoption, pg_get_expr_array(pg_get_expr(indexprs, indrelid, 'true')) as indexprs
, generate_series( 1, array_length(indkey, 1)) as ord
FROM pg_catalog.pg_index) i
join pg_catalog.pg_class c on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
left join pg_catalog.pg_attribute a on a.attrelid = i.indrelid and i.indkey = a.attnum
WHERE nc.nspname::text not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_IND_COLUMNS AS SELECT index_name, table_owner, table_name, column_name, column_position, column_length, descend FROM compat_tools.DBA_IND_COLUMNS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_IND_COLUMNS for compat_tools.DBA_IND_COLUMNS;
CREATE OR REPLACE SYNONYM public.ALL_IND_COLUMNS for compat_tools.DBA_IND_COLUMNS;
CREATE OR REPLACE SYNONYM public.USER_IND_COLUMNS for compat_tools.USER_IND_COLUMNS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_IND_COLUMNS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_IND_EXPRESSIONS
-- ALL_IND_EXPRESSIONS
-- USER_IND_EXPRESSIONS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_IND_EXPRESSIONS', '2.0')
then
-- 创建必要的函数，用于解析函数索引的字段表达式
create or replace function pg_get_expr_array (expr_list text)
returns text[] immutable strict as $$
declare
l_result text[] := '{}';
l_len_left int := 0;
l_len_right int := 0;
l_temp_elem text := '';
l_rec record;
begin
for l_rec in select t
, length(replace(regexp_replace(t, '''[^'']+''', ''), '(', '')) as len_left
, length(replace(regexp_replace(t, '''[^'']+''', ''), ')', '')) as len_right
from regexp_split_to_table(expr_list, ',') as t
loop
l_len_left := l_len_left + l_rec.len_left;
l_len_right := l_len_right + l_rec.len_right;
l_temp_elem := l_temp_elem || l_rec.t;
if l_len_left = l_len_right
then
l_result := l_result || trim(l_temp_elem);
l_len_left := 0;
l_len_right := 0;
l_temp_elem := '';
end if;
end loop;
if l_temp_elem != '' and l_temp_elem is not null
then
l_result := l_result || trim(l_temp_elem);
end if;
return l_result;
end;
$$ language plpgsql;
-- 创建视图
CREATE OR REPLACE VIEW compat_tools.DBA_IND_EXPRESSIONS
AS
SELECT case when nc.nspname::text = lower(nc.nspname::text) then compat_tools.f_upper_name(nc.nspname::text) else nc.nspname::text end AS INDEX_OWNER
, case when c.relname::text = lower(c.relname::text) then compat_tools.f_upper_name(c.relname::text) else c.relname::text end AS INDEX_NAME
, case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
, case when t.relname::text = lower(t.relname::text) then compat_tools.f_upper_name(t.relname::text) else t.relname::text end AS TABLE_NAME
, indexprs[row_number() over (partition by indexrelid order by ord)] AS COLUMN_EXPRESSION
, i.ord as COLUMN_POSITION
FROM (select indexrelid, indrelid, unnest(indkey) as indkey, unnest(indoption) as indoption, pg_get_expr_array(pg_get_expr(indexprs, indrelid, 'true')) as indexprs
, generate_series( 1, array_length(indkey, 1)) as ord
FROM pg_catalog.pg_index) i
join pg_catalog.pg_class c on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
join pg_catalog.pg_namespace nc on nc.oid = c.relnamespace
join pg_catalog.pg_namespace nt on nt.oid = t.relnamespace
WHERE nc.nspname::text not like 'pg_toast%'
AND i.indkey = 0; -- 只显示函数表达式字段
CREATE OR REPLACE VIEW compat_tools.USER_IND_EXPRESSIONS AS SELECT index_name, table_owner, table_name, column_expression, column_position FROM compat_tools.DBA_IND_EXPRESSIONS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_IND_EXPRESSIONS for compat_tools.DBA_IND_EXPRESSIONS;
CREATE OR REPLACE SYNONYM public.ALL_IND_EXPRESSIONS for compat_tools.DBA_IND_EXPRESSIONS;
CREATE OR REPLACE SYNONYM public.USER_IND_EXPRESSIONS for compat_tools.USER_IND_EXPRESSIONS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_IND_EXPRESSIONS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_IND_PARTITIONS
-- ALL_IND_PARTITIONS
-- USER_IND_PARTITIONS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_IND_PARTITIONS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_IND_PARTITIONS
AS
SELECT case when ni.nspname::text = lower(ni.nspname::text) then compat_tools.f_upper_name(ni.nspname::text) else ni.nspname::text end AS INDEX_OWNER
, case when ci.relname::text = lower(ci.relname::text) then compat_tools.f_upper_name(ci.relname::text) else ci.relname::text end AS INDEX_NAME
, case when pi.relname::text = lower(pi.relname::text) then compat_tools.f_upper_name(pi.relname::text) else pi.relname::text end AS PARTITION_NAME
, case when nt.nspname::text = lower(nt.nspname::text) then compat_tools.f_upper_name(nt.nspname::text) else nt.nspname::text end AS TABLE_OWNER
, case when ct.relname::text = lower(ct.relname::text) then compat_tools.f_upper_name(ct.relname::text) else ct.relname::text end AS TABLE_NAME
, coalesce(array_to_string(pt.boundaries, ','), 'MAXVALUE') as HIGH_VALUE
, length(array_to_string(pt.boundaries, ',')) as HIGH_VALUE_LENGTH
, row_number() over (partition by pt.parentid order by pt.boundaries) as PARTITION_POSITION
, compat_tools.f_upper_name(coalesce(t.spcname::text, 'default')) as TABLESPACE_NAME
, case ct.relpersistence when 'p' then 'YES' else 'NO' end as LOGGING
, pi.reltuples as NUM_ROWS
, coalesce(pg_stat_get_last_analyze_time(ct.oid), pg_stat_get_last_autoanalyze_time(ct.oid)) as LAST_ANALYZED
FROM pg_catalog.pg_partition pi
join pg_catalog.pg_partition pt on pi.indextblid = pt.oid
join pg_catalog.pg_class ci on pi.parentid = ci.oid
join pg_catalog.pg_class ct on pt.parentid = ct.oid
join pg_catalog.pg_namespace ni on ci.relnamespace = ni.oid
join pg_catalog.pg_namespace nt on ct.relnamespace = nt.oid
left join pg_catalog.pg_tablespace t on pi.reltablespace = t.oid
WHERE pi.parttype = 'x';
CREATE OR REPLACE VIEW compat_tools.USER_IND_PARTITIONS AS SELECT index_name, partition_name, table_owner, table_name, high_value, high_value_length, partition_position, tablespace_name, logging, num_rows, last_analyzed FROM compat_tools.DBA_IND_PARTITIONS WHERE INDEX_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_IND_PARTITIONS for compat_tools.DBA_IND_PARTITIONS;
CREATE OR REPLACE SYNONYM public.ALL_IND_PARTITIONS for compat_tools.DBA_IND_PARTITIONS;
CREATE OR REPLACE SYNONYM public.USER_IND_PARTITIONS for compat_tools.USER_IND_PARTITIONS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_IND_PARTITIONS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_IND_STATISTICS
-- ALL_IND_STATISTICS
-- USER_IND_STATISTICS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_IND_STATISTICS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_IND_STATISTICS
AS
SELECT OWNER
, INDEX_NAME
, TABLE_OWNER
, TABLE_NAME
, null as PARTITION_NAME
, null as PARTITION_POSITION
, 'INDEX' as OBJECT_TYPE
, DISTINCT_KEYS
, NUM_ROWS
, LAST_ANALYZED
FROM compat_tools.DBA_INDEXES
WHERE partitioned = 'NO'
union all
select INDEX_OWNER as OWNER
, INDEX_NAME
, TABLE_OWNER
, TABLE_NAME
, PARTITION_NAME
, PARTITION_POSITION
, 'PARTITION' as OBJECT_TYPE
, null as DISTINCT_KEYS
, NUM_ROWS
, LAST_ANALYZED
FROM compat_tools.DBA_IND_PARTITIONS;
CREATE OR REPLACE VIEW compat_tools.USER_IND_STATISTICS AS SELECT * FROM compat_tools.DBA_IND_STATISTICS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_IND_STATISTICS for compat_tools.DBA_IND_STATISTICS;
CREATE OR REPLACE SYNONYM public.ALL_IND_STATISTICS for compat_tools.DBA_IND_STATISTICS;
CREATE OR REPLACE SYNONYM public.USER_IND_STATISTICS for compat_tools.USER_IND_STATISTICS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_IND_STATISTICS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_PART_INDEXES
-- ALL_PART_INDEXES
-- USER_PART_INDEXES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_PART_INDEXES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_PART_INDEXES
AS
SELECT case when n.nspname::text = lower(n.nspname::text) then compat_tools.f_upper_name(n.nspname::text) else n.nspname::text end AS OWNER
, case when i.relname::text = lower(i.relname::text) then compat_tools.f_upper_name(i.relname::text) else i.relname::text end AS INDEX_NAME
, case when tp.relname::text = lower(tp.relname::text) then compat_tools.f_upper_name(tp.relname::text) else tp.relname::text end AS TABLE_NAME
, case tp.partstrategy
when 'r' then 'RANGE'
when 'v' then 'VALUE'
when 'i' then 'INTERVAL'
when 'l' then 'LIST'
when 'h' then 'HASH'
when 'n' then 'INVALID'
else compat_tools.f_upper_name(tp.partstrategy::text) end as PARTITIONING_TYPE
, coalesce(ip.part_count, 0) as PARTITION_COUNT
, array_length(tp.partkey::int[], 1) as PARTITIONING_KEY_COUNT
, compat_tools.f_upper_name(coalesce(s.spcname::text, 'default')) as DEF_TABLESPACE_NAME
, case i.relpersistence when 'p' then 'YES' else 'NO' end as DEF_LOGGING
FROM pg_catalog.pg_index it
join pg_catalog.pg_class i on i.oid = it.indexrelid
join pg_catalog.pg_namespace n on i.relnamespace = n.oid
join pg_catalog.pg_partition tp on tp.parentid = it.indrelid and tp.parttype = 'r'
left join (select parentid, count(*) part_count FROM pg_catalog.pg_partition group by parentid) ip on ip.parentid = it.indexrelid
left join pg_catalog.pg_tablespace s on i.reltablespace = s.oid
WHERE n.nspname not like 'pg_toast%';
CREATE OR REPLACE VIEW compat_tools.USER_PART_INDEXES AS SELECT index_name, table_name, partitioning_type, partition_count, partitioning_key_count, def_tablespace_name, def_logging FROM compat_tools.DBA_PART_INDEXES WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_PART_INDEXES for compat_tools.DBA_PART_INDEXES;
CREATE OR REPLACE SYNONYM public.ALL_PART_INDEXES for compat_tools.DBA_PART_INDEXES;
CREATE OR REPLACE SYNONYM public.USER_PART_INDEXES for compat_tools.USER_PART_INDEXES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_PART_INDEXES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_USERS
-- ALL_USERS
-- USER_USERS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_USERS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_USERS
AS
SELECT case when usename::text = lower(usename::text) then compat_tools.f_upper_name(usename::text) else usename::text end AS USERNAME
, usesysid as USER_ID
, passwd as PASSWORD
, CASE s.rolstatus
WHEN 2 THEN 'LOCK (ADMIN)'
WHEN 1 THEN 'LOCK (FAILURE)'
ELSE 'NORMAL'
END as account_status
, CASE WHEN s.rolstatus in (1, 2) then locktime else null END as LOCK_DATE
, valuntil as EXPIRY_DATE
, compat_tools.f_upper_name(respool::text) as PROFILE
FROM pg_catalog.pg_user u
LEFT JOIN pg_catalog.pg_user_status s on u.usesysid = s.roloid;
CREATE OR REPLACE VIEW compat_tools.USER_USERS AS SELECT USERNAME, USER_ID, account_status, LOCK_DATE, EXPIRY_DATE FROM compat_tools.DBA_USERS WHERE USERNAME = (case when current_user::text = lower(current_user::text) then compat_tools.f_upper_name(current_user::text) else current_user::text end);
CREATE OR REPLACE SYNONYM public.DBA_USERS for compat_tools.DBA_USERS;
CREATE OR REPLACE SYNONYM public.ALL_USERS for compat_tools.DBA_USERS;
CREATE OR REPLACE SYNONYM public.USER_USERS for compat_tools.USER_USERS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_USERS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_ROLES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_ROLES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_ROLES
AS
SELECT case when rolname::text = lower(rolname::text) then compat_tools.f_upper_name(rolname::text) else rolname::text end AS ROLE
, 'NO' as PASSWORD_REQUIRED
, 'NONE' as AUTHENTICATION_TYPE
, 'YES' as COMMON
FROM pg_catalog.pg_roles;
CREATE OR REPLACE SYNONYM public.DBA_ROLES for compat_tools.DBA_ROLES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_ROLES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_ROLE_PRIVS
-- USER_ROLE_PRIVS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_ROLE_PRIVS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_ROLE_PRIVS
AS
SELECT case when grantee.rolname::text = lower(grantee.rolname::text) then compat_tools.f_upper_name(grantee.rolname::text) else grantee.rolname::text end AS GRANTEE
, case when granted.rolname::text = lower(granted.rolname::text) then compat_tools.f_upper_name(granted.rolname::text) else granted.rolname::text end AS GRANTED_ROLE
, case when m.admin_option then 'YES' else 'NO' end as ADMIN_OPTION
, CASE WHEN grantee.rolinherit THEN 'YES' ELSE 'NO' END AS default_role
, 'NO' as DELEGATE_OPTION
, 'YES' as COMMON
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles as grantee on m.member = grantee.oid
JOIN pg_catalog.pg_roles as granted on m.roleid = granted.oid;
CREATE OR REPLACE VIEW compat_tools.USER_ROLE_PRIVS AS SELECT GRANTEE as USERNAME, GRANTED_ROLE, ADMIN_OPTION, default_role, DELEGATE_OPTION, COMMON FROM compat_tools.DBA_ROLE_PRIVS WHERE GRANTEE = (case when current_user::text = lower(current_user::text) then compat_tools.f_upper_name(current_user::text) else current_user::text end);
CREATE OR REPLACE SYNONYM public.DBA_ROLE_PRIVS for compat_tools.DBA_ROLE_PRIVS;
CREATE OR REPLACE SYNONYM public.USER_ROLE_PRIVS for compat_tools.USER_ROLE_PRIVS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_ROLE_PRIVS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- PRODUCT_COMPONENT_VERSION
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'PRODUCT_COMPONENT_VERSION', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.PRODUCT_COMPONENT_VERSION
AS
WITH t_ver as (select row_number() over () as id, ver_item from regexp_split_to_table(version(), '\s+') as ver_item)
SELECT case when n.name_item = '' or n.name_item is null then 'openGauss' else n.name_item end || ' Enterprise Edition' AS product
, v.ver_item AS VERSION
, version() AS VERSION_FULL
, case when version() ~ 'x86_64' then '64bit Production' else 'Production' end AS status
FROM (select ver_item from t_ver where id = (select id - 1 from t_ver where ver_item = 'build')
union all
select ver_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 2) as v
, (select trim(ver_item, '() ') as name_item from t_ver where id = (select id - 2 from t_ver where ver_item = 'build')
union all
select trim(ver_item, '() ') as name_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 1) as n
UNION ALL
SELECT extname AS product
, extversion AS VERSION
, NULL as VERSION_FULL
, 'Production' AS status
FROM pg_extension;
CREATE OR REPLACE SYNONYM public.PRODUCT_COMPONENT_VERSION for compat_tools.PRODUCT_COMPONENT_VERSION;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in PRODUCT_COMPONENT_VERSION: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- PLAN_TABLE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'PLAN_TABLE', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.PLAN_TABLE
AS
SELECT statement_id
, plan_id
, id
, operation
, options
, object_name
, object_type
, object_owner
, projection
FROM plan_table_data
WHERE session_id = pg_current_sessionid()
AND user_id = pg_current_userid();
CREATE OR REPLACE SYNONYM public.PLAN_TABLE for compat_tools.PLAN_TABLE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in PLAN_TABLE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$PARAMETER
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$PARAMETER', '1.1')
then
CREATE OR REPLACE VIEW compat_tools.V$PARAMETER
AS
SELECT NAME
, case vartype when 'bool' then 1 when 'string' then 2 when 'integer' then 3 when 'enum' then 4 when 'real' then 6 end as TYPE
, vartype as TYPE_NAME
, setting as VALUE
, setting as DISPLAY_VALUE
, boot_val as DEFAULT_VALUE
, case when setting = boot_val then 'TRUE' else 'FALSE' end as ISDEFAULT
, case context when 'internal' then 'FLASE' else 'TRUE' end as ISMODIFIED
, case context when 'internal' then 'TRUE' else 'FALSE' end as ISADJUSTED
, short_desc as DESCRIPTION
FROM pg_catalog.pg_settings;
create view compat_tools.GV$PARAMETER as select 1 as inst_id, t.* from compat_tools.v$PARAMETER as t;
CREATE OR REPLACE SYNONYM public.V$PARAMETER for compat_tools.V$PARAMETER;
CREATE OR REPLACE SYNONYM public.GV$PARAMETER for compat_tools.GV$PARAMETER;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$PARAMETER: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SPPARAMETER
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SPPARAMETER', '1.1')
then
CREATE OR REPLACE VIEW compat_tools.V$SPPARAMETER
AS
SELECT NAME
, case vartype when 'bool' then 1 when 'string' then 2 when 'integer' then 3 when 'enum' then 4 when 'real' then 6 end as TYPE
, vartype as TYPE_NAME
, setting as VALUE
, setting as DISPLAY_VALUE
, case when sourcefile is null then 'FALSE' else 'TRUE' end as ISSPECIFIED
FROM pg_catalog.pg_settings;
create view compat_tools.GV$SPPARAMETER as select 1 as inst_id, t.* from compat_tools.v$SPPARAMETER as t;
CREATE OR REPLACE SYNONYM public.V$SPPARAMETER for compat_tools.V$SPPARAMETER;
CREATE OR REPLACE SYNONYM public.GV$SPPARAMETER for compat_tools.GV$SPPARAMETER;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SPPARAMETER: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$VERSION
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$VERSION', '1.1')
then
CREATE OR REPLACE VIEW compat_tools.V$VERSION
AS
select regexp_substr(version(), 'MogDB[^\)]+') as banner
union all
select regexp_substr(version(), 'compiled at[\s\d\-\:]+')
union all
select replace(regexp_substr(version(), 'on\s+[^,]+'), 'on', 'Platform architecture:');
create view compat_tools.GV$VERSION as select 1 as inst_id, t.* from compat_tools.v$VERSION as t;
CREATE OR REPLACE SYNONYM public.V$VERSION for compat_tools.V$VERSION;
CREATE OR REPLACE SYNONYM public.GV$VERSION for compat_tools.GV$VERSION;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$VERSION: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$PARAMETER_VALID_VALUES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$PARAMETER_VALID_VALUES', '1.1')
then
CREATE OR REPLACE VIEW compat_tools.V$PARAMETER_VALID_VALUES
AS
SELECT NAME
, row_number() over (partition by name) as ORDINAL
, VALUE
, case when boot_val = VALUE then 'TRUE' else 'FALSE' end as ISDEFAULT
FROM (select name, boot_val, unnest(enumvals) as VALUE FROM pg_catalog.pg_settings) as p;
create view compat_tools.GV$PARAMETER_VALID_VALUES as select 1 as inst_id, t.* from compat_tools.v$PARAMETER_VALID_VALUES as t;
CREATE OR REPLACE SYNONYM public.V$PARAMETER_VALID_VALUES for compat_tools.V$PARAMETER_VALID_VALUES;
CREATE OR REPLACE SYNONYM public.GV$PARAMETER_VALID_VALUES for compat_tools.GV$PARAMETER_VALID_VALUES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$PARAMETER_VALID_VALUES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SESSION
-- =========================================================================
-- 变更历史：
-- 1.2 : 增加字段 SQL_EXEC_START/BLOCKING_SESSION_STATUS/WAIT_TIME_MICRO
-- 1.3 : 增加字段 serial#/audsid/user#
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SESSION', '1.3')
then
CREATE OR REPLACE VIEW compat_tools.V$SESSION
AS
SELECT a.sessionid as SID
, 0::int as serial#
, case when trim(connection_info) is null then 0 else a.sessionid end as audsid
, a.usesysid as user#
, a.usename as USERNAME
, a.state as STATUS
, coalesce(a.client_hostname, a.client_addr::text) as MACHINE
, a.client_port as PORT
, a.application_name as PROGRAM
, case when trim(connection_info) is null then 'BACKGROUND' else 'USER' end as TYPE
, a.query_id as SQL_ID
, a.query as SQL_TEXT
, a.query_start as SQL_EXEC_START
, a.connection_info as CLIENT_INFO
, a.backend_start as LOGON_TIME
, case when a.waiting then 'VALID' else 'NOT IN WAIT' end as BLOCKING_SESSION_STATUS
, w.wait_status as WAIT_CLASS
, w.wait_event as EVENT
, 'Thread' as P1TEXT
, w.tid as P1
, 'LW Thread' as P2TEXT
, w.lwtid as P2
, 'Thread Level' as P3TEXT
, w.tlevel as P3
, extract(epoch from now() - a.state_change) * 1000000 as WAIT_TIME_MICRO
FROM pg_catalog.pg_stat_activity as a
LEFT JOIN (select * from pg_stat_get_status(NULL::bigint)) as w on a.sessionid = w.sessionid;
create view compat_tools.GV$SESSION as select 1 as inst_id, t.* from compat_tools.v$SESSION as t;
CREATE OR REPLACE SYNONYM public.V$SESSION for compat_tools.V$SESSION;
CREATE OR REPLACE SYNONYM public.GV$SESSION for compat_tools.GV$SESSION;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SESSION: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SYSTEM_WAIT_CLASS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SYSTEM_WAIT_CLASS', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$SYSTEM_WAIT_CLASS
AS
SELECT TYPE as WAIT_CLASS
, SUM(wait) as TOTAL_WAITS
, sum(total_wait_time)/10000 as TIME_WAITED
, sum(avg_wait_time)/10000 as AVG_TIME_WAITED
, max(avg_wait_time)/10000 as MAX_TIME_WAITED
, min(avg_wait_time)/10000 as MIN_TIME_WAITED
, max(last_updated) as last_updated
FROM get_instr_wait_event(NULL::integer) get_instr_wait_event(nodename, type, event, wait, failed_wait, total_wait_time, avg_wait_time, max_wait_time, min_wait_time, last_updated)
group by TYPE;
create view compat_tools.GV$SYSTEM_WAIT_CLASS as select 1 as inst_id, t.* from compat_tools.V$SYSTEM_WAIT_CLASS as t;
CREATE OR REPLACE SYNONYM public.V$SYSTEM_WAIT_CLASS for compat_tools.V$SYSTEM_WAIT_CLASS;
CREATE OR REPLACE SYNONYM public.GV$SYSTEM_WAIT_CLASS for compat_tools.GV$SYSTEM_WAIT_CLASS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SYSTEM_WAIT_CLASS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- FUNCTION: F_QUERY_STATMENT()
-- 用于处理 v$sql 视图在普通用户下的访问权限
-- =========================================================================
begin
if compat_tools.drop_compat_object('FUNCTION', 'F_QUERY_STATMENT()', '1.0')
then
create function compat_tools.f_query_statment()
returns table( node_name name
, node_id integer
, user_name name
, user_id oid
, unique_sql_id bigint
, query text
, n_calls bigint
, min_elapse_time bigint
, max_elapse_time bigint
, total_elapse_time bigint
, n_returned_rows bigint
, n_tuples_fetched bigint
, n_tuples_returned bigint
, n_tuples_inserted bigint
, n_tuples_updated bigint
, n_tuples_deleted bigint
, n_blocks_fetched bigint
, n_blocks_hit bigint
, n_soft_parse bigint
, n_hard_parse bigint
, db_time bigint
, cpu_time bigint
, execution_time bigint
, parse_time bigint
, plan_time bigint
, rewrite_time bigint
, pl_execution_time bigint
, pl_compilation_time bigint
, data_io_time bigint
, net_send_info text
, net_recv_info text
, net_stream_send_info text
, net_stream_recv_info text
, last_updated timestamp with time zone
, sort_count bigint
, sort_time bigint
, sort_mem_used bigint
, sort_spill_count bigint
, sort_spill_size bigint
, hash_count bigint
, hash_time bigint
, hash_mem_used bigint
, hash_spill_count bigint
, hash_spill_size bigint)
SECURITY DEFINER
as $$
SELECT node_name
, node_id
, user_name
, user_id
, unique_sql_id
, query
, n_calls
, min_elapse_time
, max_elapse_time
, total_elapse_time
, n_returned_rows
, n_tuples_fetched
, n_tuples_returned
, n_tuples_inserted
, n_tuples_updated
, n_tuples_deleted
, n_blocks_fetched
, n_blocks_hit
, n_soft_parse
, n_hard_parse
, db_time
, cpu_time
, execution_time
, parse_time
, plan_time
, rewrite_time
, pl_execution_time
, pl_compilation_time
, data_io_time
, net_send_info
, net_recv_info
, net_stream_send_info
, net_stream_recv_info
, last_updated
, sort_count
, sort_time
, sort_mem_used
, sort_spill_count
, sort_spill_size
, hash_count
, hash_time
, hash_mem_used
, hash_spill_count
, hash_spill_size
FROM get_instr_unique_sql() get_instr_unique_sql( node_name
, node_id
, user_name
, user_id
, unique_sql_id
, query
, n_calls
, min_elapse_time
, max_elapse_time
, total_elapse_time
, n_returned_rows
, n_tuples_fetched
, n_tuples_returned
, n_tuples_inserted
, n_tuples_updated
, n_tuples_deleted
, n_blocks_fetched
, n_blocks_hit
, n_soft_parse
, n_hard_parse
, db_time
, cpu_time
, execution_time
, parse_time
, plan_time
, rewrite_time
, pl_execution_time
, pl_compilation_time
, data_io_time
, net_send_info
, net_recv_info
, net_stream_send_info
, net_stream_recv_info
, last_updated
, sort_count
, sort_time
, sort_mem_used
, sort_spill_count
, sort_spill_size
, hash_count
, hash_time
, hash_mem_used
, hash_spill_count
, hash_spill_size);
$$ language sql;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in F_QUERY_STATMENT(): %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SQL
-- 依赖于 F_QUERY_STATMENT() 函数
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SQL', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$SQL
AS
select substr(query, 1, 1000) as SQL_TEXT
, query as SQL_FULLTEXT
, unique_sql_id as SQL_ID
, sort_count as SORTS
, n_calls as EXECUTIONS
, n_soft_parse + n_hard_parse as PARSE_CALLS
, n_blocks_fetched - n_blocks_hit as DISK_READS
, n_blocks_fetched as BUFFER_GETS
, pl_execution_time as PLSQL_EXEC_TIME
, n_returned_rows as ROWS_PROCESSED
, user_id as PARSING_USER_ID
, user_name as PARSING_SCHEMA_NAME
, hashtext(query) as HASH_VALUE
, cpu_time as CPU_TIME
, total_elapse_time as ELAPSED_TIME
, unique_sql_id as FORCE_MATCHING_SIGNATURE
, last_updated as LAST_ACTIVE_TIME
, db_time
, unique_sql_id
, n_calls
, min_elapse_time
, max_elapse_time
, total_elapse_time
, n_returned_rows
, n_tuples_fetched
, n_tuples_returned
, n_tuples_inserted
, n_tuples_updated
, n_tuples_deleted
, n_blocks_fetched
, n_blocks_hit
, n_soft_parse
, n_hard_parse
, execution_time
, parse_time
, plan_time
, rewrite_time
, pl_execution_time
, pl_compilation_time
, data_io_time
, net_send_info
, net_recv_info
, net_stream_send_info
, net_stream_recv_info
, last_updated
, sort_count
, sort_time
, sort_mem_used
, sort_spill_count
, sort_spill_size
, hash_count
, hash_time
, hash_mem_used
, hash_spill_count
, hash_spill_size
from compat_tools.f_query_statment();
create view compat_tools.GV$SQL as select 1 as inst_id, t.* from compat_tools.V$SQL as t;
CREATE OR REPLACE SYNONYM public.V$SQL for compat_tools.V$SQL;
CREATE OR REPLACE SYNONYM public.GV$SQL for compat_tools.GV$SQL;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SQL: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$OPTION
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$OPTION', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$OPTION
AS
select name as parameter
, case when installed_version is not null then 'TRUE' else 'FALSE' end as VALUE
from pg_available_extensions;
create view compat_tools.GV$OPTION as select 1 as inst_id, t.* from compat_tools.V$OPTION as t;
CREATE OR REPLACE SYNONYM public.V$OPTION for compat_tools.V$OPTION;
CREATE OR REPLACE SYNONYM public.GV$OPTION for compat_tools.GV$OPTION;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$OPTION: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$LICENSE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$LICENSE', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$LICENSE
AS
select current_setting('max_connections') as SESSIONS_MAX
, current_setting('connection_alarm_rate') * current_setting('max_connections') as SESSIONS_WARNING
, (select count(*) from pg_stat_activity) as SESSIONS_CURRENT
, current_setting('max_connections') as SESSIONS_HIGHWATER
, 0 as USERS_MAX
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPUS') as CPU_COUNT_CURRENT
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_CORES') as CPU_CORE_COUNT_CURRENT
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_SOCKETS') as CPU_SOCKET_COUNT_CURRENT
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPUS') as CPU_COUNT_HIGHWATER
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_CORES') as CPU_CORE_COUNT_HIGHWATER
, (select value from GS_OS_RUN_INFO where name = 'NUM_CPU_SOCKETS') as CPU_SOCKET_COUNT_HIGHWATER;
create view compat_tools.GV$LICENSE as select 1 as inst_id, t.* from compat_tools.V$LICENSE as t;
CREATE OR REPLACE SYNONYM public.V$LICENSE for compat_tools.V$LICENSE;
CREATE OR REPLACE SYNONYM public.GV$LICENSE for compat_tools.GV$LICENSE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$LICENSE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_DETAIL_PRIVILEGES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_DETAIL_PRIVILEGES', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_DETAIL_PRIVILEGES
AS
SELECT /*+no nestloop*/ t.oid, t.oid_class, t.type, t.privilege_type
, r.rolname as owner, n.nspname as schema
, t.name, t.column_name
, ro.rolname as grantor, re.rolname as grantee
, t.privilege, t.is_grantable
FROM (select oid, 'pg_default_acl' as oid_class
, case defaclobjtype when 'r' then 'RELATION' when 'S' then 'SEQUENCE' when 'f' then 'FUNCTION' when 'T' then 'TYPE' when 'n' then 'SCHEMA' else defaclobjtype::text end as type
, 'DEFAULT PRIVILEGES' as privilege_type
, defaclrole as owner
, defaclnamespace as schema
, null as NAME
, NULL as column_name
, (aclexplode(defaclacl)).grantor AS grantor
, (aclexplode(defaclacl)).grantee AS grantee
, (aclexplode(defaclacl)).privilege_type AS privilege
, (aclexplode(defaclacl)).is_grantable AS is_grantable
from pg_catalog.pg_default_acl
where defaclacl is not null
union all
select oid, 'pg_class' as oid_class
, case relkind when 'r' then 'TABLE' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'm' then 'MATERIALIZED VIEW' when 'f' then 'FOREIGN TABLE' when 'p' then 'PARTITIONED TABLE' else relkind::text end as type
, case relkind when 'S' then 'SEQUENCE' else 'TABLE' end as privilege_type
, relowner as owner
, relnamespace as schema
, relname::text as NAME
, NULL as column_name
, (aclexplode(relacl)).grantor AS grantor
, (aclexplode(relacl)).grantee AS grantee
, (aclexplode(relacl)).privilege_type AS privilege
, (aclexplode(relacl)).is_grantable AS is_grantable
from pg_catalog.pg_class
where relacl is not null
union all
select c.oid, 'pg_class' as oid_class
, 'COLUMN' as type
, 'TABLE (COLUMN)' as privilege_type
, c.relowner as owner
, c.relnamespace as schema
, c.relname::text as name
, a.attname::text as column_name
, (aclexplode(a.attacl)).grantor AS grantor
, (aclexplode(a.attacl)).grantee AS grantee
, (aclexplode(a.attacl)).privilege_type AS privilege
, (aclexplode(a.attacl)).is_grantable AS is_grantable
from pg_catalog.pg_attribute as a
join pg_catalog.pg_class as c on a.attrelid = c.oid
where a.attacl is not null
union all
select oid, 'pg_database' as oid_class
, 'DATABASE' as type
, 'DATABASE' as privilege_type
, datdba as owner
, null as schema
, datname::text as name
, null as column_name
, (aclexplode(datacl)).grantor AS grantor
, (aclexplode(datacl)).grantee AS grantee
, (aclexplode(datacl)).privilege_type AS privilege
, (aclexplode(datacl)).is_grantable AS is_grantable
from pg_catalog.pg_database
where datacl is not null
union all
select oid, 'pg_foreign_data_wrapper' as oid_class
, 'FOREIGN DATA WRAPPER' as type
, 'FOREIGN DATA WRAPPER' as privilege_type
, fdwowner as owner
, null as schema
, fdwname::text as name
, null as column_name
, (aclexplode(fdwacl)).grantor AS grantor
, (aclexplode(fdwacl)).grantee AS grantee
, (aclexplode(fdwacl)).privilege_type AS privilege
, (aclexplode(fdwacl)).is_grantable AS is_grantable
from pg_catalog.pg_foreign_data_wrapper
where fdwacl is not null
union all
select oid, 'pg_foreign_server' as oid_class
, 'FOREIGN SERVER' as type
, 'FOREIGN SERVER' as privilege_type
, srvowner as owner
, null as schema
, srvname::text as name
, null as column_name
, (aclexplode(srvacl)).grantor AS grantor
, (aclexplode(srvacl)).grantee AS grantee
, (aclexplode(srvacl)).privilege_type AS privilege
, (aclexplode(srvacl)).is_grantable AS is_grantable
from pg_catalog.pg_foreign_server
where srvacl is not null
union all
select oid, 'pg_type' as oid_class
, case typtype when 'b' then 'BASE' when 'c' then 'COMPOSITE' when 'd' then 'DOMAIN' when 'e' then 'ENUM' when 'p' then 'PSEUDO' when 'r' then 'RANGE' else typtype::text end as type
, case typtype when 'd' then 'DOMAIN' else 'TYPE' end as privilege_type
, typowner as owner
, typnamespace as schema
, typname::text as name
, null as column_name
, (aclexplode(typacl)).grantor AS grantor
, (aclexplode(typacl)).grantee AS grantee
, (aclexplode(typacl)).privilege_type AS privilege
, (aclexplode(typacl)).is_grantable AS is_grantable
from pg_catalog.pg_type
where typacl is not null
union all
select oid, 'pg_proc' as oid_class
, case prokind when 'f' then 'FUNCTION' when 'p' then 'PROCEDURE' when 'a' then 'AGGREGATE' when 'w' then 'WINDOW' else prokind::text end as type
, case prokind when 'p' then 'PROCEDURE' else 'FUNCTION' end as privilege_type
, proowner as owner
, pronamespace as schema
, proname::text as name
, null as column_name
, (aclexplode(proacl)).grantor AS grantor
, (aclexplode(proacl)).grantee AS grantee
, (aclexplode(proacl)).privilege_type AS privilege
, (aclexplode(proacl)).is_grantable AS is_grantable
from pg_catalog.pg_proc
where proacl is not null
union all
select oid, 'pg_language' as oid_class
, 'LANGUAGE' as type
, 'LANGUAGE' as privilege_type
, lanowner as owner
, null as schema
, lanname::text as name
, null as column_name
, (aclexplode(lanacl)).grantor AS grantor
, (aclexplode(lanacl)).grantee AS grantee
, (aclexplode(lanacl)).privilege_type AS privilege
, (aclexplode(lanacl)).is_grantable AS is_grantable
from pg_catalog.pg_language
where lanacl is not null
union all
select oid, 'pg_largeobject_metadata' as oid_class
, 'LARGE OBJECT' as type
, 'LARGE OBJECT' as privilege_type
, lomowner as owner
, null as schema
, oid::text as name
, null as column_name
, (aclexplode(lomacl)).grantor AS grantor
, (aclexplode(lomacl)).grantee AS grantee
, (aclexplode(lomacl)).privilege_type AS privilege
, (aclexplode(lomacl)).is_grantable AS is_grantable
from pg_catalog.pg_largeobject_metadata
where lomacl is not null
union all
select oid, 'pg_namespace' as oid_class
, 'SCHEMA' as type
, 'SCHEMA' as privilege_type
, nspowner as owner
, null as schema
, nspname::text as name
, null as column_name
, (aclexplode(nspacl)).grantor AS grantor
, (aclexplode(nspacl)).grantee AS grantee
, (aclexplode(nspacl)).privilege_type AS privilege
, (aclexplode(nspacl)).is_grantable AS is_grantable
from pg_catalog.pg_namespace
where nspacl is not null
union all
select oid, 'pg_tablespace' as oid_class
, 'TABLESPACE' as type
, 'TABLESPACE' as privilege_type
, spcowner as owner
, null as schema
, spcname::text as name
, null as column_name
, (aclexplode(spcacl)).grantor AS grantor
, (aclexplode(spcacl)).grantee AS grantee
, (aclexplode(spcacl)).privilege_type AS privilege
, (aclexplode(spcacl)).is_grantable AS is_grantable
from pg_catalog.pg_tablespace
where spcacl is not null
) t
join pg_catalog.pg_roles r on t.owner = r.oid
join pg_catalog.pg_namespace n on t.schema = n.oid
left join pg_catalog.pg_roles ro on t.grantor = ro.oid
left join pg_catalog.pg_roles re on t.grantee = re.oid
where t.grantor != t.grantee
and t.grantee != 0
and t.grantor != 0;
CREATE OR REPLACE SYNONYM public.DBA_DETAIL_PRIVILEGES for compat_tools.DBA_DETAIL_PRIVILEGES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_DETAIL_PRIVILEGES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_ALL_PRIVILEGES_SQL
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_ALL_PRIVILEGES_SQL', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_ALL_PRIVILEGES_SQL
AS
with all_privs as (select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, is_grantable
from compat_tools.dba_detail_privileges
group by oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, is_grantable
having (privilege_type = 'TABLE' and count(*) = 7)
or (privilege_type = 'TABLE (COLUMN)' and count(*) = 4)
or (privilege_type = 'SEQUENCE' and count(*) = 3)
or (privilege_type = 'DATABASE' and count(*) = 3)
or (privilege_type = 'LARGE OBJECT' and count(*) = 2)
or (privilege_type = 'SCHEMA' and count(*) = 2)
or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'RELATION' and count(*) = 7)
or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'SEQUENCE' and count(*) = 3)
or (privilege_type = 'DEFAULT PRIVILEGES' and type = 'SCHEMA' and count(*) = 2)
)
SELECT t.*
, case when privilege_type = 'TABLE (COLUMN)'
then 'GRANT '||privilege||' ('||column_name||') on TABLE '||schema||'.'||name||' to '||grantee
when privilege_type = 'DEFAULT PRIVILEGES'
then 'alter default privileges for user '||grantor||' GRANT '||privilege||' on '||(case type when 'RELATION' then 'TABLE' else type end)||'S to '||grantee
else 'GRANT '||privilege||' on '||privilege_type||' '||(case when schema is not null then schema||'.'||name else name end)||' to '||grantee
end as grant_sql
, case when privilege_type = 'TABLE (COLUMN)'
then 'REVOKE '||privilege||' ('||column_name||') on TABLE '||schema||'.'||name||' from '||grantee
when privilege_type = 'DEFAULT PRIVILEGES'
then 'alter default privileges for user '||grantor||' REVOKE '||privilege||' on '||(case type when 'RELATION' then 'TABLE' else type end)||'S from '||grantee
else 'REVOKE '||privilege||' on '||privilege_type||' '||(case when schema is not null then schema||'.'||name else name end)||' from '||grantee
end as revoke_sql
from (select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, 'ALL' as privilege, is_grantable
from all_privs
union all
select oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, privilege, is_grantable
from compat_tools.dba_detail_privileges x
where not exists (select 1 from all_privs where oid = x.oid and grantor = x.grantor and grantee = x.grantee)
) t;
CREATE OR REPLACE VIEW compat_tools.DBA_ALL_PRIVILEGES AS SELECT oid, oid_class, type, privilege_type, owner, schema, name, column_name, grantor, grantee, privilege, is_grantable FROM compat_tools.DBA_ALL_PRIVILEGES_SQL;
CREATE OR REPLACE SYNONYM public.DBA_ALL_PRIVILEGES_SQL for compat_tools.DBA_ALL_PRIVILEGES_SQL;
CREATE OR REPLACE SYNONYM public.DBA_ALL_PRIVILEGES for compat_tools.DBA_ALL_PRIVILEGES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_ALL_PRIVILEGES_SQL: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DUAL
-- =========================================================================
begin
if compat_tools.drop_compat_object('SYNONYM', 'DUAL', '1.0')
then
select count(*) into l_cnt
from pg_class
where relname = 'sys_dummy'
and relnamespace = (select oid from pg_namespace where nspname = 'pg_catalog')
and not exists (select 1 from pg_class where relname = 'dual'); -- 忽略已有 dual 对象（避免和 orafce 插件内容冲突）
if l_cnt = 1
then
CREATE OR REPLACE SYNONYM public.DUAL for pg_catalog.sys_dummy;
end if;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DUAL: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_TABLESPACES
-- ALL_TABLESPACES
-- USER_TABLESPACES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_TABLESPACES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_TABLESPACES
AS
SELECT case when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
, b.setting as BLOCK_SIZE
, t.spcmaxsize as MAX_SIZE
, 'ONLINE' as STATUS
, 'PERMANENT' as CONTENTS
, r.rolname as TABLESPACE_OWNER
from pg_tablespace as t
join pg_roles as r on t.spcowner = r.oid
join (select setting from pg_settings where name = 'block_size') as b on 1=1;
CREATE OR REPLACE VIEW compat_tools.USER_TABLESPACES AS SELECT tablespace_name, block_size, max_size, status, contents FROM compat_tools.DBA_TABLESPACES WHERE TABLESPACE_OWNER = current_user;
CREATE OR REPLACE SYNONYM public.DBA_TABLESPACES for compat_tools.DBA_TABLESPACES;
CREATE OR REPLACE SYNONYM public.ALL_TABLESPACES for compat_tools.DBA_TABLESPACES;
CREATE OR REPLACE SYNONYM public.USER_TABLESPACES for compat_tools.USER_TABLESPACES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_TABLESPACES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$TABLESPACE
-- GV$TABLESPACE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$TABLESPACE', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.V$TABLESPACE
AS
SELECT t.oid::bigint as TS#
, case when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
from pg_tablespace as t;
CREATE OR REPLACE VIEW compat_tools.GV$TABLESPACE AS SELECT 1 as INST_ID, t.* from compat_tools.V$TABLESPACE as t;
CREATE OR REPLACE SYNONYM public.V$TABLESPACE for compat_tools.V$TABLESPACE;
CREATE OR REPLACE SYNONYM public.GV$TABLESPACE for compat_tools.GV$TABLESPACE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$TABLESPACE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_DATA_FILES
-- ALL_DATA_FILES
-- USER_DATA_FILES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_DATA_FILES', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_DATA_FILES
AS
SELECT d.setting||'/'||pg_relation_filepath(c.oid) as FILE_NAME
, c.oid::bigint as FILE_ID
, case when t.spcname is null then compat_tools.f_upper_name('pg_default') when t.spcname::text = lower(t.spcname::text) then compat_tools.f_upper_name(t.spcname::text) else t.spcname::text end as TABLESPACE_NAME
, pg_relation_size(c.oid) as BYTES
, round(pg_relation_size(c.oid)/b.setting) as BLOCKS
, 'AVAILABLE' as STATUS
, c.oid::bigint as RELATIVE_FNO
, 'YES' as AUTOEXTENSIBLE
, t.spcmaxsize as MAXBYTES
, round(t.spcmaxsize/b.setting) as MAXBLOCKS
, b.setting as INCREMENT_BY
, 'ONLINE' as ONLINE_STATUS
, n.nspname::text as schema_name
, c.relname::text as object_name
, case c.relkind
when 'r' then 'TABLE'
when 'i' then 'INDEX'
when 'I' then 'GLOBAL INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'COMPOSITE TYPE'
when 't' then 'TOAST'
when 'f' then 'FOREIGN TABLE'
end as object_type
from pg_class as c
join pg_namespace as n on c.relnamespace = n.oid
left join pg_tablespace as t on c.reltablespace = t.oid
join (select setting from pg_settings where name = 'block_size') as b on 1=1
join (select setting from pg_settings where name = 'data_directory') as d on 1=1
where pg_relation_filepath(c.oid) is not null;
CREATE OR REPLACE VIEW compat_tools.USER_DATA_FILES AS SELECT FILE_NAME, FILE_ID, tablespace_name, bytes, blocks, status, RELATIVE_FNO, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, increment_by, ONLINE_STATUS, object_name, object_type FROM compat_tools.DBA_DATA_FILES WHERE schema_name = current_schema();
CREATE OR REPLACE SYNONYM public.DBA_DATA_FILES for compat_tools.DBA_DATA_FILES;
CREATE OR REPLACE SYNONYM public.ALL_DATA_FILES for compat_tools.DBA_DATA_FILES;
CREATE OR REPLACE SYNONYM public.USER_DATA_FILES for compat_tools.USER_DATA_FILES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_DATA_FILES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$DATABASE
-- GV$DATABASE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$DATABASE', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.GV$DATABASE
AS
SELECT d.oid AS DBID
, d.datname::text AS NAME
, CASE WHEN t.spcname = 'pg_default' THEN (pg_stat_file('base/' || d.oid || '/PG_VERSION')).modification
ELSE (pg_stat_file('pg_tblspc/' || t.oid)).modification
END AS CREATED
, pg_stat_get_db_stat_reset_time(d.oid) AS RESETLOGS_TIME
, case when current_setting('archive_mode') = 'off' then 'NOARCHIVELOG'
when current_setting('archive_command') = '(disabled)' then 'MANUAL'
else 'ARCHIVELOG'
end as LOG_MODE
, case when pg_is_in_recovery() then 'STANDBY' else 'CURRENT' end as CONTROLFILE_TYPE
, (pg_stat_file('PG_VERSION')).modification AS CONTROLFILE_CREATED
, (pg_stat_file('PG_VERSION')).modification AS VERSION_TIME
, case when pg_is_in_recovery() then 'READ ONLY' else 'READ WRITE' end as OPEN_MODE -- READ WRITE, READ ONLY, READ ONLY WITH APPLY
, 'MAXIMUM PERFORMANCE' AS PROTECTION_MODE
, 'UNPROTECTED' AS PROTECTION_LEVEL
, 'DISABLE' AS REMOTE_ARCHIVE
, case when pg_is_in_recovery() then 'PHYSICAL STANDBY' else 'PRIMARY' end as DATABASE_ROLE
, (select case when max(setting) = 'on' then 'ENABLED' else 'DISABLED' end from pg_settings where name = 'enable_wal_shipping_compression') AS ARCHIVELOG_COMPRESSION
, 'NOT ALLOWED' AS SWITCHOVER_STATUS
, 'NO' AS SUPPLEMENTAL_LOG_DATA_MIN
, 'NO' AS SUPPLEMENTAL_LOG_DATA_PK
, 'NO' AS SUPPLEMENTAL_LOG_DATA_UI
, 'NO' AS FORCE_LOGGING
, trim(replace(regexp_substr(version(), 'on\s+([^,])+,'), 'on',''), ', ') AS PLATFORM_NAME
, 'NO' AS FLASHBACK_ON
, 'NO' AS SUPPLEMENTAL_LOG_DATA_FK
, 'NO' AS SUPPLEMENTAL_LOG_DATA_ALL
, d.datname AS DB_UNIQUE_NAME
, 'DISABLED' AS FS_FAILOVER_STATUS
FROM pg_database d
join pg_tablespace t on d.dattablespace = t.oid;
CREATE OR REPLACE VIEW compat_tools.V$DATABASE AS SELECT * FROM compat_tools.GV$DATABASE where name = current_database()::text;
CREATE OR REPLACE SYNONYM public.V$DATABASE for compat_tools.V$DATABASE;
CREATE OR REPLACE SYNONYM public.GV$DATABASE for compat_tools.GV$DATABASE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$DATABASE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$INSTANCE
-- GV$INSTANCE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$INSTANCE', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.GV$INSTANCE
AS
with t_ver as (select row_number() over () as id, ver_item from regexp_split_to_table(version(), '\s+') as ver_item)
SELECT 1 AS INST_ID
, 1 AS INSTANCE_NUMBER
, current_database() AS INSTANCE_NAME
, get_hostname() AS HOST_NAME
, ver_item AS VERSION
, pg_postmaster_start_time() AS STARTUP_TIME
, 'OPEN' AS STATUS
, 'NO' AS PARALLEL
, 1 AS THREAD#
, case when current_setting('archive_mode') = 'off' or current_setting('archive_command') = '(disabled)' then 'STOPPED'
else 'STARTED'
end as ARCHIVER
, 'ALLOWED' AS LOGINS
, 'NO' AS SHUTDOWN_PENDING
, 'ACTIVE' AS DATABASE_STATUS
, 'PRIMARY_INSTANCE' AS INSTANCE_ROLE
, 'NORMAL' AS ACTIVE_STATE
, 'NO' AS BLOCKED
, 'EE' AS EDITION
from (select ver_item from t_ver where id = (select id - 1 from t_ver where ver_item = 'build')
union all
select ver_item from t_ver where not exists (select 1 from t_ver where ver_item = 'build') and id = 2);
CREATE OR REPLACE VIEW compat_tools.V$INSTANCE AS SELECT instance_number, instance_name, host_name, version, startup_time, status, parallel, thread#, archiver, logins, shutdown_pending, database_status, instance_role, active_state, blocked, edition FROM compat_tools.GV$INSTANCE;
CREATE OR REPLACE SYNONYM public.V$INSTANCE for compat_tools.V$INSTANCE;
CREATE OR REPLACE SYNONYM public.GV$INSTANCE for compat_tools.GV$INSTANCE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$INSTANCE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$DATAFILE
-- GV$DATAFILE
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$DATAFILE', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$DATAFILE
AS
SELECT c.oid::bigint as FILE#
, o.ctime as CREATION_TIME
, c.reltablespace::bigint as TS#
, c.oid::bigint as RFILE#
, 'ONLINE' as status
, 'READ WRITE' as enabled
, pg_relation_size(c.oid) as BYTES
, round(pg_relation_size(c.oid)/b.setting) as BLOCKS
, b.setting as BLOCK_SIZE
, d.setting||'/'||pg_relation_filepath(c.oid) as NAME
, n.nspname::text as schema_name
, c.relname::text as object_name
, case c.relkind
when 'r' then 'TABLE'
when 'i' then 'INDEX'
when 'I' then 'GLOBAL INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'COMPOSITE TYPE'
when 't' then 'TOAST'
when 'f' then 'FOREIGN TABLE'
end as object_type
from pg_class as c
join pg_namespace as n on c.relnamespace = n.oid
left join pg_object as o on c.oid = o.object_oid
join (select setting from pg_settings where name = 'block_size') as b on 1=1
join (select setting from pg_settings where name = 'data_directory') as d on 1=1
where pg_relation_filepath(c.oid) is not null;
CREATE OR REPLACE VIEW compat_tools.GV$DATAFILE AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$DATAFILE t;
CREATE OR REPLACE SYNONYM public.V$DATAFILE for compat_tools.V$DATAFILE;
CREATE OR REPLACE SYNONYM public.GV$DATAFILE for compat_tools.GV$DATAFILE;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$DATAFILE: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$LOCK
-- GV$LOCK
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$LOCK', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$LOCK
AS
SELECT distinct l.sessionid as SID
, l.pid as PID
, l.locktype as TYPE
, coalesce(l.relation::regclass::text, l.virtualxid::text) as ID1
, coalesce(l.page::regclass::text, l.transactionid::text) as ID2
, l.mode as LMODE
, extract(epoch from now() - a.xact_start)::bigint as CTIME
from pg_locks as l
join pg_stat_activity as a on l.pid = a.pid and l.sessionid = a.sessionid;
CREATE OR REPLACE VIEW compat_tools.GV$LOCK AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$LOCK t;
CREATE OR REPLACE SYNONYM public.V$LOCK for compat_tools.V$LOCK;
CREATE OR REPLACE SYNONYM public.GV$LOCK for compat_tools.GV$LOCK;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$LOCK: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SYSSTAT
-- GV$SYSSTAT
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SYSSTAT', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$SYSSTAT
AS
SELECT STAT_ID as STATISTIC#
, stat_name as NAME
, 1 as CLASS
, VALUE
, STAT_ID
FROM GS_INSTANCE_TIME
UNION ALL
SELECT 200 as STATISTIC#, 'BGWRITER checkpoints_timed', 2 as CLASS, checkpoints_timed as value, 100 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 201 as STATISTIC#, 'BGWRITER checkpoints_req', 2 as CLASS, checkpoints_req as value, 101 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 202 as STATISTIC#, 'BGWRITER checkpoint_write_time', 2 as CLASS, checkpoint_write_time as value, 102 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 203 as STATISTIC#, 'BGWRITER checkpoint_sync_time', 2 as CLASS, checkpoint_sync_time as value, 103 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 204 as STATISTIC#, 'BGWRITER buffers_checkpoint', 2 as CLASS, buffers_checkpoint as value, 104 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 205 as STATISTIC#, 'BGWRITER buffers_clean', 2 as CLASS, buffers_clean as value, 105 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 206 as STATISTIC#, 'BGWRITER maxwritten_clean', 2 as CLASS, maxwritten_clean as value, 106 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 207 as STATISTIC#, 'BGWRITER buffers_backend', 2 as CLASS, buffers_backend as value, 107 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 208 as STATISTIC#, 'BGWRITER buffers_backend_fsync', 2 as CLASS, buffers_backend_fsync as value, 108 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT 209 as STATISTIC#, 'BGWRITER buffers_alloc', 2 as CLASS, buffers_alloc as value, 109 as STAT_ID FROM PG_STAT_BGWRITER
UNION ALL
SELECT STAT_ID, NAME, 3 as CLASS, VALUE, STAT_ID
FROM (with t as (SELECT sum(numbackends) as numbackends
, sum(xact_commit) as xact_commit
, sum(xact_rollback) as xact_rollback
, sum(blks_read) as blks_read
, sum(blks_hit) as blks_hit
, sum(tup_returned) as tup_returned
, sum(tup_fetched) as tup_fetched
, sum(tup_inserted) as tup_inserted
, sum(tup_updated) as tup_updated
, sum(tup_deleted) as tup_deleted
, sum(conflicts) as conflicts
, sum(temp_files) as temp_files
, sum(temp_bytes) as temp_bytes
, sum(deadlocks) as deadlocks
, sum(blk_read_time) as blk_read_time
, sum(blk_write_time) as blk_write_time
FROM PG_STAT_DATABASE)
SELECT 300 as STAT_ID, 'DB numbackends' as NAME, numbackends as VALUE FROM T
UNION ALL SELECT 301 as STAT_ID, 'DB xact_commit', xact_commit FROM T
UNION ALL SELECT 302 as STAT_ID, 'DB xact_rollback', xact_rollback FROM T
UNION ALL SELECT 303 as STAT_ID, 'DB blks_read', blks_read FROM T
UNION ALL SELECT 304 as STAT_ID, 'DB blks_hit', blks_hit FROM T
UNION ALL SELECT 305 as STAT_ID, 'DB tup_returned', tup_returned FROM T
UNION ALL SELECT 306 as STAT_ID, 'DB tup_fetched', tup_fetched FROM T
UNION ALL SELECT 307 as STAT_ID, 'DB tup_inserted', tup_inserted FROM T
UNION ALL SELECT 308 as STAT_ID, 'DB tup_updated', tup_updated FROM T
UNION ALL SELECT 309 as STAT_ID, 'DB tup_deleted', tup_deleted FROM T
UNION ALL SELECT 310 as STAT_ID, 'DB conflicts', conflicts FROM T
UNION ALL SELECT 311 as STAT_ID, 'DB temp_files', temp_files FROM T
UNION ALL SELECT 312 as STAT_ID, 'DB temp_bytes', temp_bytes FROM T
UNION ALL SELECT 313 as STAT_ID, 'DB deadlocks', deadlocks FROM T
UNION ALL SELECT 314 as STAT_ID, 'DB blk_read_time', blk_read_time FROM T
UNION ALL SELECT 315 as STAT_ID, 'DB blk_write_time', blk_write_time FROM T
)
UNION ALL
SELECT STAT_ID, NAME, 3 as CLASS, VALUE, STAT_ID
FROM (with t as (SELECT sum(confl_tablespace) as confl_tablespace
, sum(confl_lock) as confl_lock
, sum(confl_snapshot) as confl_snapshot
, sum(confl_bufferpin) as confl_bufferpin
, sum(confl_deadlock) as confl_deadlock
FROM PG_STAT_DATABASE_CONFLICTS)
SELECT 316 as STAT_ID, 'DB confl_tablespace' as NAME, confl_tablespace as VALUE FROM T
UNION ALL SELECT 317 as STAT_ID, 'DB confl_lock', confl_lock FROM T
UNION ALL SELECT 318 as STAT_ID, 'DB confl_snapshot', confl_snapshot FROM T
UNION ALL SELECT 319 as STAT_ID, 'DB confl_bufferpin', confl_bufferpin FROM T
UNION ALL SELECT 320 as STAT_ID, 'DB confl_deadlock', confl_deadlock FROM T
)
UNION ALL
SELECT 400 + ID AS STATISTIC#, Name, 4 as CLASS, value, 400 + id STAT_ID
FROM GS_OS_RUN_INFO;
CREATE OR REPLACE VIEW compat_tools.GV$SYSSTAT AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$SYSSTAT t;
CREATE OR REPLACE SYNONYM public.V$SYSSTAT for compat_tools.V$SYSSTAT;
CREATE OR REPLACE SYNONYM public.GV$SYSSTAT for compat_tools.GV$SYSSTAT;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SYSSTAT: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$SESSTAT
-- GV$SESSTAT
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$SESSTAT', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$SESSTAT
AS
SELECT sessid as SID, statid AS STATISTIC#, statname as Name, value
FROM GS_SESSION_STAT
UNION ALL
SELECT sessid as SID, stat_id AS STATISTIC#, stat_name as Name, value
FROM GS_SESSION_TIME;
CREATE OR REPLACE VIEW compat_tools.GV$SESSTAT AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$SESSTAT t;
CREATE OR REPLACE SYNONYM public.V$SESSTAT for compat_tools.V$SESSTAT;
CREATE OR REPLACE SYNONYM public.GV$SESSTAT for compat_tools.GV$SESSTAT;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$SESSTAT: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$NLS_PARAMETERS
-- GV$NLS_PARAMETERS
-- NLS_DATABASE_PARAMETERS
-- NLS_INSTANCE_PARAMETERS
-- NLS_SESSION_PARAMETERS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$NLS_PARAMETERS', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$NLS_PARAMETERS
AS
SELECT NAME AS PARAMETER
, SETTING AS VALUE
, SHORT_DESC AS DESCRIPTION
FROM PG_SETTINGS
WHERE NAME IN ( 'lc_messages'
, 'lc_monetary'
, 'lc_numeric'
, 'lc_time'
, 'nls_timestamp_format'
, 'lc_collate'
, 'lc_ctype')
UNION ALL
SELECT 'NLS_CHARACTERSET'
, getdatabaseencoding()
, 'Database/Server encoding' AS DESCRIPTION;
CREATE OR REPLACE VIEW compat_tools.GV$NLS_PARAMETERS AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$NLS_PARAMETERS t;
CREATE OR REPLACE VIEW compat_tools.NLS_DATABASE_PARAMETERS AS SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t;
CREATE OR REPLACE VIEW compat_tools.NLS_INSTANCE_PARAMETERS AS SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t;
CREATE OR REPLACE VIEW compat_tools.NLS_SESSION_PARAMETERS
AS
SELECT t.* FROM compat_tools.V$NLS_PARAMETERS t where PARAMETER != 'NLS_CHARACTERSET'
UNION ALL
SELECT 'NLS_CHARACTERSET'
, pg_client_encoding()
, 'Client encoding' AS DESCRIPTION;
CREATE OR REPLACE SYNONYM public.V$NLS_PARAMETERS for compat_tools.V$NLS_PARAMETERS;
CREATE OR REPLACE SYNONYM public.GV$NLS_PARAMETERS for compat_tools.GV$NLS_PARAMETERS;
CREATE OR REPLACE SYNONYM public.NLS_DATABASE_PARAMETERS for compat_tools.NLS_DATABASE_PARAMETERS;
CREATE OR REPLACE SYNONYM public.NLS_INSTANCE_PARAMETERS for compat_tools.NLS_INSTANCE_PARAMETERS;
CREATE OR REPLACE SYNONYM public.NLS_SESSION_PARAMETERS for compat_tools.NLS_SESSION_PARAMETERS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$NLS_PARAMETERS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$NLS_VALID_VALUES
-- GV$NLS_PARAMETERS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$NLS_VALID_VALUES', '1.0')
then
CREATE OR REPLACE VIEW compat_tools.V$NLS_VALID_VALUES
AS
SELECT COLLNAME AS NAME
, PG_ENCODING_TO_CHAR(COLLENCODING) AS ENCODING
, COLLCOLLATE AS COLLATE
, COLLCTYPE AS CTYPE
FROM PG_COLLATION;
CREATE OR REPLACE VIEW compat_tools.GV$NLS_VALID_VALUES AS SELECT 1 as INST_ID, t.* FROM compat_tools.V$NLS_VALID_VALUES t;
CREATE OR REPLACE SYNONYM public.V$NLS_VALID_VALUES for compat_tools.V$NLS_VALID_VALUES;
CREATE OR REPLACE SYNONYM public.GV$NLS_VALID_VALUES for compat_tools.GV$NLS_VALID_VALUES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$NLS_VALID_VALUES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_JOBS
-- ALL_JOBS
-- USER_JOBS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_JOBS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_JOBS
AS
SELECT j.job_id as JOB
, j.log_user as LOG_USER
, j.priv_user as PRIV_USER
, j.nspname as SCHEMA_USER
, j.start_date
, j.last_start_date as LAST_DATE
, j.last_suc_date as LAST_SEC
, j.last_end_date
, j.this_run_date as THIS_DATE
, j.next_run_date as NEXT_DATE
, extract(second from j.last_end_date - j.last_start_date) as TOTAL_TIME
, CASE j.job_status WHEN 'd' then 'Y' else 'N' end as BROKEN
, j.interval
, j.failure_count as FAILURES
, p.what
, j.node_name as INSTANCE
, case j.job_status when 'r' then 'RUNNING' when 'f' then 'FAILED' when 'd' then 'DISABLE' else 'SUCCESS' end as job_status
, j.dbname
FROM pg_job as j
JOIN pg_job_proc as p ON j.job_id = p.job_id;
CREATE OR REPLACE VIEW compat_tools.ALL_JOBS AS SELECT t.* FROM compat_tools.DBA_JOBS t;
CREATE OR REPLACE VIEW compat_tools.USER_JOBS
AS
SELECT JOB
, LOG_USER
, PRIV_USER
, START_DATE
, LAST_DATE
, LAST_SEC
, last_end_date
, THIS_DATE
, NEXT_DATE
, TOTAL_TIME
, BROKEN
, interval
, FAILURES
, what
, INSTANCE
, job_status
, dbname
FROM compat_tools.DBA_JOBS t
WHERE SCHEMA_USER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_JOBS for compat_tools.DBA_JOBS;
CREATE OR REPLACE SYNONYM public.ALL_JOBS for compat_tools.ALL_JOBS;
CREATE OR REPLACE SYNONYM public.USER_JOBS for compat_tools.USER_JOBS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_JOBS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_JOBS_RUNNING
-- ALL_JOBS_RUNNING
-- USER_JOBS_RUNNING
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_JOBS_RUNNING', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_JOBS_RUNNING
AS
SELECT current_postgres_pid as SID
, job_id as JOB
, failure_count as FAILURES
, last_end_date as LAST_DATE
, last_suc_date as LAST_SEC
, this_run_date as THIS_DATE
, last_suc_date as THIS_SEC
, node_name as INSTANCE
FROM pg_job
WHERE job_status = 'r';
CREATE OR REPLACE VIEW compat_tools.ALL_JOBS_RUNNING AS SELECT t.* FROM compat_tools.DBA_JOBS_RUNNING t;
CREATE OR REPLACE VIEW compat_tools.USER_JOBS_RUNNING
AS
SELECT current_postgres_pid as SID
, job_id as JOB
, failure_count as FAILURES
, last_end_date as LAST_DATE
, last_suc_date as LAST_SEC
, this_run_date as THIS_DATE
, last_suc_date as THIS_SEC
, node_name as INSTANCE
FROM pg_job
WHERE job_status = 'r'
AND nspname = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_JOBS_RUNNING for compat_tools.DBA_JOBS_RUNNING;
CREATE OR REPLACE SYNONYM public.ALL_JOBS_RUNNING for compat_tools.ALL_JOBS_RUNNING;
CREATE OR REPLACE SYNONYM public.USER_JOBS_RUNNING for compat_tools.USER_JOBS_RUNNING;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_JOBS_RUNNING: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_MVIEWS
-- ALL_MVIEWS
-- USER_MVIEWS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEWS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_MVIEWS
AS
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
, case when pc.relname::text = lower(pc.relname::text) then compat_tools.f_upper_name(pc.relname::text) else pc.relname::text end AS MVIEW_NAME
, case when pc.relname::text = lower(pc.relname::text) then compat_tools.f_upper_name(pc.relname::text) else pc.relname::text end AS CONTAINER_NAME
, pg_get_viewdef(mv.matviewid) as QUERY
, lengthb(pg_get_viewdef(mv.matviewid)) as QUERY_LEN
, 'N' as UPDATABLE
, 'DEMAND' as REFRESH_MODE
, case when ivm then 'FAST' else 'COMPLETE' end as REFRESH_METHOD
, 'IMMEDIATE' as BUILD_MODE
, case when ivm then 'DML' else 'NO' end as FAST_REFRESHABLE
, case when ivm then 'FAST' else 'COMPLETE' end as LAST_REFRESH_TYPE
, refreshtime as LAST_REFRESH_DATE
, case when needrefresh then 'STALE' else 'FRESH' end as STALENESS
FROM gs_matview as mv
JOIN pg_class as pc on mv.matviewid = pc.oid
JOIN pg_namespace as nsp on pc.relnamespace = nsp.oid;
CREATE OR REPLACE VIEW compat_tools.ALL_MVIEWS AS SELECT t.* FROM compat_tools.DBA_MVIEWS t;
CREATE OR REPLACE VIEW compat_tools.USER_MVIEWS
AS
SELECT MVIEW_NAME
, CONTAINER_NAME
, QUERY
, QUERY_LEN
, UPDATABLE
, REFRESH_MODE
, REFRESH_METHOD
, BUILD_MODE
, FAST_REFRESHABLE
, LAST_REFRESH_TYPE
, LAST_REFRESH_DATE
, STALENESS
FROM compat_tools.DBA_MVIEWS
WHERE owner = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_MVIEWS for compat_tools.DBA_MVIEWS;
CREATE OR REPLACE SYNONYM public.ALL_MVIEWS for compat_tools.ALL_MVIEWS;
CREATE OR REPLACE SYNONYM public.USER_MVIEWS for compat_tools.USER_MVIEWS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_MVIEWS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_MVIEW_LOGS
-- ALL_MVIEW_LOGS
-- USER_MVIEW_LOGS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEW_LOGS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_MVIEW_LOGS
AS
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS LOG_OWNER
, case when rel.relname::text = lower(rel.relname::text) then compat_tools.f_upper_name(rel.relname::text) else rel.relname::text end AS MASTER
, case when lc.relname::text = lower(lc.relname::text) then compat_tools.f_upper_name(lc.relname::text) else lc.relname::text end AS LOG_TABLE
FROM gs_matview_dependency as mvl
JOIN pg_class as lc on mvl.mlogid = lc.oid
JOIN pg_class as rel on mvl.relid = rel.oid
JOIN pg_namespace as nsp on lc.relnamespace = nsp.oid;
CREATE OR REPLACE VIEW compat_tools.ALL_MVIEW_LOGS AS SELECT t.* FROM compat_tools.DBA_MVIEW_LOGS t;
CREATE OR REPLACE VIEW compat_tools.USER_MVIEW_LOGS
AS
SELECT MASTER
, LOG_TABLE
FROM compat_tools.DBA_MVIEW_LOGS
WHERE LOG_OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_MVIEW_LOGS for compat_tools.DBA_MVIEW_LOGS;
CREATE OR REPLACE SYNONYM public.ALL_MVIEW_LOGS for compat_tools.ALL_MVIEW_LOGS;
CREATE OR REPLACE SYNONYM public.USER_MVIEW_LOGS for compat_tools.USER_MVIEW_LOGS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_MVIEW_LOGS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_MVIEW_COMMENTS
-- ALL_MVIEW_COMMENTS
-- USER_MVIEW_COMMENTS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_MVIEW_COMMENTS', '2.0')
then
CREATE OR REPLACE VIEW compat_tools.DBA_MVIEW_COMMENTS
AS
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS OWNER
, case when rel.relname::text = lower(rel.relname::text) then compat_tools.f_upper_name(rel.relname::text) else rel.relname::text end AS MVIEW_NAME
, des.description as COMMENTS
FROM gs_matview as mv
JOIN pg_class as rel on mv.matviewid = rel.oid
JOIN pg_namespace as nsp on rel.relnamespace = nsp.oid
LEFT JOIN pg_description as des on mv.matviewid = des.objoid;
CREATE OR REPLACE VIEW compat_tools.ALL_MVIEW_COMMENTS AS SELECT t.* FROM compat_tools.DBA_MVIEW_COMMENTS t;
CREATE OR REPLACE VIEW compat_tools.USER_MVIEW_COMMENTS
AS
SELECT MVIEW_NAME
, COMMENTS
FROM compat_tools.DBA_MVIEW_COMMENTS
WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE SYNONYM public.DBA_MVIEW_COMMENTS for compat_tools.DBA_MVIEW_COMMENTS;
CREATE OR REPLACE SYNONYM public.ALL_MVIEW_COMMENTS for compat_tools.ALL_MVIEW_COMMENTS;
CREATE OR REPLACE SYNONYM public.USER_MVIEW_COMMENTS for compat_tools.USER_MVIEW_COMMENTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_MVIEW_COMMENTS: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_DIRECTORIES
-- ALL_DIRECTORIES
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'DBA_DIRECTORIES', '1.0')
then
create view compat_tools.DBA_DIRECTORIES as
select u.usename as OWNER ,d.dirname DIRECTORY_NAME,d.dirpath DIRECTORY_PATH
from pg_catalog.pg_directory d ,pg_catalog.pg_user u
where d."owner" =u.usesysid (+);
CREATE OR REPLACE VIEW compat_tools.ALL_DIRECTORIES AS SELECT t.* FROM compat_tools.DBA_DIRECTORIES t;
CREATE OR REPLACE SYNONYM public.DBA_DIRECTORIES for compat_tools.DBA_DIRECTORIES;
CREATE OR REPLACE SYNONYM public.ALL_DIRECTORIES for compat_tools.ALL_DIRECTORIES;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_DIRECTORIES: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- DBA_ARGUMENTS
-- ALL_ARGUMENTS
-- USER_ARGUMENTS
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW','DBA_ARGUMENTS','1.0')
then
if f_version_compare((regexp_split_to_array(substr(version(), instr(version(), '(') + 1), '\s+'))[2], '2.0.9') = 1 then
create view compat_tools.DBA_ARGUMENTS as
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS owner--
, case when f.proname::text = lower(f.proname::text) then compat_tools.f_upper_name(f.proname::text) else f.proname::text end AS object_name--
, CASE WHEN (f.propackageid <> (0)::oid) THEN (case when pkg.pkgname::text = lower(pkg.pkgname::text) then compat_tools.f_upper_name(pkg.pkgname::text) else pkg.pkgname::text end) ELSE NULL::text END AS package_name--
, a.object_id
, CASE WHEN ((f.propackage = true) AND ((SELECT count(pg_proc.proname) AS count FROM pg_proc WHERE ((pg_proc.proname = f.proname) AND (pg_proc.propackageid = f.propackageid))) > 1)) THEN (dense_rank() OVER (PARTITION BY f.propackageid, f.proname ORDER BY f.oid))::text ELSE NULL::text END AS overload
, a.subprogram_id
, case when a.argument_name::text = lower(a.argument_name::text) then compat_tools.f_upper_name(a.argument_name::text) else a.argument_name::text end AS argument_name
, a."position"
, CASE WHEN (f.prokind = 'f'::"char") THEN (a."position" + (1)::numeric) ELSE a."position" END AS sequence
, (0)::numeric AS data_level
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) WHEN typ.typtype='c' AND ((SELECT COUNT(1) AS count FROM pg_proc WHERE pg_proc.proname = typ.typname ) > 0) THEN 'OBJECT'::text WHEN typ.typtype = 'o' THEN 'TABLE'::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS data_type--
, CASE WHEN (ARRAY[((a."position" - (1)::numeric))::smallint] <@ (string_to_array((f.prodefaultargpos)::text, ' '))::smallint[]) THEN 'Y'::text ELSE 'N'::text END AS defaulted
, NULL::text AS default_value
, NULL::numeric AS default_length
, CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END AS in_out
, NULL::numeric as data_length
, information_schema._pg_numeric_precision(a.data_type_oid, typ.typtypmod)::numeric as data_precision
, information_schema._pg_numeric_scale(a.data_type_oid, typ.typtypmod)::numeric as data_scale
, CASE WHEN ((((((((((typ.typname)::text)::text = 'int1'::text) OR (((typ.typname)::text)::text = 'int2'::text)) OR (((typ.typname)::text)::text = 'int4'::text)) OR (((typ.typname)::text)::text = 'int8'::text)) OR (((typ.typname)::text)::text = 'int16'::text)) OR (((typ.typname)::text)::text = 'numeric'::text)) OR (((typ.typname)::text)::text = 'float4'::text)) OR (((typ.typname)::text)::text = 'float8'::text)) THEN (10)::numeric ELSE NULL::numeric END AS radix
, NULL::text AS character_set_name
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE(case when nsp1.nspname::text = lower(nsp1.nspname::text) then compat_tools.f_upper_name(nsp1.nspname::text) else nsp1.nspname::text end) END AS type_owner--
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS type_name--
, NULL::text AS type_subname
, NULL::text AS type_link
, CASE WHEN (((cla.relkind = 'r'::"char") OR (cla.relkind = 'f'::"char")) OR (cla.relkind = 't'::"char")) THEN 'TABLE'::text WHEN (cla.relkind = 'v'::"char") THEN 'VIEW'::text ELSE NULL::text END AS type_object_type
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) ELSE NULL::text END AS pls_type--
, NULL::numeric AS char_length
, CASE WHEN ((((((typ.typname)::text)::text = 'char'::text) OR (((typ.typname)::text)::text = 'bpchar'::text)) OR (((typ.typname)::text)::text = 'varchar'::text)) OR (((typ.typname)::text)::text = 'nvarchar2'::text)) THEN 'B'::text ELSE NULL::text END AS char_used
, (1)::text AS origin_con_id
FROM (
SELECT object_id,subprogram_id,argument_name,"position",
CASE WHEN (typ.typbasetype <> 0) THEN typ.typbasetype ELSE a.data_type_oid END AS data_type_oid,
in_out_abbr
FROM
(SELECT f.oid AS object_id
, CASE WHEN (f.propackageid <> (0)::oid) THEN (dense_rank() OVER (PARTITION BY f.propackageid ORDER BY f.oid))::numeric ELSE (1)::numeric END AS subprogram_id
, (unnest(f.proargnames))::text AS argument_name
, CASE WHEN (array_length(f.proargnames, 1) > 1) THEN (generate_series(1, array_length(f.proargnames, 1)))::numeric ELSE (1)::numeric END AS "position"
, CASE WHEN (f.proallargtypes IS NOT NULL) THEN unnest(f.proallargtypes) ELSE unnest(f.proargtypes) END AS data_type_oid
, CASE WHEN (f.proargmodes IS NOT NULL) THEN (unnest(f.proargmodes))::text ELSE 'i'::text END AS in_out_abbr
FROM pg_proc f
UNION ALL
SELECT f.oid AS object_id
, CASE WHEN (f.propackageid <> (0)::oid) THEN (dense_rank() OVER (PARTITION BY f.propackageid ORDER BY f.oid))::numeric ELSE (1)::numeric END AS subprogram_id
, NULL::text AS argument_name
, (0)::numeric AS "position"
, f.prorettype AS data_type_oid
, 'o'::text AS in_out_abbr
FROM pg_proc f
WHERE f.prokind = 'f'::"char") a
LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
) a
LEFT JOIN pg_proc f ON a.object_id = f.oid
LEFT JOIN pg_authid n ON f.proowner = n.oid
LEFT JOIN gs_package pkg ON pkg.oid = f.propackageid
LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
LEFT JOIN pg_namespace nsp1 ON typ.typnamespace = nsp1.oid
LEFT JOIN pg_class cla ON cla.reltype = typ.oid
LEFT JOIN pg_namespace nsp ON f.pronamespace = nsp.oid
WHERE ((CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END IS NOT NULL)
AND (pg_has_role(((n.rolname)::text)::name, 'USAGE'::text) OR has_function_privilege(a.object_id, 'EXECUTE'::text)))
ORDER BY a.object_id, a."position";
else
create view compat_tools.DBA_ARGUMENTS as
SELECT case when nsp.nspname::text = lower(nsp.nspname::text) then compat_tools.f_upper_name(nsp.nspname::text) else nsp.nspname::text end AS owner--
, case when f.proname::text = lower(f.proname::text) then compat_tools.f_upper_name(f.proname::text) else f.proname::text end AS object_name--
, NULL::text AS package_name
, a.object_id
, CASE WHEN ((f.propackage = true) AND ((SELECT count(pg_proc.proname) AS count FROM pg_proc WHERE ((pg_proc.proname = f.proname))) > 1)) THEN (dense_rank() OVER (PARTITION BY f.proname ORDER BY f.oid))::text ELSE NULL::text END AS overload
, a.subprogram_id
, case when a.argument_name::text = lower(a.argument_name::text) then compat_tools.f_upper_name(a.argument_name::text) else a.argument_name::text end AS argument_name
, a."position"
, CASE WHEN (f.prokind = 'f'::"char") THEN (a."position" + (1)::numeric) ELSE a."position" END AS sequence
, (0)::numeric AS data_level
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) WHEN typ.typtype='c' AND ((SELECT COUNT(1) AS count FROM pg_proc WHERE pg_proc.proname = typ.typname ) > 0) THEN 'OBJECT'::text WHEN typ.typtype = 'o' THEN 'TABLE'::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS data_type--
, CASE WHEN (ARRAY[((a."position" - (1)::numeric))::smallint] <@ (string_to_array((f.prodefaultargpos)::text, ' '))::smallint[]) THEN 'Y'::text ELSE 'N'::text END AS defaulted
, NULL::text AS default_value
, NULL::numeric AS default_length
, CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END AS in_out
, NULL::numeric as data_length
, information_schema._pg_numeric_precision(a.data_type_oid, typ.typtypmod)::numeric as data_precision
, information_schema._pg_numeric_scale(a.data_type_oid, typ.typtypmod)::numeric as data_scale
, CASE WHEN ((((((((((typ.typname)::text)::text = 'int1'::text) OR (((typ.typname)::text)::text = 'int2'::text)) OR (((typ.typname)::text)::text = 'int4'::text)) OR (((typ.typname)::text)::text = 'int8'::text)) OR (((typ.typname)::text)::text = 'int16'::text)) OR (((typ.typname)::text)::text = 'numeric'::text)) OR (((typ.typname)::text)::text = 'float4'::text)) OR (((typ.typname)::text)::text = 'float8'::text)) THEN (10)::numeric ELSE NULL::numeric END AS radix
, NULL::text AS character_set_name
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE(case when nsp1.nspname::text = lower(nsp1.nspname::text) then compat_tools.f_upper_name(nsp1.nspname::text) else nsp1.nspname::text end) END AS type_owner--
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN NULL::text ELSE (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) END AS type_name--
, NULL::text AS type_subname
, NULL::text AS type_link
, CASE WHEN (((cla.relkind = 'r'::"char") OR (cla.relkind = 'f'::"char")) OR (cla.relkind = 't'::"char")) THEN 'TABLE'::text WHEN (cla.relkind = 'v'::"char") THEN 'VIEW'::text ELSE NULL::text END AS type_object_type
, CASE WHEN (((a.data_type_oid <= (16384)::oid) AND (typ.typtype = 'b'::"char")) AND (typ.typcategory <> 'A'::"char")) THEN (case when typ.typname::text = lower(typ.typname::text) then compat_tools.f_upper_name(typ.typname::text) else typ.typname::text end) ELSE NULL::text END AS pls_type--
, NULL::numeric AS char_length
, CASE WHEN ((((((typ.typname)::text)::text = 'char'::text) OR (((typ.typname)::text)::text = 'bpchar'::text)) OR (((typ.typname)::text)::text = 'varchar'::text)) OR (((typ.typname)::text)::text = 'nvarchar2'::text)) THEN 'B'::text ELSE NULL::text END AS char_used
, (1)::text AS origin_con_id
FROM (
SELECT object_id,subprogram_id,argument_name,"position",
CASE WHEN (typ.typbasetype <> 0) THEN typ.typbasetype ELSE a.data_type_oid END AS data_type_oid,
in_out_abbr
FROM
(SELECT f.oid AS object_id
, (1)::numeric AS subprogram_id
, (unnest(f.proargnames))::text AS argument_name
, CASE WHEN (array_length(f.proargnames, 1) > 1) THEN (generate_series(1, array_length(f.proargnames, 1)))::numeric ELSE (1)::numeric END AS "position"
, CASE WHEN (f.proallargtypes IS NOT NULL) THEN unnest(f.proallargtypes) ELSE unnest(f.proargtypes) END AS data_type_oid
, CASE WHEN (f.proargmodes IS NOT NULL) THEN (unnest(f.proargmodes))::text ELSE 'i'::text END AS in_out_abbr
FROM pg_proc f
UNION ALL
SELECT f.oid AS object_id
, (1)::numeric AS subprogram_id
, NULL::text AS argument_name
, (0)::numeric AS "position"
, f.prorettype AS data_type_oid
, 'o'::text AS in_out_abbr
FROM pg_proc f
WHERE f.prokind = 'f'::"char") a
LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
) a
LEFT JOIN pg_proc f ON a.object_id = f.oid
LEFT JOIN pg_authid n ON f.proowner = n.oid
LEFT JOIN pg_type typ ON typ.oid = a.data_type_oid
LEFT JOIN pg_namespace nsp1 ON typ.typnamespace = nsp1.oid
LEFT JOIN pg_class cla ON cla.reltype = typ.oid
LEFT JOIN pg_namespace nsp ON f.pronamespace = nsp.oid
WHERE ((CASE WHEN ((a.in_out_abbr)::text = 'i'::text) THEN 'IN'::text WHEN ((a.in_out_abbr)::text = 'o'::text) THEN 'OUT'::text WHEN ((a.in_out_abbr)::text = 'b'::text) THEN 'IN/OUT'::text ELSE 'VARIADIC'::text END IS NOT NULL)
AND (pg_has_role(((n.rolname)::text)::name, 'USAGE'::text) OR has_function_privilege(a.object_id, 'EXECUTE'::text)))
ORDER BY a.object_id, a."position";
end if;
CREATE OR REPLACE VIEW compat_tools.USER_ARGUMENTS AS SELECT object_name,package_name,object_id,overload,subprogram_id,argument_name,position,sequence,data_level,data_type,defaulted,default_value,default_length,in_out,data_length,data_precision,data_scale,radix,character_set_name,type_owner,type_name,type_subname,type_link,type_object_type,pls_type,char_length,char_used,origin_con_id
FROM compat_tools.DBA_ARGUMENTS WHERE OWNER = (case when current_schema()::text = lower(current_schema()::text) then compat_tools.f_upper_name(current_schema()::text) else current_schema()::text end);
CREATE OR REPLACE VIEW compat_tools.ALL_ARGUMENTS AS SELECT t.* FROM compat_tools.DBA_ARGUMENTS t;
CREATE OR REPLACE SYNONYM public.DBA_ARGUMENTS for compat_tools.DBA_ARGUMENTS;
CREATE OR REPLACE SYNONYM public.ALL_ARGUMENTS for compat_tools.ALL_ARGUMENTS;
CREATE OR REPLACE SYNONYM public.USER_ARGUMENTS for compat_tools.USER_ARGUMENTS;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in DBA_ARGUMENTS: %',SQLERRM;
rollback;
end;
-- =========================================================================
-- V$STATNAME
-- GV$STATNAME
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$STATNAME', '1.0')
then
create view compat_tools.V$STATNAME as
SELECT DISTINCT statid STATISTIC# ,
statname NAME ,
statid STAT_ID ,
statname DISPLAY_NAME
FROM pv_session_stat()
ORDER BY 1;
create view compat_tools.GV$STATNAME as select 1 as inst_id, t.* from compat_tools.V$STATNAME as t;
create synonym public.V$STATNAME for compat_tools.V$STATNAME;
CREATE OR REPLACE SYNONYM public.GV$STATNAME for compat_tools.GV$STATNAME;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$STATNAME: %', SQLERRM;
rollback;
end;
-- =========================================================================
-- V$MYSTAT
-- GV$MYSTAT
-- =========================================================================
begin
if compat_tools.drop_compat_object('VIEW', 'V$MYSTAT', '1.0')
then
create or replace view compat_tools.V$MYSTAT as
SELECT pg_backend_pid()::text sid
,statid STATISTIC#
,value
,1::int con_id
FROM pv_session_stat()
WHERE regexp_substr(SESSID,'(?<=\.)\d+')=pg_backend_pid()::text;
create view compat_tools.GV$MYSTAT as select 1 as inst_id, t.* from compat_tools.V$MYSTAT as t;
create synonym public.V$MYSTAT for compat_tools.V$MYSTAT;
CREATE OR REPLACE SYNONYM public.GV$MYSTAT for compat_tools.GV$MYSTAT;
end if;
commit;
exception
when others then
RAISE WARNING 'Error in V$MYSTAT: %', SQLERRM;
rollback;
end;
end;
$VIEW_CREATION$ language plpgsql;
-- =============================================================================
-- 根据 public synonym 创建 sys synonym
-- =============================================================================
do $SYS_SYNONYM$
declare
l_sym_record record;
l_cnt bigint;
begin
if current_setting('application_name') != 'checkMe'
then
-- 创建 sys schema
select count(*) into l_cnt
from pg_catalog.pg_namespace
where nspname = 'sys';
if l_cnt = 0
then
create schema sys;
end if;
-- 创建 sys synonym
for l_sym_record in select synname, synobjname
from pg_catalog.pg_synonym
where synobjschema = 'compat_tools'
and synnamespace = (select oid
from pg_catalog.pg_namespace
where nspname = 'public')
and not exists (select synname
from pg_catalog.pg_synonym
where synnamespace = (select oid
from pg_catalog.pg_namespace
where nspname = 'sys'))
loop
-- raise notice '%', 'create synonym sys.'||l_sym_record.synname||' for compat_tools.'||l_sym_record.synobjname;
execute 'create synonym sys.'||l_sym_record.synname||' for compat_tools.'||l_sym_record.synobjname;
end loop;
-- 创建 sys.dual synonym
for l_sym_record in select 1
from pg_catalog.pg_class
where relname = 'sys_dummy'
and relnamespace = (select oid
from pg_catalog.pg_namespace
where nspname = 'pg_catalog')
and not exists (select 1
from pg_catalog.pg_synonym
where synname = 'dual'
and synnamespace = (select oid
from pg_catalog.pg_namespace
where nspname = 'sys'))
loop
-- raise notice '%', 'create synonym sys.dual for pg_catalog.sys_dummy';
create synonym sys.dual for pg_catalog.sys_dummy;
end loop;
end if;
exception
when others then
RAISE WARNING 'Error in sys synonym creation: %', SQLERRM;
rollback;
end;
$SYS_SYNONYM$ language plpgsql;
-- Show result & Exit
do $RESULT_SUMMARY$
declare
l_app_name text := current_setting('application_name');
begin
set client_min_messages='notice';
if l_app_name not in ('runMe', 'checkMe')
then
raise notice '';
raise notice '-- =====================================================================';
raise notice '-- Compat Object List: ';
raise notice '-- =====================================================================';
for l_app_name in select ' |' || pad_char
|| rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
|| rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
|| rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
|| rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
|| rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
from (select greatest(max(length(object_type)), 5) max_object_type
, greatest(max(length(object_name)), 6) max_object_name
, greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
, greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
, greatest(max(length(operation)), 9) max_operation
from temp_result) l
join (select 'type' as object_type
, 'name' as object_name
, 'version' as object_version
, 'language' as object_language
, 'operation' as operation
, ' ' as pad_char
union all
select '-' as object_type
, '-' as object_name
, '-' as object_version
, '-' as object_language
, '-' as operation
, '-' as pad_char
union all
select object_type, object_name
, case when local_version = script_version then local_version else local_version || ' => ' || script_version end as object_version
, case when local_language = script_language then local_language else local_language || ' => ' || script_language end as object_language
, operation, ' ' from temp_result) r on 1 = 1
loop
raise notice '%', l_app_name;
end loop;
end if;
end;
$RESULT_SUMMARY$ language plpgsql;
reset behavior_compat_options;
\q
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。