Postgresql获取表字段,因为一个表空间下多个模式导致读取不到 返回

SqlSugar
2 274
该叫什么 OBJECT 发布于2022/1/13
悬赏:0 飞吻

使用接口:client.DbMaintenance.GetColumnInfosByTableName(tableName, false);

该接口的sql语句看起来是这样的,如下:

select cast (pclass.oid as int4) as TableId,cast(ptables.tablename as varchar) as TableName,

                                pcolumn.column_name as DbColumnName,pcolumn.udt_name as DataType,

                                CASE WHEN pcolumn.numeric_scale >0 THEN pcolumn.numeric_precision ELSE pcolumn.character_maximum_length END   as Length,

                                pcolumn.column_default as DefaultValue,

                                pcolumn.numeric_scale as DecimalDigits,

                                pcolumn.numeric_scale as Scale,

                                col_description(pclass.oid, pcolumn.ordinal_position) as ColumnDescription,

                                case when pkey.colname = pcolumn.column_name

                                then true else false end as IsPrimaryKey,

                                case when pcolumn.column_default like 'nextval%'

                                then true else false end as IsIdentity,

                                case when pcolumn.is_nullable = 'YES'

                                then true else false end as IsNullable

                                 from (select * from pg_tables where tablename = 'smdtv_272' and schemaname='public') ptables inner join pg_class pclass

                                on ptables.tablename = pclass.relname inner join (SELECT *

                                FROM information_schema.columns

                                ) pcolumn on pcolumn.table_name = ptables.tablename

                                left join (

                                select  pg_class.relname,pg_attribute.attname as colname 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 = pg_constraint.conkey[1]

                                inner join pg_type on pg_type.oid = pg_attribute.atttypid

                                where pg_constraint.contype='p'

                                ) pkey on pcolumn.table_name = pkey.relname

                                order by ptables.tablename



请问这个问题怎么解决呀?

热忱回答2

  • fate sta fate sta VIP0
    2022/1/13

    目前只支持  schemaname='public'

    0 回复
  • OBJECT OBJECT VIP0
    2022/1/13

    @fate sta:后续有升级计划吗?或者我们自己能重写一下实现先?


    0 回复