PostgreSQL 저장 프로시저와 함수: 데이터베이스 효율성을 극대화하는 핵심 도구

안녕하세요, 데이터베이스 애호가 여러분! PostgreSQL은 오픈소스 관계형 데이터베이스(RDBMS) 중에서도 가장 강력하고 유연한 도구 중 하나로 평가받습니다. 오늘은 PostgreSQL에서 데이터베이스 관리와 애플리케이션 개발의 효율성을 한층 높여주는 두 가지 핵심 기능, 저장 프로시저(Stored Procedure) 와 함수(Function) 에 대해 깊이 파헤쳐보겠습니다. 이들은 단순히 쿼리 실행 속도를 빠르게 하는 데 그치지 않고, 코드 재사용성, 유지보수성, 보안성을 강화하여 견고한 시스템을 구축하는 데 필수적입니다.

PostgreSQL의 PL/pgSQL(Procedural Language/PostgreSQL) 같은 프로시저 언어를 활용하면 복잡한 비즈니스 로직을 데이터베이스 레벨에서 처리할 수 있어, 애플리케이션 코드와 데이터베이스 간의 중복을 최소화합니다. 초보자부터 전문가까지, 이 두 도구의 차이와 활용 팁을 통해 실무에서 바로 적용할 수 있는 인사이트를 얻어가세요. 자, 시작해볼까요?

728x90

저장 프로시저란 무엇인가요?

저장 프로시저는 데이터베이스 서버에 미리 저장된 SQL 문장들의 집합으로, 하나의 단위로 실행되는 '미리 컴파일된' 코드 블록입니다. 이는 개발자들이 반복되는 쿼리를 매번 작성하지 않고, 복잡한 작업을 한 번에 처리할 수 있게 해줍니다. 예를 들어, 여러 테이블을 업데이트하거나 트랜잭션을 관리하는 작업에 이상적입니다.

저장 프로시저의 주요 특징

• 캡슐화: 복잡한 비즈니스 로직을 프로시저 안으로 모아 관리하므로, 코드의 가독성과 유지보수성을 크게 향상시킵니다. 애플리케이션 개발자가 데이터베이스 내부 로직을 몰라도 쉽게 호출할 수 있어요.
• 성능 최적화: 컴파일된 상태로 저장되어 있어, 매번 쿼리를 파싱할 필요가 없어 실행 속도가 빠릅니다. 특히 대량 데이터 처리나 반복 작업(예: 배치 업데이트)에서 빛을 발합니다.
• 보안 강화: 사용자가 테이블에 직접 접근하지 못하게 하고, 프로시저 실행 권한만 부여할 수 있습니다. 이는 SQL 인젝션 방지나 민감 데이터 보호에 유용하며, 감사 추적(Audit Trail)을 쉽게 구현할 수 있어요.

저장 프로시저 예시

직원의 급여를 업데이트하면서 로그를 남기는 간단한 저장 프로시저를 보죠. 이 예시에서는 에러 핸들링도 추가해 안정성을 높였습니다.

