호출 예시

CALL COPY_COMMETNS_TABLE_TO_VIEW('테이블명', '뷰 이름');

CALL COPY_COMMETNS_TABLE_TO_VIEW('스키마명.테이블명', '뷰 이름');

정의

-- ---------------------------------------------------------------------------------------------
-- 원본 테이블로부터 뷰에 코멘트 복제
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - CALL COPY_COMMETNS_TABLE_TO_VIEW('테이블명', '뷰 이름'); -- public 스키마: 스키마 이름 생략
--   - CALL COPY_COMMETNS_TABLE_TO_VIEW('스키마명.테이블명', '뷰 이름');
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS COPY_COMMETNS_TABLE_TO_VIEW;
CREATE PROCEDURE COPY_COMMETNS_TABLE_TO_VIEW(table_name text, view_name text)
AS $$
DECLARE
    rcd record;
    table_comment text = '';
    column_comment text = '';
    exec_table_comment text = '';
BEGIN
    -- 스키마명이 명시되지 않은 경우 public
    IF strpos(table_name, '.') = 0 THEN
        table_name := 'public.' || table_name;
    END IF;
    
    -- 테이블 코멘트 조회
    SELECT 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 || '.' || c.relname) = table_name
    INTO table_comment;
    
    -- 테이블 코멘트를 뷰로 복제
    IF table_comment IS NOT NULL THEN
        exec_table_comment := 'COMMENT ON VIEW ' || view_name || ' IS ''' || table_comment || ''';';
        EXECUTE exec_table_comment;
        RAISE NOTICE '%', exec_table_comment;
    END IF;
    
    -- 컬럼 코멘트 복제
    FOR rcd IN
        WITH cte_tbl AS (
            SELECT a.attname AS column_name,
                   pg_catalog.col_description(a.attrelid, a.attnum) AS column_comment
              FROM pg_catalog.pg_attribute a
             WHERE a.attrelid = table_name::regclass
               AND a.attnum > 0
               AND NOT a.attisdropped
             ORDER BY a.attnum
        ),
        cte_view AS (
            SELECT a.attname AS column_name
              FROM pg_catalog.pg_attribute a
             WHERE a.attrelid = view_name::regclass
               AND a.attnum > 0
               AND NOT a.attisdropped
             ORDER BY a.attnum
        )
        SELECT
              cte_tbl.column_name  AS t_col_name,
              cte_view.column_name AS v_col_name,
              cte_tbl.column_comment AS col_comment
         FROM cte_tbl INNER JOIN cte_view USING (column_name)
        WHERE cte_tbl.column_comment IS NOT NULL
    LOOP
        column_comment = 'COMMENT ON COLUMN ' || view_name || '.' || rcd.v_col_name || ' IS ''' || rcd.col_comment || ''';';
        EXECUTE column_comment;
        RAISE NOTICE '%', column_comment;
    END LOOP;
END; $$
LANGUAGE 'plpgsql';