PL/pgSQL — SQL 프로시저 언어

PL/pgSQL — SQL 프로시저 언어 (Chapter 41. PL/pgSQL — SQL Procedural Language)

PL/pgSQL은 PostgreSQL의 **로드 가능한 프로시저 언어(loadable procedural language)**예요. SQL만으로는 표현하기 어려운 분기, 반복, 예외 처리 같은 제어 구조가 필요할 때, 이 언어로 함수를 만들어 데이터베이스 서버 안에서 복잡한 연산을 처리할 수 있어요. 이 페이지에서는 PL/pgSQL의 구조, 선언, 제어 구조, 커서, 트랜잭션, 트리거 함수까지 한 흐름으로 정리해 드릴게요.

출처: PostgreSQL 공식 문서 — plpgsql

개요 (Overview)

PL/pgSQL이 설계될 때 목표로 삼은 특징은 이러해요.

함수(function), 프로시저(procedure), 트리거(trigger)를 만들 수 있어요.
SQL 언어에 제어 구조를 더해줘요.
복잡한 계산을 수행할 수 있어요.
사용자 정의 타입, 함수, 프로시저, 연산자를 모두 물려받아요.
서버가 신뢰할 수 있는 언어로 정의할 수 있어요.
사용하기 쉬워요.

PL/pgSQL로 만든 함수는 내장 함수를 쓸 수 있는 어디에서든 사용할 수 있어요. 예를 들어 복잡한 조건 계산 함수를 만들어 두고, 나중에 그 함수로 연산자를 정의하거나 인덱스 표현식(index expression)에 사용할 수 있죠.

PostgreSQL 9.0 이후 버전에서는 PL/pgSQL이 기본 설치됩니다. 다만 여전히 로드 가능한 모듈이라서, 보안에 민감한 관리자는 이를 제거할 수도 있어요.

PL/pgSQL을 쓰면 좋은 이유

SQL은 PostgreSQL과 대부분의 관계형 데이터베이스가 쓰는 질의 언어예요. 표준적이고 배우기 쉽죠. 그런데 SQL 문장 하나하나는 데이터베이스 서버가 개별적으로 실행해야 해요. 클라이언트가 각 질의를 서버로 보내고, 처리 결과를 받아 계산하고, 다시 다음 질의를 보내는 식이죠. 이 과정마다 프로세스 간 통신이 발생하고, 클라이언트가 서버와 다른 머신에 있다면 네트워크 오버헤드까지 붙어요.

PL/pgSQL을 쓰면 계산 블록과 일련의 질의를 서버 안에 묶어 둘 수 있어요. 프로시저 언어의 힘과 SQL의 사용 편의성을 모두 얻으면서, 클라이언트/서버 간 통신 오버헤드를 크게 줄일 수 있어요.

클라이언트와 서버 사이의 왕복(round trip)이 줄어들어요.
클라이언트가 필요로 하지 않는 중간 결과를 서버 밖으로 주고받지 않아도 돼요.
여러 번의 질의 파싱을 피할 수 있어요.

PL/pgSQL에서도 SQL의 모든 데이터 타입, 연산자, 함수를 그대로 쓸 수 있어요.

지원되는 인자·결과 데이터 타입

PL/pgSQL로 작성한 함수는 서버가 지원하는 모든 스칼라 타입과 배열 타입을 인자로 받고, 그 결과를 돌려줄 수 있어요. 이름으로 지정된 복합 타입(row type)도 받거나 반환할 수 있고요. record 타입을 받는 함수로 선언하면 어떤 복합 타입이든 입력으로 받아들이고, record를 반환한다고 선언하면 결과가 호출 시점에 컬럼이 결정되는 행 타입이 돼요.

PL/pgSQL의 구조 (Structure)

PL/pgSQL 함수는 CREATE FUNCTION 명령으로 서버에 정의돼요. 보통 이런 모양이에요.

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

CREATE FUNCTION 입장에서 함수 본문은 그저 문자열 리터럴이에요. 함수 본문을 쓸 때는 일반적인 작은따옴표 문법 대신 **달러 따옴표(dollar quoting)**를 쓰는 게 편해요. 달러 따옴표를 안 쓰면 본문 안의 작은따옴표나 역슬래시를 전부 두 배로 이스케이프해야 하거든요.

