호출 예시

SELECT * FROM DESCRIBE_SCHEMA('테이블명');

SELECT * FROM DESCRIBE_SCHEMA('스키마명.테이블명');

정의

-- ---------------------------------------------------------------------------------------------
-- 스키마 내 모든 테이블/컬럼 정보 조회
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS DESCRIBE_SCHEMA;
CREATE FUNCTION DESCRIBE_SCHEMA(input_schema_name TEXT)
RETURNS TABLE(
    _table_name TEXT
    ,_table_comment TEXT
    ,_column_order INTEGER
    ,_column_name TEXT
    ,_column_comment TEXT
    ,_data_type TEXT
    ,_null TEXT
    ,_default TEXT
    ,_constraints TEXT
) AS $$
DECLARE
    schema_name TEXT;
BEGIN
    schema_name := LOWER(input_schema_name);
    
    RETURN QUERY
    WITH 
    WTAB AS (
        SELECT c.relname AS _table_name,
               d.description AS _table_comment
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
         WHERE n.nspname = 'public'
           AND c.relkind = 'r'
    ),
    WCOL AS (
        SELECT 
            c.table_name::TEXT AS _table_name,
            c.column_name::TEXT AS _column_name, 
            CASE 
                WHEN (c.data_type::TEXT = 'character') THEN FORMAT('char(%s)', c.character_maximum_length)
                WHEN (c.data_type::TEXT = 'character varying') THEN FORMAT('varchar(%s)', c.character_maximum_length)
                WHEN (c.data_type::TEXT = 'timestamp with time zone') THEN 'timestamptz'
                WHEN (c.data_type::TEXT = 'integer' AND c.column_default::TEXT LIKE 'nextval%') THEN 'serial'
                ELSE c.data_type::TEXT
            END AS _data_type, 
            CASE WHEN (c.is_nullable::TEXT) = 'YES' THEN 'NULL' ELSE 'NOT NULL' END AS _null, 
            c.column_default::TEXT AS _default,
            d.description AS _column_comment,
            c.ordinal_position::INT AS _column_order
        FROM information_schema.columns c
        LEFT JOIN pg_catalog.pg_description d 
                  ON (quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass = d.objoid 
                 AND c.ordinal_position = d.objsubid
        WHERE c.table_schema = schema_name
        ORDER BY c.table_name, c.ordinal_position ASC
    ),
    WCON AS (
        SELECT 
               c.table_name::TEXT AS _table_name,
               c.column_name AS _column_name, 
               STRING_AGG(DISTINCT tc.constraint_type::TEXT, ', ') AS _constraints
          FROM information_schema.table_constraints tc 
          JOIN information_schema.constraint_column_usage AS ccu 
               USING (constraint_schema, constraint_name) 
          JOIN information_schema.columns AS c 
               ON c.table_schema  = tc.constraint_schema
              AND tc.table_name   = c.table_name 
              AND ccu.column_name = c.column_name
        WHERE c.table_schema = schema_name
        GROUP BY c.table_name, c.column_name
    )
    SELECT 
         WCOL._table_name
        ,WTAB._table_comment
        ,WCOL._column_order
        ,WCOL._column_name
        ,WCOL._column_comment
        ,WCOL._data_type
        ,WCOL._null
        ,WCOL._default
        ,COALESCE(WCON._constraints, '') AS _constraints
    FROM WCOL
    LEFT JOIN WCON
           ON WCOL._table_name  = WCON._table_name 
          AND WCOL._column_name = WCON._column_name
    LEFT JOIN WTAB
           ON WCOL._table_name  = WTAB._table_name 
    ORDER BY WCOL._table_name, WCOL._column_order;
END;
$$ LANGUAGE plpgsql;