PL/pgSQL 제어 구조 — 함수 안에서 흐름을 자유롭게 다루기

PL/pgSQL에서 가장 유용하고 중요한 부분을 꼽으라면 단연 제어 구조예요. IF와 CASE로 분기하고, LOOP·FOR·FOREACH로 반복하며, EXCEPTION으로 오류를 잡아낼 수 있다면 데이터를 매우 유연하고 강력하게 다룰 수 있습니다. 함수에서 값을 반환하는 법부터 오류 처리까지, 흐름을 어떻게 조율하는지 하나씩 볼게요.

출처: 공식문서

함수에서 값 반환하기: RETURN

함수에서 데이터를 반환하는 명령은 RETURN과 RETURN NEXT 두 가지예요.

RETURN

RETURN expression;

표현식과 함께 쓰는 RETURN은 함수를 종료하고 expression의 값을 호출자에게 돌려줍니다. 집합(set)을 반환하지 않는 함수에서 쓰는 형태예요.

스칼라 타입을 반환하는 함수라면 표현식의 결과가 할당 규칙에 따라 함수의 반환 타입으로 자동 변환돼요. 하지만 복합(행) 값을 반환하려면 요청된 열 집합을 정확히 만들어내는 표현식을 써야 해요. 명시적 캐스팅이 필요할 수 있습니다.
출력 파라미터(output parameters)로 선언했다면 RETURN만 쓰고 표현식은 붙이지 말아요. 출력 파라미터 변수들의 현재 값이 반환됩니다.
void를 반환하도록 선언했다면 RETURN으로 일찍 빠져나갈 수 있는데, RETURN 뒤에 표현식은 쓰면 안 돼요.
함수의 반환 값은 정의되지 않은 채 남을 수 없어요. 최상위 블록 끝까지 RETURN 없이 도달하면 런타임 오류가 나요. 다만 출력 파라미터가 있는 함수와 void를 반환하는 함수는 예외라서, 최상위 블록이 끝나면 RETURN이 자동으로 실행됩니다.

-- 스칼라 타입을 반환하는 함수
RETURN 1 + 2;
RETURN scalar_var;

-- 복합 타입을 반환하는 함수
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- 열 타입을 맞추려면 캐스팅 필요

RETURN NEXT와 RETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

함수가 SETOF sometype을 반환하도록 선언됐다면 절차가 조금 달라져요. 반환할 항목들을 RETURN NEXT나 RETURN QUERY 시퀀스로 지정하고, 마지막에 인자 없는 RETURN으로 "함수 실행이 끝났다"고 알려줍니다. RETURN NEXT는 스칼라·복합 타입 모두 쓸 수 있고, RETURN QUERY는 쿼리 실행 결과를 함수의 결과 집합에 추가해요. 하나의 집합 반환 함수 안에서 RETURN NEXT와 RETURN QUERY를 섞어 쓰면 결과가 이어붙여집니다.

핵심은 이 둘이 실제로 함수를 종료하지 않는다는 점이에요. 단지 결과 집합에 0개 이상의 행을 추가할 뿐이고, 실행은 다음 문장으로 이어집니다. 마지막의 인자 없는 RETURN이 제어를 함수 밖으로 빼내죠. 동적 쿼리를 실행하는 RETURN QUERY EXECUTE 변형도 있고, USING으로 파라미터를 동적 문자열에 넣을 수 있어요(EXECUTE 명령과 동일).

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        RETURN NEXT r; -- SELECT의 현재 행 반환
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

출력 파라미터가 있는 함수라면 RETURN NEXT만 써도 돼요. 각 실행마다 출력 파라미터 변수들의 현재 값이 결과의 한 행으로 저장됩니다.

📌 참고: 현재 구현에서 RETURN NEXT·RETURN QUERY는 함수를 반환하기 전에 결과 집합 전체를 저장해요. 결과 집합이 아주 크면 성능이 나빠질 수 있는데, 메모리 고갈을 피하려고 디스크에 쓰기 시작하는데 work_mem이 기준이고, 함수 자체는 결과 집합 전체가 생성될 때까지 반환하지 않아요. 메모리가 충분한 관리자는 work_mem을 키우는 걸 고려해볼 수 있어요.