PL/pgSQL은 블록 구조 언어예요. 함수 본문 전체가 하나의 블록이어야 하고, 블록은 다음과 같이 정의돼요.

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

블록 안의 각 선언과 문장은 세미콜론으로 끝나요. 다른 블록 안에 나타나는 블록은 위처럼 END 뒤에 세미콜론을 붙여야 해요. 다만 함수 본문을 끝맺는 마지막 END는 세미콜론이 필요 없어요.

팁

BEGIN 바로 뒤에 세미콜론을 쓰는 실수는 흔한데, 이건 틀린 문법이라 오류가 나요. 조심하세요.

label은 블록을 EXIT 문에서 참조하거나, 블록 안에서 선언한 변수의 이름을 한정(qualify)할 때만 필요해요. END 뒤에 label이 오면 블록 시작의 label과 반드시 일치해야 해요.

모든 키워드는 대소문자를 구분하지 않아요. 식별자(identifier)는 일반 SQL처럼 따옴표로 감싸지 않으면 자동으로 소문자로 변환돼요.

주석도 일반 SQL과 동일하게 동작해요. --는 그 줄이 끝날 때까지의 주석이고, /* ... */는 블록 주석인데 중첩이 허용돼요.

블록의 문장 섹션에는 어떤 문장이든 서브블록(subblock)이 될 수 있어요. 서브블록은 논리적 그룹화나 변수 범위 지역화에 쓰죠. 서브블록 안에서 선언한 변수는 서브블록이 유지되는 동안 바깥 블록의 같은 이름 변수를 가려요(mask). 다만 블록 라벨로 이름을 한정하면 바깥 변수에도 접근할 수 있어요.

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

선언 (Declarations)

블록에서 쓰는 모든 변수는 블록의 선언 섹션에서 선언해야 해요. 단, 두 가지 예외가 있어요. 정수 범위를 도는 FOR 루프의 루프 변수는 자동으로 정수로 선언되고, 커서의 결과를 도는 FOR 루프의 루프 변수는 자동으로 레코드 변수로 선언돼요.

PL/pgSQL 변수는 integer, varchar, char 같은 어떤 SQL 데이터 타입이든 가질 수 있어요. 선언 예시를 볼게요.

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

변수 선언의 일반 문법은 이래요.

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

DEFAULT 절은 블록에 들어갈 때 변수에 할당할 초기값을 지정해요. 없으면 SQL의 null 값으로 초기화돼요.
CONSTANT 옵션은 초기화 이후의 재할당을 막아요.
COLLATE 옵션은 변수에 사용할 콜레이션(collation)을 지정해요.
NOT NULL이 지정되면 null을 할당할 때 런타임 오류가 나요.
=는 PL/SQL 호환 := 대신 사용할 수 있어요.

변수의 기본값은 함수 호출마다가 아니라 블록에 들어갈 때마다 평가돼요. 그래서 timestamp 타입 변수에 now()를 할당하면 함수가 미리 컴파일된 시각이 아니라 현재 함수 호출 시각이 들어가요.

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
transaction_time CONSTANT timestamp with time zone := now();

선언한 변수는 같은 블록의 나중 초기화 표현식에서도 쓸 수 있어요.

DECLARE
  x integer := 1;
  y integer := x + 1;

함수 파라미터 선언

함수로 전달되는 파라미터는 $1, $2 같은 식별자로 이름 지어져요. 가독성을 위해 $n 파라미터 이름에 별칭(alias)을 달 수도 있어요.

별칭을 만드는 방법은 두 가지예요. 먼저 CREATE FUNCTION 명령에서 파라미터에 이름을 주는 방법이 있어요.

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

다른 방법은 선언 문법으로 명시적으로 별칭을 선언하는 거예요. 이 밖에도 ALIAS, %TYPE/%ROWTYPE을 이용한 타입 복사, 행 타입(row type), 레코드 타입(record type), 콜레이션 지정 등 다양한 선언 기능이 있어요.

기본 문장 (Basic Statements)

할당 (Assignment)

변수에 값을 할당하려면 :=를 써요.

variable := expression;

프로시저 이름을 왼쪽에 쓴다는 점만 빼면 SQL의 SELECT INTO와 비슷해요. 이 형식은 함수 본문 안에서만 유효해요.

