PL/pgSQL 기본 문장 — 할당부터 동적 SQL 실행까지

PL/pgSQL 기본 문장 — 할당부터 동적 SQL 실행까지

PL/pgSQL이 명시적으로 이해하는 문장 유형들을 이제 하나씩 살펴볼게요. PL/pgSQL이 인식하는 특수 문장이 아닌 것은 모두 SQL 명령으로 간주되어 주 데이터베이스 엔진에 보내져 실행됩니다. 변수에 값을 넣고, SQL을 실행하고, 그 결과를 어떻게 다룰지가 이번 주제의 전부예요.

출처: 공식문서

할당 (Assignment)

PL/pgSQL 변수에 값을 넣는 것은 다음과 같이 써요.

variable { := | = } expression;

여기서 표현식은 주 데이터베이스 엔진으로 보내는 SQL SELECT 명령으로 평가되고, 단일 값(변수가 행·레코드 변수라면 행 값일 수도 있어요)을 내놓아야 해요. 대상 변수는 단순 변수(블록 이름으로 한정 가능), 행·레코드 대상의 한 필드, 또는 배열 대상의 한 원소·슬라이스일 수 있어요. PL/SQL 호환 기호 := 대신 =도 쓸 수 있습니다.

표현식 결과 타입이 변수 타입과 안 맞으면 할당 캐스팅처럼 값이 변환돼요. 해당 타입 쌍에 할당 캐스팅이 알려져 있지 않다면, 인터프리터가 결과 값을 텍스트로 변환(결과 타입의 출력 함수 + 변수 타입의 입력 함수 적용)하려고 시도합니다. 이때 결과 값의 문자열 형태가 입력 함수에 받아들여지지 않으면 입력 함수가 런타임 오류를 낼 수 있어요.

tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;

SQL 명령 실행하기

행을 반환하지 않는 SQL 명령은 일반적으로 PL/pgSQL 함수 안에 그냥 써서 실행할 수 있어요.

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

만약 명령이 행을 반환한다면(SELECT, 또는 RETURNING이 있는 INSERT/UPDATE/DELETE/MERGE) 두 가지 방법이 있어요. 최대 한 행을 반환하거나 첫 행만 관심 있다면, 명령에 INTO 절을 붙여 출력을 잡습니다(아래 41.5.3). 모든 출력 행을 처리하려면 FOR 루프의 데이터 소스로 명령을 씁니다.

정적으로 정의된 SQL만 실행하는 걸로는 부족할 때가 많아요. 값이 달라지거나, 심지어 테이블 이름이 시점마다 달라지는 등 더 근본적으로 달라지길 원하죠. 상황에 따라 두 가지 방법이 있어요.

PL/pgSQL 변수 값은 최적화 가능한(optimizable) SQL 명령에 자동으로 삽입돼요. 최적화 가능한 명령은 SELECT, INSERT, UPDATE, DELETE, MERGE, 그리고 이를 포함하는 일부 유틸리티 명령(EXPLAIN, CREATE TABLE ... AS SELECT)이에요. 이 명령들 텍스트에 나타나는 PL/pgSQL 변수 이름은 쿼리 파라미터로 대체되고, 런타임에 변수의 현재 값이 파라미터 값으로 제공됩니다. 이렇게 실행하면 PL/pgSQL은 그 명령의 실행 계획을 캐시해서 재사용할 수 있어요.

반면 최적화 불가능한(utility) 명령은 쿼리 파라미터를 받을 수 없어서, PL/pgSQL 변수의 자동 대체가 동작하지 않아요. 유틸리티 명령에 비상수 텍스트를 포함하려면 명령을 문자열로 만들어 EXECUTE 해야 해요. 테이블 이름을 바꾸는 것처럼 데이터 값 제공이 아닌 다른 방식으로 명령을 수정하고 싶을 때도 EXECUTE를 써야 합니다.

표현식이나 SELECT 쿼리를 평가하되 결과를 버리고 싶을 때도 있어요(부수 효과만 있고 유용한 결과 값이 없는 함수를 호출할 때 같은 경우). 그럴 땐 PERFORM 문장을 써요.

PERFORM query;

이것은 query를 실행하고 결과를 버립니다. 쿼리는 SQL SELECT처럼 쓰되 처음 키워드 SELECT를 PERFORM으로 바꾸면 돼요. WITH 쿼리는 PERFORM 다음에 쿼리를 괄호로 묶어서 써요(이 경우 쿼리는 한 행만 반환할 수 있어요). PL/pgSQL 변수가 쿼리에 대체되고 계획도 똑같이 캐시되며, 쿼리가 하나 이상의 행을 만들면 특수 변수 FOUND가 true, 아니면 false가 됩니다.