프로시저에서 반환하기와 호출하기

프로시저는 반환 값이 없어서 RETURN 없이 끝날 수 있어요. 일찍 빠져나가고 싶다면 표현식 없는 RETURN만 씁니다. 출력 파라미터가 있다면 최종 값들이 호출자에게 돌아가요.

PL/pgSQL 함수·프로시저·DO 블록은 CALL로 프로시저를 호출할 수 있어요. 출력 파라미터 처리는 평범한 SQL의 CALL과 다릅니다. 프로시저의 각 OUT·INOUT 파라미터는 CALL 문 안의 변수와 대응해야 하고, 프로시저가 반환한 것은 그 변수에 다시 할당돼요.

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- 15 출력
END;
$$;

출력 파라미터와 대응하는 변수는 단순 변수나 복합 타입 변수의 필드일 수 있고, 배열의 원소는 될 수 없어요.

조건 분기: IF와 CASE

IF와 CASE 문으로 조건에 따라 다른 명령을 실행할 수 있어요. PL/pgSQL에는 세 가지 형태의 IF가 있습니다.

IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

그리고 두 가지 형태의 CASE가 있어요.

CASE ... WHEN ... THEN ... ELSE ... END CASE       -- 단순 CASE
CASE WHEN ... THEN ... ELSE ... END CASE           -- 검색 CASE

IF-THEN — 가장 단순한 형태예요. THEN과 END IF 사이 문장들은 조건이 참일 때만 실행되고, 아니면 건너뜁니다.

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE — 조건이 참이 아닐 때(조건이 NULL로 평가되는 경우 포함) 실행할 대안 문장을 정합니다.

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

IF-THEN-ELSIF — 선택지가 두 개보다 많을 때 편리해요. IF 조건들을 차례로 검사하다 처음으로 참이 되는 것을 찾으면 그 문장을 실행하고 END IF 다음으로 넘어갑니다(이후 IF 조건은 검사하지 않아요). 아무 조건도 참이 아니면 ELSE 블록(있다면)이 실행돼요. ELSIF는 ELSEIF로도 쓸 수 있어요.

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    result := 'NULL';
END IF;

단순 CASE — 피연산자의 동등성으로 조건부 실행을 해요. search-expression을 (한 번) 평가해 각 WHEN 절의 표현식과 차례로 비교하고, 일치하면 해당 문장을 실행한 뒤 END CASE 다음으로 넘어갑니다. 일치가 없으면 ELSE 문장이 실행되고, ELSE가 없다면 CASE_NOT_FOUND 예외가 발생해요.

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

검색 CASE — 불리언 표현식의 참/거짓으로 조건부 실행을 해요. 각 WHEN의 불리언 표현식을 차례로 평가해 처음 참이 되는 것을 찾고 해당 문장을 실행합니다. 참이 없으면 ELSE 문장이, ELSE가 없으면 CASE_NOT_FOUND 예외가 발생해요.

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

이 형태는 IF-THEN-ELSIF와 완전히 동등한데, 한 가지가 달라요. ELSE 절이 생략된 경우 아무것도 하지 않고 넘어가는 대신 오류가 납니다.

반복: LOOP, EXIT, CONTINUE, WHILE, FOR, FOREACH

LOOP — 무조건 반복하는 루프로, EXIT나 RETURN으로 끝날 때까지 무한히 반복해요. 선택적 라벨은 중첩 루프 안에서 EXIT·CONTINUE가 어떤 루프를 가리키는지 지정하는 데 쓰입니다.

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

EXIT — 라벨이 없으면 가장 안쪽 루프를 끝내고 END LOOP 다음 문장을 실행해요. 라벨이 있으면 현재 또는 바깥쪽 중첩 루프·블록의 라벨이어야 하며, 그 루프·블록을 종료합니다. WHEN이 지정되면 불리언 표현식이 참일 때만 종료돼요. 모든 종류의 루프에서 쓸 수 있고, BEGIN 블록과 함께 쓸 수도 있는데 그 경우 반드시 라벨을 사용해야 해요(무라벨 EXIT는 BEGIN 블록과 매칭되지 않습니다).

