postgresql 메타 조회

select distinct table_name, concat('--',TABLE_COMMENT) from (

SELECT A.ordinal_position as COLUMN_ID

, '' OWNER

, A.table_name as TABLE_NAME

, B.obj_description as TABLE_COMMENT

, A.column_name as COLUMN_NAME

, C.comments as COLUMNS_COMMENTS

, A.data_type

||

CASE WHEN A.data_type != 'TIMESTAMP(6)'

THEN

(

CASE WHEN A.data_type = 'numeric'

THEN ' (' || A.numeric_precision || ') '

WHEN A.data_type = 'timestamp without time zone'

THEN NULL

ELSE ' (' || A.character_maximum_length || ') '

END

)

END as data_type

, A.is_nullable as NULLABLE

, '' as DATA_DEFAULT

, A.numeric_precision as DATA_PRECISION

, A.numeric_scale as DATA_SCALE

FROM information_schema.columns A

JOIN (

SELECT N.NSPNAME, C.RELNAME, OBJ_DESCRIPTION(C.OID) as obj_description

FROM PG_CATALOG.PG_CLASS C INNER JOIN PG_CATALOG.PG_NAMESPACE N ON C.RELNAMESPACE=N.OID

WHERE C.RELKIND = 'r'

AND NSPNAME = 'public'

--AND RELNAME = 'pts_cmmnty_master_mstr'

) B

ON A.table_name = B.relname

JOIN (

SELECT

PS.SCHEMANAME AS SCHEMA,

PS.RELNAME AS TABLE_NAME,

PA.ATTNAME AS COLUMN_NAME,

PD.DESCRIPTION AS comments

FROM PG_STAT_ALL_TABLES PS, PG_DESCRIPTION PD, PG_ATTRIBUTE PA

WHERE PD.OBJSUBID<>0

AND PS.RELID=PD.OBJOID

AND PD.OBJOID=PA.ATTRELID

AND PD.OBJSUBID=PA.ATTNUM

AND PS.SCHEMANAME= 'public'

--AND PS.RELNAME= 'pts_cmmnty_master_mstr'

ORDER BY PS.RELNAME, PD.OBJSUBID

) C

ON A.table_name = C.table_name

AND A.column_name = C.column_name

WHERE 1=1

-- AND A.OWNER IN ( 'KWM' )

AND A.table_name LIKE 'mw_%'

-- AND LENGTH (A.TABLE_NAME) = 9

-- 변경 -- PFCSOWR --AFCOWR

--AND A.column_name LIKE '%equipment_mng_num%'

-- AND C.COMMENTS LIKE '%담당자%'

--AND B.obj_description LIKE '%기관%' /* xpdlqmfaud */

-- AND A.COLUMN_ID = '1'

-- AND A.tablename = '테이블명'

ORDER BY A.table_name

, A.ordinal_position

) A

;