| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL수속 언어 | Fast Forward | Next |
본절 및 차절에서는, 명시적으로 PL/pgSQL로 해석되는, 모든 종류의 명령문에 대해 설명합니다. 이러한 종류의 명령문으로서 인정되지 않는 것은 모두, SQL 명령이라고 가정되어 Section 37.6.2 및 Section 37.6.3에 기술한 것처럼, 메인 데이터베이스 엔진에 송신되고 실행됩니다.
값을 PL/pgSQL변수, 혹은 행/레코드 필드에 대입하는 경우는 이하와 같이 기술합니다.
identifier := expression ;
상술한 대로, 이러한 명령문중에 있는 식은, 메인 데이터베이스 엔진에 송신되는 SELECT SQL 명령에 의해 평가됩니다. 식은 1개의 값을 생성해야 합니다.
식의 결과 데이터형이 변수의 데이터형에 일치하지 않는 경우, 또는, 변수가(char(20)와 같이) 특정의 크기/정밀도를 가지는 경우, 결과의 값은 PL/pgSQL인터프리터에 의해, 결과의 형태의 출력 함수와 변수의 형태의 입력 함수를 사용해 암묵적으로 변환됩니다. 이것에 의해, 결과값의 문자열 형식을 입력 함수로 받아들일 수가 없는 경우에, 입력 함수에 대해 실행시 에러가 발생할 가능성이 있는 것에 주의해 주세요.
례：
user_id := 20; tax := subtotal * 0.06;
예를 들면, RETURNING구가 없는 INSERT와 같이, 행을 돌려주지 않는 SQL 쿼리에 대해, 단지 기술하는 것에 의해 PL/pgSQL함수의 내부에서 쿼리를 실행할 수 있습니다.
쿼리 텍스트에 나타나는 모든 PL/pgSQL변수명도, 매개변수의 심볼로 옮겨집니다. 그 후, 실행시의 매개변수값으로서 그 시점의 변수값이 제공됩니다. 이것은 함수의 다른 콜에 다른 일을 하는 같은 텍스트 쿼리에 허용됩니다.
Note: 2 단계 프로세스가 쿼리를 계획하고, 서브시퀀트 실행계획을 재사용하기 위해 PL/pgSQL를 허용합니다. 그 예로서 다음과 같이 쓸 수 있습니다.
DECLARE key TEXT; delta INTEGER; BEGIN ... UPDATE mytab SET val = val + delta WHERE id = key;쿼리 텍스트는, SQL 주엔진으로부터 아래와 같이 보입니다.
UPDATE mytab SET val = val + $1 WHERE id = $2;통상 이와 같이 생각할 것은 없습니다만, 구문 에러 메세지의 해석력이 필요한 때는, 이 해석이 유용이 됩니다.
| Caution |
|
PL/pgSQL는, 함수에 대해 선언한 변수와 일치하는 모든 식별자에의 치환을 실시합니다. 따라서, 함수내의 명령으로 참조해야 하는 테이블명, 렬명 또는 함수명과 같은 이름을 변수에 사용하는 것은, 부적절한 생각입니다. |
식 또는 쿼리SELECT를 평가해 결과를 파기하는 것이, 도움이 되는 경우가 있습니다. 예를 들면, 함수의 호출에 대해, 부차적인 성과를 취득할 수 있지만, 결과는 소용없는 경우입니다. 이러한 때 PL/pgSQL에서는, PERFORM문을 사용해 주세요.
PERFORM query ;
이것은 query 를 실행해, 그 결과를 파기합니다. SQL의 SELECT명령문과 같은 방법으로 쿼리 를 기술합니다만, 최초의 키워드 SELECT를 PERFORM에 옮겨놓아 주세요. PL/pgSQL변수는 여느때처럼 쿼리에 치환됩니다. 또, 특수한 변수인 FOUND는 쿼리 결과가 1행에서 생성되었을 경우는 참으로 설정되고, 생성되지 않는 경우는 거짓으로 설정됩니다(Section 37.6.6를 봐 주세요).
Note: 직접 SELECT를 기술하면, 이 결과를 얻을 수 있다고 생각할지도 모릅니다만, 현시점에서 이것을 실시하는 방법은 PERFORM밖에 없습니다. SELECT와 같이 행을 돌려주는 SQL 명령은, 에러로서 거절됩니다. INTO구를 가질 때는 예외이며, 다음 섹션에서 설명합니다.
이하에 예를 나타냅니다.
PERFORM create_mv('cs_session_page_requests_mv', my_query);
1행을 돌려주는(아마, 복수열의) 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 ;
여기서, target 는 레코드 변수, 행 변수, 혹은, 단순한 변수와 레코드/행 변수의 필드를 컴마로 단락지은 리스트입니다. PL/pgSQL변수는 치환되어 나머지의 쿼리가 됩니다. 이와 같이 작동하는 것은, RETURNING를 수반한 INSERT/UPDATE/DELETE와 SELECT 및 행 세트의 결과를 돌려주는 유틸리티 명령(예를 들면,EXPLAIN)입니다. INTO구 이외에는, SQL 명령은 PL/pgSQL의 외부에 기술한 것 것과 같습니다.
Tip: 통상의 PostgreSQL의 SELECT INTO문에서는, INTO의 대상은 새롭게 작성되는 테이블입니다. 그러나, INTO를 수반한 SELECT에서는, 이 해석이 일반적인 것과 크게 다른 것에 주의해 주세요. PL/pgSQL 함수 내부에서 SELECT의 결과로부터 테이블을 작성하고 싶은 경우는, CREATE TABLE ... AS SELECT구문을 사용해 주세요.
행 또는 변수 리스트가 대상으로 사용되었을 경우, 렬수와 데이터형에 대해 쿼리의 결과와 대상의 구조가 정확하게 일치해야합니다. 그렇지 않으면, 실행시 에러가 발생합니다. 레코드 변수가 대상의 경우, 쿼리 결과의 열의 행형에 자기 자신을 자동적으로 설정합니다.
INTO구는 SQL 명령의 거의 아무 장소에나 기술할 수 있습니다. 관습적으로는, SELECT문에 대해 select_expressions 의 직전 또는 직후에, 다른 명령에 대해 명령문의 마지막에 기술됩니다. 향후, PL/pgSQL의 파서가 엄격하게 되는 경우에 대비해, 이 관습에 따르는 것을 추천 합니다.
INTO구에 대해 STRICT가 지정되지 않는 경우, target 는 쿼리가 돌려주는 최초의 행이 되어, 행을 돌려주지 않을 때는 NULL이 됩니다. ("t최초의 행"은 ORDER BY를 사용하지 않는다고 정의할 수 없는 것에 주의해 주세요. ) 2행째 이후의 행의 결과는, 모두 파기됩니다. 특수한 FOUND변수를 조사해, 어느 행이 돌려주어졌는지 알 수 있습니다. (Section 37.6.6를 참조해 주세요) 이하에 예를 나타냅니다.
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
STRICT옵션이 지정되었을 경우, 쿼리는 정확하게 1행을 돌려줘야 합니다. 그렇지 않으면, 행이 없을 때는 NO_DATA_FOUND, 2행 이상이 돌아갔을 때는 TOO_MANY_ROWS의 실행시 에러가 생깁니다. 에러를 포착하고 싶을 때는, 예외 블록을 사용할 수 있습니다. 이하에 예를 나타냅니다.
BEGIN
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee % not unique', myname;
END;
STRICT를 지정한 명령이 성공하기 위해서는, FOUND변수를 항상 참으로 설정해 주세요.
STRICT가 지정되지 않는 경우에서도, RETURNING를 수반한 INSERT/UPDATE/DELETE가 2행 이상을 돌려주었을 때는, 에러가 됩니다. 왜냐하면, 어느 1행을 돌려줄지 결정하는 ORDER BY와 같은 옵션이 존재하지 않기 때문입니다.
Note: STRICT옵션은, Oracle의 PL/SQL의 SELECT INTO및 관련한 명령문에 대응합니다.
SQL의 쿼리가 돌려주는 복수행의 결과를 처리하고 싶은 경우는, Section 37.7.4을 참조해 주세요.
아무것도 하지 않는 플레이스홀더문이 유용하게 되는 일이 있습니다. 예를 들면, IF/THEN/ELSE문의 일부가 공문인 것을 명시하고 싶을 때입니다. 이러한 목적으로는, NULL문을 사용합니다.
NULL;
예를 들면, 다음의 2개의 코드는 동등합니다.
BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
NULL; -- 에러를 무시한다
END;
BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN -- 에러를 무시한다
END;
어느 쪽이 바람직하다고 생각할지는, 기호의 문제입니다.
Note: Oracle의 PL/SQL에서는, 빈 명령문은 허용되지 않기 때문에 NULL쿼리 필수 가 됩니다. 거기에 반해 PL/pgSQL에서는, 빈 명령문이 허용되고 있습니다.
PL/pgSQL함수의 내부에서, 동적 명령문, 즉, 실행할 때에 다른 테이블이나 다른 데이터형을 사용하는 명령문을 갖고 싶은 일이 자주 있겠지요. PL/pgSQL가 통상 실시하는 명령문 계획의 캐쉬는 이러한 상황에서는 작동하지 않습니다. 이런 종류의 문제를 취급하기 위해서, 이하의 EXECUTE문이 준비되어 있습니다.
EXECUTE
command-string
[ INTO [STRICT]
target
];
여기서, command-string 는 실행되는 명령을 포함한(text형의) 문자열에 따른 평가식에서, target 는 레코드 변수, 행 변수, 혹은, 단순한 변수와 레코드/행 변수의 필드를 컴마로 단락지은 리스트입니다.
특히, PL/pgSQL변수가 이 연산용의 문자열 라인에 치환되지 않는 것을 주의해주세요. 어느 변수의 값도, 명령문 문자열을 작성할 때에 물어야합니다.
또, EXECUTE에 대해서 실행되는 명령문의 계획을 캐쉬할 것은 없습니다. 대신에, 명령은 명령문이 실행될 때에 준비됩니다. 따라서, 다른 테이블과 열에 대한 조작을 실행할 수 있도록, 명령문 문자열을 함수 내부에서 동적으로 작성할 수 있습니다.
INTO구는, 행을 돌려주는 SQL 명령의 결과를 대입해야 할 장소를 지정합니다. 행 또는 변수 리스트가 이용될 때, 그것은 쿼리의 결과의 구조와 정확하게 일치해야 합니다. (레코드 변수가 사용될 때, 그것은 자기 자신을 결과의 구조와 자동적으로 일치시킵니다). 복수의 행이 돌려주어졌을 때, 최초의 행만이 INTO변수에 대입됩니다. 1행도 돌려주어지지 않을 때, NULL가 INTO변수에 대입됩니다. INTO구가 지정되지 않을 때, 쿼리의 결과는 버려집니다.
STRICT옵션이 지정되었을 때, 쿼리의 결과가 정확하게 1행의 경우를 제외하고는, 에러가 됩니다.
SELECT INTO는 EXECUTE에서는 현재 지원되지 않습니다.
동적 명령을 실행할 때, PL/pgSQL에서는 단일 인용부호를 회피해야합니다. 추천 되는 방법은, 함수 본문의 고정된 텍스트를 달러 인용부호로 둘러싸는 방법입니다 (달러 인용부호를 이용하지 않는 구식의 코드를 보유하고 있는 경우는, Section 37.2.1의 개요를 참조하는 것이, 이해하기 쉬운 코드에의 변환 작업의 도움이 됩니다).
작성한 쿼리에 삽입해야 할 동적인 값은, 그것 자신의 내부에 인용부호를 포함할 가능성이 있기 때문에, 주의 깊은 처리가 필요합니다. 이하에 예를 나타냅니다(여기에서는 함수에 달러 인용부호를 이용하는 방법을 가정하고 있으므로, 인용부호를 이중화할 필요는 없습니다).
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE key = '
|| quote_literal(keyvalue);
이 예는, quote_ident와 quote_literal함수의 사용 방법을 나타내고 있습니다.
안전을 위해, 열과 테이블의 식별자를 포함하는 식은 quote_ident함수를 통해야 합니다.
동적으로 구축되는 명령내에서, 리터럴 문자열이 되어야 할 값을 포함하는 식은 quote_literal함수를 통해야합니다.
양쪽 모두, 적절한 처리를 실시해, 입력된 텍스트를 단일 인용부호 혹은 이중 인용부호로 괄, 특수 문자를 모두 적절히 회피해 끼워넣은 것을 돌려줍니다.
달러 인용부호의 용법은 일정한 텍스트를 둘러쌀 때만 유용한 것에 주의해 주세요. 위의 예를 다음과 같이 하는 것은, 매우 나쁜 생각입니다.
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);
왜냐하면, newvalue의 내용이 우연히 $$를 포함할 때는, 도중에 다음의 처리에 옮겨지기 때문입니다.
같은 불측 사태는, 달러 인용부호의 다른 단락 문자를 선택했을 때도 일어납니다.
따라서, 텍스트의 내용이 기존이 아닐 때는, 안전을 위해서 quote_literal함수를 사용
해야 합니다
.
동적 쿼리와 EXECUTE의 장대한 예는 Example 37-6으로 볼 수가 있습니다. 그것은 새로운 함수를 정의하기 위해서 CREATE FUNCTION명령을 이용해 실행하는 것입니다.
명령의 효과를 판단하려면, 몇 가지 방법이 있습니다. 첫번째 방법은, 이하와 같은 형식의 GET DIAGNOSTICS를 사용하는 방법입니다.
GET DIAGNOSTICS
variable
=
item
[ , ... ];
이 명령에 의해, 시스템 상태 지시자를 꺼낼 수가 있습니다. 각 item 는, 지정된 변수에 할당할 수 있었던 상태값을 식별하기 위한 키워드입니다 (이것은 받기 위해서(때문에) 올바른 데이터형이 아니면 안됩니다). 현재 사용 가능한 상태 항목은, ROW_COUNT, RESULT_OID의 2개입니다. ROW_COUNT는, 마지막에 SQL엔진에 송신된 SQL명령에 의해 처리된 행수를 나타냅니다. RESULT_OID는, 가장 최근의 SQL명령에 의해 삽입된 마지막 행의 OID입니다. RESULT_OID는 OID를 포함하는 테이블에 INSERT명령 후에 유용한 것을 주의해 주세요.
이하에 예를 나타냅니다.
GET DIAGNOSTICS integer_var = ROW_COUNT;
2번째의 명령의 효과를 판단하는 방법은, FOUND라는 boolean형의 특수한 변수를 체크하는 것입니다. PL/pgSQL의 각 함수내에서 사용될 때, FOUND는 처음엔 거짓으로 설정되어 있습니다. 이하와 같이, 각각의 명령문의 종류에 의해 설정이 변경됩니다.
SELECT INTO문은, 행이 대입되었을 경우는 참, 돌려주어지지 않았던 경우는 거짓을 FOUND로 설정합니다.
PERFORM문은, 행이 생성(파기)되었을 경우는 참, 생성되지 않았던 경우는 거짓을 FOUND로 설정합니다.
UPDATE, INSERT, 및 DELETE문은, 적어도 1행이 영향을 받았을 경우는 참, 전혀 영향을 받지 않았던 경우는 거짓을 FOUND로 설정합니다.
FETCH문은, 행이 돌려주어졌을 경우는 참, 전혀 돌려주어지지 않았던 경우는 거짓을 FOUND로 설정합니다.
FOR문은 1회이상 반복을 했을 경우는 진, 행해지지 않았던 경우는 거짓을 FOUND로 설정합니다. 이것은 3종의 FOR문 모두(정수FOR루프, 레코드 세트FOR루프, 동적 레코드 세트FOR루프)에 적용합니다. FOUND는 FOR루프가 종료했을 때, 이와 같이 설정됩니다. 즉, 실행 루프내에서는 FOR문에 의해 FOUND가 변경되지 않습니다. 다만, 루프 본문 내의 타종류의 명령문을 실행하는 것에 의해, 변경될지도 모릅니다.
FOUND는 각각의 PL/pgSQL함수 내부의 로컬 변수입니다. FOUND에 대해서 행해진 모든 변경은, 현재의 함수에만 영향을 줍니다.