호출 예시

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

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

정의

-- ---------------------------------------------------------------------------------------------
-- DESCRIBE
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS DESCRIBE;
CREATE FUNCTION DESCRIBE(table_identifier TEXT)
RETURNS TABLE(
    _column_name TEXT, 
    _data_type TEXT, 
    _null TEXT, 
    _default TEXT,
    _constraints TEXT,
    _comment TEXT
) AS $$
DECLARE
    _schema_name TEXT;
    _table_name  TEXT;
    _table_oid    OID;
BEGIN
    table_identifier = LOWER(table_identifier);
    
    IF strpos(table_identifier, '.') > 0 THEN
        _schema_name := split_part(table_identifier, '.', 1);
        _table_name  := split_part(table_identifier, '.', 2);
    ELSE
        _schema_name := 'public';
        _table_name  := table_identifier;
    END IF;
    
    -- 테이블의 OID 획득
    SELECT oid INTO _table_oid
    FROM pg_catalog.pg_class
    WHERE relname = _table_name
    AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = _schema_name);

    RETURN QUERY
    WITH RECURSIVE 
    WCOL AS(
        SELECT 
            column_name::TEXT AS _column_name, 
            CASE 
                WHEN (data_type::TEXT = 'character') THEN FORMAT('char(%s)', character_maximum_length)
                WHEN (data_type::TEXT = 'character varying') THEN FORMAT('varchar(%s)', character_maximum_length)
                WHEN (data_type::TEXT = 'timestamp with time zone') THEN 'timestamptz'
                WHEN (data_type::TEXT = 'integer' AND column_default::TEXT LIKE 'nextval%') THEN 'serial'
                ELSE data_type::TEXT
            END
                AS _data_type, 
            CASE WHEN (is_nullable::TEXT) = 'YES' THEN 'NULL' ELSE 'NOT NULL' END AS _null, 
            column_default::TEXT AS _default,
            d.description AS _comment
        FROM 
            information_schema.columns c
        LEFT JOIN 
            pg_catalog.pg_description d 
                ON _table_oid = d.objoid 
                AND c.ordinal_position = d.objsubid
        WHERE 
            table_schema = _schema_name AND 
            table_name = _table_name
        ORDER BY ordinal_position ASC
    ),
    WCON AS (
        SELECT c.column_name AS _column_name, c.data_type AS _data_type, STRING_AGG(DISTINCT 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 tc.table_name = _table_name
        GROUP BY (c.column_name, c.data_type)
    )
    SELECT WCOL._column_name
         , WCOL._data_type
         , WCOL._null
         , WCOL._default
         , WCON._constraints
         , WCOL._comment
    FROM WCOL
         LEFT JOIN WCON
         USING (_column_name)
    ;
END;
$$ LANGUAGE plpgsql;