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

一、openGauss查表

/* openGauss查表 */
select (case
         when 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
      ,(case
         when 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 NULL
           then upper(ic.data_type) || '(' || ic.character_maximum_length || ')'
         when ic.data_type in ('nvarchar2') AND ic.character_maximum_length IS NULL
           then '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 null
           then 'NUMERIC(' || ic.numeric_precision || ',' || ic.numeric_scale || ')'
         when ic.data_type = 'numeric' and ic.numeric_precision is null and ic.numeric_scale is null
           then '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
      ,(CASE
         when (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_default
       end)::text as column_default
      ,ic.is_nullable::text
      ,(case
         when pc.conname is null then null::text
         else 'Y'
       end)::text as pkey
  from 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 n
    on c.relnamespace = n.oid
  join information_schema.columns ic
    on ic.table_name = c.relname
   and n.nspname = ic.table_schema
  left join pg_description tab_com
    on tab_com.objoid = c.oid
   and tab_com.objsubid = 0
  left join pg_description col_com
    on col_com.objoid = c.oid
   and col_com.objsubid = ic.ordinal_position
  left join (
    SELECT conname, conrelid , unnest(conkey) as column_num
      FROM pg_constraint
     where contype = 'p') as pc
    on pc.conrelid = c.oid
   and pc.column_num = ic.ordinal_position
 where 1=1
 order 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.indisunique
                   when 't' then 'Y'
                   when 'f' then 'N'
                 end)::varchar as index_is_unique
                ,(case i.indisprimary
                   when '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 NULL
                 end)::varchar as where_condition
                ,(case
                   when 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 n
           where i.indexrelid = c1.oid
             and i.indrelid = c.oid
             and c.relnamespace = n.oid
             and c.relname = ist.table_name
             and n.nspname = ist.table_schema
             and o.object_id = c.oid
             and 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_condition
  from t
 order 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_name
  from pg_class c, pg_roles r, pg_namespace n
 where c.relowner = r.oid
   and c.relnamespace = n.oid
   and 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::varchar
  from 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::varchar
  from C
 inner join s on c.owner = s.owner and c.sequence_name = s.sequence_name
 order 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 grantor
    FROM (SELECT (SELECT n.nspname
                    FROM pg_namespace n
                   WHERE n.oid = tt.relnamespace) AS schemaname
                ,o.owner
                ,tt.relname
                ,tt.relkind
                ,regexp_split_to_array(unnest(tt.relacl)::character varying::text, '=|/'::text) AS relacls
            FROM pg_class tt
            LEFT JOIN db_objects o
              ON tt.oid = o.object_id
           WHERE tt.relacl IS NOT NULL) xx)
SELECT t.schemaname::varchar as schema
      ,t.owner::varchar
      ,t.relname::varchar as object_name
      ,(CASE
         WHEN t.relkind = 'r' ::char THEN 'table' ::text
         WHEN t.relkind = 'S' ::char THEN 'sequence' ::text
         WHEN t.relkind = 'v' ::char THEN 'view' ::text
         WHEN t.relkind = 'p' ::char THEN 'patition table' ::text
         ELSE t.relkind ::text
       END)::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 privilege
  FROM pg_rel_privs t
 WHERE t.grantor != 'rdsAdmin'
   AND t.grantee != t.grantor
 order by schema,object_name,type,grantor,grantee,privilege;

五、openGauss或PostgreSQL查函数

/* openGauss或PostgreSQL查函数 */
select (CASE schema
         when 'tzq' then 'f_g_tzq'
         else schema
       END)::varchar as schema
      ,(CASE owner
         when 'tzq' then 'f_g_tzq'
         else owner
       END)::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')::text
             as pro_content
      from pg_proc pc
     inner join pg_roles r
        on pc.proowner = r.oid
     inner join pg_namespace pn
        on pc.pronamespace = pn.oid
     where 1=1
       and pn.nspname not in ('pg_catalog','dbe_pldebugger')
       and r.rolname not in ('rdsAdmin') 
       and pc.proargnames is not null
     order 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_content
      from pg_proc pc
     inner join pg_roles r
        on pc.proowner = r.oid
     inner join pg_namespace pn
        on pc.pronamespace = pn.oid
     where 1=1
       and 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
        ,(CASE
           WHEN a.attlen > 0 THEN a.attlen
           WHEN t.typname = 'bit' THEN a.atttypmod
           ELSE a.atttypmod - 4
          END) AS data_length
        ,CASE
          -- 为了和 高斯比对
           WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'date'::text) THEN 'TIMESTAMP(0)'::character varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'bigint'::text) THEN 'INT8'::character varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'smallint'::text) THEN 'INT2'::character varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::character varying)::text = 'integer'::text) THEN 'INT4'::character varying
           WHEN (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 varying
           WHEN (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 varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp(0) without time zone'::text) THEN 'TIMESTAMP(0)'::character varying
           WHEN (((format_type(a.atttypid, a.atttypmod))::text)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP(6)'::character varying
           ELSE (format_type(a.atttypid, a.atttypmod))::character varying
         END AS typelen
        ,(CASE
            WHEN a.attnotnull = TRUE THEN 'NO'
            ELSE 'YES'
          END) AS is_nullable  --  可为空
        ,(CASE
            WHEN (SELECT COUNT(pg_constraint.*)
                    FROM pg_constraint
                   INNER JOIN pg_class
                      ON pg_constraint.conrelid = pg_class.oid
                   INNER JOIN pg_attribute
                      ON pg_attribute.attrelid = pg_class.oid
                     AND pg_attribute.attnum = ANY(pg_constraint.conkey)
                   INNER JOIN pg_type
                      ON pg_type.oid = pg_attribute.atttypid
                   WHERE pg_class.relname = c.relname
                     AND pg_constraint.contype = 'p'
                     AND pg_attribute.attname = a.attname
                     and pg_class.relowner = c.relowner
                     ) > 0 THEN 'Y'
            ELSE NULL
          END) AS pkey
        ,pg_get_expr(ad.adbin, ad.adrelid) as data_default
        ,(SELECT description
            FROM pg_description
           WHERE objoid = a.attrelid
             AND objsubid = a.attnum) AS column_comment
    FROM pg_class c
   inner join pg_roles r
      on c.relowner = r.oid
     and c.relkind in ('r','p')
     and c.relpartbound is null
         -- and ((relkind = 'p') or (relkind = 'r' and relpartbound is null))
   inner join pg_attribute a
      on a.attnum > 0
     AND a.attrelid = c.oid
   inner join pg_namespace n
      on n.oid = c.relnamespace
   inner join pg_type t
      on a.atttypid = t.oid
    left join pg_attrdef ad  --  列的默认值
      on ad.adrelid = c.oid
     and ad.adnum = a.attnum
   WHERE 1 = 1
     and n.nspname not in ('information_schema')
   ORDER BY c.relname ASC
           ,a.attnum ASC
           ,a.attname ASC
)
select database_type
      -- ,schema
      ,CASE schema
         when 'tzq' then 'f_g_tzq'
         else schema
       END as schema
      ,CASE owner
         when 'tzq' then 'f_g_tzq'
         else schema
       END 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
      ,pkey
  from tab_info_v v
 where 1=1
   -- and table_name = 'ets_risk_premium_check_tmp'
   -- and schema <> owner
   and 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.indisunique
         when 't' then 'Y'
         when 'f' then 'N'
       end as index_is_unique  -- 是否为唯一索引
      ,case i.indisprimary
         when '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
      ,case
         when 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 NULL
       end as where_condition
      --,i.* 
  from pg_index i
      ,pg_class c1
      ,pg_class c
      ,pg_user u
 where i.indexrelid = c1.oid
   and i.indrelid = c.oid
   and c.relowner = u.usesysid
   and c.relkind in ('r','p')
   and c.relpartbound is null
   -- and u.usename = CURRENT_USER
   and c.relname !~* '^pg\_toast\_.*$')
select CASE t.usename
         when 'tzq' then 'f_g_tzq'
         else t.usename
       END as schema
      ,t.tablename
      ,t.indexname
      ,case
         when 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_condition
  from t
 where t.tablename not like 'adms_ogg_checkpoint%'
  order by t.usename, t.tablename, t.indexname;

八、PostgreSQL查序列

/* PostgreSQL查序列 */
select (CASE sequenceowner
         when 'tzq' then 'f_g_tzq'
         else sequenceowner
       END)::varchar as owner
      ,(CASE schemaname
         when 'tzq' then 'f_g_tzq'
         else schemaname
       END)::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_number
  from pg_sequences
 where 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 grantor
           FROM ( SELECT ( SELECT u.usename
                           FROM pg_user u
                          WHERE u.usesysid = tt.relowner) AS relowner,
                    ( SELECT n.nspname
                           FROM pg_namespace n
                          WHERE n.oid = tt.relnamespace) AS schemaname,
                    tt.relname,
                    tt.relkind,
                    regexp_split_to_array(unnest(tt.relacl)::character varying::text, '=|/'::text) AS relacls
                   FROM pg_class tt
                  WHERE 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,
        CASE
            WHEN x.relkind = 'r'::char THEN 'table'::text
            WHEN x.relkind = 'S'::char THEN 'sequence'::text
            WHEN x.relkind = 'v'::char THEN 'view'::text
            WHEN x.relkind = 'p'::char THEN 'patition table'::text
            ELSE x.relkind::text
        END AS type_,
    x.grantee,
        CASE
            WHEN x.privilege = 'r'::text THEN 'select'::text
            WHEN x.privilege = 'a'::text THEN 'insert'::text
            WHEN x.privilege = 'd'::text THEN 'delete'::text
            WHEN x.privilege = 'w'::text THEN 'update'::text
            WHEN x.privilege = 'D'::text THEN 'truncate'::text
            WHEN x.privilege = 'X'::text THEN 'execute'::text
            WHEN x.privilege = 'U'::text THEN 'usage'::text
            WHEN x.privilege = 'x'::text THEN 'references'::text
            WHEN x.privilege = 't'::text THEN 'trigger'::text
            ELSE x.privilege
        END AS privilege,
    x.grantor
   FROM ( SELECT t.relowner,
            t.schemaname,
            t.relname,
            t.relkind,
            t.grantee,
            regexp_split_to_table(t.privilege, ''::text) AS privilege,
            t.grantor
           FROM pg_rel_privs t
          WHERE t.relowner <> t.grantee) x)
select (CASE schema_name
         when 'tzq' then 'f_g_tzq'
         else schema_name
       END)::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::varchar
  from dba_tab_privs t
 where 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;