[PostgreSQL] PL/pgSQL 함수 로직을 DB 안에 넣어 한 번에 굴린다

설치·접속: PostgreSQL 설치와 접속

부제: 결제 금액대별 할인율 같은 분기 로직을 애플리케이션마다 중복 구현하지 말고, DB 함수 하나로 만들어 SQL에서 컬럼처럼 불러 쓰고 싶을 때

-- 조건 분기가 든 계산을 함수로 캡슐화한다
CREATE FUNCTION m10_discount(total numeric) RETURNS numeric AS $$
DECLARE rate numeric;
BEGIN
  IF    total >= 1000 THEN rate := 0.20;
  ELSIF total >=  500 THEN rate := 0.10;
  ELSIF total >=  100 THEN rate := 0.05;
  ELSE  rate := 0;
  END IF;
  RETURN round(total * (1 - rate), 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT t AS 결제금액, m10_discount(t) AS 할인후
FROM (VALUES (50),(120),(600),(1500)) v(t);

 결제금액 | 할인후  
----------+---------
       50 |   50.00
      120 |  114.00
      600 |  540.00
     1500 | 1200.00
(4 rows)

PL/pgSQL은 PostgreSQL에 내장된 절차형 언어로, 변수 선언·조건 분기·반복문 같은 "코드"를 DB 안에서 실행한다. 순수 SQL로는 표현하기 껄끄러운 분기 로직을 함수로 감싸 두면, 위처럼 SELECT 안에서 일반 함수처럼 호출할 수 있다. 로직이 DB 한 곳에 모이므로 여러 애플리케이션이 같은 규칙을 공유하고, 계산이 데이터 옆에서 돌아 왕복(round-trip)도 줄어든다. 부작용이 없는 함수엔 IMMUTABLE을 붙여 옵티마이저가 결과를 재사용하게 한다.

이렇게도 쓴다

여러 행을 반환하는 함수를 만든다. (조합: RETURNS TABLE + RETURN QUERY)

CREATE FUNCTION m10_user_summary(min_qty int)
RETURNS TABLE(user_id int, orders bigint, total_qty bigint) AS $$
BEGIN
  RETURN QUERY
    SELECT o.user_id, count(*), sum(o.qty)::bigint
    FROM orders o GROUP BY o.user_id
    HAVING sum(o.qty) >= min_qty ORDER BY sum(o.qty) DESC;
END; $$ LANGUAGE plpgsql;

SELECT * FROM m10_user_summary(15);   -- 테이블처럼 FROM에 넣어 쓴다

 

반복문으로 여러 행을 순회한다. (조합: FOR ... IN LOOP)

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT id, price FROM products WHERE price > 200 LOOP
    RAISE NOTICE '상품 % 가격 %', r.id, r.price;
  END LOOP;
END $$;

 

예외를 잡아 안전하게 처리한다. (조합: EXCEPTION)

CREATE FUNCTION m10_safe_div(a numeric, b numeric) RETURNS numeric AS $$
BEGIN
  RETURN a / b;
EXCEPTION WHEN division_by_zero THEN RETURN NULL;
END; $$ LANGUAGE plpgsql;

 

쿼리 한 건만 감싸는 가벼운 함수는 SQL 함수로 충분하다. (조합: LANGUAGE sql)

CREATE FUNCTION m10_order_cnt(uid int) RETURNS bigint AS $$
  SELECT count(*) FROM orders WHERE user_id = uid;
$$ LANGUAGE sql STABLE;

 

함수 목록·정의를 확인한다. (조합: \df)

\df m10_*          -- 함수 시그니처 목록
\sf m10_discount   -- 함수 정의 소스 보기