PL/pgSQL 변수 선언

PL/pgSQL 변수 선언 (Declarations)

PL/pgSQL 블록에서 쓰는 모든 변수는 블록의 선언부(declarations section)에 미리 선언해야 해요. 예외는 거의 없는데, 정수 범위를 도는 FOR 루프의 루프 변수는 자동으로 정수 변수가 되고, 커서 결과를 도는 FOR 루프의 루프 변수는 자동으로 record 변수가 돼요. 데이터 타입은 integer·varchar·char처럼 SQL 데이터 타입이면 무엇이든 쓸 수 있어요. 어떻게 선언하고, 옵션을 어떻게 붙이는지 살펴볼게요.

출처: 공식문서

기본 선언 문법

변수 선언의 일반 문법은 이렇게 생겼어요.

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

실제 예시를 보면 더 직관적이에요.

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

DEFAULT 절을 주면 블록에 진입할 때 그 값으로 초기화돼요. 안 주면 SQL의 null 값으로 초기화되어요. CONSTANT를 붙이면 초기화 이후에는 값을 못 바꿔요. NOT NULL을 지정하면 null 값을 할당할 때 런타임 에러가 나고, NOT NULL로 선언한 변수는 반드시 null이 아닌 기본값을 줘야 해요. =는 PL/SQL 호환 표기 := 대신 쓸 수 있어요.

여기서 재미있는 점은 기본값이 함수 호출당 한 번이 아니라 블록에 진입할 때마다 다시 평가된다는 거예요. 그래서 now()를 timestamp 변수에 할당하면, 그 변수는 함수가 컴파일(precompile)된 시점이 아니라 함수가 실제로 호출된 그 시점의 시간을 갖게 돼요.

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;

다른 방법은 선언 문법으로 별칭을 명시하는 거예요.

name ALIAS FOR $n;

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

이 두 예시는 완전히 동등하지 않아요. 첫 번째 경우엔 subtotal을 sales_tax.subtotal처럼 참조할 수 있지만, 두 번째 경우엔 그렇게 못 해요.

출력 파라미터(OUT): 함수에 출력 파라미터를 선언하면, 일반 입력 파라미터와 똑같이 $n 이름과 선택적 별칭을 받아요. 출력 파라미터는 본질적으로 null로 시작하는 변수이고, 함수 실행 중에 값을 할당받아 최종 값이 반환돼요. 예를 들어 세금 예시는 이렇게도 쓸 수 있어요.

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

RETURNS real을 생략했는데, 포함시켜도 되지만 중복이에요. OUT 파라미터가 있는 함수를 호출할 땐 출력 파라미터를 빼고 호출해요. SELECT sales_tax(100.00);

출력 파라미터는 여러 값을 반환할 때 가장 유용해요.

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM sum_n_product(2, 4);
 sum | prod
-----+------
   6 |    8

이것은 함수 결과용 익명 record 타입을 만드는 셈이고, RETURNS 절을 주면 RETURNS record라고 써야 해요. 프로시저에서도 같은 방식이 동작해요. 프로시저 호출에선 모든 파라미터를 지정해야 하며, 순수 SQL에서 호출할 때 출력 파라미터 자리에 NULL을 넣을 수 있어요. 하지만 PL/pgSQL에서 호출할 땐 출력 파라미터마다 변수를 써야 그 변수가 결과를 받아요.

RETURNS TABLE로 선언하는 방법도 있어요.

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

이것은 OUT 파라미터를 하나 이상 선언하고 RETURNS SETOF sometype으로 지정한 것과 정확히 동등해요.

다형성 타입(anyelement/anycompatible): 함수 반환 타입이 다형성 타입이면 특별한 파라미터 $0이 만들어져요. 그 타입은 실제 입력 타입에서 추론된 함수의 실제 반환 타입이에요. $0은 null로 초기화되고 함수가 수정할 수 있어서, 원하면 반환값을 담는 데 쓸 수 있어요. 예시를 볼게요.

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

실무에서는 anycompatible 계열을 쓰는 게 더 편한 경우가 많아요. 입력 인자들이 공통 타입으로 자동 승격되기 때문이에요.

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURNS anycompatible AS $$
BEGIN
    RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

이렇게 하면 SELECT add_three_values(1, 2, 4.7); 같은 호출이 작동하면서 정수 입력을 자동으로 numeric으로 승격시켜요. anyelement를 쓰는 버전은 세 입력을 수동으로 같은 타입으로 캐스팅해 줘야 해요.

ALIAS로 이름 다시 붙이기

ALIAS 문법은 파라미터뿐 아니라 어떤 변수에도 별칭을 붙일 수 있어요.

newname ALIAS FOR oldname;

주된 실용 용도는 이미 정해진 이름을 가진 변수, 이를테면 트리거 함수 안의 NEW나 OLD에 다른 이름을 붙이는 거예요.

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

다만 ALIAS는 같은 객체를 가리키는 이름을 두 개 만드는 것이니, 남용하면 헷갈려요. 정해진 이름을 덮어쓰는 목적으로만 쓰는 게 좋아요.

타입 복사하기: %TYPE / %ROWTYPE

%TYPE은 테이블 컬럼이나 이미 선언된 PL/pgSQL 변수의 데이터 타입을 가져다 쓸 수 있게 해 줘요. 데이터베이스 값을 담을 변수를 선언할 때 유용해요.

name table.column%TYPE
name variable%TYPE

%ROWTYPE은 테이블의 전체 행 구조를 담는 변수를 만들어요.

name table_name%ROWTYPE;

Row 타입과 Record 타입

Row 타입은 %ROWTYPE처럼 테이블 행 단위의 구조를 담는 변수예요. 뷰나 CREATE TYPE로 만든 합성 타입(composite type)에도 쓸 수 있어요. row 변수의 필드는 변수명.필드명처럼 접근해요.

DECLARE
  r mytable%ROWTYPE;
BEGIN
  r.id := 1;

Record 타입은 미리 정해진 구조가 없는 특별한 row 타입이에요. 아무 컬럼 구조도 정의하지 않은 채 선언하고, 나중에 SELECT나 FOR 루프에서 실제 행 구조를 할당받아 써요. 처음에는 필드가 없어서 필드 접근을 시도하면 에러가 나요.

DECLARE
  rec RECORD;
BEGIN
  SELECT * INTO rec FROM mytable WHERE id = 1;

변수의 콜레이션(Collation)

콜레이션 가능한 데이터 타입의 로컬 변수는 선언에 COLLATE 옵션을 넣어 다른 콜레이션을 지정할 수 있어요.

DECLARE
    local_a text COLLATE "en_US";

이 옵션은 위 규칙대로 변수에 부여될 콜레이션을 덮어써요. 물론 함수 안의 특정 연산에서 특정 콜레이션을 강제하고 싶다면 일반 SQL처럼 명시적 COLLATE 절을 쓸 수도 있어요.

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

이것은 평범한 SQL 명령에서 하듯 표현식에 쓰인 테이블 컬럼·파라미터·로컬 변수와 연관된 콜레이션을 덮어써요.

더 알아보기 (Learn more)

PL/pgSQL 전체 구조: Section 41.2. Structure of PL/pgSQL
조건·루프 등 제어 구조: Section 41.6. Control Structures
함수 파라미터·반환 타입 규칙: Section 36.5.4