PL/pgSQL 커서

PL/pgSQL 커서 (Cursors)

쿼리 전체를 한 번에 실행하는 대신, 쿼리를 커서(cursor)에 담아 두고 결과를 몇 행씩 읽어 나가고 싶을 때가 있어요. 그 주된 이유는 결과에 행이 아주 많을 때 메모리가 넘치지 않게 하려는 거예요. 사실 PL/pgSQL 사용자는 그걸 크게 걱정하지 않아도 되는데, FOR 루프가 내부적으로 커서를 자동으로 써서 메모리 문제를 피해 주기 때문이에요. 더 흥미로운 용법은 함수가 만든 커서에 대한 참조를 반환해서, 호출한 쪽이 행을 읽어 가게 하는 거예요. 이러면 큰 결과 집합을 함수에서 효율적으로 돌려줄 수 있어요. 커서를 어떻게 다루는지 살펴볼게요.

출처: 공식문서

커서 변수 선언하기

PL/pgSQL에서 커서에 접근하는 모든 경로는 커서 변수를 통해 이뤄져요. 커서 변수는 항상 특별한 데이터 타입 refcursor예요. 만드는 방법은 둘이에요. 하나는 그냥 refcursor 타입 변수로 선언하는 거고, 다른 하나는 커서 선언 문법을 쓰는 거예요.

일반 문법은 이렇게 생겼어요.

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

(FOR는 Oracle 호환을 위해 IS로 바꿔 쓸 수 있어요.) SCROLL을 지정하면 커서가 뒤로 스크롤할 수 있고, NO SCROLL을 지정하면 뒤로 가는 fetch가 거부돼요. 둘 다 안 쓰면 뒤로 가는 fetch를 허용할지가 쿼리에 따라 결정돼요. arguments는 커서를 열 때 나중에 치환될 파라미터 이름·타입의 쌍을 쉼표로 나열한 거예요.

예시를 볼게요.

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

세 변수 모두 데이터 타입은 refcursor예요. 다만 첫 번째(curs1)는 아무 쿼리나 쓸 수 있는 unbound(바인딩 안 됨) 커서이고, 두 번째는 이미 정해진 쿼리가 바인딩된 커서, 세 번째는 파라미터화된 쿼리가 바인딩된 커서예요.

주의할 점: 커서 쿼리가 FOR UPDATE/SHARE를 쓰면 SCROLL 옵션을 쓸 수 없어요. 또 휘발성(volatile) 함수를 포함하는 쿼리에는 NO SCROLL을 쓰는 게 좋아요. SCROLL 구현은 쿼리 출력을 다시 읽으면 일관된 결과를 준다고 가정하는데, 휘발성 함수는 그렇지 않을 수 있거든요.

커서 열기 (Opening Cursors)

커서로 행을 가져오려면 먼저 열어야 해요. (이는 SQL의 DECLARE CURSOR 명령과 같은 동작이에요.) PL/pgSQL의 OPEN 문장은 세 가지 형태가 있는데, 둘은 unbound 커서 변수를 쓰고 하나는 bound 커서 변수를 써요.

참고로 bound 커서 변수는 명시적으로 열지 않고도 FOR 문장으로 쓸 수 있어요. FOR 루프가 커서를 열고, 루프가 끝나면 다시 닫아 줘요.

커서를 열면 서버 내부에 portal이라는 데이터 구조가 만들어져서 커서 쿼리의 실행 상태를 담아요. portal에는 이름이 있고, portal이 존재하는 동안 그 이름은 세션 안에서 유일해야 해요. 기본적으로 PL/pgSQL은 만드는 각 portal에 유일한 이름을 부여해요. 다만 커서 변수에 null이 아닌 문자열 값을 할당하면 그 문자열이 portal 이름으로 쓰여요.

OPEN FOR query — unbound 커서에 쿼리 넣기

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

