| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL수속 언어 | Fast Forward | Next |
제어 구조는 아마 PL/pgSQL의 가장 유용(한편 중요)한 부분입니다. PL/pgSQL의 제어 구조를 사용해, PostgreSQL의 데이터를 매우 유연, 강력하게 조작할 수가 있습니다
함수로부터 데이터를 돌려주기 위해서 사용할 수 있는 명령이 2개 있습니다. RETURN및 RETURN NEXT입니다.
RETURN expression ;
RETURN와 표현식은 함수를 종료해, 표현식 의 값을 호출해 돌려줍니다. 이 형식은 집합을 돌려주지 않는 PL/pgSQL함수로 사용됩니다.
스칼라형을 돌려주는 경우는 임의의 식을 사용할 수 있습니다. 이 식의 결과는, 대입에서 설명한 것처럼, 자동적으로 함수의 반환값의 형태에 캐스트 됩니다. 복합(행)값을 돌려주기 위해서, expression 로서 레코드 혹은 행 변수를 기술해야 합니다.
출력 매개변수를 가진 함수를 선언했을 때는, 식이 없는 RETURN를 기술해 주세요. 그 시점에 있어서의 출력 매개변수의 값이 돌려주어집니다.
void를 돌려주도록 함수를 선언했을 경우에서도, 함수를 즉시 빠져 나가기 위해서 RETURN를 사용할 수 있습니다만, RETURN의 뒤에 식을 기술하지 말아 주세요.
함수의 반환값은 정의되지 않은 채로 있을 수 없습니다. 제어가 RETURN문이 발견되지 않는 상태로 함수의 최상정도의 블록의 마지막까지 달했을 때, 실행시 에러가 발생합니다. 그러나, 이 제한은 출력 매개변수를 가진 함수 및 void를 돌려주는 함수에는 적용하지 않습니다. 이러한 경우는 최상레벨의 블록이 끝났을 때, RETURN문이 자동적으로 실행됩니다.
RETURN NEXT expression ;
PL/pgSQL함수가 SETOF sometype 를 돌려주도록 선언했을 경우, 후속의 처리가 다소 다릅니다. 이 경우, 반환값의 개개의 항목은, RETURN NEXT명령 또는 RETURN QUERY명령으로 지정됩니다. 그리고, 인자가 없는 마지막 RETURN명령에 의해, 함수가 실행을 종료했던 것이 나타납니다. RETURN NEXT는, 스칼라형 및 복합형의 모두를 사용할 수 있습니다. 복합형의 경우, 결과의 "테이블"전체가 돌려주어집니다.
실제로는, RETURN NEXT는 함수로부터 돌아오지 않습니다. 단지 함수의 결과 집합에 행을 추가하고 있을 뿐입니다. 그리고, 그 실행은 PL/pgSQL함수내의 다음의 명령문에 계속합니다. RETURN NEXT 또는 RETURN QUERY명령이 연속해 실행되면, 결과 집합이 작성됩니다. 마지막, 인자를 가져서는 안 되는 RETURN에 의해, 함수의 종료를 제어합니다. (또는 제어를 함수의 마지막에 옮길 수 있습니다).
출력 매개변수를 가진 함수를 선언했을 때는, 식이 없는 RETURN NEXT만을 기술해 주세요. 그 시점에 있어서의 출력 매개변수의 값이, 함수로부터의 반환값을 위해서 결과의 행으로서 보존됩니다. 출력 매개변수를 가진 집합을 돌려주는 함수를 작성하기 위해서는, 출력 매개변수가 복수일때는 SETOF record를 돌려주도록 함수를 선언해, sometype 형의 출력 매개변수일때는 SETOF sometype 를 돌려주도록 함수를 선언해야 하는 것에 주의해 주세요.
RETURN NEXT을 사용하는 함수는 다음의 양식에 따라 불려가야 합니다.
SELECT * FROM some_func();
즉, 함수는 FROM구에서 테이블 소스로서 사용되어야합니다.
Note: 상기와 같이, RETURN NEXT 및 RETURN QUERY의 현재의 구현에서는, 함수로부터 돌려주어지기 전에 결과 집합 전체를 보관합니다. 이것에 의해, PL/pgSQL함수가 매우 대량의 결과 집합을 돌려주었을 경우, 성능이 저하할 가능성이 있습니다. 메모리의 소모를 피하기 위해서, 데이터는 디스크에 기입해집니다. 그러나, 함수 자체는 결과 집합 전체가 생성될 때까지는 돌아가지 않습니다. 항후, PL/pgSQL의 버전에서는, 이 제한을 받지 않고 집합을 돌려주는 함수를 사용자가 정의할 수 있게 될지도 모릅니다. 현재, 디스크에 기입해지는 데이터의 개시점은 work_mem설정 변수에 의해 제어되고 있습니다. 대량의 결과 집합을 보관하는데 충분한 메모리가 있는 경우, 관리자는 이 매개변수의 값을 크게 하는 것을 고려해야 합니다.
IF명령문의 조건에 근거해 명령을 실행시킵니다. PL/pgSQL에는, 이하와 같은 5개의 IF의 형식이 있습니다. IF:
IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSE IF
IF ... THEN ... ELSIF ... THEN ... ELSE
IF ... THEN ... ELSEIF ... THEN ... ELSE
IF
boolean-expression
THEN
statements
END IF;
IF-THEN문은, 가장 단순한 IF의 형식입니다. THEN와 END IF의 사이의 명령문의 조건이 참인 경우에 실행됩니다. 아예 없으면 그것들은 날아갑니다.
Example:
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
IF
boolean-expression
THEN
statements
ELSE
statements
END IF;
IF-THEN-ELSE문은 IF-THEN에 추가해 조건 평가가 거짓의 경우에 실행해야 할 쿼리 집합을 지정할 수 있습니다.
례：
IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN 't';
ELSE
RETURN 'f';
END IF;
이하의 예와 같이, IF문은 상자로 할 수 있습니다.
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
이 형식을 사용하는 경우, 실제로 IF명령문을 외측의 IF명령문의 ELSE 부분의 안쪽에 상자로 하고 있습니다. 따라서, 상자로 한 IF마다 1개의 END IF명령문이, 그 부모가 되는 IF-ELSE에 1개의 END IF명령문이 필요합니다. 이것에 의해 정상적으로 동작할 수 있습니다만, 검사해야 할 후보가 많이 있는 경우는 장황하게 됩니다. 그 결과, 다음의 형식입니다.
IF
boolean-expression
THEN
statements
[ ELSIF
boolean-expression
THEN
statements
[ ELSIF
boolean-expression
THEN
statements
...]]
[ ELSE
statements
]
END IF;
IF-THEN-ELSIF-ELSE는, 어느 문장에 많은 대체 수단이 있는 경우의 체크에, 보다 편리한 방법을 제공합니다. 기능적으로는, IF-THEN-ELSE-IF-THEN명령을 상자로 한 것 것과 같습니다만, 필요한 END IF는 1개 뿐입니다.
이하에 예를 나타냅니다.
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- 남는 유일한 가능성은 그 값이 NULL인 것이다
result := 'NULL';
END IF;
ELSEIF는ELSIF의 별명입니다.
LOOP, EXIT, CONTINUE, WHILE, FOR문을 사용해, PL/pgSQL함수로, 일련의 명령군을 반복할 수가 있습니다.
[ << label >> ] LOOP statements END LOOP [ label ];
LOOP는, EXIT문 또는 RETURN문에 의해 종료될 때까지 무한하게 반복되는, 조건 없는 루프를 정의합니다. 옵션의 label 는, 상자장 루프내의 EXIT및CONTINUE문으로, 어느 레벨의 상자에 적용하는지를 지정하기 위해서 사용됩니다.
EXIT [ label ] [ WHEN expression ];
label 가 지정되지 않는 경우, 가장 안쪽의 루프를 끝내 END LOOP의 다음의 명령문이 그 후에 실행됩니다. label 가 지정되었을 경우, 그것은 현재의 루프, 혹은 상자가 된 루프나 블록의 외측의 레벨의 라벨일 필요가 있습니다. 그 후, 지명된 루프 또는 블록을 끝내고 그 루프 또는 블록의 대응하는 END의 다음의 명령문에 제어를 옮깁니다.
WHEN가 지정되었을 경우, boolean-expression 가 진정한 경우만 루프의 종료가 일어납니다. 아예 없으면, EXIT의 뒤의 행에 제어가 옮깁니다.
EXIT는, 모든 루프와 함께 사용될 수 있어 조건 없는 루프로 한정되지 않습니다. BEGIN블록과 함께 사용했을 때, EXIT에 의해 블록의 다음의 명령문에 제어가 넘어갑니다.
예：
LOOP
-- 연산
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- 연산
EXIT WHEN count > 0; -- 전의 예와 같은 결과
END LOOP;
BEGIN
-- 연산
IF stocks > 100000 THEN
EXIT; -- 이것에 의해 BEGIN 블록을 빠져 나간다
END IF;
END;
CONTINUE [ label ] [ WHEN expression ];
label 가 없는 경우, 바로 외측의 루프의 다음의 반복이 개시됩니다. 즉, 루제어는 루프 제어 식의 뒤를 지나가고, 루프의 본문은 재평가됩니다. label 가 존재하는 경우, 실행을 계속하는 루프의 라벨을 지정합니다.
WHEN가 지정되었을 경우, boolean-expression 가 참인 경우만 루프에 있어서의 다음의 반복이 시작됩니다. 아예 없으면, CONTINUE의 뒤의 행에 제어가 넘어갑니다.
CONTINUE는 모든 종류의 루프로 사용 가능합니다. 조건 없는 루프로 한정되지 않습니다.
예:
LOOP
-- 연산
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- 50에서 100을 세는, 연산
END LOOP;
[ << label >> ] WHILE expression LOOP statements END LOOP [ label ];
WHILE문은 boolean-expression 의 평가가 참인 동안, 명령문의 줄을 반복합니다. 조건식은, 루프 본문에 들어가기 전에만 체크됩니다.
이하에 예를 나타냅니다.
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- 여기서 연산을 실시합니다.
END LOOP;
WHILE NOT boolean_expression LOOP
-- 여기서 연산을 실시합니다.
END LOOP;
[ << label >> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP statements END LOOP [ label ];
이 형식의 FOR는 정수값의 범위를 반복하는 루프를 생성합니다. name 변수는 integer형으로서 자동적으로 정의되어 루프 내부만으로 존재합니다 (루프 외부에서 정의해도, 루프 내부에서는 모두 무시됩니다). 범위의 하한, 상한으로서 주어지는 2개의 식은 루프에 들어갔을 때에 한 번만 평가됩니다. BY구를 지정하지 않을 때의 상호작용 단계는 1입니다만, BY구를 이용해 지정할 수 있어 루프에 들어갔을 때에 한 번만 평가됩니다. REVERSE가 지정되었을 경우, 상호작용 단계의 값은 부로 간주됩니다.
정수 FOR 루프의 예를 이하에 나타냅니다.
FOR i IN 1..10 LOOP
연산
RAISE NOTICE 'i is %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
연산
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
연산
RAISE NOTICE 'i is %', i;
END LOOP;
하한이 상한보다 큰(REVERSE의 경우는 보다 작다) 경우, 루프 본문에는 전혀 실행되지 않습니다. 에러는 발생하지 않습니다.
다른 종류의 FOR루프를 사용해, 쿼리의 결과를 반복해, 그 데이터를 취급할 수 있습니다. 이하에 구문을 나타냅니다.
[ << label >> ] FOR target IN query LOOP statements END LOOP [ label ];
target 는, 레코드 변수, 행 변수 또는 컴마로 단락지은 스칼라 변수의 리스트입니다. target 에는 차례차례, query 의 결과의 모든 행이 대입되어 각 행에 대해서 루프 본문이 실행됩니다. 이하에 예를 나타냅니다.
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized views...');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- 여기서 "mviews"는 cs_materialized_views의 하나의 레코드를 가집니다
PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
END LOOP;
PERFORM cs_log('Done refreshing materialized views.');
RETURN 1;
END;
$$ LANGUAGE plpgsql;
이 루프가 EXIT문으로 종료했을 경우, 마지막에 할당할 수 있었던 행의 값은 루프를 빠진 다음에도 접근 할 수가 있습니다.
이 형태의 FOR쿼리 query 로서 행을 돌려주는 모든 SQL 명령을 사용할 수 있습니다. 통상은 SELECT입니다만, RETURNING구를 수반한 INSERT, UPDATE 또는 DELETE도 사용할 수 있습니다. EXPLAIN등의 유틸리티 명령도 작동합니다.
FOR-IN-EXECUTE명령문은 행을 반복하는 1개의 방법입니다.
[ << label >> ] FOR target IN EXECUTE text_expression LOOP statements END LOOP [ label ];
이 방법은, 쿼리의 소스가 문자열식에서 지정되는 점을 제외해, 전의 형식과 닮아 있습니다. 이 식은 FOR루프의 각 엔트리로 평가되어 재계획을 합니다. 이것에 의해, 프로그래머는, 통상의 EXECUTE문과 같이 사전에 계획된 쿼리에 의한 고속성과 동적인 쿼리가 가지는 유연성을 선택할 수 있습니다.
Note: PL/pgSQL파서는 IN와 LOOP 사이에 괄호 밖에 ..가 나타나는지 아닌지를 체크하는 것으로 현재 두 종류의 FOR루프(정수 혹은 쿼리결과)로 구별됩니다.
기본값으로, PL/pgSQL함수의 내부에서 발생한 에러는 함수의 실행을 중지해, 실제로 주변의 트랜잭션을 중단(abort) 합니다. BEGIN블록 및 EXCEPTION구를 사용하면, 에러를 포착해 그 상태로부터 회복할 수 있습니다. 그 구문은 통상의 BEGIN블록의 구문을 확장한 것입니다.
[ << label >> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;
에러가 발생하지 않을 때, 이 형식의 블록은 단지 모든 statements 를 실행해, END의 다음의 문장에 제어가 옮깁니다. 그러나, 명령문 의 내부에서 에러가 발생하면, 그 이후의 명령문 의 처리는 중단되어 EXCEPTION에 나타낸 일람에 제어가 옮깁니다. 그리고 일람중에서, 발생한 에러와 합치하는 첫번째 조건 를 찾습니다. 합치하는 것이 있으면, 대응하는 핸들러 명령문 을 실행해, END의 다음의 문장에 제어가 옮깁니다. 합치하는 것이 없으면, EXCEPTION구가 존재하지 않는 것과 같아, 에러는 외측에 파급합니다. EXCEPTION를 포함한 외측의 블록은 에러를 포착할 수 있습니다만, 실패하면 함수의 처리는 중단됩니다.
모든 상태 명은 Appendix A에 나타낼 수가 있습니다. 분류명은 거기로 분류되는 모든 에러에 합치합니다. OTHERS라는 특별한 상태명은 QUERY_CANCELED이외의 모든 에러에 합치합니다 (QUERY_CANCELED를 이름으로 포착하는 것은 가능합니다만, 현명하지는 않습니다). 상태명은, 대문자와 소문자를 구별하지 않습니다.
에러가 해당하는 핸들러 명령문 내부에서 새롭게 발생했을 때, EXCEPTION구는 그 에러를 포착하지 못하지만, 에러는 외측에 파급됩니다. 외측의 EXCEPTION구는, 그 에러를 포착할 수 있습니다.
EXCEPTION구가 에러를 포착했을 때, PL/pgSQL함수의 로컬 변수는 에러가 일어난 후 상태를 유지합니다. 그러나, 블록 내부의 영속적인 데이터베이스 상태는, 롤백(rollback) 됩니다. 그러한 예를 이하에 나타냅니다.
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
제어가 변수 y의 대입으로 옮기면, 제로로 나누기 때문에 division_by_zero에러가 되어, EXCEPTION구가 그 에러를 포착합니다. RETURN문에 의한 함수의 반환값은, 1을 가산한 후의 x의 값이 됩니다만, UPDATE명령에 의한 결과는 롤백(rollback) 됩니다. 그러나, 이전 블록의 INSERT명령은 롤백(rollback) 되지 않습니다. 따라서, 데이터베이스의 내용의 최종 결과는 Tom Jones이며, Joe Jones가 아닙니다.
Tip: EXCEPTION구를 포함한 블록의 실행에 필요로 하는 시간은, 포함하지 않는 블록에 비해 매우 길어집니다. 따라서, 필요가 없을 때에 EXCEPTION를 사용 해서는 안됩니다.
예외 핸들러의 내부에서는, SQLSTATE변수가 일어난 예외에 대응한 에러 코드를 보유합니다. (Table A-1의 에러 코드표를 참조해 주세요). SQLERRM변수는 예외에 대응한 에러 메세지를 보유합니다. 이러한 변수는, 예외 핸들러의 외부에서는 정의되지 않습니다.
Example 37-1. UPDATE/INSERT의 예외
이것은 UPDATE 또는 INSERT의 실행에 있어서의 예외 처리를 사용한 적당한 예제입니다.
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');