PostgreSQL PL/pgSQL 함수 작성 완벽 가이드 — 기본 구조부터 동적 SQL까지 (dynamic table select with function)

AI 생성 이미지: PostgreSQL PL/pgSQL 함수 작성 완벽 가이드 — 기본 구조부터 동적 SQL까지 (dynamic table select with function)

핵심 개념과 실무 중심 사용법

PostgreSQL에서 함수를 만드는 방식은 크게 두 가지입니다. 간단한 연산이나 조회는 순수 SQL 함수로 처리할 수 있고, 복잡한 제어 흐름이나 반복, 예외 처리 등이 필요하면 PL/pgSQL을 사용합니다. PL/pgSQL은 절차형 언어로서 조건문, 반복문, 지역 변수 같은 프로그래밍 요소를 제공해 복잡한 비즈니스 로직을 데이터베이스 안에서 직접 구현할 수 있습니다. 성능과 유지보수 측면에서 서버 측 로직을 적절히 분리하면 이점이 큽니다.

📘 1. 기본 함수 구조

PostgreSQL 함수는 CREATE FUNCTION 문을 통해 선언합니다. 기본 형태는 다음과 같고, 실제 구현은 반환 타입과 필요한 권한에 따라 달라집니다.

CREATE OR REPLACE FUNCTION 함수이름(매개변수)
RETURNS 반환타입
LANGUAGE plpgsql
AS $function$
BEGIN
    -- 함수 내용 작성
END;
$function$;
  

• OR REPLACE : 기존 함수를 안전하게 덮어씀
• RETURNS : 스칼라 타입, 레코드, 테이블 형태 등 반환 타입 지정
• LANGUAGE plpgsql : PL/pgSQL로 작성되었음을 명시

🔍 2. 예제 함수 - 기간 내 월별 테이블 조회

아래 예제는 두 개의 날짜 입력(p_searchstartdate, p_searchenddate)을 받아 해당 기간에 해당하는 월별 테이블 이름을 조회합니다. 실무에서는 월 단위로 파티셔닝하거나 로그를 월별 테이블로 관리할 때 유용합니다.

CREATE OR REPLACE FUNCTION get_monthly_tables(
    p_searchstartdate DATE,
    p_searchenddate DATE
)
RETURNS TABLE(table_name TEXT)
LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT table_name
  FROM information_schema.tables
  WHERE table_schema = 'text'
    AND UPPER(table_name) IN (
      SELECT UPPER('SEND_DATA_LOG_' ||
      TO_CHAR(DATE_TRUNC('MONTH', GENERATE_SERIES(start_date, end_date, INTERVAL '1 MONTH')), 'YYYYMM'))
      FROM (SELECT p_searchstartdate AS start_date, p_searchenddate AS end_date) t
    );
END;
$function$;
  

위 함수는 지정된 기간의 각 월에 해당하는 테이블 이름을 information_schema에서 조회해 반환합니다. 예제에서는 테이블명 패턴을 'SEND_DATA_LOG_YYYYMM' 형태로 가정하고 있으며, 실제 스키마나 네이밍 규칙에 맞춰 쿼리를 조정해야 합니다. 또한 대규모 기간을 조회할 때는 generate_series가 생성하는 행 수를 고려해 성능을 검토하세요.

⚙️ 3. 동적 SQL을 활용한 데이터 통합 예제

각 월별 테이블에서 데이터를 모아 단일 결과 집합으로 반환하려면 동적 SQL을 사용해 여러 SELECT 문을 연결할 수 있습니다. 아래 예시는 FOR 루프와 format 함수를 사용해 안전하게 쿼리 문자열을 구성한 뒤, RETURN QUERY EXECUTE로 실행합니다.

CREATE OR REPLACE FUNCTION get_send_data(
    p_searchstartdate DATE,
    p_searchenddate DATE
)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $function$
DECLARE
    queryString TEXT := '';
    rec RECORD;
BEGIN
    -- 월별 테이블 목록 조회
    FOR rec IN
        SELECT 'SEND_DATA_LOG_' || TO_CHAR(DATE_TRUNC('MONTH', gs), 'YYYYMM') AS tbl
        FROM generate_series(p_searchstartdate, p_searchenddate, '1 month') AS gs
    LOOP
        queryString := queryString || format(
            'SELECT * FROM %I WHERE send_date BETWEEN %L AND %L UNION ALL ',
            rec.tbl, p_searchstartdate, p_searchenddate
        );
    END LOOP;

    -- 마지막 UNION ALL 제거
    queryString := left(queryString, length(queryString) - 11);

    -- 동적 SQL 실행
    RETURN QUERY EXECUTE queryString;
END;
$function$;
  

이 패턴은 유연하지만 몇 가지를 유의해야 합니다. 첫째, 동적 SQL에 테이블명이나 식별자를 직접 삽입할 때는 format(... %I ...)처럼 식별자 이스케이프를 사용해 SQL 인젝션 위험을 줄이세요. 둘째, 반환 타입을 명확히 지정하지 않으면 호출 시 컬럼 정의가 필요할 수 있습니다(예: SELECT * FROM get_send_data(...) AS t(col1 type1, ...)). 셋째, 생성된 쿼리의 길이가 길어지면 성능과 디버깅이 복잡해질 수 있으므로 로그와 실행 계획을 확인하세요.

🧠 4. 정리

• CREATE FUNCTION로 함수 선언 후 반환 타입을 신중히 선택합니다.
• 로직은 BEGIN ~ END; 블록 안에서 작성하며, 필요하면 지역 변수와 예외 처리를 사용합니다.
• 동적 SQL은 RETURN QUERY EXECUTE로 실행하여 여러 테이블의 결과를 합칠 수 있습니다.
• 월별 분할(파티셔닝)된 테이블을 통합 조회하거나 기간 기반 로그 수집에 특히 유용합니다.

PostgreSQL의 PL/pgSQL 함수는 데이터베이스 내부에서 복잡한 처리 로직을 안전하고 효율적으로 수행하도록 도와줍니다. 대용량 로그 처리, 정기 집계, ETL 단계 일부를 서버 측에서 처리하면 네트워크 비용과 애플리케이션 복잡성을 줄일 수 있습니다. 실제 적용 시에는 권한, 트랜잭션 경계, 예외 처리 정책을 명확히 정하고, 테스트 환경에서 충분히 검증한 후 운영 환경에 배포하세요.

함께 보면 좋은 엔터프라이즈 사례

• 제로트러스트 네트워크로 DevOps 보안경계 재설계 실전 가이드
• 데이터 플랫폼 품질검증과 실시간 파이프라인 최적화 핵심 전략
• SRE 관점에서의 SLA 예측과 자동복구 회로설계 실전 가이드

🚀 이 주제, 우리 서비스에 어떻게 적용할까요?

PostgreSQL PL/pgSQL 함수 작성 완벽 가이드 — 기본 구조부터 동적 SQL까지 (dynamic table select with function)를 실제 서비스와 조직에 녹여보고 싶다면, 현재 아키텍처와 운영 방식을 한 번 점검해 보는 것부터 시작해 보세요. 팀 위키나 기술 블로그, 사내 스터디 주제로도 아주 좋습니다.

이 글이 도움이 됐다면, 비슷한 엔터프라이즈 사례 글들도 함께 살펴보면서 우리 조직에 맞는 운영 상용구를 정의해 보세요.