SQL 명령 실행

일반적으로 함수 안의 SQL 명령은 값이 반환되는 경우에만 처리하면 돼요. 행을 반환하는 SQL 명령이 있고 그 결과가 필요하다면, 호출한 코드에 그 결과를 반환하라는 RETURN QUERY 또는 이후에 다룰 명령을 사용해야 해요.

단일 행 결과를 얻는 명령 실행

결과가 정확히 한 행인 SQL 명령의 결과를 변수에 담으려면 SELECT INTO를 써요. 복합 변수나 레코드 변수, 또는 단순 변수의 콤마 구분 목록으로 결과를 받을 수 있어요.

SELECT select_expressions INTO target FROM ...;

명령이 행을 반환하지 않으면 대상 변수에는 null이 설정돼요. 행이 여러 개면 첫 행만 대상에 저장되고 나머지는 버려져요. 추가로 STRICT 옵션을 쓰면 명령이 행을 반환하지 않거나 둘 이상 반환할 때 오류를 발생시킬 수 있어요.

동적 명령 실행 (EXECUTE)

실행할 명령을 실행 시점에 만들어야 한다면, 정적 SQL 대신 동적 명령을 써야 해요. EXECUTE를 사용하면 문자열로 만들어진 명령을 실행할 수 있어요.

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

예를 들어 테이블 이름이 실행 시점에 정해진다면 format() 함수로 명령 문자열을 만들고, 값을 USING으로 전달해요. USING으로 넘긴 값은 $1, $2처럼 참조할 수 있어요.

결과 상태 얻기 (GET DIAGNOSTICS)

GET DIAGNOSTICS를 쓰면 명령 실행 후의 상태 정보를 얻을 수 있어요. 대표적으로 ROW_COUNT로 영향받은 행 수를 알 수 있고, PG_CONTEXT로 현재 실행 위치 컨텍스트를 얻죠.

아무것도 하지 않기 (NULL)

NULL; 문은 아무것도 하지 않는 자리표시자(no-op)예요. 조건부 분기의 한 갈래가 아무 일도 안 해야 할 때 유용해요.

제어 구조 (Control Structures)

제어 구조는 아마 PL/pgSQL에서 가장 유용하고 중요한 부분이에요. 이 제어 구조 덕분에 PostgreSQL 데이터를 매우 유연하고 강력하게 다룰 수 있죠.

함수에서 값 반환하기

데이터를 반환하는 명령은 두 가지가 있어요: RETURN과 RETURN NEXT.

RETURN은 표현식과 함께 쓰면 함수를 종료하고 그 값을 호출자에게 돌려줘요. 집합을 반환하지 않는 함수에 쓰는 형태죠.

RETURN expression;

스칼라 타입을 반환하는 함수에서는 표현식 결과가 자동으로 함수의 반환 타입으로 캐스팅돼요. 하지만 복합(row) 값을 반환하려면 정확히 요구되는 컬럼 집합을 만들어내는 표현식을 써야 해요.
출력 파라미터(output parameter)로 선언했다면 표현식 없이 RETURN;만 쓰면 돼요. 그때 출력 파라미터 변수의 현재 값이 반환돼요.
함수가 void를 반환하도록 선언했다면, RETURN;으로 함수를 일찍 종료할 수 있어요.
함수의 반환 값은 정의되지 않은 채 남을 수 없어요. 최상위 블록 끝까지 RETURN을 만나지 않으면 런타임 오류가 나요. (출력 파라미터가 있거나 void를 반환하는 함수에는 적용되지 않아요.)

예시:

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

RETURN NEXT와 RETURN QUERY는 함수가 SETOF sometype을 반환하도록 선언된 경우에 써요. 반환할 항목들을 RETURN NEXT 또는 RETURN QUERY로 하나씩 지정하고, 마지막에 인자 없는 RETURN;으로 함수가 끝났음을 알려요.

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

RETURN NEXT는 스칼라와 복합 타입 모두에 쓸 수 있어요. 복합 반환 타입에서는 결과의 "테이블" 전체를 돌려줄 수 있죠.

프로시저에서 반환하기

