PostgreSQL의 PL/pgSQL 튜토리얼 – 7 : 질의 결과를 반환하는 함수

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';

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

답글 남기기 응답 취소