PostgreSQL 저장 프로시저 예제 튜토리얼

소개

PostgreSQL의 저장 프로시저는 중요한 역할을 하며 데이터베이스 관리에 여러 가지 이점을 제공해요. 이 튜토리얼에서는 PostgreSQL에서 저장 프로시저를 사용해 SQL 작업을 더 효율적이고 안전하게 처리하는 방법을 알아볼 거예요. 이 글은 PostgreSQL의 고급 기능을 활용해 데이터베이스 관리 역량을 높이고 싶은 소프트웨어 엔지니어와 데이터베이스 관리자를 위한 글이에요.

계속 읽기 전에 저희가 만든 오픈 소스 PostgreSQL GUI인 Beekeeper Studio를 소개해 드릴게요. 사이드바에 저장 프로시저와 함수가 표시되어 데이터베이스에 무엇이 있는지 언제든 확인할 수 있어요. PostgreSQL에서는 무료로 사용할 수 있으니 여기에서 다운로드하세요.

저장 프로시저란?

예제를 시작하기 전에 저장 프로시저의 개념을 이해하는 것이 중요해요. PostgreSQL에서 저장 프로시저는 일련의 SQL 명령을 담고 있는 미리 컴파일된 실행 객체예요. 주요 역할은 삽입, 수정, 삭제, 생성 같은 데이터베이스 작업을 수행하는 거예요. 특히 여러 단계나 복잡한 로직이 필요한 작업에 유용하며, 트랜잭션 관리를 개선하고 데이터베이스 취약점을 줄여 줘요.

참고: 이 튜토리얼의 예제와 명령은 PostgreSQL 11 이상 버전과 호환돼요.

사전 요구 사항

이 튜토리얼을 따라 하려면 다음 항목이 설치 및 설정되어 있어야 해요:

1. 로컬 머신이나 서버에 설치된 PostgreSQL
2. SQL 명령을 실행할 커맨드 라인 도구 또는 Beekeeper Studio 같은 GUI 도구

PostgreSQL에서 저장 프로시저 만들기

PostgreSQL은 전통적으로 값을 반환할 수 있는 루틴을 정의할 때 FUNCTION 키워드를 사용해 왔어요. PostgreSQL의 함수는 작업을 수행할 수 있으며, 반드시 스칼라 값이나 테이블 같은 값 또는 집합을 반환해야 해요. 또한 SQL 쿼리 안에서도 사용할 수 있어요.

PostgreSQL 11에서 도입된 PROCEDURE는 값을 반환하지 않는 루틴을 정의할 때 사용해요. 프로시저는 데이터를 직접 반환하지 않고 데이터베이스 상태를 변경하는 등의 작업을 수행하는 데 쓸 수 있어요. 프로시저는 CALL 문으로 호출하는데, 보통 SELECT 문의 일부로 호출되는 함수와는 다른 점이에요.

PROCEDURE로 테이블 업데이트하기

먼저 PostgreSQL에서 간단한 저장 프로시저를 만들어 볼게요. 직원의 급여를 업데이트하는 프로시저를 만들 거예요.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

위 코드에서 update_employee_salary는 프로시저 이름이며, 직원 ID(p_id)와 새 급여(p_salary) 두 개의 매개변수를 받아요.

PostgreSQL에서 저장 프로시저 호출하기

새로 만든 프로시저를 호출하려면 아래처럼 CALL 명령을 사용해요:

CALL update_employee_salary(1, 50000);

위 명령은 ID가 1인 직원의 급여를 50000으로 업데이트해요.

고급 활용: 조건 로직이 있는 프로시저

프로시저는 조건문이나 오류 처리 같은 더 복잡한 로직을 사용해 더 정교한 작업을 수행할 수도 있어요.

이 프로시저는 주어진 조건에 따라 직원의 부서를 업데이트하며, 프로시저 안에서 제어 구조를 사용하는 방법을 보여 줘요.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

예상 출력:

CREATE PROCEDURE

FUNCTION으로 데이터 조회하기

PROCEDURE와 달리 PostgreSQL의 FUNCTION은 반환 값이 필요한 작업에 더 적합해요. 특히 SQL 쿼리의 일부로 들어가야 하는 데이터 조회나 계산에 유용해요. 여기서는 특정 조건에 따라 정보를 조회하는 함수 예제를 만들어 볼게요.

이 함수는 ID를 기준으로 직원의 상세 정보를 가져와요. 함수가 데이터를 반환하고, 그 데이터를 SQL 쿼리에서 바로 활용하는 방법을 보여 줄 거예요.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

예상 출력:

CREATE FUNCTION

함수 호출하기

이 FUNCTION으로 데이터를 조회하려면 테이블을 쿼리할 때처럼 SELECT 문을 사용하면 돼요.

SELECT * FROM get_employee_details(1);

예상 출력:

여기서는 ID가 1인 직원의 상세 정보가 출력돼요. 해당 직원이 있으면 이름, 성, 부서 ID가 표시되고, 없으면 결과가 비어 있어요.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

마무리

이 튜토리얼에서는 PROCEDURE와 FUNCTION을 사용해 PostgreSQL에서 저장 프로시저를 작성하고 호출하는 방법을 배웠어요. 저장 프로시저 사용법을 이해하면 코드 구성, 보안, 실행 속도 면에서 이점을 얻을 수 있어요. 예제는 단순하지만 더 복잡한 작업에도 같은 개념이 그대로 적용돼요.

PostgreSQL의 저장 프로시저는 로직을 캡슐화하는 강력한 방법으로, 작업을 효율적이고 안전하게 수행할 수 있게 해 줘요. 데이터베이스 설계에 저장 프로시저를 도입하면 유지보수가 쉽고 안정적인 데이터베이스 애플리케이션을 만들 수 있어요. 이 가이드를 출발점 삼아 PostgreSQL의 프로시저 기능이 제공하는 폭넓은 가능성을 살펴보세요.