커서 변수가 열리고 주어진 쿼리를 실행하도록 설정돼요. 커서는 이미 열려 있으면 안 되고, unbound 커서 변수(즉 단순 refcursor 변수)로 선언되어 있어야 해요. 쿼리는 SELECT이거나 행을 반환하는 다른 것이어야 해요(EXPLAIN 등). PL/pgSQL 변수 이름이 치환되고 쿼리 플랜은 재사용을 위해 캐시돼요. 변수를 커서 쿼리에 치환할 때 치환되는 값은 OPEN 시점의 값이에요. 이후 변수 값을 바꿔도 커서 동작에는 영향이 없어요.

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE — 동적 쿼리로 열기

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                     [ USING expression [, ... ] ];

커서가 열리고 지정된 쿼리를 실행하도록 설정돼요. 쿼리를 문자열 표현식으로 지정한다는 점이 EXECUTE 명령과 같아요. 이러면 실행마다 쿼리 플랜이 달라질 수 있는 유연성이 생기고, 동시에 명령 문자열에는 변수 치환이 일어나지 않아요. EXECUTE처럼 format()과 USING으로 동적 명령에 파라미터 값을 넣을 수 있어요.

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

여기서는 테이블 이름을 format()으로 넣고, col1과 비교할 값은 USING 파라미터로 넣어 따옴표가 필요 없어요.

bound 커서 열기

OPEN bound_cursorvar [ ( [ argument_name { := | => } ] argument_value [, ...] ) ];

선언할 때 쿼리가 바인딩된 커서 변수를 여는 형태예요. 커서는 이미 열려 있으면 안 되고, 커서가 인자를 받도록 선언됐다면 반드시 그 인자 값들을 줘야 해요. bound 커서의 쿼리 플랜은 항상 캐시 가능으로 간주되고, 이 경우엔 EXECUTE 같은 것이 없어요. 커서의 스크롤 동작은 이미 결정됐으므로 OPEN에서 SCROLL/NO SCROLL을 지정할 수 없어요.

인자 값은 위치(positional) 표기나 이름(named) 표기로 전달할 수 있고, 함수 호출처럼 섞어 쓸 수도 있어요.

OPEN curs2;
OPEN curs3 (42);

커서 사용하기 (Using Cursors)

커서가 열리면 아래 문장들로 조작할 수 있어요. 참고로 이런 조작은 커서를 연 함수와 같은 함수에서 할 필요가 없어요. 함수 밖으로 refcursor 값을 반환해서 호출한 쪽이 커서를 다루게 할 수 있어요. 내부적으로 refcursor 값은 커서의 활성 쿼리를 담은 portal의 문자열 이름일 뿐이라, 이 이름을 여기저기 전달하고 다른 refcursor 변수에 할당해도 portal은 흔들리지 않아요.

주의: 모든 portal은 트랜잭션 종료 시 암묵적으로 닫혀요. 그래서 refcursor 값은 트랜잭션이 끝날 때까지만 열린 커서를 가리키는 데 쓸 수 있어요.

FETCH — 행 가져오기

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH는 커서에서 지정된 방향으로 다음 행을 가져와서 target에 넣어요. target은 row 변수, record 변수, 또는 쉼표로 구분된 단순 변수 목록일 수 있어요. 적당한 행이 없으면 target이 NULL로 설정돼요. SELECT INTO처럼 특별 변수 FOUND로 행을 얻었는지 확인할 수 있어요. 행을 얻지 못하면 커서는 이동 방향에 따라 마지막 행 뒤 또는 첫 행 앞에 위치해요.

direction 절은 여러 행을 가져올 수 있는 것들을 제외한 SQL FETCH의 변형들이 허용돼요. 곧 NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, BACKWARD예요. direction을 생략하면 NEXT를 지정한 것과 같아요. count를 쓰는 형태에서 count는 정수 값 표현식이면 되는데, SQL FETCH는 정수 상수만 허용하는 점과 달라요. 뒤로 이동하는 direction은 커서가 SCROLL 옵션으로 선언·열리지 않았다면 실패할 가능성이 커요.

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE — 움직이기만 하기