LOOP
    EXIT WHEN count > 0;  -- count가 0보다 크면 루프 종료
END LOOP;

CONTINUE — 라벨이 없으면 가장 안쪽 루프의 다음 반복을 시작해요. 루프 본문의 남은 문장을 건너뛰고 제어 표현식으로 돌아갑니다. WHEN이 있으면 조건이 참일 때만 다음 반복으로 넘어가요.

LOOP
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- count가 [50 .. 100]일 때의 계산
END LOOP;

WHILE — 불리언 표현식이 참인 동안 문장 시퀀스를 반복해요. 표현식은 루프 본문에 들어가기 직전마다 검사됩니다.

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 계산
END LOOP;

FOR (정수 변형) — 정수 값의 범위를 반복해요. 변수 name은 자동으로 integer 타입으로 정의되고 루프 안에서만 존재합니다. 하한·상한 두 표현식은 루프 진입 시 한 번 평가돼요. BY 절이 없으면 단계는 1이고, 있으면 그 값(역시 루프 진입 시 한 번 평가)이 단계예요. REVERSE를 지정하면 매 반복 후 단계 값을 더하는 대신 뺍니다.

FOR i IN 1..10 LOOP ... END LOOP;            -- 1..10
FOR i IN REVERSE 10..1 LOOP ... END LOOP;    -- 10..1
FOR i IN REVERSE 10..1 BY 2 LOOP ... END LOOP; -- 10,8,6,4,2

하한이 상한보다 크면(REVERSE면 그 반대) 루프 본문은 아예 실행되지 않고 오류도 나지 않아요.

쿼리 결과 반복 (FOR IN query) — 쿼리 결과를 반복하며 데이터를 다룰 수 있어요. target은 레코드 변수, 행 변수, 또는 스칼라 변수의 쉼표 구분 리스트이고, 쿼리에서 나온 각 행이 순서대로 할당된 뒤 루프 본문이 각 행마다 실행됩니다.

FOR mviews IN
   SELECT n.nspname AS mv_schema, c.relname AS mv_name, ...
   FROM pg_catalog.pg_class c
   LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
   WHERE c.relkind = 'm'
   ORDER BY 1
LOOP
    RAISE NOTICE 'Refreshing materialized view %.% ...',
                 quote_ident(mviews.mv_schema), quote_ident(mviews.mv_name);
    EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
END LOOP;

이 타입의 FOR에 쓰는 쿼리는 호출자에게 행을 반환하는 어떤 SQL 명령이든 가능해요. SELECT가 가장 흔하고, RETURNING 절이 있는 INSERT·UPDATE·DELETE·MERGE도 쓸 수 있으며 EXPLAIN 같은 유틸리티 명령도 동작합니다. FOR target IN EXECUTE text_expression [ USING ... ] 형태는 소스 쿼리를 문자열로 지정해 FOR 루프에 들어갈 때마다 재평가·재계획하는 동적 변형이에요. EXIT로 루프가 끝났다면 마지막에 할당된 행 값은 루프 뒤에서도 접근할 수 있어요.

배열 반복: FOREACH — FOREACH는 SQL 쿼리 결과가 아니라 배열 값의 원소를 반복해요. SLICE 없이(또는 SLICE 0) 쓰면 배열의 개별 원소를 반복합니다.

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

원소는 배열 차원 수와 무관하게 저장 순서대로 방문돼요. 양수 SLICE 값을 주면 단일 원소 대신 배열의 슬라이스를 반복하는데, SLICE 값은 배열 차원 수보다 크지 않은 정수 상수여야 하고 대상 변수는 배열이어야 해요.

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]] → 각 행이 {1,2,3}처럼 출력

오류 잡기: EXCEPTION

기본적으로 PL/pgSQL 함수에서 발생하는 어떤 오류든 함수 실행과 주변 트랜잭션을 중단시켜요. EXCEPTION 절이 있는 BEGIN 블록으로 오류를 잡고 회복할 수 있습니다.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

