【openGauss︱PostgreSQL】openGauss或PostgreSQL查表、索引、序列、权限、函数

---

一、openGauss查表

/* openGauss查表 */
select (casewhen substr(version(), 0, 1) = '('then substring(substr(version(), 2, length(version())) from '^[^ ]+')else substring(version() from '^[^ ]+')end)::text as database_type,ic.table_schema::text as schema,o.owner::text,ic.table_name::text,tab_com.description::text as table_comment,ic.ordinal_position::text as column_num,ic.column_name::text,(casewhen ic.data_type = 'bigint'then 'INT8'when ic.data_type = 'integer'then 'INT4'when ic.data_type = 'smallint'then 'INT2'when ic.data_type in ('nvarchar2') AND ic.character_maximum_length IS NOT NULLthen upper(ic.data_type) || '(' || ic.character_maximum_length || ')'when ic.data_type in ('nvarchar2') AND ic.character_maximum_length IS NULLthen 'NVARCHAR2'when ic.data_type in ('character varying')then 'VARCHAR(' || ic.character_maximum_length || ')'when ic.data_type in ('character')then 'CHAR(' || ic.character_maximum_length || ')'when ic.data_type = 'timestamp without time zone'then 'TIMESTAMP(' || ic.datetime_precision || ')'when ic.data_type = 'time without time zone'then 'TIME(' || ic.datetime_precision || ')'when ic.data_type = 'numeric' and ic.numeric_precision is not null and ic.numeric_scale is not nullthen 'NUMERIC(' || ic.numeric_precision || ',' || ic.numeric_scale || ')'when ic.data_type = 'numeric' and ic.numeric_precision is null and ic.numeric_scale is nullthen 'NUMERIC'when ic.data_type = 'text'then 'TEXT'when ic.data_type = 'date'then 'DATE'else upper(ic.data_type)end)::text as data_type,col_com.description::text as column_comment,(CASEwhen (POSITION(lower('::regclass') IN lower(ic.column_default)) > 0)then REPLACE(ic.column_default, '::regclass', '')when (POSITION(lower('::integer') IN lower(ic.column_default)) > 0)then REPLACE(REPLACE(ic.column_default, '::integer', ''), '''', '')else ic.column_defaultend)::text as column_default,ic.is_nullable::text,(casewhen pc.conname is null then null::textelse 'Y'end)::text as pkeyfrom pg_class c-- join db_objects o join adm_objects o on o.object_id = c.oid and o.object_type like '%table%'and c.relkind in ('r','p')-- and c.relname ~ '[0-9]'join pg_namespace non c.relnamespace = n.oidjoin information_schema.columns icon ic.table_name = c.relnameand n.nspname = ic.table_schemaleft join pg_description tab_comon tab_com.objoid = c.oidand tab_com.objsubid = 0left join pg_description col_comon col_com.objoid = c.oidand col_com.objsubid = ic.ordinal_positionleft join (SELECT conname, conrelid , unnest(conkey) as column_numFROM pg_constraintwhere contype = 'p') as pcon pc.conrelid = c.oidand pc.column_num = ic.ordinal_positionwhere 1=1order by ic.table_schema, ic.table_name, ic.ordinal_position
;

二、openGauss查索引

/* openGauss查索引 */
with t as (select (case when t1.indexdef ~ '^.* WHERE .*$'then REGEXP_REPLACE(t1.indexdef, '^.*\((.*)\).* WHERE .*$', '\1')else REGEXP_REPLACE(t1.indexdef, '^.*\((.*)\).*$', '\1')end)::varchar as index_columns,t1.* from (select 'gs_et_sit'::varchar as source,o.owner::varchar,ist.table_schema::varchar as schemaname,c.relname::varchar as tablename,c1.relname::varchar as indexname,(case i.indisuniquewhen 't' then 'Y'when 'f' then 'N'end)::varchar as index_is_unique,(case i.indisprimarywhen 't' then 'Y'when 'f' then 'N'end)::varchar as index_is_primary,(pg_get_indexdef(i.indexrelid))::varchar as indexdef_old,(case when pg_get_indexdef(i.indexrelid) ~ '^.*WHERE.*$'then replace(replace(replace(REGEXP_REPLACE(pg_get_indexdef(i.indexrelid),'^.*WHERE \((.*)\).*.*$', '\1'),'::text', ''),'(', ''),')', '')else NULLend)::varchar as where_condition,(casewhen i.indisprimary = 't'then 'ALTER TABLE ' || c.relname || ' ADD CONSTRAINT ' || c1.relname || ' PRIMARY KEY (' || (case when pg_get_indexdef(i.indexrelid) ~ '^.*\((.*)\).*\((.*)\).*$'then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).*\((.*)\).*$', '\1')else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).*$', '\1')end) || ');' else replace(replace(replace(replace(replace(replace(pg_get_indexdef(i.indexrelid),' USING btree', ''),' TABLESPACE pg_default', ''),'::text', ''),' WITH (storage_type=USTORE)', ''),'WITH (storage_type=ustore)', ''),' USING ubtree', '')||';'end)::varchar as indexdef           from pg_index i,pg_class c1,pg_class c,information_schema.tables ist,db_objects o,pg_namespace nwhere i.indexrelid = c1.oidand i.indrelid = c.oidand c.relnamespace = n.oidand c.relname = ist.table_nameand n.nspname = ist.table_schemaand o.object_id = c.oidand ist.table_schema not in ('pg_catalog','db4ai')and ist.table_schema not like '%\_test%'and o.owner not in ('rdsAdmin','hisAdmin')and o.owner not like '%test%'and c.relname !~* '^pg\_toast\_.*$') t1)
select t.source,t.owner,t.schemaname as schema,t.tablename,t.indexname,t.indexdef,t.index_is_primary,t.index_is_unique,t.index_columns,t.where_conditionfrom torder by t.schemaname, t.tablename, t.indexname
;

三、openGauss查序列

/* openGauss查序列 */
with c as (
select r.rolname::varchar as owner,n.nspname::varchar as schema,c.relname::varchar as sequence_namefrom pg_class c, pg_roles r, pg_namespace nwhere c.relowner = r.oidand c.relnamespace = n.oidand c.relkind = 'S')
,s as (
select sequence_owner::varchar as owner,sequence_name::varchar,min_value::varchar,max_value::varchar,increment_by::varchar,cache_size::varchar,cycle_flag::varchar,last_number::varcharfrom db_sequences
)
select c.owner::varchar,c.schema::varchar,c.sequence_name::varchar,s.min_value::varchar,s.max_value::varchar,s.increment_by::varchar,s.cache_size::varchar,s.cycle_flag::varchar,s.last_number::varcharfrom Cinner join s on c.owner = s.owner and c.sequence_name = s.sequence_nameorder by schema, sequence_name;

四、openGauss查权限

/* openGauss查权限 */
WITH pg_rel_privs AS(SELECT xx.schemaname,xx.owner,xx.relname,xx.relkind,xx.relacls[1] AS grantee,xx.relacls[2] AS privilege,xx.relacls[3] AS grantorFROM (SELECT (SELECT n.nspnameFROM pg_namespace nWHERE n.oid = tt.relnamespace) AS schemaname,o.owner,tt.relname,tt.relkind,regexp_split_to_array(unnest(tt.relacl)::character varying::text, '=|/'::text) AS relaclsFROM pg_class ttLEFT JOIN db_objects oON tt.oid = o.object_idWHERE tt.relacl IS NOT NULL) xx)
SELECT t.schemaname::varchar as schema,t.owner::varchar,t.relname::varchar as object_name,(CASEWHEN t.relkind = 'r' ::char THEN 'table' ::textWHEN t.relkind = 'S' ::char THEN 'sequence' ::textWHEN t.relkind = 'v' ::char THEN 'view' ::textWHEN t.relkind = 'p' ::char THEN 'patition table' ::textELSE t.relkind ::textEND)::varchar AS type,t.grantor::varchar,t.grantee::varchar,(TRIM(CASE WHEN instr(t.privilege, 'a') > 0 THEN 'insert,' END || CASE WHEN instr(t.privilege, 'r') > 0 THEN 'select,' END || CASE WHEN instr(t.privilege, 'w') > 0 THEN 'update,' END || CASE WHEN instr(t.privilege, 'd') > 0 THEN 'delete,' END || CASE WHEN instr(t.privilege, 'D') > 0 THEN 'truncate,' END || CASE WHEN instr(t.privilege, 'U') > 0 THEN 'usage,' END,','))::varchar AS privilegeFROM pg_rel_privs tWHERE t.grantor != 'rdsAdmin'AND t.grantee != t.grantororder by schema,object_name,type,grantor,grantee,privilege;

五、openGauss或PostgreSQL查函数

/* openGauss或PostgreSQL查函数 */
select (CASE schemawhen 'tzq' then 'f_g_tzq'else schemaEND)::varchar as schema,(CASE ownerwhen 'tzq' then 'f_g_tzq'else ownerEND)::varchar as owner,pro_name,pro_arg,pro_arg_type,pro_ret_type,pro_content  from (
select * from (select pn.nspname::varchar AS schema,r.rolname::varchar as owner,pc.proname::varchar as pro_name,pc.proargnames::varchar as pro_arg,(SELECT array(SELECT format_type(val, NULL)FROM unnest(proargtypes) as val))::varchar as pro_arg_type,format_type(pc.prorettype, NULL)::varchar as pro_ret_type,REGEXP_REPLACE(pg_get_functiondef(pc."oid")::text, '^\(4,"(.*)"\)$', '\1')::textas pro_contentfrom pg_proc pcinner join pg_roles ron pc.proowner = r.oidinner join pg_namespace pnon pc.pronamespace = pn.oidwhere 1=1and pn.nspname not in ('pg_catalog','dbe_pldebugger')and r.rolname not in ('rdsAdmin') and pc.proargnames is not nullorder by r.rolname asc
) aa
union all select pn.nspname::varchar AS schema,r.rolname::varchar as owner,pc.proname::varchar as pro_name,pc.proargnames::varchar as pro_arg,proargtypes::varchar AS pro_arg_type,format_type(pc.prorettype, NULL)::varchar as pro_ret_type-- ,pc.oid,REGEXP_REPLACE(pg_get_functiondef(pc.oid)::text, '^\(4,"(.*)"\)$', '\1')::text as pro_contentfrom pg_proc pcinner join pg_roles ron pc.proowner = r.oidinner join pg_namespace pnon pc.pronamespace = pn.oidwhere 1=1and pn.nspname not in ('pg_catalog','dbe_pldebugger')and r.rolname not in ('rdsAdmin') and pc.proargnames is null
) bb
order by schema, pro_name;

六、PostgreSQL查表

/* PostgreSQL查表 */
/* 更新日志：由于 information_schema.columns 有权限控制，不是该用户能查的权限就查不到，故下掉。采用 pg_attrdef 来获取列的默认值
*/
with tab_info_v as (SELECT substring(version() FROM '(\S+)') as database_type,n.nspname AS schema,r.rolname as owner,c.relname AS table_name,(col_description(c.oid, 0))::character varying AS table_comment,a.attnum,a.attname AS column_name,concat_ws('', t.typname) AS data_type,(CASEWHEN a.attlen > 0 THEN a.attlenWHEN t.typname = 'bit' THEN a.atttypmodELSE a.atttypmod - 4END) AS data_length,CASE-- 为了和 高斯比对WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'date'::text) THEN 'TIMESTAMP(0)'::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'bigint'::text) THEN 'INT8'::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'smallint'::text) THEN 'INT2'::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'integer'::text) THEN 'INT4'::character varyingWHEN (left(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 17) = 'character varying'::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 'character varying'::text, 'VARCHAR'::text))::character varyingWHEN (left(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 9) = 'character'::text) THEN (replace(((format_type(a.atttypid, a.atttypmod))::character varying)::text, 'character'::text, 'char'::text))::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp(0) without time zone'::text) THEN 'TIMESTAMP(0)'::character varyingWHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP(6)'::character varyingELSE (format_type(a.atttypid, a.atttypmod))::character varyingEND AS typelen,(CASEWHEN a.attnotnull = TRUE THEN 'NO'ELSE 'YES'END) AS is_nullable  --  可为空,(CASEWHEN (SELECT COUNT(pg_constraint.*)FROM pg_constraintINNER JOIN pg_classON pg_constraint.conrelid = pg_class.oidINNER JOIN pg_attributeON pg_attribute.attrelid = pg_class.oidAND pg_attribute.attnum = ANY(pg_constraint.conkey)INNER JOIN pg_typeON pg_type.oid = pg_attribute.atttypidWHERE pg_class.relname = c.relnameAND pg_constraint.contype = 'p'AND pg_attribute.attname = a.attnameand pg_class.relowner = c.relowner) > 0 THEN 'Y'ELSE NULLEND) AS pkey,pg_get_expr(ad.adbin, ad.adrelid) as data_default,(SELECT descriptionFROM pg_descriptionWHERE objoid = a.attrelidAND objsubid = a.attnum) AS column_commentFROM pg_class cinner join pg_roles ron c.relowner = r.oidand c.relkind in ('r','p')and c.relpartbound is null-- and ((relkind = 'p') or (relkind = 'r' and relpartbound is null))inner join pg_attribute aon a.attnum > 0AND a.attrelid = c.oidinner join pg_namespace non n.oid = c.relnamespaceinner join pg_type ton a.atttypid = t.oidleft join pg_attrdef ad  --  列的默认值on ad.adrelid = c.oidand ad.adnum = a.attnumWHERE 1 = 1and n.nspname not in ('information_schema')ORDER BY c.relname ASC,a.attnum ASC,a.attname ASC
)
select database_type-- ,schema,CASE schemawhen 'tzq' then 'f_g_tzq'else schemaEND as schema,CASE ownerwhen 'tzq' then 'f_g_tzq'else schemaEND as owner,table_name, table_comment,attnum,column_name,replace(upper(typelen),'VARCHAR','NVARCHAR2') as data_type,column_comment,data_default as column_default,is_nullable,pkeyfrom tab_info_v vwhere 1=1-- and table_name = 'ets_risk_premium_check_tmp'-- and schema <> ownerand table_name not like 'adms_ogg_checkpoint%'-- and table_name !~ '[p][0-9]{2}$'-- and table_name !~ '[p][0-9]{1}$'-- and table_name ~ '[p][0-9]{1}$'
order by schema, table_name, attnum, column_name;

七、PostgreSQL查索引

/* PostgreSQL查索引 */
-- drop view if exists pg_index_info_v;
-- create view pg_index_info_v as 
with t as (
select u.usename ,c.relname as tablename,c1.relname as indexname,case i.indisuniquewhen 't' then 'Y'when 'f' then 'N'end as index_is_unique  -- 是否为唯一索引,case i.indisprimarywhen 't' then 'Y'when 'f' then 'N'end as index_is_primary  -- 是否为主键,case when pg_get_indexdef(i.indexrelid) ~ '^.* WHERE .*$'then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).* WHERE .*$', '\1')else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).*$', '\1')end as index_columns,replace(pg_get_indexdef(i.indexrelid), 'USING btree ', '')||';'as indexdef1,casewhen i.indisprimary = 't'then 'ALTER TABLE ' || c.relname || ' ADD CONSTRAINT ' || c1.relname || ' PRIMARY KEY (' || (case when pg_get_indexdef(i.indexrelid) ~ '^.*\((.*)\).*\((.*)\).*$'then REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).*\((.*)\).*$', '\1')else REGEXP_REPLACE(pg_get_indexdef(i.indexrelid), '^.*\((.*)\).*$', '\1')end) || ');' else replace(replace(replace(pg_get_indexdef(i.indexrelid),' USING btree', ''),' TABLESPACE pg_default', '') ,'::text', '') ||';'end as indexdef,case when pg_get_indexdef(i.indexrelid) ~ '^.*WHERE.*$'then replace(replace(replace(REGEXP_REPLACE(pg_get_indexdef(i.indexrelid),'^.*WHERE \((.*)\).*.*$', '\1'),'::text', ''),'(', ''),')', '')else NULLend as where_condition--,i.* from pg_index i,pg_class c1,pg_class c,pg_user uwhere i.indexrelid = c1.oidand i.indrelid = c.oidand c.relowner = u.usesysidand c.relkind in ('r','p')and c.relpartbound is null-- and u.usename = CURRENT_USERand c.relname !~* '^pg\_toast\_.*$')
select CASE t.usenamewhen 'tzq' then 'f_g_tzq'else t.usenameEND as schema,t.tablename,t.indexname,casewhen index_is_primary = 'Y'then 'ALTER TABLE ' || t.tablename || ' ADD CONSTRAINT ' || t.indexname || ' PRIMARY KEY (' || t.index_columns || ');' else t.indexdef end as indexdef,t.index_is_unique,t.index_is_primary,t.index_columns,t.where_conditionfrom twhere t.tablename not like 'adms_ogg_checkpoint%'order by t.usename, t.tablename, t.indexname;

八、PostgreSQL查序列

/* PostgreSQL查序列 */
select (CASE sequenceownerwhen 'tzq' then 'f_g_tzq'else sequenceownerEND)::varchar as owner,(CASE schemanamewhen 'tzq' then 'f_g_tzq'else schemanameEND)::varchar as schema, sequencename::varchar as sequence_name, min_value::varchar, max_value::varchar, increment_by::varchar, cache_size::varchar,(case cycle when 'f' then 'n' else 'y' end)::varchar as cycle_flag,(case when last_value is null then '1' else last_value end)::varchar as last_numberfrom pg_sequenceswhere 1=1-- and schemaname in ('tzq');order by schema,sequence_name;

九、PostgreSQL查权限

/* PostgreSQL查权限 */
WITH pg_rel_privs AS (SELECT xx.relowner,xx.schemaname,xx.relname,xx.relkind,xx.relacls[1] AS grantee,xx.relacls[2] AS privilege,xx.relacls[3] AS grantorFROM ( SELECT ( SELECT u.usenameFROM pg_user uWHERE u.usesysid = tt.relowner) AS relowner,( SELECT n.nspnameFROM pg_namespace nWHERE n.oid = tt.relnamespace) AS schemaname,tt.relname,tt.relkind,regexp_split_to_array(unnest(tt.relacl)::character varying::text, '=|/'::text) AS relaclsFROM pg_class ttWHERE tt.relacl IS NOT NULL) xx),
dba_tab_privs as (SELECT x.relowner AS owner_,x.schemaname AS schema_name,x.relname AS table_name,CASEWHEN x.relkind = 'r'::char THEN 'table'::textWHEN x.relkind = 'S'::char THEN 'sequence'::textWHEN x.relkind = 'v'::char THEN 'view'::textWHEN x.relkind = 'p'::char THEN 'patition table'::textELSE x.relkind::textEND AS type_,x.grantee,CASEWHEN x.privilege = 'r'::text THEN 'select'::textWHEN x.privilege = 'a'::text THEN 'insert'::textWHEN x.privilege = 'd'::text THEN 'delete'::textWHEN x.privilege = 'w'::text THEN 'update'::textWHEN x.privilege = 'D'::text THEN 'truncate'::textWHEN x.privilege = 'X'::text THEN 'execute'::textWHEN x.privilege = 'U'::text THEN 'usage'::textWHEN x.privilege = 'x'::text THEN 'references'::textWHEN x.privilege = 't'::text THEN 'trigger'::textELSE x.privilegeEND AS privilege,x.grantorFROM ( SELECT t.relowner,t.schemaname,t.relname,t.relkind,t.grantee,regexp_split_to_table(t.privilege, ''::text) AS privilege,t.grantorFROM pg_rel_privs tWHERE t.relowner <> t.grantee) x)
select (CASE schema_namewhen 'tzq' then 'f_g_tzq'else schema_nameEND)::varchar as schema,(CASE owner_when 'tzq' then 'f_g_tzq'else owner_END)::varchar as owner,table_name::varchar as object_name,type_::varchar as type,(CASE lower(grantor)when 'tzq' then 'f_g_tzq'else lower(grantor)END)::varchar as grantor,(CASE lower(grantee)when 'tzq' then 'f_g_tzq'else lower(grantee)END)::varchar as grantee,privilege::varcharfrom dba_tab_privs twhere owner_ not in ('rdsAdmin')and schema_name not in ('pg_catalog')and lower(grantee) not in ('appmon')-- and schema_name in ('tzq')-- and (table_name like '%\_t' OR table_name like '%\_ti' OR table_name like '%\_tmp')
-- and type_ ~* 'table'and table_name not like 'adms_ogg_checkpoint%'
order by schema_name,table_name;