호출 예시

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

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

정의

-- ---------------------------------------------------------------------------------------------
-- INSERT 문 생성
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - INSERT * FROM GENERATE_INSERT('테이블명'); -- public 스키마: 스키마 이름 생략
--   - INSERT * FROM GENERATE_INSERT('스키마명.테이블명');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GENERATE_INSERT;
CREATE FUNCTION GENERATE_INSERT(table_name text, add_comment_margin int = 0)
RETURNS text AS $$
DECLARE
    column_name text;
    column_name_camel text;
    column_comment text;
    column_len int;
    query_txt text := 'INSERT INTO ';
    query_txt2 text := '';
    first_column boolean := true;
    max_column_len int;
    table_comment text;
BEGIN
    -- 스키마명이 명시되지 않은 경우 public
    IF strpos(table_name, '.') = 0 THEN
        table_name := 'public.' || table_name;
    END IF;

    -- 해당 테이블의 컬럼 최대 길이 조회
    SELECT MAX(LENGTH(attname)) + add_comment_margin
      FROM pg_catalog.pg_attribute
     WHERE attrelid = table_name::regclass
       AND attnum > 0
       AND NOT attisdropped
    INTO max_column_len;
    
    -- 테이블 코멘트 조회
    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;
    
    query_txt := query_txt || table_name;
    
    IF table_comment IS NOT NULL THEN
        query_txt := query_txt || ' -- ' || table_comment;
    END IF;
    
    query_txt := query_txt || chr(10) || '(' || chr(10);

    -- Loop: 컬럼명, 컬럼 코멘트 조회
    FOR column_name, column_comment IN
        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
    LOOP
        IF NOT first_column THEN
            query_txt := query_txt || chr(10) || ' ' || ',';
            query_txt2 := query_txt2 || chr(10) || ' ' || ',';
        ELSE
            first_column := false;
            query_txt := query_txt || '  ';
            query_txt2 := query_txt2 || '  ';
        END IF;
        
        column_len := LENGTH(column_name);
        column_name_camel := LOWER(LEFT(REPLACE(INITCAP(column_name), '_', ''), 1)) || RIGHT(REPLACE(INITCAP(column_name), '_', ''), -1);
        
        query_txt := query_txt || column_name;
        query_txt2 := query_txt2 || '#{' || column_name_camel || '}';
        
        IF column_comment IS NOT NULL THEN
            query_txt := query_txt || REPEAT(' ', GREATEST(max_column_len - column_len, 0)) || ' -- ' || column_comment;
        END IF;
    END LOOP;
    
    query_txt := query_txt || chr(10) || ')' || chr(10) || 'VALUES' || chr(10) || '(' || chr(10);
    query_txt := query_txt || query_txt2 || chr(10) || ');';
    
    RAISE NOTICE '%', chr(10) || query_txt;
    RETURN query_txt;
END;
$$ LANGUAGE plpgsql;