【openGauss、PostgreSQL】openGauss、PostgreSQL数据库通用查表字段信息脚本-v20240620-2216

【openGauss、PostgreSQL】openGauss、PostgreSQL数据库通用查表字段信息脚本-v20240620-2216

• openGauss、PostgreSQL数据库通用查表字段信息脚本-v20240620-2216

openGauss、PostgreSQL数据库通用查表字段信息脚本-v20240620-2216

此脚本，openGauss、PostgreSQL都可执行

/* openGauss、PostgreSQL数据库通用查表字段信息脚本-v20240619-1503 */
drop view if exists tab_info_v;
create view tab_info_v as 
select case
         when substr(version(), 0, 1) = '(' 
           then substring(substr(version(), 2, length(version())) from '^[^ ]+')
         else substring(version() from '^[^ ]+')
       end as database_type
      ,ic.table_schema as schema
      ,ic.table_name
      -- ,c.oid
      ,tab_com.description as table_comment
      ,ic.ordinal_position as column_num
      ,ic.column_name
      ,case
         when ic.data_type = 'bigint' 
           then 'INT8'
         when ic.data_type in ('nvarchar2') 
           then upper(ic.data_type) || '(' || ic.character_maximum_length || ')'
         when ic.data_type in ('character varying') 
           then 'VARCHAR(' || 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' 
           then 'NUMERIC(' || ic.numeric_precision || ',' || ic.numeric_scale || ')'
         when ic.data_type = 'text' 
           then 'TEXT'
         when ic.data_type = 'date' 
           then 'DATE'
         else ic.data_type
       end as data_type
      ,col_com.description as column_comment
      ,ic.column_default
      ,ic.is_nullable
      -- 查表的主键详情
      /* 
      ,case when (SELECT count(*)
                    FROM information_schema.table_constraints AS tc
                    JOIN information_schema.key_column_usage  AS kcu 
                      ON tc.constraint_name = kcu.constraint_name
                   WHERE tc.constraint_type = 'PRIMARY KEY'
                     AND tc.table_name      = ic.table_name
                     and kcu.column_name    = ic.column_name
                     ) > 0 then 'Y' 
         else NULL::text
       END AS pkey
       */
      ,case 
         when pc.conname is null then null::text
         else 'Y'
       end as pkey
  from information_schema.columns ic
  join pg_class c
    on ic.table_name = c.relname
  join pg_namespace n
    on c.relnamespace = n.oid
  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) as pc
    on pc.conrelid = c.oid
   and pc.column_num = ic.ordinal_position
 where ic.table_catalog = CURRENT_CATALOG
   and ic.table_schema = CURRENT_SCHEMA
   and ic.table_name !~ '^act\_'
   and n.nspname = CURRENT_USER
   and c.relkind in ('r','p')
   -- 把分区表过滤掉
   -- 查结尾不是"p+4位数字"的，例如：p2023
   and ic.table_name !~* '^.*[p][0-9]{4}$'
   -- 查结尾不是"p+5位数字"的，例如：p20231
   and ic.table_name !~* '^.*[p][0-9]{5}$'
   -- 查结尾6位字符不是"others"的
   and ic.table_name !~* '^.*[others]{6}$'
   -- 查结尾5位字符不是"other"的
   and ic.table_name !~* '^.*[other]{5}$'
   -- 查结尾不是数字或者不是_bak的表名
   and (ic.table_name !~ '^.*[0-9]{1}.*$' and ic.table_name !~ '^.*\_bak$')
 order by ic.table_name, ic.ordinal_position;

-- select * from tab_info_v;