PostgreSQL 함수, 프로시저, 트리거

2026-06-05Views 77약 7분생성형 AI 활용

Summary

데이터베이스 안에 로직을 두는 이유

지금까지는 SQL을 직접 작성해 데이터를 조회하고 변경했다. 실무에서는 같은 계산식이나 검증 규칙이 여러 곳에서 반복된다. 쇼핑몰 관리자 화면, 고객 앱, 정산 배치가 모두 주문 금액을 계산한다면 같은 로직이 세 군데에 흩어진다. 한 곳만 수정하고 다른 곳을 놓치면 결과가 달라진다.

이때 일부 로직을 PostgreSQL 안에 둘 수 있다. 함수는 값을 계산하거나 재사용 가능한 쿼리를 감싼다. 프로시저는 여러 단계를 하나의 작업으로 묶어 실행한다. 트리거는 테이블에 INSERT, UPDATE, DELETE가 일어날 때 자동으로 실행되는 규칙이다.

물론 모든 로직을 데이터베이스에 넣자는 뜻은 아니다. 화면 흐름, 외부 API 호출, 복잡한 비즈니스 정책은 애플리케이션에 두는 편이 낫다. 데이터베이스 함수와 트리거는 여러 애플리케이션이 공유해야 하는 계산, 데이터 무결성, 감사 기록처럼 데이터 가까이에 있어야 안전한 작업에 적합하다.

PostgreSQL 함수, 프로시저, 트리거 AI 생성 이미지

SQL 함수와 PL/pgSQL 함수

PostgreSQL 함수는 호출하면 값을 돌려준다. SELECT 문 안에서 호출할 수 있고, WHERE 조건이나 계산식에도 사용할 수 있다. 반환값은 하나의 값일 수도 있고, 여러 행일 수도 있으며, 필요하면 아무 값도 돌려주지 않는 형태로 만들 수도 있다. 이 장에서는 가장 자주 쓰는 "값을 계산하는 함수"부터 살펴보자.

가장 단순한 형태는 SQL 함수다. SQL 함수는 하나의 계산식이나 짧은 조회를 감쌀 때 좋다. 예를 들어 주문 상세 테이블에서 주문 총액을 계산하는 함수를 만들 수 있다.

CREATE OR REPLACE FUNCTION order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
  SELECT coalesce(sum(quantity * unit_price), 0)
  FROM order_items
  WHERE order_id = p_order_id;
$$;

사용은 일반 함수 호출과 같다.

SELECT order_total(1001) AS total_amount;

또는 주문 목록과 함께 사용할 수도 있다.

SELECT
  o.order_id,
  o.status,
  order_total(o.order_id) AS total_amount
FROM orders o
WHERE o.order_id = 1001;

LANGUAGE sql 함수는 SQL만으로 표현할 수 있는 로직에 적합하다. 조건문, 반복문, 예외 처리가 필요하면 PL/pgSQL 함수를 사용한다. PL/pgSQL은 PostgreSQL에서 제공하는 절차형 언어다. 변수, IF, LOOP, RAISE EXCEPTION 같은 문법을 쓸 수 있다.

다음 함수는 할인 금액을 계산하기 전에 입력값을 검증한다.

CREATE OR REPLACE FUNCTION discounted_amount(
  p_amount numeric,
  p_discount_rate numeric
)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_amount IS NULL THEN
    RAISE EXCEPTION 'amount is required';
  END IF;

  IF p_amount < 0 THEN
    RAISE EXCEPTION 'amount must be greater than or equal to 0';
  END IF;

  IF p_discount_rate IS NULL THEN
    RAISE EXCEPTION 'discount rate is required';
  END IF;

  IF p_discount_rate < 0 OR p_discount_rate > 1 THEN
    RAISE EXCEPTION 'discount rate must be between 0 and 1';
  END IF;

  RETURN round(p_amount * (1 - p_discount_rate), 0);
END;
$$;

SELECT discounted_amount(50000, 0.15) AS payment_amount;

