호출 예시

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

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

정의

-- ---------------------------------------------------------------------------------------------
-- SELECT 문 생성
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - SELECT * FROM GENERATE_SELECT('테이블명'); -- public 스키마: 스키마 이름 생략
--   - SELECT * FROM GENERATE_SELECT('스키마명.테이블명');
-- ---------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS GENERATE_SELECT;
CREATE FUNCTION GENERATE_SELECT(table_name text, add_comment_margin int = 0)
RETURNS text AS $$
DECLARE
    column_name text;
    column_comment text;
    column_len int;
    select_query text := 'SELECT' || chr(10);
    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;

    -- 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
            select_query := select_query || chr(10) || ' ' || ',';
        ELSE
            first_column := false;
            select_query := select_query || '  ';
        END IF;
        
        column_len := LENGTH(column_name);
        select_query := select_query || column_name;
        
        IF column_comment IS NOT NULL THEN
            select_query := select_query || REPEAT(' ', GREATEST(max_column_len - column_len, 0)) || ' -- ' || column_comment;
        END IF;
    END LOOP;
    
    select_query := select_query || chr(10) || 'FROM ' || table_name || ';';
    
    IF table_comment IS NOT NULL THEN
        select_query := select_query || ' -- ' || table_comment;
    END IF;
    
    RAISE NOTICE '%', chr(10) || select_query;
    RETURN select_query;
END;
$$ LANGUAGE plpgsql;