사용자 정의 함수 — SQL·PL/pgSQL

비슷한 SQL 을 반복해서 쓰고 있다면 — 뷰(13편) 대신 함수를 만들 시점. 매개변수를 받고 결과를 반환하는 진짜 함수. 18편은 SQL 언어와 PL/pgSQL 의 차이, 자주 만드는 패턴 (스칼라·테이블 반환·트리거) 까지.

가장 단순한 SQL 함수

CREATE OR REPLACE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE sql
AS $$
  SELECT first || ' ' || last;
$$;

-- 사용
SELECT full_name('준성', '박');   -- '준성 박'
SELECT id, full_name(first_name, last_name) FROM users;

PL/pgSQL — 절차형 언어

CREATE OR REPLACE FUNCTION transfer(
  from_id bigint,
  to_id   bigint,
  amount  numeric
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  from_balance numeric;
BEGIN
  SELECT balance INTO from_balance
  FROM   accounts WHERE id = from_id FOR UPDATE;

  IF from_balance < amount THEN
    RAISE EXCEPTION '잔액 부족: %', from_balance;
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

-- 사용
SELECT transfer(1, 2, 10000);

SQL vs PL/pgSQL — 언제 무엇

RETURNS TABLE — 결과 집합 반환

CREATE OR REPLACE FUNCTION top_orders(limit_n int)
RETURNS TABLE (
  user_id bigint,
  user_name text,
  total_rev numeric
)
LANGUAGE sql
AS $$
  SELECT u.id, u.name, SUM(o.total)
  FROM   users u
  JOIN   orders o ON o.user_id = u.id
  GROUP BY u.id, u.name
  ORDER BY SUM(o.total) DESC
  LIMIT limit_n;
$$;

-- 사용 (FROM 절에)
SELECT * FROM top_orders(10);

RETURNS SETOF — 한 컬럼/한 행 여러 개

-- 한 컬럼 여러 행
CREATE FUNCTION fibo(n int)
RETURNS SETOF int
LANGUAGE plpgsql
AS $$
DECLARE
  a int := 0;
  b int := 1;
  i int;
BEGIN
  FOR i IN 1..n LOOP
    RETURN NEXT a;
    SELECT b, a + b INTO a, b;
  END LOOP;
END;
$$;

SELECT * FROM fibo(10);
-- 0, 1, 1, 2, 3, 5, 8, 13, 21, 34

STABLE · IMMUTABLE · VOLATILE — 옵티마이저 힌트

CREATE FUNCTION add(a int, b int)
RETURNS int
LANGUAGE sql
IMMUTABLE        -- 입력 같으면 결과 항상 같음 (캐시 가능)
AS $$ SELECT a + b $$;

CREATE FUNCTION today()
RETURNS date
LANGUAGE sql
STABLE           -- 한 쿼리 안에서는 같은 값 (현재 시각 등)
AS $$ SELECT CURRENT_DATE $$;

-- VOLATILE (기본) — 매 호출마다 달라질 수 있음 (random, nextval 등)

예외 처리 — BEGIN ... EXCEPTION

CREATE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE '예상치 못한 에러: %', SQLERRM;
    RETURN NULL;
END;
$$;

트리거 함수 — 자동 실행

-- 1) 트리거 함수
CREATE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- 2) 테이블에 연결
CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- 이제 UPDATE 마다 updated_at 자동 갱신
UPDATE users SET name = '준석' WHERE id = 1;
-- updated_at 도 자동으로 NOW()

트리거 자세히는 19편에서.

실전 — UPSERT 헬퍼

CREATE FUNCTION upsert_user(
  p_id bigint,
  p_name text,
  p_email text
)
RETURNS users
LANGUAGE plpgsql
AS $$
DECLARE
  result users;
BEGIN
  INSERT INTO users (id, name, email)
  VALUES (p_id, p_name, p_email)
  ON CONFLICT (id) DO UPDATE
    SET name = EXCLUDED.name,
        email = EXCLUDED.email
  RETURNING * INTO result;

  RETURN result;
END;
$$;

안티패턴 4가지