함수를 만들 때 IMMUTABLE, STABLE, VOLATILE 같은 특성을 지정할 수 있다. IMMUTABLE은 같은 입력이면 항상 같은 결과를 돌려준다는 뜻이다. 방금 만든 할인 계산처럼 테이블을 읽지 않고 현재 시각에도 의존하지 않는 함수에 어울린다. STABLE은 한 SQL 문장 안에서는 결과가 안정적이라는 뜻이다. 테이블 조회를 포함하지만 데이터를 바꾸지는 않는 order_total 같은 함수는 보통 STABLE로 볼 수 있다. VOLATILE은 결과가 매번 달라질 수 있음을 뜻하며 기본값이다.

이 특성은 단순한 주석이 아니다. PostgreSQL이 함수를 언제, 몇 번, 어떤 방식으로 평가할지 판단하는 데 영향을 줄 수 있다. 잘못 지정하면 실행 계획이나 결과 해석이 예상과 달라질 수 있다. 확신이 없다면 기본값을 쓰고, 성격이 분명한 함수에만 명시한다.

함수 사용 시 확인할 점

함수는 재사용성을 높이지만 성능을 숨기기도 한다. 예를 들어 주문 10만 건을 조회하면서 각 행마다 order_total(order_id)을 호출하면 내부에서 주문 상세 테이블을 10만 번 조회할 수 있다. 이런 경우에는 함수보다 조인과 GROUP BY로 한 번에 계산하는 편이 낫다.

SELECT
  o.order_id,
  coalesce(sum(oi.quantity * oi.unit_price), 0) AS total_amount
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id;

함수를 만들 때는 다음을 확인한다.

자주 반복되는 계산인가?
여러 애플리케이션에서 같은 규칙을 공유해야 하는가?
대량 조회에서 행마다 반복 호출되어 느려지지 않는가?
예외가 발생했을 때 호출한 SQL 전체가 실패해도 괜찮은가?

함수는 코드를 짧게 만들지만, 실행 비용이 사라지는 것은 아니다. 함수 내부 SQL도 실행 계획과 인덱스의 영향을 그대로 받는다. 특히 대량 데이터를 다룰 때는 "함수로 감싸면 깔끔해진다"와 "집합 기반 SQL로 한 번에 처리한다" 사이에서 균형을 잡아야 한다.

프로시저와 CALL

함수는 값을 반환하는 데 초점이 있다. 프로시저는 작업을 수행하는 데 초점이 있다. PostgreSQL에서는 CREATE PROCEDURE로 만들고 CALL로 실행한다.

일별 매출 요약 테이블을 다시 만드는 배치 작업을 예로 보자.

CREATE TABLE daily_sales_summary (
  sales_date date PRIMARY KEY,
  order_count integer NOT NULL,
  paid_amount numeric(14, 2) NOT NULL,
  refreshed_at timestamptz NOT NULL DEFAULT now()
);

특정 날짜의 요약을 삭제한 뒤 다시 계산하는 프로시저를 만든다.

CREATE OR REPLACE PROCEDURE refresh_daily_sales_summary(p_target_date date)
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM daily_sales_summary
  WHERE sales_date = p_target_date;

  INSERT INTO daily_sales_summary (
    sales_date,
    order_count,
    paid_amount,
    refreshed_at
  )
  SELECT
    p_target_date,
    count(*)::integer,
    coalesce(sum(total_amount), 0),
    now()
  FROM orders
  WHERE status = 'paid'
    AND ordered_at >= p_target_date::timestamptz
    AND ordered_at < (p_target_date + 1)::timestamptz;
END;
$$;

실행은 CALL을 사용한다.

CALL refresh_daily_sales_summary(date '2026-05-01');

SELECT *
FROM daily_sales_summary
WHERE sales_date = date '2026-05-01';

배치 작업은 검증 후 확정하는 흐름이 중요하다. 트랜잭션으로 감싸면 결과를 확인한 뒤 커밋하거나 되돌릴 수 있다.

BEGIN;

CALL refresh_daily_sales_summary(date '2026-05-01');

SELECT *
FROM daily_sales_summary
WHERE sales_date = date '2026-05-01';

COMMIT;

검증 결과가 이상하면 COMMIT 대신 ROLLBACK을 실행한다.