MOVE [ direction { FROM | IN } ] cursor;

MOVE는 데이터를 가져오지 않고 커서만 다시 위치시키는 문장이에요. FETCH처럼 동작하되 이동한 행을 반환하지 않아요. direction 절은 여러 행을 가져올 수 있는 것들을 포함해 SQL FETCH가 허용하는 모든 변형이 허용되고, 커서는 그중 마지막 행에 위치해요. 다만 direction 절이 그냥 키워드 없는 count 표현식인 경우는 PL/pgSQL에서 폐기(deprecated) 됐어요. 그 문법이 direction을 생략한 경우와 모호해서, count가 상수가 아니면 실패할 수 있어요. SELECT INTO처럼 FOUND로 이동할 행이 있었는지 확인할 수 있어요.

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

커서가 테이블 행 위에 위치해 있으면, 그 커서로 그 행을 식별해 갱신·삭제할 수 있어요. 커서 쿼리에 제한이 있고(특히 그룹화 금지), 커서에 FOR UPDATE를 쓰는 게 가장 좋아요.

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE — 닫기

CLOSE cursor;

CLOSE는 열린 커서의 바탕이 되는 portal을 닫아요. 트랜잭션 종료보다 일찍 자원을 해제하거나, 커서 변수를 다시 열 수 있게 비우는 데 쓸 수 있어요.

CLOSE curs1;

커서 반환하기 (Returning Cursors)

PL/pgSQL 함수는 커서를 호출한 쪽에 반환할 수 있어요. 특히 결과 집합이 매우 클 때 여러 행·여러 컬럼을 반환하는 데 유용해요. 함수가 커서를 열고 커서 이름을 호출자에게 반환하면, 호출자가 그 커서에서 행을 가져와요. 커서는 호출자가 닫거나, 트랜잭션이 닫힐 때 자동으로 닫혀요.

portal 이름은 프로그래머가 지정하거나 자동 생성돼요. portal 이름을 지정하려면 커서를 열기 전에 refcursor 변수에 문자열을 할당하면 돼요. 그 문자열 값이 OPEN에서 바탕 portal의 이름으로 쓰여요. 만약 refcursor 변수 값이 null(기본)이면, OPEN이 기존 portal과 충돌하지 않는 이름을 자동 생성해 그 변수에 할당해요.

참고: PostgreSQL 16 이전에는 bound 커서 변수가 자신의 이름을 담도록 초기화돼서, 바탕 portal 이름이 기본적으로 커서 변수 이름과 같았어요. 그런데 이러면 다른 함수의 비슷한 이름을 가진 커서끼리 충돌 위험이 커서 바뀌었어요.

호출자가 커서 이름을 제공하는 예시:

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

이름을 자동 생성하는 예시:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

한 함수에서 여러 커서를 반환하는 예시:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

커서 결과 루프 돌기

커서가 반환하는 행들을 순회하는 FOR 문장의 변형이 있어요.

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name { := | => } ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

커서 변수는 선언할 때 어떤 쿼리에 바인딩되어 있어야 하고, 이미 열려 있으면 안 돼요. FOR 문장이 커서를 자동으로 열고, 루프가 끝나면 다시 닫아요. 커서가 인자를 받도록 선언됐다면 반드시 인자 값을 줘야 하고, OPEN 때와 같은 방식으로 쿼리에 치환돼요.

recordvar 변수는 record 타입으로 자동 정의되고 루프 안에서만 존재해요. 커서가 반환한 각 행이 이 record 변수에 차례대로 할당되며 루프 본문이 실행돼요.

더 알아보기 (Learn more)

  • PL/pgSQL 제어 구조: Section 41.6. Control Structures
  • 동적 SQL 실행: Section 41.6.2 EXECUTE
  • 커서 관련 SQL 명령 개요: DECLARE reference page