GIS Developer

PL/pgSQL로 만든 함수가 질의(Query)의 결과로써 테이블(Table)을 반환할 수 있습니다. 함수가 테이블을 반환한다는 의미가 어떤 것인지 실제 코드 예를 통해 보면 쉽게 이해할 수 있습니다. 먼저 예제 코드에서 사용할 person이라는 테이블이 아래와 같다고 합시다.

person 테이블에서 나이값을 지정하면 지정된 나이 이상인 레코드를 질의하여 그 결과를 반환하는 함수를 만들어 보겠습니다.

CREATE OR REPLACE FUNCTION get_persons(v INT)
RETURNS TABLE (f_name CHAR(20), f_age INT)
AS $$
BEGIN
    RETURN QUERY SELECT 
        name, age
    FROM person
    WHERE age >= v;
END; $$
LANGUAGE 'plpgsql';

위의 코드를 실행한 화면이 아래와 같은데요. get_persons(30)을 호출했으므로 30세 이상인 사람만 검색되는 것을 볼 수 있습니다.

코드에 대해 살펴보면, 이 함수는 v라는 인자를 받고 있으며 2번 코드에 반환 타입이 TABLE이라고 명시되어 있습니다. TABLE 구문 바로 뒤어 테이블의 스키마를 정의하고 있는데요. 반환하는 테이블의 스키마가 CHAR(20) 타입의 f_name과 INT 타입의 f_age 필드를 갖습니다. 실제 쿼리는 5번 코드의 QUERY 구분 바로 다음에 SELECT 구문을 지정하고 있습니다.

사실, 질의한 쿼리 결과를 그대로 반환하기 보다는 쿼리 결과에 무언가 연산을 수행해 재가공한 결과를 반환하는 것이 일반적입니다.

CREATE OR REPLACE FUNCTION get_persons(v INT)
RETURNS TABLE (f_name CHAR(20), f_age INT)
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT name, age 
             FROM person
             WHERE age >= v)
     LOOP
         f_name := r.name || '(' || r.age || ')';
         f_age := r.age - 1;
         RETURN NEXT;
     END LOOP;
END; $$
LANGUAGE 'plpgsql';

위의 함수를 보면 이름과 나이를 조합한 문자열에 대한 필드와 실제 나이에서 한 살을 뺀 나이값 필드에 대한 테이블을 반환하는 함수입니다. 실행 결과는 다음과 같습니다.

안녕하세요, 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 문을 사용해 지정할 수 있습니다.