프로시저(procedure)는 함수와 달리 반환 값이 없어요. 프로시저의 최상위 블록이 끝나면 자동으로 반환돼요. RETURN;을 사용해 일찍 종료할 수도 있는데, 이때는 표현식을 쓰면 안 돼요.

프로시저 호출

프로시저는 CALL 명령으로 호출해요. 프로시저에서 트랜잭션 관리 문장(COMMIT 등)을 사용하면, 그 문장은 프로시저가 반환되기 전에 실행돼요. 호출하는 쪽에서 나중에 실행하는 게 아니라는 점을 기억하세요.

조건문 (Conditionals)

IF 문은 여러 형태가 있어요.

IF boolean-expression THEN
    statements
END IF;

IF ... THEN ... ELSE ... END IF;, 그리고 다중 분기를 위한 IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF; 형태가 있어요. 조건이 참일 때 실행할 문장들을 지정하는 구조죠.

단순 루프 (Simple Loops)

LOOP
    statements
END LOOP;

LOOP는 무한 루프를 만들어요. 보통 EXIT나 RETURN으로 빠져나와야 해요. EXIT는 현재 루프나 블록을 끝내는 데 쓰고, CONTINUE는 현재 루프의 나머지 문장을 건너뛰고 다음 반복으로 넘어가요.

FOR 루프는 정수 범위를 반복할 수도 있어요.

FOR i IN 1..10 LOOP
    ...
END LOOP;

WHILE 조건 루프도 지원되요.

질의 결과 반복하기 (FOR IN query)

FOR 루프는 질의의 결과 행들을 하나씩 반복할 수 있어요.

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

target은 레코드 변수, 행 변수, 또는 단순 변수의 콤마 구분 목록이 될 수 있어요. 질의가 아무 행도 반환하지 않으면 루프 본문은 실행되지 않아요. FOR ... IN EXECUTE로 동적 질의 결과를 반복할 수도 있어요.

배열 반복하기 (FOREACH)

FOREACH는 배열의 요소를 하나씩 반복할 때 써요. FOR와 달리 배열 자체를 통째로 반복하는 게 아니라 각 요소를 반복한다는 점이 달라요.

FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP;

오류 잡기 (EXCEPTION)

오류가 발생했을 때 처리하려면 BEGIN ... EXCEPTION ... END 블록을 써요. 오류가 나면 그 블록의 문장들은 롤백되고, 제어는 EXCEPTION 섹션으로 넘어가요.

BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements ... ]
END;

WHEN 절의 조건으로는 SQLSTATE 코드나 조건 이름(예: division_by_zero, unique_violation)을 쓸 수 있어요. OTHERS는 그 밖의 모든 오류를 잡아요.

실행 위치 정보 얻기

GET STACKED DIAGNOSTICS와 PG_CONTEXT를 이용하면 오류가 발생한 실행 위치에 대한 정보를 얻을 수 있어요. 디버깅에 유용하죠.

커서 (Cursors)

전체 질의를 한 번에 실행하는 대신, 질의를 **커서(cursor)**로 감싸서 결과를 몇 행씩 읽을 수 있어요. 결과가 매우 많은 행을 가질 때 메모리 오버런을 피하려는 목적이 대표적이에요.

커서 변수 선언

커서 변수는 다음과 같이 선언해요.

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

FOR는 Oracle 호환을 위해 IS로 바꿀 수 있어요.
SCROLL을 지정하면 커서가 뒤로 스크롤될 수 있고, NO SCROLL이면 뒤로 가져오기(backward fetch)가 거부돼요.
arguments는 질의에서 이름이 파라미터 값으로 치환될 name datatype 쌍의 콤마 구분 목록이에요. 실제 값은 커서를 열 때 지정돼요.

커서 변수의 타입은 refcursor예요. 질의에 아무것도 바인딩되지 않은 커서를 unbound 커서라고 해요. 질의의 FOR UPDATE/SHARE를 쓸 때는 SCROLL 옵션을 쓸 수 없고, 휘발성(volatile) 함수를 포함한 질의에는 NO SCROLL을 쓰는 게 좋아요.

커서 열기 (OPEN)

커서로 행을 가져오기 전에 반드시 커서를 열어야 해요. PL/pgSQL에는 세 가지 형태의 OPEN 문이 있어요. 커서를 열면 서버 내부에 portal이라는 데이터 구조가 만들어지고, portal에는 세션 내에서 고유한 이름이 붙어요.

