postgresql 获取schema,table 信息

获取schema 信息

SELECT pn.oid AS schema_oid, iss.catalog_name, iss.schema_owner, iss.schema_name
FROM information_schema.schemata iss
         INNER JOIN pg_namespace pn ON pn.nspname = iss.schema_name;

获取某个schema下的表

 SELECT tablename::REGCLASS::OID AS table_oid,
        tablename AS table_name,
        obj_description(tablename::REGCLASS::OID) AS comments,
        schemaname AS schema_name,
        tableowner AS table_owner
  FROM pg_tables pt
  WHERE schemaname = #{schema}

获取某个表的字段信息

  SELECT table_schema,
               table_name,
               column_name,
               ordinal_position,
               column_default,
               data_type,
               udt_name,
               (
                   SELECT pg_catalog.col_description(c.oid, sc.ordinal_position::INT)
                   FROM pg_catalog.pg_class c
                   WHERE c.oid = (SELECT ('"' || sc.table_name || '"')::REGCLASS::OID)
                     AND c.relname = sc.table_name
               ) AS comment
   FROM information_schema.columns sc
   WHERE table_schema = #{schema}
     AND table_name = #{tableName}