PostgreSQL의 PL/pgSQL 튜토리얼 – 6 : 반복문

안녕하세요, GIS Developer 김형준입니다. 이번 글에서는 PL/pgSQL에서 반복문에 대해 살펴보겠습니다. PL/pgSQL에서 제공하는 반복문은 LOOP, WHILE, FOR 문이 있는데요. 하나씩 살펴보도록 하겠습니다.

먼저 예제 코드를 통해 LOOP 문을 살펴 보도록 하겠습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    res INTEGER := 0;
    i INTEGER;
BEGIN
    i := 1;

    LOOP
        res := res + i;
        
        EXIT WHEN i = n;
        
        SELECT i+1 INTO i;
    END LOOP;

    RETURN res;
END;
$$ LANGUAGE plpgsql;

위의 adder 함수는 1부터 인자로 주어진 정수값까지의 합한 누적값을 반환하는 함수입니다. 9번~15번 까지가 LOOP 반복문인데요. 필수 조건은 아니지만 반복문은 반복을 끝내기 위한 조건이 필요합니다. 12번 코드가 바로 반복문을 종료하기 위한 조건 코드로 i 값과 n 값이 같으면 반복문을 종료하게 됩니다. 여기서 14번 코드가 재미있는데요. 선택한(SELECT) 값(i+1)을 원하는 변수(i) 안으로(INTO) 대입시켜 주는 구문입니다. 이 SELECT i+1 INTO i; 문은 i := i + 1; 과 같습니다.

실행 결과는 아래와 같습니다.

다음은 WHILE 반복문을 통해 위의 adder 함수를 재작성해 보도록 하겠습니다. 아래와 같습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
    i INTEGER;
BEGIN
    i := 1;
    
    WHILE i <= n LOOP
        SELECT result + i INTO result;

        SELECT i+1 INTO i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

WHILE 반복문에 해당하는 코드는 9번 ~ 13번인데요. WHILE 반복문은 반복문의 시작과 함께 반복 조건이 지정됩니다. 이 반복 조건이 참(true)일 때 반복문 안의 코드(10번~12번)이 실행되다가, 반복 조건이 거짓(false)가 될때 반복문을 탈출합니다.

다음은 반복문 중 가장 유연한 FOR 문입니다. FOR 반복문으로 여기 adder 함수를 재작성해 보도록 하겠습니다. 아래와 같습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

위의 코드에서 반복문에 해당하는 코드는 6번~9번입니다. 반복 조건을 FOR 문에서 지정하고 있는데요. i 변수를 1..n까지, 즉 만약 n 인자값을 10이라고 한다면 i 변수는 총 10번 반복되며 각 반복에서 i의 값은 1씩 증가되어 각각 1, 2, 3, 4, 5, 6, 7, 8, 9, 10이 됩니다. 아래의 실행을 통해 이러한 내용을 살펴볼 수 있습니다.

만약 FOR 문의 반복 조건이 큰 값에서 작은 값으로 진행된다면 다음처럼 FOR 문에 REVERSE를 붙여줘야 합니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN REVERSE n..1 LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

실행해 보면 아래와 같은 결과를 볼 수 있습니다.

위의 결과를 보면, i 값에 대한 Iteration이 10부터 1까지 감소하면서 반복되는 것을 볼 수 있습니다.

이처럼 FOR 문의 증감값은 1인데요. 이 값은 변경할 수 있습니다. 다음 코드는 증가값을 2로 지정하고 있는 FOR 문입니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN 1..n BY 2 LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

실행해 보면, i 값의 반복이 1부터 시작해서 2씩 증가함으로써 3, 5, 7, 9가 되며, 아래의 실행 결과를 통해 확인할 수 있습니다.

PG/pgSQL에서 반복문의 백미는 데이타베이스의 테이블에 대한 쿼리에 대한 반복입니다.

CREATE OR REPLACE FUNCTION avg_ages()
RETURNS NUMERIC AS $$
DECLARE
    r RECORD;
    total NUMERIC := 0;
    count INTEGER := 0;
BEGIN
    FOR r IN SELECT age FROM person
    LOOP
        total := total + r.age;
        count := count + 1;
    END LOOP;

    RETURN total / count;
END;
$$ LANGUAGE plpgsql;

SELECT 문을 통한 결과셋의 반복을 위해 8번 코드의 FOR 문에서 RECORD 타입의 r 변수와 쿼리문이 필요합니다. 반복문 안에서 쿼리 결과에 대한 Row의 필드값 접근을 위해 {RECORD Type 객체}.{필드명}과 같이 접근할 수 있는데요. 위의 코드에서는 r.age와 같이 접근하고 있습니다. 실행 결과는 아래와 같습니다.

SELECT 문을 동적 쿼리로 실행할 수 있습니다. 예를 들어 아래의 코드를 살펴보면..

CREATE OR REPLACE FUNCTION avg_ages(n INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    r RECORD;
    total NUMERIC := 0;
    query TEXT;
BEGIN
    query := 'SELECT age FROM person LIMIT $1';
    FOR r IN EXECUTE query USING n
    LOOP
        total := total + r.age;
    END LOOP;

    RETURN total / n;
END;
$$ LANGUAGE plpgsql;

쿼리문에 해당하는 문자열에 대한 변수가 8번 코드입니다. 즉, $1이 동적으로 변하는 부분인데요. 쿼리문 실행 후 Row의 개수를 제한하려고 하는 것입니다. 이러한 동적 쿼리를 FOR 문에서 실행하는 것은 9번 코드입니다. %1에 해당하는 값은 FOR 문에서 USING 문을 사용해 지정할 수 있습니다.