오류가 없으면 블록의 모든 문장을 실행하고 END 다음으로 넘어갑니다. 하지만 문장 실행 중 오류가 나면 남은 처리를 버리고 EXCEPTION 목록으로 제어가 넘어가요. 목록에서 발생한 오류와 일치하는 첫 조건을 찾고, 찾으면 그 핸들러 문장을 실행한 뒤 END 다음으로 넘어갑니다. 일치하는 것이 없으면 오류는 EXCEPTION 절이 없던 것처럼 밖으로 전파됩니다.

조건 이름은 부록 A의 어떤 것이든 쓸 수 있고, 카테고리 이름은 그 카테고리 안의 모든 오류와 매칭돼요. 특별 조건 이름 OTHERS는 QUERY_CANCELED와 ASSERT_FAILURE를 제외한 모든 오류 타입과 매칭됩니다. 조건 이름은 대소문자를 구분하지 않고, SQLSTATE 코드로도 지정할 수 있어요.

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...   -- 위와 동일

선택된 핸들러 문장 안에서 새 오류가 나면 이 EXCEPTION 절로 잡을 수 없고 바깥으로 전파되며, 바깥 EXCEPTION 절이 있다면 잡아낼 수 있어요.

중요한 동작: EXCEPTION 절이 오류를 잡으면 PL/pgSQL 함수의 로컬 변수는 오류 발생 시점 그대로 남지만, 블록 안에서 이뤄진 영구 데이터베이스 상태에 대한 모든 변경은 롤백됩니다. 예제를 볼게요.

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;               -- division_by_zero 오류 발생
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

y에 할당할 때 division_by_zero로 실패하고 EXCEPTION 절이 잡아요. RETURN으로 돌려주는 값은 증가된 x지만, UPDATE의 효과는 롤백됩니다. 반면 블록 앞의 INSERT는 롤백되지 않아서 최종적으로 DB에는 Joe가 아닌 Tom Jones가 남게 돼요.

💡 팁: EXCEPTION 절이 있는 블록은 없는 블록보다 진입·이탈 비용이 크게 비싸요. 꼭 필요할 때만 쓰세요.

예제의 "UPDATE 또는 INSERT" 패턴은 실제 애플리케이션에서는 INSERT ... ON CONFLICT DO UPDATE를 쓰는 게 권장됩니다. 다만 제어 구조에 대한 예시로는 유익한데, unique_violation이 INSERT 때문에 났다고 가정하고 루프를 돌며 재시도하는 로직이에요. 잡힌 오류가 기대한 것인지 확인하는 더 안전한 방법에 대해서는 다음 절에서 다뤄요.

오류 정보 얻기 + 실행 위치 확인

예외 핸들러 안에서 특수 변수 SQLSTATE(예외에 해당하는 오류 코드)와 SQLERRM(오류 메시지)로 현재 예외를 식별할 수 있어요. 이 변수들은 예외 핸들러 밖에서는 정의되지 않아요.

더 자세한 정보는 GET STACKED DIAGNOSTICS 명령을 써요.

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

각 item은 지정 변수에 할당할 상태 값을 가리키는 키워드예요(예: MESSAGE_TEXT, PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT). 예외가 어떤 항목 값을 설정하지 않았다면 빈 문자열이 반환됩니다.

EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;

실행 위치 정보를 얻으려면 GET DIAGNOSTICS의 PG_CONTEXT 항목을 써요. 호출 스택을 설명하는 텍스트 줄들을 반환하는데, 첫 줄은 현재 함수와 현재 실행 중인 GET DIAGNOSTICS 명령을, 그다음 줄부터는 호출 스택 위쪽의 호출 함수들을 가리켜요. GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT는 같은 종류의 스택 추적을 반환하되, 현재 위치가 아니라 오류가 감지된 위치를 설명합니다.

더 알아보기 (Learn more)

PL/pgSQL 개요 (Overview) — 언어 설계 목표와 장점
PL/pgSQL 문장 (Statements) — SQL 실행과 결과 다루기
PL/pgSQL 선언 (Declarations) — 변수·타입·인자 선언
PL/pgSQL 전체 문서 — 커서·트리거·동적 명령 등