📌 참고: SELECT를 직접 쓰면 되지 않을까 기대할 수 있는데, 현재 유일하게 받아들여지는 방법은 PERFORM이에요. 행을 반환할 수 있는 SELECT 같은 SQL 명령은 다음 절에서 다룰 INTO 절이 없으면 오류로 거부됩니다.

단일 행 결과 실행 (INTO 절)

단일 행(여러 열일 수도 있어요)을 만드는 SQL 명령의 결과는 레코드 변수, 행 타입 변수, 또는 스칼라 변수 리스트에 할당할 수 있어요. 기본 SQL 명령에 INTO 절을 붙이면 됩니다.

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
MERGE ... RETURNING expressions INTO [STRICT] target;

target은 레코드 변수, 행 변수, 또는 단순 변수와 레코드/행 필드의 쉼표 구분 리스트예요. INTO 절을 뺀 나머지 명령에는 PL/pgSQL 변수가 위에서처럼 대체되고 계획도 동일하게 캐시됩니다.

💡 주의: 이 SELECT ... INTO 해석은 PostgreSQL의 일반 SELECT INTO 명령(INTO 대상이 새로 생성되는 테이블)과 상당히 달라요. PL/pgSQL 함수 안에서 SELECT 결과로 표를 만들고 싶다면 CREATE TABLE ... AS SELECT를 쓰세요.

대상으로 행 변수·변수 리스트를 쓰면 명령의 결과 열이 대상 구조와 수·데이터 타입이 정확히 일치해야 해요. 아니면 런타임 오류가 나요. 레코드 변수가 대상이면 명령 결과 열의 행 타입에 자동으로 맞춰집니다.

  • STRICT를 지정하지 않으면 대상은 명령이 반환한 첫 행으로, 행이 없으면 null로 설정돼요. ("첫 행"은 ORDER BY를 쓰지 않으면 잘 정의되지 않는다는 점 통지) 첫 행 이후의 행은 버려집니다. FOUND 변수로 행이 반환됐는지 확인할 수 있어요.
  • STRICT를 지정하면 명령이 정확히 한 행을 반환해야 해요. 아니면 NO_DATA_FOUND(행 없음)이나 TOO_MANY_ROWS(한 행 초과) 런타임 오류가 나요. 예외 블록으로 잡을 수 있어요.
  • STRICT 명령이 성공하면 항상 FOUND를 true로 설정해요.
  • RETURNING이 있는 INSERT/UPDATE/DELETE/MERGE는 STRICT가 아니어도 반환 행이 둘 이상이면 오류를 보고해요. 어떤 영향 받은 행을 반환할지 정할 ORDER BY 같은 수단이 없기 때문이에요.

STRICT 요건을 충족하지 못해 오류가 날 때 유용한 설정이 있는데, 함수에서 print_strict_params를 켜면 오류 메시지의 DETAIL 부분에 그 명령에 전달된 파라미터 정보가 포함돼요. 함수별로는 #print_strict_params on 컴파일러 옵션으로 켤 수 있어요.

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

동적 명령 실행 (EXECUTE)

실행할 때마다 테이블이나 데이터 타입이 달라지는 동적 명령은 PL/pgSQL의 일반적인 계획 캐시가 동작하지 않아요. 이럴 때 EXECUTE 문장을 씁니다.

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

command-string은 실행할 명령을 담은 문자열(text 타입)을 만드는 표현식이에요. 선택적 target은 결과가 저장될 레코드 변수·행 변수·변수 리스트이고, 선택적 USING 표현식은 명령에 삽입할 값들을 제공해요.

  • 계산된 명령 문자열에는 PL/pgSQL 변수의 대체가 일어나지 않아요. 필요한 변수 값은 명령 문자열을 만들 때 넣거나, 아래 파라미터를 써야 합니다.
  • EXECUTE로 실행하는 명령에는 계획 캐시가 없어요. 매 실행마다 항상 계획됩니다.
  • INTO 절은 행을 반환하는 명령의 결과를 어디에 할당할지 지정해요. 행 변수·변수 리스트라면 결과 구조와 정확히 일치해야 하고, 레코드 변수면 자동으로 맞춰져요. 여러 행이면 첫 행만, 행이 없으면 NULL이 할당됩니다. INTO가 없으면 결과는 버려져요. STRICT를 주면 정확히 한 행일 때만 오류가 나지 않아요.

명령 문자열은 파라미터 값을 $1, $2...로 참조할 수 있는데, 이들은 USING 절에서 준 값들을 가리켜요. 이 방법이 텍스트로 값을 삽입하는 것보다 보통 낫습니다. 값의 텍스트 변환 오버헤드를 피하고, 따옴표·이스케이프가 필요 없어 SQL 인젝션 공격에 훨씬 안전하기 때문이에요.

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