OPEN unbound_cursorvar FOR query; — 커서 변수를 열고 지정한 질의를 실행해요. 커서는 이미 열려 있으면 안 되고, unbound 커서 변수여야 해요.

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

OPEN unbound_cursorvar FOR EXECUTE query_string [ USING ... ]; — 질의를 문자열 표현식으로 지정해요. 일반 EXECUTE처럼 동적이라 질의 플랜이 실행마다 달라질 수 있어요.

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

바운드 커서(bound cursor)를 여는 형태 OPEN bound_cursorvar [(arguments)];도 있어요. 이 경우 커서의 스크롤 동작은 이미 결정돼 있으므로 SCROLL/NO SCROLL을 지정할 수 없어요. 인자는 :=나 =>를 쓰는 named notation도 지원해요.

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
OPEN curs3(key => 42);

커서 사용하기 (FETCH, MOVE)

FETCH는 커서에서 다음 행을 가져와 대상 변수에 넣어요.

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

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는 커서를 특정 위치로 이동시킬 때 써요. 그리고 커서가 테이블 행에 위치해 있을 때, 그 행을 UPDATE/DELETE ... WHERE CURRENT OF cursor로 갱신하거나 삭제할 수 있어요. 다만 커서 질의에는 제약이 있으니(특히 그룹핑 금지) 커서에 FOR UPDATE를 쓰는 게 좋아요.

모든 portal은 트랜잭션이 끝나면 암시적으로 닫혀요. 그래서 refcursor 값은 트랜잭션 끝까지 열린 커서를 참조하는 데만 쓸 수 있어요. 커서를 함수 밖으로 반환하려면 함수를 호출하는 쪽에서 트랜잭션 안에 있어야 해요.

커서 결과 반복하기 (FOR cursor)

FOR 문으로 커서의 결과를 반복할 수도 있어요. 커서 변수는 선언할 때 특정 질의에 바인딩되어 있어야 하고, 이미 열려 있으면 안 돼요. FOR 문이 커서를 자동으로 열고, 루프가 끝나면 다시 닫아요.

트랜잭션 관리 (Transaction Management)

PL/pgSQL에서는 프로시저 안에서 COMMIT과 ROLLBACK을 쓸 수 있어요. 예를 들어 대량의 행을 하나씩 커밋하면서 처리하는 프로시저를 짤 수 있어요.

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

다만 다음 사항을 기억해야 해요. 첫 번째 COMMIT이나 ROLLBACK 이후에는 커서 질의가 잡고 있던 테이블/행 락이 더 이상 유지되지 않아요.

오류와 메시지 (Errors and Messages)

오류와 메시지 보고 (RAISE)

RAISE 문으로 메시지를 보고하고 오류를 일으킬 수 있어요.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] condition_name [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] USING option { = | := } expression [, ... ];
RAISE ;

level은 오류 심각도를 지정해요. 허용되는 레벨은 DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION이고 기본값은 EXCEPTION이에요. EXCEPTION은 오류를 일으켜 보통 현재 트랜잭션을 중단시키는 반면, 나머지 레벨은 단지 우선순위가 다른 메시지만 생성해요. 어떤 우선순위의 메시지가 클라이언트로 보고되는지, 서버 로그에 기록되는지는 log_min_messages와 client_min_messages 설정 변수가 제어해요.

첫 번째 문법에서 형식 문자열(format string) 뒤에 인자 표현식을 붙이면 % 자리가 그 값으로 치환돼요. 리터럴 %를 출력하려면 %%를 써요. 인자의 개수는 형식 문자열의 % 자리 수와 일치해야 해요.

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

조건 이름이나 SQLSTATE 코드로 오류를 지정할 수도 있어요.

RAISE division_by_zero;
RAISE WARNING SQLSTATE '22012';

USING 옵션으로 오류 보고에 추가 정보를 붙일 수 있어요. 허용되는 옵션 키워드는 MESSAGE, DETAIL, HINT, ERRCODE, COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA 등이에요.

단언 확인 (ASSERT)

ASSERT 문은 조건이 거짓일 때 오류를 발생시키는 데 써요. 디버깅용으로 유용하죠.

ASSERT condition [ , message ];

