호출 예시

SELECT * FROM LIST_SCHEMA('public');

정의

-- ---------------------------------------------------------------------------------------------
-- 스키마 내 모든 테이블명, 설명, 행 개수 조회
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - SELECT * FROM LIST_SCHEMA();
--   - SELECT * FROM LIST_SCHEMA('public');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS LIST_SCHEMA;
CREATE OR REPLACE FUNCTION LIST_SCHEMA(_schema_name TEXT DEFAULT 'public')
RETURNS TABLE(table_name TEXT, table_comment TEXT, row_count BIGINT) AS $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN
        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 = _schema_name
           AND c.relkind = 'r'  -- 일반 테이블
         ORDER BY 1 ASC
    LOOP
        table_name := table_record.table_name;
        table_comment := table_record.table_comment;
        EXECUTE format('SELECT COUNT(*) FROM %I.%I', _schema_name, table_record.table_name) INTO row_count;
        RETURN NEXT;
    END LOOP;
END; $$ 
LANGUAGE plpgsql;