CREATE OR REPLACE PROCEDURE update_employee_salary(
    emp_id INT, 
    new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 직원 존재 여부 확인
    IF NOT EXISTS (SELECT 1 FROM employees WHERE id = emp_id) THEN
        RAISE EXCEPTION '직원 ID %가 존재하지 않습니다.', emp_id;
    END IF;

    -- 급여 업데이트
    UPDATE employees 
    SET salary = new_salary, updated_at = CURRENT_TIMESTAMP 
    WHERE id = emp_id;

    -- 로그 테이블에 기록 (예시)
    INSERT INTO salary_logs (emp_id, old_salary, new_salary, updated_at)
    VALUES (emp_id, (SELECT salary FROM employees WHERE id = emp_id LIMIT 1 OFFSET 1), new_salary, CURRENT_TIMESTAMP);

    RAISE NOTICE '직원 ID %의 급여가 %로 업데이트되었습니다.', emp_id, new_salary;
END;
$$;

이 프로시저를 호출하는 방법은 간단합니다:

CALL update_employee_salary(101, 75000);

ID가 101인 직원의 급여를 75,000으로 업데이트하고, 로그를 남깁니다. 만약 직원이 존재하지 않으면 예외를 발생시켜 안전합니다.

함수란 무엇인가요?

PostgreSQL의 함수는 저장 프로시저와 비슷하지만, 반드시 값을 반환해야 한다는 점이 핵심 차이입니다. 반환된 값은 쿼리나 애플리케이션에서 바로 사용할 수 있어, 데이터 처리의 유연성을 더합니다. 함수는 주로 계산, 변환, 검색 같은 '결과 중심' 작업에 적합합니다.

함수의 주요 특징

• 반환 값 필수: 실행 후 단일 값(스칼라)이나 테이블(SETOF)을 반환하며, 이는 SQL 쿼리의 일부로 자연스럽게 통합됩니다. 예를 들어, SELECT 문에서 직접 호출 가능합니다.
• 쿼리 통합성: WHERE, SELECT, JOIN 절 등에서 함수를 호출할 수 있어, 데이터 분석이나 보고서 생성 시 재사용성이 높습니다. 이는 애플리케이션 로직을 데이터베이스로 옮겨 네트워크 오버헤드를 줄이는 데도 효과적입니다.

함수 예시

직원 ID를 입력받아 이름을 반환하는 함수를 예로 들어보죠. 여기서는 NULL 처리도 추가했습니다.

CREATE OR REPLACE FUNCTION get_employee_name(emp_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    emp_name TEXT;
BEGIN
    SELECT name INTO emp_name 
    FROM employees 
    WHERE id = emp_id;

    IF emp_name IS NULL THEN
        RETURN '알 수 없는 직원';
    END IF;

    RETURN emp_name;
END;
$$;

이 함수를 호출하면:

SELECT get_employee_name(101) AS employee_name;

결과: "John Doe" (ID 101인 직원의 이름). 이처럼 쿼리 내에서 쉽게 활용할 수 있습니다. 더 복잡한 예로, 여러 직원의 평균 급여를 계산하는 함수도 만들 수 있어요.

저장 프로시저 vs. 함수: 핵심 차이점

두 기능은 모두 데이터베이스 로직을 캡슐화하지만, 목적과 사용법이 다릅니다. 아래 표로 주요 차이점을 정리했습니다.

기능	저장 프로시저	함수
반환 값	없음 (OUT 매개변수로 간접 반환 가능)	필수 (스칼라, 테이블 등)
실행 컨텍스트	CALL 문으로 독립 실행	SELECT, WHERE 등 쿼리 내 호출 가능
사용 사례	복잡한 트랜잭션, 배치 처리, 관리 작업	계산, 검색, 변환, 데이터 유효성 검사
트랜잭션 처리	내부에서 COMMIT/ROLLBACK 가능	쿼리 컨텍스트에 의존

이 차이 덕분에 PostgreSQL은 다양한 시나리오에 유연하게 대응합니다.

각 기능을 언제 사용해야 할까요?

올바른 도구 선택이 성공의 열쇠입니다. 아래 가이드라인을 참고하세요.

• 저장 프로시저 추천 시나리오:
  • 여러 테이블을 아우르는 복잡한 작업(예: 주문 처리 시 재고 업데이트 + 결제 로그 + 이메일 알림).
  • 트랜잭션 원자성(Atomicity)이 중요한 배치 작업(예: 월말 정산).
  • 보안이 민감한 관리 작업(예: 사용자 권한 변경). 반환 값이 필요 없고, '작업 수행' 자체가 목적일 때 적합합니다. PL/pgSQL의 예외 처리(EXCEPTION 블록)를 활용하면 더 안정적입니다.
• 함수 추천 시나리오:
  • 쿼리에서 반복되는 계산(예: 세금 계산 함수를 보고서 쿼리에 재사용).
  • 데이터 변환이나 유효성 검사(예: 이메일 형식 검증 함수).
  • 분석 쿼리(예: 직원별 성과 지표 계산). 반환 값이 쿼리의 일부가 되어야 할 때 강력합니다. 성능을 위해 IMMUTABLE이나 STABLE 같은 volatility 속성을 지정하면 쿼리 최적화에 도움이 됩니다.

실무 팁: 프로시저는 '명령형(Imperative)' 스타일, 함수는 '함수형(Functional)' 스타일로 생각하면 선택이 쉬워집니다. 테스트는 pgAdmin이나 psql에서 간단히 해보세요!

결론: 효율적 데이터베이스 구축의 첫걸음

PostgreSQL의 저장 프로시저와 함수는 데이터베이스 개발의 생산성을 혁신적으로 높여줍니다. 이 도구들을 통해 성능을 최적화하고, 코드를 재사용하며, 보안을 강화할 수 있어요. 초보자라면 간단한 예시부터 시작해 점차 복잡한 로직으로 확장해보세요. 올바른 선택과 활용이 안정적이고 확장 가능한 시스템을 만드는 데 핵심입니다.