단언은 기본적으로 활성화되지 않을 수 있으며, plpgsql.check_asserts 설정으로 켜고 끌 수 있어요 (확인 필요).

트리거 함수 (Trigger Functions)

PL/pgSQL은 데이터 변경이나 데이터베이스 이벤트에 대한 트리거 함수를 정의하는 데 쓸 수 있어요. 트리거 함수는 CREATE FUNCTION으로 만들되, 인자가 없고 반환 타입이 trigger(데이터 변경 트리거) 또는 event_trigger(데이터베이스 이벤트 트리거)인 함수로 선언해요. TG_...로 시작하는 특별한 지역 변수들이 트리거 호출 조건을 설명하기 위해 자동으로 정의돼요.

데이터 변경 트리거

데이터 변경 트리거에서 자동으로 생성되는 주요 특별 변수들이에요.

변수	타입	설명
`NEW`	`record`	행 수준 트리거에서 `INSERT`/`UPDATE` 연산의 새 데이터베이스 행. 문장 수준 트리거나 `DELETE`에서는 null.
`OLD`	`record`	행 수준 트리거에서 `UPDATE`/`DELETE` 연산의 이전 데이터베이스 행. 문장 수준 트리거나 `INSERT`에서는 null.
`TG_NAME`	`name`	발화된 트리거의 이름
`TG_WHEN`	`text`	트리거 정의에 따라 `BEFORE`, `AFTER`, 또는 `INSTEAD OF`
`TG_LEVEL`	`text`	`ROW` 또는 `STATEMENT`
`TG_OP`	`text`	트리거가 발화된 연산: `INSERT`, `UPDATE`, `DELETE`, 또는 `TRUNCATE`
`TG_RELID`	`oid`	트리거 호출을 일으킨 테이블의 객체 ID (`pg_class`.`oid` 참조)
`TG_RELNAME`	`name`	트리거 호출을 일으킨 테이블. deprecated라서 `TG_TABLE_NAME`을 쓰는 게 좋아요.
`TG_TABLE_NAME`	`name`	트리거 호출을 일으킨 테이블
`TG_TABLE_SCHEMA`	`name`	그 테이블의 스키마
`TG_NARGS`	`integer`	`CREATE TRIGGER`에서 트리거 함수에 준 인자의 개수
`TG_ARGV`	`text[]`	`CREATE TRIGGER`의 인자들. 인덱스는 0부터 세요.

CREATE TRIGGER에서 지정한 인자들은 함수에 직접 전달되는 게 아니라 TG_ARGV를 통해서 접근해요.

트리거 함수는 NULL 또는 트리거가 발화된 테이블과 정확히 같은 구조의 레코드/행 값을 반환해야 해요. BEFORE로 발화되는 행 수준 트리거가 null을 반환하면, 그 행에 대해 나머지 연산을 건너뛰라는 신호가 돼요(뒤따르는 트리거도 발화하지 않고 그 행의 INSERT/UPDATE/DELETE도 일어나지 않아요). null이 아닌 값을 반환하면 그 값이 연산에 사용돼요.

이벤트 트리거

이벤트 트리거는 ddl_command_start 같은 특정 데이터베이스 이벤트에서 발화돼요. 이 경우 트리거 함수는 event_trigger 타입으로 선언하고, 이벤트 정보를 담은 특별한 변수들(TG_EVENT, TG_TAG 등)이 제공돼요.

PL/pgSQL의 내부 동작 (Under the Hood)

변수 치환 (Variable Substitution)

PL/pgSQL 함수 안의 SQL 문장과 표현식은 함수의 변수와 파라미터를 참조할 수 있어요. 내부적으로 PL/pgSQL은 그런 참조를 쿼리 파라미터로 치환해요. 단, 치환은 문법적으로 허용되는 위치에서만 일어나요. 예를 들어 다음은 나쁜 프로그래밍 스타일의 극단적인 예시예요.

INSERT INTO foo (foo) VALUES (foo(foo));

첫 foo는 문법상 테이블 이름이어야 하니 치환되지 않고, 두 번째는 컬럼 이름, 세 번째는 함수 이름이라 역시 치환되지 않아요. 오직 마지막 foo만 PL/pgSQL 변수 참조 후보가 돼요. 즉 변수 치환은 SQL 명령에 데이터 값을 삽입할 수 있을 뿐, 명령이 참조하는 데이터베이스 객체를 동적으로 바꿀 수는 없어요. 객체를 동적으로 바꾸려면 명령 문자열을 동적으로 만들어야 해요.