PostgreSQL 프로시저는 상황에 따라 트랜잭션 제어를 포함할 수도 있다. 다만 제약이 있다. 명시적인 BEGIN 안에서 호출한 프로시저는 내부에서 COMMIT이나 ROLLBACK을 마음대로 수행할 수 없다. 애플리케이션에서 호출할 때도 연결 풀, 자동 트랜잭션, 프레임워크 설정이 영향을 준다. 그래서 처음에는 프로시저 내부에서 트랜잭션을 끊기보다, 호출하는 쪽에서 BEGIN, CALL, 검증, COMMIT 흐름을 관리하는 방식이 이해하기 쉽고 안전하다.

트리거의 기본 구조

트리거는 테이블에 변경이 발생했을 때 자동으로 실행된다. PostgreSQL 트리거는 먼저 트리거 함수를 만들고, 그 함수를 특정 테이블 이벤트에 연결하는 방식으로 구성한다.

트리거 함수는 RETURNS trigger를 사용한다. 내부에서는 NEW, OLD라는 특별한 값을 사용할 수 있다. NEW는 새 행, OLD는 변경 전 행이다. INSERT에서는 보통 NEW만 있고, DELETE에서는 보통 OLD만 있다. UPDATE에서는 두 값을 모두 비교할 수 있다.

대표적인 예는 수정 시각 자동 갱신이다.

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)
EXECUTE FUNCTION set_updated_at();

이제 상품 정보가 수정되면 updated_at이 자동으로 바뀐다.

UPDATE products
SET price = price + 1000
WHERE product_id = 10
RETURNING product_id, price, updated_at;

BEFORE UPDATE 트리거는 실제 UPDATE가 반영되기 전에 실행된다. 그래서 NEW.updated_at 값을 바꿔 저장할 수 있다. FOR EACH ROW는 변경되는 행마다 실행된다는 뜻이다. 1만 행을 UPDATE하면 트리거 함수도 1만 번 실행된다.

여기서 WHEN (OLD IS DISTINCT FROM NEW) 조건은 실제로 행 내용이 달라지는 경우에만 트리거 함수를 실행하겠다는 뜻이다. 값이 전혀 바뀌지 않는 UPDATE까지 수정 시각을 바꾸고 싶지 않을 때 유용하다.

감사 로그 기록하기

감사 로그는 누가, 언제, 무엇을 바꿨는지 남기는 기록이다. 주문 상태 변경처럼 나중에 추적이 필요한 이벤트는 트리거로 기록할 수 있다.