주의할 점이 두 가지 있어요.

  1. 파라미터 기호는 데이터 값에만 쓸 수 있어요. 동적으로 결정된 테이블·열 이름은 텍스트로 삽입해야 해요. quote_ident로 테이블·열 이름을, format()의 %I 명세로 자동 따옴표를 붙이는 게 깔끔해요.
  2. 파라미터 기호는 최적화 가능한 명령에서만 동작해요. 유틸리티 문장에서는 데이터 값이라도 텍스트로 삽입해야 해요.

단순 상수 명령 문자열 + USING 파라미터를 쓰는 EXECUTE는, 명령을 직접 쓰고 PL/pgSQL 변수 대체를 쓰는 것과 기능적으로 동등해요. 중요한 차이는 EXECUTE가 실행마다 재계획해서 현재 파라미터 값에 특화된 계획을 만든다는 점이에요. 최적의 계획이 파라미터 값에 크게 의존하는 상황에서는 EXECUTE로 일반 계획이 선택되지 않게 할 수 있어서 도움이 될 수 있어요.

참고: EXECUTE 안에서는 SELECT INTO가 지원되지 않아요. 평범한 SELECT를 실행하고 INTO를 EXECUTE 자체의 일부로 지정하세요. 또 PL/pgSQL의 EXECUTE 문장은 PostgreSQL 서버가 지원하는 EXECUTE SQL 문과 무관해요.

동적 쿼리에서 따옴표 다루기 — 동적 명령을 다루다 보면 작은따옴표 이스케이프 처리를 자주 해야 해요. 함수 본문의 고정 텍스트는 **달러 따옴표(dollar quoting)**로 감싸는 걸 권장합니다. 데이터 값 등 미리 알 수 없는 텍스트는 안전하게 quote_literal, quote_nullable, quote_ident를 상황에 맞게 써야 해요. null이 될 수 있는 값을 다룰 땐 보통 quote_literal 대신 quote_nullable을 씁니다(quote_literal은 null 인자에서 null을 반환하고, quote_nullable은 문자열 NULL을 반환해요). format()의 %I(식별자, quote_ident와 동등)·%L(리터럴, quote_nullable과 동등)을 쓰면 더 깔끔하게 만들 수 있고, USING 절과 함께 쓰면 값이 %L 텍스트 변환 대신 네이티브 타입으로 처리돼 더 효율적이에요.

결과 상태 얻기

명령의 효과를 판단하는 방법은 두 가지가 있어요.

GET DIAGNOSTICS — 시스템 상태 표시기를 가져옵니다.

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

각 item은 지정 변수에 할당할 상태 값을 가리키는 키워드예요(예: ROW_COUNT). CURRENT는 잡음 단어(noise word)입니다.

GET DIAGNOSTICS integer_var = ROW_COUNT;

FOUND 변수 — boolean 타입의 특수 변수예요. PL/pgSQL 함수 호출 안에서 처음엔 false로 시작하고, 다음과 같은 문장들에 의해 설정됩니다.

  • SELECT INTO: 행이 할당되면 true, 행이 없으면 false.
  • PERFORM: 하나 이상의 행을 만들면 true, 아니면 false.
  • UPDATE, INSERT, DELETE, MERGE: 최소 한 행이 영향 받으면 true, 아니면 false.
  • FETCH: 행을 반환하면 true, 아니면 false.
  • MOVE: 커서 재배치에 성공하면 true, 아니면 false.
  • FOR·FOREACH: 한 번 이상 반복하면 true, 아니면 false(루프 종료 시 설정되고, 루프 본문 안에서는 루프 문장이 FOUND를 바꾸지 않아요).
  • RETURN QUERY·RETURN QUERY EXECUTE: 쿼리가 최소 한 행을 반환하면 true, 아니면 false.

다른 PL/pgSQL 문장은 FOUND 상태를 바꾸지 않아요. 특히 EXECUTE는 GET DIAGNOSTICS의 출력은 바꾸지만 FOUND는 바꾸지 않습니다. FOUND는 각 PL/pgSQL 함수 안의 로컬 변수라서, 변경해도 현재 함수에만 영향을 줘요.

아무것도 안 하기: NULL 문장

때로는 아무것도 하지 않는 자리 표시자 문장이 유용해요. if/then/else 체인의 한 가지를 의도적으로 비워둔다는 표시로 쓸 수 있어요. 그럴 때 NULL; 문장을 씁니다.

NULL;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- 오류 무시
END;

참고: Oracle PL/SQL에서는 빈 문장 리스트가 허용되지 않아 이런 자리에 NULL 문장이 필요해요. PL/pgSQL은 그냥 아무것도 안 쓰는 것도 허용합니다.

더 알아보기 (Learn more)