--獲得指定schema范圍內(nèi)的所有表和視圖的列表，可指定一個排除表前綴模式

with param as (select 'public,iit' as schema_name,'db2g%' as exclude_pattern)
    ,base_info as (
        --獲得所有基表
        select pg_namespace.nspname as schema_name, a.relname as tbl_name ,'TBL' as tbl_type, b.description as tbl_comment
        from pg_class a
            join pg_namespace on a.relnamespace=pg_namespace.oid
            left join (select * from pg_description where objsubid =0 ) b
                on a.oid = b.objoid
        where a.relkind='r'
             and a.relname not like (select exclude_pattern from param)
             and pg_namespace.nspname in (select regexp_split_to_table(schema_name,',') from param)
        union all
        ---獲取所有視圖
        SELECT schemaname as schema_name, viewname as tbl_name,'VW' as tbl_type, null as tbl_comment
        FROM pg_views
        WHERE schemaname in (select regexp_split_to_table(schema_name,',') from param)
    )
select * from base_info;

--獲得指定schema范圍內(nèi)的所有表和視圖的數(shù)據(jù)列信息，可指定一個排除表前綴模式

with param as (select 'public,iit' as schema_name,'db2g%' as exclude_pattern),
     base_info as (
         select table_schema
              , table_name
              , ordinal_position                                                      as Colorder
              , column_name                                                           as ColumnName
              , data_type                                                             as TypeName
              , coalesce(character_maximum_length, numeric_precision, -1)             as Length
              , numeric_scale                                                         as Scale
              , case is_nullable when 'NO' then 0 else 1 end                          as CanNull
              , column_default                                                        as DefaultVal
              , case when position('nextval' in column_default) > 0 then 1 else 0 end as IsIdentity
              , case when b.pk_name is null then 0 else 1 end                         as IsPK
              , c.DeText
         from information_schema.columns
                  left join (
                             select pg_attr.attname as colname
                                    ,pg_constraint.conname as pk_name
                                    ,pg_class.relname as tblname
                                    ,pg_namespace.nspname as schema_name
                             from pg_constraint
                                      join pg_class on pg_constraint.conrelid = pg_class.oid
                                      join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid
                                            and pg_attr.attnum = pg_constraint.conkey[1]
                                      join pg_type on pg_type.oid = pg_attr.atttypid
                                      join pg_namespace on pg_constraint.connamespace=pg_namespace.oid
                             where pg_constraint.contype = 'p'
                            ) b on b.colname = information_schema.columns.column_name
                                    and b.tblname=information_schema.columns.table_name
                                    and b.schema_name=information_schema.columns.table_schema
                  left join (
                             select attname, description as DeText, pg_class.relname as tblname,pg_namespace.nspname as schema_name
                             from pg_class
                                      join pg_namespace on pg_class.relnamespace=pg_namespace.oid
                                      left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid
                                      left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid
                                 and pg_desc.objsubid = pg_attr.attnum
                             where pg_attr.attnum > 0
                               and pg_attr.attrelid = pg_class.oid
                         ) c on c.attname = information_schema.columns.column_name
                                and c.tblname=information_schema.columns.table_name
                                and c.schema_name=information_schema.columns.table_schema
         where table_schema in (select regexp_split_to_table(schema_name,',') from param)
              and table_name not like (select exclude_pattern from param)
         order by table_name,ordinal_position
     )
select * from base_info;

--查詢指定模式下的表和視圖

with param as (select 'public' as schema_name,'db2g%' as exclude_pattern)
--獲得所有基表
select a.relname as tbl_name ,'TBL' as tbl_type, b.description as tbl_comment
from pg_class a
    left join (select *
                from pg_description where objsubid =0 ) b
        on a.oid = b.objoid
where a.relname in (select tablename
                    from pg_tables
                    where schemaname = (select schema_name from param))
     and a.relname not like (select exclude_pattern from param)
union all
---獲取所有視圖
SELECT viewname as tbl_name,'VW' as tbl_type, null as tbl_comment
FROM pg_views
WHERE schemaname =(select schema_name from param)
order by tbl_name asc;

--查詢指定數(shù)據(jù)基表的列信息文章來源地址http://www.zghlxwxcb.cn/news/detail-660720.html

with param as (select 'emp' as tblname),
     base_info as (
         select ordinal_position                                                      as Colorder
              , column_name                                                           as ColumnName
              , data_type                                                             as TypeName
              , coalesce(character_maximum_length, numeric_precision, -1)             as Length
              , numeric_scale                                                         as Scale
              , case is_nullable when 'NO' then 0 else 1 end                          as CanNull
              , column_default                                                        as DefaultVal
              , case when position('nextval' in column_default) > 0 then 1 else 0 end as IsIdentity
              , case when b.pk_name is null then 0 else 1 end                         as IsPK
              , c.DeText
         from information_schema.columns
                  left join (
                             select pg_attr.attname as colname, pg_constraint.conname as pk_name
                             from pg_constraint
                                      join pg_class on pg_constraint.conrelid = pg_class.oid
                                      join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid
                                 and pg_attr.attnum = pg_constraint.conkey[1]
                                      join pg_type on pg_type.oid = pg_attr.atttypid
                             where pg_class.relname = (select tblname from param)
                               and pg_constraint.contype = 'p'
                            ) b on b.colname = information_schema.columns.column_name
                  left join (
             select attname, description as DeText
             from pg_class
                      left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid
                      left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid
                 and pg_desc.objsubid = pg_attr.attnum
             where pg_attr.attnum > 0
               and pg_attr.attrelid = pg_class.oid
               and pg_class.relname = (select tblname from param)
         ) c on c.attname = information_schema.columns.column_name
         where table_schema = 'public'
           and table_name = (select tblname from param)
         order by ordinal_position asc
     )
select * from base_info

到了這里，關(guān)于PostgreSQL 查詢數(shù)據(jù)表、視圖信息的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！