변수 이름은 테이블 컬럼 이름과 문법적으로 다를 게 없어서, 테이블도 참조하는 문장에서는 모호함이 생길 수 있어요. 기본적으로 PL/pgSQL은 이름이 변수인지 테이블 컬럼인지 둘 다 될 수 있으면 오류를 보고해요. 해결책은 변수나 컬럼의 이름을 바꾸거나, 모호한 참조를 한정(qualify)하거나, 어떤 해석을 선호할지 지정하는 거예요. 흔한 코딩 규칙은 PL/pgSQL 변수에 컬럼 이름과 다른 네이밍 규칙(예: v_ 접두사)을 쓰는 거예요.

플랜 캐싱 (Plan Caching)

성능을 위해 PL/pgSQL은 함수의 각 SQL 명령의 실행 계획을 캐시해요. 단, 플랜이 어떤 경우에 재사용 가능한지는 상황에 따라 달라져요. 일반적으로 캐시된 플랜은 재사용되지만, 특정 조건(예: 이름 있는 테이블/함수를 참조하고 search_path가 바뀐 경우 등)에서는 처리가 달라질 수 있어 확인이 필요해요.

개발 팁 (Development Tips)

좋은 개발 방법 하나는 즐겨 쓰는 텍스트 에디터로 함수를 작성하고, 다른 창에서는 psql로 그 함수를 로드/테스트하는 거예요. 이때 CREATE OR REPLACE FUNCTION으로 작성하면 파일을 다시 로드하는 것만으로 함수 정의를 갱신할 수 있어요.

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;

psql에서 함수 정의 파일을 로드하려면:

\i filename.sql

pgAdmin 같은 GUI 도구도 프로시저 언어 개발을 편리하게 해줘요. 작은따옴표 이스케이프나 재생성·디버깅을 쉽게 해주는 기능이 있죠.

따옴표 다루기

함수 본문은 CREATE FUNCTION에서 문자열 리터럴로 지정돼요. 일반적인 작은따옴표로 감싸면 본문 안의 작은따옴표를 두 배로, 역슬래시도 두 배로 이스케이프해야 해요. 이건 지루하고 복잡한 경우엔 코드가 거의 이해 불가능해져요. 그래서 달러 따옴표(dollar-quoted) 문자열 리터럴로 본문을 쓰는 게 권장돼요. 달러 따옴표 방식에서는 따옴표를 두 배로 할 필요가 없고, 중첩 수준마다 다른 달러 따옴표 구분자만 골라 쓰면 돼요.

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;

추가 컴파일·런타임 검사

plpgsql.extra_warnings와 plpgsql.extra_errors 설정을 통해 일반 SQL보다 더 엄격한 검사를 켤 수 있어요. 예를 들어 shadowed_variables 경고는 이름이 겹치는 변수를 감지해줘요.

Oracle PL/SQL에서 이식 (Porting from PL/SQL)

PL/pgSQL 문법은 Oracle의 PL/SQL과 상당히 비슷해서 이식이 비교적 수월해요. 다만 몇 가지 차이점을 주의해야 해요.

PL/pgSQL은 함수 본문에서 BEGIN ... END 블록을 반드시 사용해야 하지만, PL/SQL의 경우 함수 전체가 하나의 BEGIN ... END 블록이 아니어도 됐어요.
:= 할당 연산자는 둘 다 쓰지만, PL/pgSQL은 =도 허용해요.
PL/pgSQL은 함수의 $1, $2 파라미터 참조를 지원하는데 PL/SQL은 :NEW, :OLD 같은 바인드 변수 체계를 써요.
Oracle의 EXECUTE IMMEDIATE는 PL/pgSQL의 EXECUTE와 비슷해요.
커서, 예외 처리 등은 개념적으로 유사하지만 문법 세부가 달라요.

이식할 때는 개별 구문 차이와 트리거/문자열 처리 방식의 차이를 하나씩 확인해 가며 옮기는 게 좋아요.

PL/pgSQL — SQL 프로시저 언어