호출 예시

CALL COPY_COMMETNS_TABLE_TO_TABLE('원본테이블명', '대상테이블명');

CALL COPY_COMMETNS_TABLE_TO_TABLE('스키마명.원본테이블명', '대상테이블명');

정의

-- ---------------------------------------------------------------------------------------------
-- 원본 테이블로부터 대상 테이블에 코멘트 복제
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - CALL COPY_COMMETNS_TABLE_TO_TABLE('원본테이블명', '대상테이블명'); -- public 스키마: 스키마 이름 생략
--   - CALL COPY_COMMETNS_TABLE_TO_TABLE('스키마명.원본테이블명', '대상테이블명');
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS COPY_COMMETNS_TABLE_TO_TABLE;
CREATE PROCEDURE COPY_COMMETNS_TABLE_TO_TABLE(source_table_name text, target_table_name text)
AS $$
DECLARE
    rcd record;
    table_comment text = '';
    exec_table_comment text = '';
    exec_column_comment text = '';
BEGIN
    -- 스키마명이 명시되지 않은 경우 public
    IF strpos(source_table_name, '.') = 0 THEN
        source_table_name := 'public.' || source_table_name;
    END IF;
    IF strpos(target_table_name, '.') = 0 THEN
        target_table_name := 'public.' || target_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) = source_table_name
    INTO table_comment;
    
    -- 테이블 코멘트를 대상으로 복제
    IF table_comment IS NOT NULL THEN
        exec_table_comment := 'COMMENT ON TABLE ' || target_table_name || ' IS ''' || table_comment || ''';';
        EXECUTE exec_table_comment;
        RAISE NOTICE '%', exec_table_comment;
    END IF;
    
    -- 컬럼 코멘트 복제
    FOR rcd IN
        WITH 
        cte_src 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 = source_table_name::regclass
               AND a.attnum > 0
               AND NOT a.attisdropped
             ORDER BY a.attnum
        ),
        cte_dst AS (
            SELECT a.attname AS column_name
              FROM pg_catalog.pg_attribute a
             WHERE a.attrelid = target_table_name::regclass
               AND a.attnum > 0
               AND NOT a.attisdropped
             ORDER BY a.attnum
        )
        SELECT
              cte_src.column_name    AS src_col_name,
              cte_dst.column_name    AS tgt_col_name,
              cte_src.column_comment AS col_comment
         FROM cte_src INNER JOIN cte_dst USING (column_name)
        WHERE cte_src.column_comment IS NOT NULL
    LOOP
        exec_column_comment = 'COMMENT ON COLUMN ' || target_table_name || '.' || rcd.tgt_col_name || ' IS ''' || rcd.col_comment || ''';';
        EXECUTE exec_column_comment;
        RAISE NOTICE '%', exec_column_comment;
    END LOOP;
END; $$
LANGUAGE 'plpgsql';