CREATE TABLE order_status_audit (
  audit_id bigserial PRIMARY KEY,
  order_id bigint NOT NULL,
  old_status text,
  new_status text NOT NULL,
  changed_by text NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO order_status_audit (
    order_id,
    old_status,
    new_status,
    changed_by
  )
  VALUES (
    NEW.order_id,
    OLD.status,
    NEW.status,
    current_user
  );

  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_order_status_change();

상태가 실제로 바뀐 경우에만 로그를 남기기 위해 WHEN 조건을 사용했다. AFTER UPDATE이므로 주문 상태 변경이 성공한 뒤 같은 트랜잭션 안에서 감사 로그가 기록된다. 트리거 함수에서 오류가 발생하면 원래 UPDATE도 함께 실패한다.

검증은 단순해야 한다.

UPDATE orders
SET status = 'shipped'
WHERE order_id = 1001;

SELECT *
FROM order_status_audit
WHERE order_id = 1001
ORDER BY changed_at DESC;

파생 데이터 검증과 자동 계산

파생 데이터는 다른 값으로부터 계산되는 값이다. 예를 들어 주문 상세의 line_amount는 보통 quantity * unit_price로 계산된다. 가능하면 조회 시 계산하거나, 생성 칼럼, CHECK 제약 조건을 먼저 검토한다. 그래도 기존 테이블 구조나 성능상의 이유로 값을 저장해야 한다면 트리거를 사용할 수 있다.

ALTER TABLE order_items
  ADD COLUMN IF NOT EXISTS line_amount numeric(14, 2);

CREATE OR REPLACE FUNCTION fill_order_item_amount()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.quantity IS NULL OR NEW.quantity <= 0 THEN
    RAISE EXCEPTION 'quantity must be greater than 0';
  END IF;

  IF NEW.unit_price IS NULL OR NEW.unit_price < 0 THEN
    RAISE EXCEPTION 'unit price must be greater than or equal to 0';
  END IF;

  NEW.line_amount := NEW.quantity * NEW.unit_price;
  RETURN NEW;
END;
$$;

CREATE TRIGGER order_items_fill_amount
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION fill_order_item_amount();

이제 애플리케이션이 line_amount를 잘못 보내거나 아예 보내지 않아도 데이터베이스가 다시 계산한다. INSERT뿐 아니라 UPDATE 때도 다시 계산하므로, quantity나 unit_price가 바뀌면 line_amount가 함께 맞춰진다. 누군가 line_amount만 직접 수정하려고 해도 트리거가 원래 계산식으로 덮어쓴다.

하지만 이런 방식은 숨은 동작을 만든다. 개발자가 INSERT 문이나 UPDATE 문만 보고는 실제 저장 값이 바뀐다는 사실을 놓칠 수 있다. 그래서 트리거 이름, 함수 이름, 주석, 테스트를 통해 의도를 분명히 남겨야 한다.

트리거를 남용하면 생기는 문제

트리거는 데이터베이스의 마지막 방어선으로 유용하지만, 남용하면 문제를 찾기 어려워진다.

첫째, 실행 흐름이 숨는다. 애플리케이션은 단순히 UPDATE 한 줄을 실행했다고 생각하지만, 내부에서 다른 테이블 INSERT, 추가 UPDATE, 예외 발생이 이어질 수 있다.

둘째, 성능 비용이 커질 수 있다. 행 단위 트리거는 변경 행 수만큼 반복 실행된다. 대량 UPDATE나 대량 적재 작업에서는 트리거 하나가 전체 작업 시간을 크게 늘릴 수 있다.

셋째, 재귀와 연쇄 변경을 조심해야 한다. A 테이블 트리거가 B 테이블을 바꾸고, B 테이블 트리거가 다시 A 테이블을 바꾸면 예상하지 못한 반복 실행이 생길 수 있다.

넷째, 디버깅이 어렵다. 오류 메시지가 트리거 함수 내부에서 발생하면 처음 SQL만 보고 원인을 찾기 어렵다. 필요한 경우 함수 안에 RAISE NOTICE를 임시로 넣거나, 작은 테스트 데이터로 트리거만 따로 검증해야 한다.

트리거를 만들기 전에 다음 순서로 대안을 검토한다.

NOT NULL, CHECK, UNIQUE, 외래 키로 막을 수 있는가?
조회 시 계산하거나 뷰로 표현할 수 있는가?
애플리케이션 서비스 계층에 두는 편이 더 명확한가?
여러 애플리케이션이 반드시 공유해야 하는 데이터 규칙인가?
변경량이 많아져도 트리거 비용을 감당할 수 있는가?

이 질문을 통과한 규칙만 트리거로 만든다.

선택 기준 정리

함수, 프로시저, 트리거는 역할이 다르다.

함수는 값을 계산하거나 재사용 가능한 조회를 표현할 때 사용한다. SQL 함수는 단순 계산과 조회에 적합하고, PL/pgSQL 함수는 검증, 조건 분기, 예외 처리가 필요할 때 적합하다.

프로시저는 여러 단계를 가진 작업을 이름 붙여 실행할 때 사용한다. 일별 요약 재생성, 만료 쿠폰 정리, 정산 임시 테이블 생성처럼 배치성 작업에 잘 맞는다. 실행 후에는 SELECT로 결과를 검증하고 트랜잭션 범위를 명확히 한다.

트리거는 테이블 변경에 자동으로 반응해야 하는 규칙에 사용한다. 수정 시각 갱신, 감사 로그, 저장 전 값 보정처럼 데이터 자체의 일관성을 지키는 작업에 적합하다. 다만 트리거는 보이지 않는 실행 비용을 만들기 때문에 적게, 명확하게, 검증 가능하게 작성해야 한다.

데이터베이스 내부 로직의 목적은 애플리케이션을 대체하는 것이 아니다. 데이터가 어디에서 변경되든 반드시 지켜야 하는 규칙을 데이터 가까이에 두는 것이다. 이 기준을 지키면 함수와 트리거는 숨은 복잡도가 아니라 데이터 신뢰성을 높이는 도구가 된다.

책 목록

PostgreSQL 입문부터 실전 운영까지

전체 목차 보기