절차적 언어(Procedural language)
AgensGraph는 SQL과 C 이외의 언어로도 사용자 정의 함수를 작성할 수 있습니다. 이러한 다른 언어들을 일반적으로 절차적 언어(procedural languages, PLs)라고 부릅니다. 절차적 언어로 작성된 함수의 경우, 데이터베이스 서버는 함수의 소스 텍스트를 해석할 수 없습니다. 따라서 해당 작업은 그 언어의 세부 사항을 알고 있는 특수한 핸들러로 전달됩니다. 핸들러는 파싱, 구문 분석, 실행을 포함한 모든 작업을 수행합니다. 핸들러 자체는 C 언어 함수이며, 다른 C 함수와 마찬가지로 공유 객체로 컴파일되어 필요 시 로드됩니다. 현재 AgensGraph에는 PL/pgSQL, PL/Tcl, PL/Perl, PL/Python의 네 가지 절차적 언어가 있습니다.
설치
절차적 언어는 사용하려는 각 데이터베이스에 설치해야 합니다. 다만, template1 데이터베이스에 설치된 절차적 언어는 CREATE DATABASE에 의해 template1의 항목이 복사되므로, 이후에 생성되는 모든 데이터베이스에서 자동으로 사용할 수 있습니다. 데이터베이스 관리자는 어떤 언어를 어떤 데이터베이스에서 사용할 수 있는지를 결정할 수 있으며, 필요한 경우 특정 언어만 사용할 수 있도록 설정할 수 있습니다.
표준 배포판과 함께 제공되는 언어의 경우, 현재 데이터베이스에 설치하려면 CREATE EXTENSION language_name 을 실행하기만 하면 됩니다. 또는 createlang 프로그램을 사용하여 셸 명령줄에서 이 작업을 수행할 수도 있습니다. 예를 들어, template1 데이터베이스에 PL/Python이라는 언어를 설치하려면 다음 예제를 참고하십시오.
createlang plpython template1
아래에 설명된 수동 절차는 익스텐션으로 패키징되지 않은 언어를 설치하는 경우에만 권장됩니다.
수동 절차적 언어 설치
절차적 언어는 다섯 단계로 데이터베이스에 설치할 수 있으며, 데이터베이스 슈퍼유저가 수행해야 합니다. 대부분의 경우 필요한 SQL 명령은 익스텐션의 설치 스크립트로 패키징하여 CREATE EXTENSION을 사용해 실행할 수 있습니다.
언어 핸들러의 공유 객체를 컴파일하여 적절한 라이브러리 디렉터리에 설치해야 합니다. 이는 일반적인 사용자 정의 C 함수가 포함된 모듈을 빌드하고 설치하는 방식과 동일하게 동작합니다. 언어 핸들러는 실제 프로그래밍 언어 엔진을 제공하는 외부 라이브러리에 의존하는 경우가 많으며, 이러한 경우 공유 객체를 설치해야 합니다.
핸들러를 명령으로 선언해야 합니다.
CREATE FUNCTION handler_function_name() RETURNS language_handler AS 'path-to-shared-object' LANGUAGE C;
language_handler라는 특수한 반환 타입은 이 함수가 정의된 SQL 데이터 타입 중 하나를 반환하지 않으며 SQL 구문에서 직접 사용할 수 없음을 데이터베이스 시스템에 알려줍니다.선택적으로, 언어 핸들러는 이 언어로 작성된 익명 코드 블록(DO 명령)을 실행하는 “인라인(inline)” 핸들러 함수를 제공할 수 있습니다. 언어에서 인라인 핸들러 함수를 제공하는 경우, 다음 명령으로 선언해야 합니다.
CREATE FUNCTION inline_function_name(internal) RETURNS void AS 'path-to-shared-object' LANGUAGE C;
선택적으로, 언어 핸들러는 함수 정의를 실제로 실행하지 않고 그 정확성을 검사하는 “검증기(validator)” 함수를 제공할 수 있습니다. 검증기 함수는 CREATE FUNCTION에 의해 호출됩니다. 언어에서 검증기 함수를 제공하는 경우, 다음 명령으로 선언해야 합니다.
CREATE FUNCTION validator_function_name(oid) RETURNS void AS 'path-to-shared-object' LANGUAGE C STRICT;
마지막으로, PL은 다음 명령으로 선언해야 합니다.
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name HANDLER handler_function_name [INLINE inline_function_name] [VALIDATOR validator_function_name] ;
선택적 키워드
TRUSTED는 해당 언어가 사용자가 사용하지 않을 데이터에 대한 접근 권한을 부여하지 않음을 지정합니다. 신뢰할 수 있는(trusted) 언어는 일반 데이터베이스 사용자(즉, 슈퍼유저 권한이 없는 사용자)를 위해 설계되었으며, 함수와 트리거 프로시저를 안전하게 생성할 수 있습니다. PL 함수는 데이터베이스 서버 내에서 실행되므로,TRUSTED플래그는 데이터베이스 서버나 파일 시스템에 대한 접근을 허용하지 않는 언어에만 부여해야 합니다. PL/pgSQL, PL/Tcl, PL/Perl 언어는 신뢰할 수 있는 언어로 간주됩니다. PL/TclU, PL/PerlU, PL/PythonU 언어는 무제한 기능을 제공하도록 설계되었으므로 신뢰할 수 있는 언어로 표시해서는 안 됩니다.기본 AgensGraph 설치에서는 PL/pgSQL 언어용 핸들러가 빌드되어 “library” 디렉터리에 설치됩니다. PL/pgSQL 언어 자체는 모든 데이터베이스에 설치됩니다. Tcl 지원이 구성되고 PL/Tcl 및 PL/TclU용 핸들러가 빌드되어 라이브러리 디렉터리에 설치되지만, 언어 자체는 기본적으로 데이터베이스에 설치되지 않습니다. 마찬가지로, Perl 지원이 구성되어 PL/Perl 및 PL/PerlU 핸들러가 빌드 및 설치되고, Python 지원이 구성되어 PL/PythonU 핸들러가 설치되더라도 이러한 언어들은 기본적으로 설치되지 않습니다.
PL/pgSQL
소개
PL/pgSQL은 AgensGraph의 로드 가능한(loadable) 절차적 언어입니다. PL/pgSQL의 설계 목표는 다음과 같은 기능을 갖춘 로드 가능한 절차적 언어가 되는 것입니다.
함수와 트리거 프로시저를 생성하는 데 사용할 수 있습니다.
SQL 언어에 제어 구조를 추가합니다.
복잡한 계산을 수행할 수 있습니다.
모든 사용자 정의 타입, 함수, 연산자를 상속합니다.
서버가 신뢰하도록 정의할 수 있습니다.
사용하기 쉽습니다.
PL/pgSQL로 생성된 함수는 내장 함수를 사용할 수 있는 모든 곳에서 사용할 수 있습니다. 예를 들어, 복잡한 조건을 처리하는 함수를 생성한 다음, 이후에 그 함수를 연산자로 정의하거나 인덱스 표현식에서 사용할 수 있습니다.
AgensGraph에서는 PL/pgSQL이 기본적으로 설치되어 있습니다. 다만, 여전히 로드 가능한 모듈이므로 보안을 엄격하게 고려하는 관리자는 PL/pgSQL을 제거할 수 있습니다.
PL/pgSQL의 장점
SQL은 데이터베이스에서 사용되는 쿼리 언어입니다. SQL은 배우기 쉽지만, 모든 SQL 구문은 데이터베이스 서버에서 구문 단위로 개별 실행되어야 합니다.
즉, 클라이언트 애플리케이션은 데이터베이스 서버에 쿼리를 개별적으로 전송하고, 각 쿼리가 처리될 때까지 기다린 다음, 결과를 받아 계산하고, 그 후 다음 쿼리를 서버로 전송합니다. 이러한 과정은 내부 처리를 발생시키며, 데이터베이스와 클라이언트가 서로 다른 머신에 있는 경우 네트워크 부하를 유발합니다.
PL/pgSQL은 절차적 언어로서 사용하기 쉽고 SQL도 사용하기 쉬우므로, 데이터베이스 서버 내에서 쿼리와 연산을 그룹화하여 클라이언트/서버 통신 부하를 절약할 수 있습니다.
클라이언트와 서버 간의 불필요한 통신을 제거합니다.
클라이언트는 불필요한 중간 결과를 보유하거나 클라이언트와 서버 사이에서 전송할 필요가 없습니다.
반복적인 쿼리 파싱을 수행할 필요가 없습니다.
이러한 요인들로 인해 저장된 함수를 사용하지 않는 애플리케이션에 비해 눈에 띄는 성능 향상을 기대할 수 있습니다.
또한, PL/pgSQL은 SQL의 모든 데이터 타입, 연산, 함수를 사용할 수 있습니다.
지원되는 인수 및 결과 데이터 타입
PL/pgSQL로 작성된 함수는 서버에서 지원하는 스칼라 또는 배열 데이터 타입을 인수로 받을 수 있으며, 결과를 반환할 수 있습니다. 지정된 복합 타입(행 타입)을 사용하거나 반환할 수도 있습니다. PL/pgSQL 함수는 레코드도 반환할 수 있습니다.
PL/pgSQL 함수는 VARIADIC 마커를 사용하여 선언함으로써 가변적인 개수의 인수를 허용할 수 있습니다. 이는 SQL 함수와 정확히 동일한 방식으로 동작합니다.
PL/pgSQL 함수는 anyelement, anyarray, anynonarray, anyenum, anyrange와 같은 다양한 타입을 받고 반환하도록 선언할 수 있습니다. 다형성(polymorphic) 함수가 처리하는 실제 데이터 타입은 호출마다 달라질 수 있습니다.
PL/pgSQL의 구조
PL/pgSQL로 작성된 함수는 다음과 같이 CREATE FUNCTION(명령)을 실행하여 서버에 정의됩니다.
CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;
함수 본문은 단순히 CREATE FUNCTION과 연결된 리터럴 문자열입니다. 함수 본문을 작성할 때는 일반적인 작은따옴표 구문을 사용하는 것보다 달러($) 인용을 사용하는 것이 더 유용합니다. 달러 인용 부호가 없다면 함수 본문의 작은따옴표나 백슬래시를 이중으로 처리하여 이스케이프해야 합니다. 이 절의 거의 모든 예제는 함수 본문에서 달러 인용 리터럴을 사용합니다.
PL/pgSQL은 블록 구조 언어입니다. 함수 정의의 전체 텍스트는 하나의 블록이어야 합니다. 블록은 다음과 같이 정의됩니다.
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];
블록 내의 각 선언과 각 구문은 세미콜론으로 끝납니다. 위에서 보인 것처럼 다른 블록 내에 나타나는 블록은 END 뒤에 세미콜론이 있어야 합니다. 다만, 함수 본문의 끝에는 세미콜론이 필요하지 않습니다.
팁: 일반적으로 BEGIN 바로 뒤에 세미콜론을 잘못 사용하는 경우가 매우 흔합니다. 이는 올바르지 않으며 구문 오류를 발생시킵니다.
Label은 EXIT 구문에서 사용하기 위해 블록을 식별하거나 블록에서 선언된 변수의 이름을 제한하려는 경우에만 필요합니다. label이 END 뒤에 위치하는 경우, 블록 시작 부분의 label과 일치해야 합니다. 식별자는 일반 SQL 명령과 마찬가지로 큰따옴표가 없는 경우 기본적으로 소문자로 변환됩니다.
주석은 일반 SQL과 동일한 방식으로 PL/pgSQL 코드에서도 동작합니다. 두 개의 대시(–)는 (해당 줄의 시작부터 끝까지) 주석으로 인식됩니다. 주석을 블록으로 처리하려면 /* 와 */ 사이에 주석을 포함하면 됩니다.
블록의 구문 섹션에 있는 모든 구문은 하위 블록(sub-block)이 될 수 있습니다. 하위 블록은 논리적 그룹화나 소규모 변수 그룹의 지역화를 위해 사용될 수 있습니다. 하위 블록에서 선언된 변수는 하위 블록이 지속되는 동안 외부 블록에 있는 비슷한 이름의 변수를 가립니다. 블록 이름을 지정하면 외부 변수에 접근할 수 있습니다.
CREATE FUNCTION somefunc() RETURNS integer AS $$
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;
참고: 실제로 PL/pgSQL 함수의 본문을 둘러싸는 숨겨진 “외부 블록(outer block)”이 있습니다. 이 블록은 함수의 매개변수 선언(있는 경우)과 FOUND와 같은 특수 변수를 제공합니다. 외부 블록은 함수의 이름으로 레이블이 지정됩니다. 즉, 매개변수와 특수 변수를 함수의 이름으로 한정할 수 있습니다.
PL/pgSQL에서 구문을 그룹화하기 위해 BEGIN/END를 사용하는 것은 트랜잭션 제어를 위한 유사한 이름의 SQL 명령과 혼동하지 않는 것이 중요합니다. PL/pgSQL의 BEGIN/END는 그룹화에만 사용되며, 어떠한 트랜잭션도 시작하거나 종료하지 않습니다. 함수와 트리거 프로시저는 항상 외부 쿼리에 의해 설정된 트랜잭션 내에서 실행됩니다. 트랜잭션을 시작할 컨텍스트가 없으므로 트랜잭션을 시작하거나 커밋할 수 없습니다. 다만, 블록이 EXCEPTION 절을 포함하므로 외부 트랜잭션에 영향을 주지 않고 롤백할 수 있는 하위 트랜잭션을 구성할 수는 있습니다.
선언
블록에서 사용되는 모든 변수는 블록의 선언 섹션에서 선언되어야 합니다. (유일한 예외는 정수 범위를 반복하는 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 절이 주어지면, 블록 진입 시 변수에 할당되는 초기값을 지정합니다. DEFAULT 절이 제공되지 않으면, 변수는 SQL null 값으로 초기화됩니다. CONSTANT 옵션은 초기화 이후 변수에 값이 할당되는 것을 막아, 블록이 지속되는 동안 값이 상수로 유지되도록 합니다. COLLATE 옵션은 변수에 사용할 데이터 정렬(collation)을 지정합니다. NOT NULL이 지정되면, 실행 시 null 값이 할당될 경우 런타임 오류가 발생합니다. NOT NULL로 선언된 모든 변수는 null이 아닌 기본값을 지정해야 합니다. “:=” 대신 등호(=)를 사용할 수 있으며, 이는 PL/SQL과 호환됩니다.
변수의 기본값은 (함수 호출당 한 번이 아니라) 블록에 진입할 때마다 평가되어 변수에 할당됩니다. 예를 들어, timestamp 타입의 변수에 now()를 할당하면, 함수는 사전 컴파일된 시간이 아니라 현재 함수 호출 시간을 갖게 됩니다.
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;
함수 매개변수 선언
함수에 전달되는 매개변수는 $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로 참조할 수 있지만, 두 번째 경우에는subtotal로 표시할 수 없습니다. (내부 블록에 label을 붙이면, 그 부분적인 label을 대신 해당 label로 한정할 수 있습니다.)
PL/pgSQL 함수가 출력 매개변수로 선언되면, 출력 매개변수는 일반 입력 매개변수와 동일한 방식으로 $n 이름과 선택적 별칭을 갖습니다. 출력 매개변수는 사실상 NULL로 시작하는 변수를 나타냅니다. 이 변수는 함수 실행 중에 할당되어야 합니다. 매개변수의 최종 값이 반환될 값입니다. 예를 들어, 판매세(sales-tax) 예제는 다음과 같이 작성할 수 있습니다.
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
출력 매개변수는 여러 값을 반환할 때 가장 유용합니다. 다음은 간단한 예입니다.
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;
이는 사실상 함수의 결과에 대한 익명 레코드 타입을 생성합니다. RETURNS 절이 제공되는 경우, RETURNS record를 지정해야 합니다.
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 을 지정하는 것과 정확히 동일합니다.
별칭(Alias)
함수 매개변수뿐만 아니라 모든 변수에 대해 별칭을 선언할 수 있습니다. 별칭은 실제 사용 사례에서 트리거 프로시저의 NEW나 OLD와 같은 미리 정의된 이름을 가진 변수에 다른 이름을 할당하는 데 사용됩니다. 예를 들어 다음과 같습니다.
DECLARE
prior ALIAS FOR old;
updated ALIAS FOR new;
ALIAS는 동일한 객체에 이름을 붙이는 두 가지 방법을 만들어내므로, 무분별하게 사용하면 혼란을 줄 수 있습니다. 별칭은 미리 정의된 이름을 무시하려는 목적으로만 사용하는 것이 가장 좋습니다.
표현식
PL/pgSQL 구문에서 사용되는 모든 표현식은 서버의 기본 SQL 실행기를 사용하여 처리됩니다. PL/pgSQL 구문은 다음과 같이 작성할 수 있습니다.
IF expression THEN ...
PL/pgSQL은 기본 SQL 엔진과 동일한 쿼리를 제공하여 표현식을 평가합니다. SELECT 명령을 구성하는 동안 PL/pgSQL 변수 이름은 매개변수로 변경됩니다. 이를 통해 SELECT에 대한 쿼리 계획을 한 번 준비한 다음, 변수의 서로 다른 값을 사용하는 이후의 평가에 이를 재사용할 수 있습니다. 예를 들어, 두 개의 정수 변수 x와 y를 다음과 같이 작성하면
IF x < y THEN ...
다음과 같이 사용됩니다.
PREPARE statement_name(integer, integer) AS SELECT $1 < $2;
이 준비된 구문은 IF 구문이 실행될 때마다 제공되는 PL/pgSQL 변수의 값으로 수행됩니다. 일반적으로 이와 같은 구체적인 정보는 PL/pgSQL 사용자에게 그다지 유용하지 않습니다. 다만, 문제를 진단하는 경우에는 알아둘 가치가 있습니다.
기본 구문
이 절과 이후의 절에서는 PL/pgSQL이 명시적으로 이해하는 모든 구문 유형을 설명합니다. 이러한 구문 유형의 일부로 인식되지 않는 것들은 SQL 명령으로 간주되어 기반 데이터베이스 엔진으로 전송되어 실행됩니다.
할당(Assignment)
PL/pgSQL 변수에 값을 할당하려면 다음과 같이 합니다.
variable { := | = } expression;
앞서 언급한 것처럼, 이러한 구문의 표현식은 기반 데이터베이스 엔진으로 전송되는 SQL SELECT(명령)를 사용하여 얻어집니다. 표현식은 단일 값을 가져야 합니다(변수가 행 또는 레코드 변수인 경우 행 값일 수 있습니다). 대상 변수는 단순 변수(선택적으로 블록 이름으로 정의됨), 행 또는 레코드 변수 필드, 또는 단순 변수나 필드인 배열 요소일 수 있습니다. “:=” 대신 등호(=)를 사용할 수 있으며, 이는 PL/SQL과 호환됩니다.
표현식의 결과 데이터 타입이 변수의 데이터 타입과 일치하지 않거나 특정 길이 또는 정밀도와 일치하지 않는 경우, PL/pgSQL 인터프리터는 출력 함수의 결과 타입과 입력 함수의 변수 타입을 사용하여 결과 값을 암시적으로 변환하려고 시도합니다. 결과 값의 문자열 형태가 입력 함수에서 허용되지 않는 타입인 경우, 입력 함수가 런타임 오류를 발생시킬 수 있습니다.
예를 들어 다음과 같습니다.
tax := subtotal * 0.06;
my_record.user_id := 20;
결과 없이 명령 실행하기
행을 반환하지 않는 SQL 명령(예: RETURNING 절이 없는 INSERT)의 경우, 명령을 단순히 작성하기만 하면 PL/pgSQL 함수 내에서 실행할 수 있습니다.
명령 텍스트에 나타나는 PL/pgSQL 변수 이름은 매개변수로 취급되며, 변수의 현재 값이 런타임에 매개변수 값으로 제공됩니다. 이는 표현식에 대해 위에서 설명한 과정과 정확히 동일합니다.
이러한 방식으로 SQL 명령을 실행하면, PL/pgSQL은 명령 실행 계획을 캐시하여 재사용할 수 있습니다.
유용한 결과 값이 없는 함수를 호출하는 것은 때때로 표현식이나 SELECT 쿼리를 평가하는 데 유용할 수 있으며, 결과는 무시될 수 있습니다. PL/pgSQL에서 이를 수행하려면 PERFORM 구문을 사용하는 것이 좋습니다.
PERFORM query;
그러면 쿼리가 실행되고 결과는 폐기됩니다. SQL SELECT 명령을 작성하는 것과 동일한 방식으로 쿼리를 작성하고, 시작 키워드 SELECT를 PERFORM으로 대체해야 합니다. WITH 쿼리의 경우 PERFORM을 사용하고 쿼리를 괄호로 묶습니다(이 경우 쿼리는 한 행만 반환할 수 있습니다). 결과를 반환하지 않는 명령과 마찬가지로, PL/pgSQL 변수는 쿼리로 대체되며 계획은 동일한 방식으로 캐시됩니다. 특수 변수 FOUND는 쿼리가 최소 한 행을 생성한 경우 true로 설정되고, 행이 생성되지 않은 경우 false로 설정됩니다.
참고: 직접
SELECT를 작성하여 이 결과를 얻을 수 있다고 기대할 수 있지만, 현재로서는PERFORM이 이를 수행하는 유일한 방법입니다.SELECT와 같이 행을 반환할 수 있는 SQL 명령은 다음 절에서 설명하는INTO절이 없으면 오류로 취급되어 거부됩니다.
예를 들어 다음과 같습니다.
PERFORM create_mv('cs_session_page_requests_mv', my_query);
단일 행 결과로 쿼리 실행하기
SQL 명령의 결과로 생성된 단일 행(여러 열일 수 있음)을 생성한 레코드 변수, 행 타입 변수, 또는 스칼라 변수 목록에 할당할 수 있습니다. 이는 기본 SQL 명령을 작성하고 INTO 절을 추가하여 수행합니다. 예를 들어, target은 레코드 변수, 행 변수, 또는 쉼표로 구분된 단순 변수와 레코드/행 필드 목록일 수 있습니다.
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;
행을 반환하지 않는 명령의 경우, 위에서 설명한 것처럼 PL/pgSQL 변수가 쿼리의 나머지 부분으로 대체되며 계획은 캐시됩니다. 이는 SELECT(RETURNING을 사용하는 INSERT/UPDATE/DELETE)에서 동작하며, 행 집합 결과를 반환하는 유틸리티 명령(예: EXPLAIN)에 따라 동작합니다. INTO 절을 제외하면, SQL 명령은 PL/pgSQL 외부에서 작성된 것과 동일합니다.
팁: PL/pgSQL 함수 내에서
SELECT의 결과로 테이블을 생성하려면CREATE TABLE ... AS SELECT구문을 사용해야 합니다.
행 또는 변수 목록을 target으로 사용할 때, 쿼리의 결과 열은 개수와 데이터 타입에 대해 target 구조와 정확히 일치해야 합니다. 그렇지 않으면 런타임 오류가 발생합니다. 레코드 변수가 target인 경우, 쿼리 결과 열의 행 타입을 자동으로 구성합니다.
INTO 절은 SQL 명령의 거의 모든 위치에 나타날 수 있습니다. 일반적으로 SELECT 명령의 select_expressions 목록 바로 앞이나 뒤에 작성되거나, 다른 명령 유형의 경우 명령의 끝에 작성됩니다.
INTO 절에 STRICT가 지정되지 않은 경우, target 은 쿼리가 반환한 첫 번째 행으로 설정되며, 쿼리가 행을 반환하지 않은 경우 null로 설정됩니다. (ORDER BY를 사용하지 않으면 “첫 번째 행”은 명확하게 정의되지 않습니다.) 첫 번째 행 이후의 결과 행은 폐기됩니다. 특수 FOUND 변수를 확인하여 행이 반환되었는지 확인할 수 있습니다.
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
STRICT 옵션이 지정된 경우, 쿼리는 정확히 단일 행을 반환해야 합니다.
그렇지 않으면 NO_DATA_FOUND(행 없음) 또는 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가 true로 설정됩니다.
RETURNING을 사용하는 INSERT/UPDATE/DELETE의 경우, STRICT가 지정되지 않더라도 둘 이상의 행이 반환되면 PL/pgSQL은 오류를 보고합니다. 이는 영향을 받은 행을 반환해야 하는지 결정할 ORDER BY와 같은 옵션이 없기 때문입니다.
함수에 대해 print_strict_params가 활성화되어 있고 STRICT의 요구 사항이 충족되지 않아 오류가 발생하는 경우, 오류 메시지의 DETAIL에는 쿼리에 전달된 매개변수에 대한 정보가 포함됩니다. plpgsql.print_strict_params를 설정하여 모든 함수에 대한 print_strict_params 설정을 변경할 수 있지만, 이는 이후에 작성되는 함수에만 영향을 줍니다. 컴파일러 옵션을 사용하여 함수별로 활성화하는 것도 가능합니다. 예를 들어 다음과 같습니다.
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;
실패하는 경우, 이 함수는 다음과 같은 오류 메시지를 생성할 수 있습니다.
ERROR: query returned no rows
DETAIL: parameters: $1 = 'nosuchuser'
CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
참고:
STRICT옵션은 Oracle PL/SQL의SELECT INTO및 관련 구문의 동작과 일치합니다.
동적 명령 실행하기
PL/pgSQL 함수 내에서 동적 명령(즉, 실행될 때마다 서로 다른 테이블이나 서로 다른 데이터 타입을 포함하는 명령)을 생성해야 하는 경우가 종종 있습니다. 명령에 대한 계획을 캐시하려는 PL/pgSQL의 일반적인 시도는 이 시나리오에서 동작하지 않습니다. 이러한 종류의 문제를 처리하기 위해 EXECUTE 구문이 제공됩니다.
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
여기서 command-string 은 실행할 명령을 포함하는 문자열(text 타입)을 생성하는 표현식입니다. 선택적 target 은 레코드 변수, 행 변수, 또는 쉼표로 구분된 단순 변수이며, 명령의 결과가 저장될 레코드/행 필드 목록입니다. 선택적 USING 표현식은 명령에 삽입할 값을 제공합니다.
계산된 명령 문자열에서는 PL/pgSQL 변수를 대체할 수 없습니다. 필요한 모든 변수 값은 구성된 대로 명령 문자열에 삽입되어야 합니다. 또는 아래에 설명된 대로 매개변수를 사용할 수 있습니다.
EXECUTE를 통해 실행되는 명령에 대해서는 캐싱 계획도 없습니다. 대신, 명령은 구문이 실행될 때마다 항상 스케줄링됩니다. 따라서 함수 내에서 명령 문자열을 동적으로 생성하여 다른 테이블과 열에서 작업을 수행할 수 있습니다.
INTO 절은 반환된 행의 결과를 할당하는 SQL 명령의 결과를 지정합니다. 행 또는 변수 목록이 제공되면, 쿼리 결과의 구조와 정확히 일치해야 합니다(레코드 변수가 사용되면 결과 구조와 일치하도록 자동으로 구성됩니다). 여러 행이 반환되면 첫 번째 행만 INTO 변수에 할당되며, 행이 반환되지 않으면 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;
매개변수 기호는 데이터 값에만 사용할 수 있습니다. 동적으로 결정되는 테이블 또는 열 이름을 사용하려면 해당 문자를 명령 문자열에 삽입해야 합니다. 예를 들어, 앞의 쿼리를 동적으로 선택된 테이블에서 수행해야 하는 경우 다음과 같이 할 수 있습니다.
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
더 간단한 방법은 테이블 또는 열 이름에 format()의 %I를 사용하는 것입니다(줄바꿈으로 구분되어 연결된 문자열).
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND inserted <= $2', tabname)
INTO c
USING checked_user, checked_date;
매개변수 기호의 또 다른 제한 사항은 SELECT, INSERT, UPDATE, DELETE 명령에서만 동작한다는 것입니다. 다른 구문 유형(일반적으로 유틸리티 구문이라고 함)에서는 값이 데이터 값이더라도 텍스트로 값을 삽입해야 합니다.
위의 첫 번째 예제처럼, 단순한 상수 명령 문자열과 USING 매개변수를 사용한 EXECUTE는 PL/pgSQL에서 직접 명령을 작성하고 PL/pgSQL 변수가 자동으로 대체되도록 하는 것과 기능적으로 동일합니다. 중요한 차이점은 EXECUTE가 각 실행 시 명령을 다시 계획하여 현재 매개변수 값과 연관된 계획을 생성한다는 것입니다. 반면, PL/pgSQL은 일반 계획을 생성하여 재사용을 위해 캐시할 수 있습니다. 최적의 계획이 매개변수 값에 크게 의존하는 상황에서는 EXECUTE를 사용하여 일반 계획이 선택되지 않도록 하는 것이 좋습니다.
SELECT INTO는 현재 EXECUTE 내에서 지원되지 않습니다. 대신, 일반 SELECT 명령을 실행하고 INTO를 EXECUTE의 일부로 지정해야 합니다.
제어 구조
제어 구조는 아마도 PL/pgSQL에서 가장 유용하고 중요한 부분일 것입니다. PL/pgSQL 제어 구조를 사용하면 매우 유연하고 강력한 방식으로 AgensGraph 데이터를 조작할 수 있습니다.
함수에서 반환하기
함수에서 데이터를 반환할 수 있는 명령에는 RETURN과 RETURN NEXT의 두 가지가 있습니다.
RETURN
RETURN expression;
표현식이 있는 RETURN은 함수를 종료하고 표현식의 값을 호출자에게 반환합니다. 이 형식은 집합을 반환하지 않는 PL/pgSQL 함수에 사용됩니다.
스칼라 타입을 반환하는 함수에서는 할당에 대해 설명한 것처럼 표현식의 결과가 자동으로 함수의 반환 타입으로 변환됩니다. 다만, 복합(행) 값을 반환하려면 요청된 열의 집합을 올바르게 전달하는 표현식을 작성해야 합니다. 이는 명시적 타입 변환을 필요로 할 수 있습니다.
출력 매개변수를 사용하여 함수를 선언하는 경우, 표현식 없이 RETURN만 작성하면 됩니다. 출력 매개변수 변수의 현재 값이 반환됩니다.
void를 반환하는 함수를 선언하는 경우, RETURN 구문을 사용하여 함수를 일찍 종료할 수 있습니다. RETURN 뒤에 표현식을 작성하지 마십시오.
함수의 반환 값은 정의되지 않은 상태로 남을 수 없습니다. RETURN 구문을 사용하지 않고 제어가 함수의 최상위 블록 끝에 도달하면 런타임 오류가 발생합니다. 다만, 이 제한은 출력 매개변수가 있는 함수와 void를 반환하는 함수에는 적용되지 않습니다. 이러한 경우 최상위 블록이 완료되면 RETURN 구문이 자동으로 실행됩니다.
예를 들어 다음과 같습니다.
-- 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
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
PL/pgSQL 함수가 SETOF sometype 을 반환하도록 선언된 경우, 약간 다른 절차를 따라야 합니다. 이러한 경우, 반환할 개별 항목은 RETURN NEXT 또는 RETURN QUERY의 순서로 지정되며, 인수가 없는 최종 RETURN 명령은 함수의 실행이 완료되었음을 나타내는 데 사용됩니다. RETURN NEXT는 스칼라 및 복합 데이터 타입 모두에 사용할 수 있습니다. 결과의 전체 “테이블”은 복합 결과 타입으로 반환됩니다. RETURN QUERY는 쿼리 실행 결과를 함수의 결과 집합에 추가합니다. RETURN NEXT와 RETURN QUERY는 단일 집합 반환 함수에서 자유롭게 혼합하여 사용할 수 있으며, 이 경우 결과가 연결됩니다.
RETURN NEXT와 RETURN QUERY는 실제로 함수에 의해 반환되지 않습니다. 단순히 함수의 결과 집합에 0개 이상의 행을 추가할 수 있습니다. 실행은 PL/pgSQL 함수의 다음 구문으로 계속됩니다. 결과 집합은 연속적인 RETURN NEXT 또는 RETURN QUERY 명령이 실행될 때 구축됩니다. 인수가 없어야 하는 마지막 return은 제어가 함수를 종료하게 합니다(또는 제어가 함수의 끝에 도달할 수 있습니다).
RETURN QUERY에는 동적으로 실행할 쿼리를 지정하는 RETURN QUERY EXECUTE 변형이 있습니다. EXECUTE와 마찬가지로, 매개변수 표현식을 USING을 통해 계산된 쿼리 문자열에 삽입할 수 있습니다.
출력 매개변수를 사용하여 함수를 선언하는 경우, 표현식 없이 RETURN NEXT를 작성해야 합니다. 각 실행 시 결과 행의 최종 반환을 위해 출력 매개변수 변수의 현재 값이 저장됩니다. 출력 매개변수가 여러 개인 경우 SETOF record를 반환하는 함수를 선언하고, sometype 타입의 출력 매개변수가 하나뿐인 경우 출력 매개변수를 사용하는 집합 반환 함수를 만들기 위해 SETOF sometype 을 선언해야 합니다.
다음은 RETURN NEXT를 사용하는 함수의 예입니다.
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
SELECT * FROM get_all_foo();
다음은 RETURN QUERY를 사용하는 함수의 예입니다.
CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
RETURN QUERY SELECT flightid
FROM flight
WHERE flightdate >= $1
AND flightdate < ($1 + 1);
-- Since execution is not finished, we can check whether rows were returned
-- and raise exception if not.
IF NOT FOUND THEN
RAISE EXCEPTION 'No flight at %.', $1;
END IF;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);
참고: 위에서 설명한 것처럼, RETURN NEXT와 RETURN QUERY의 현재 구현은 함수가 반환되기 전에 전체 결과 집합을 저장합니다. 즉, PL/pgSQL 함수가 매우 큰 결과 집합을 생성하면 성능이 저하될 수 있습니다. 메모리 고갈을 방지하기 위해 데이터가 디스크에 기록되지만, 전체 결과 집합이 생성될 때까지 함수 자체는 반환되지 않습니다. 현재 데이터가 디스크에 기록되기 시작하는 지점은 work_mem 설정 변수에 의해 제어됩니다. 더 많은 양의 결과 집합을 저장할 수 있을 만큼 충분한 메모리를 가진 관리자는 이 매개변수를 늘려야 합니다.
단순 루프
LOOP, EXIT, CONTINUE, WHILE, FOR, FOREACH 구문을 사용하면, 일련의 명령을 정리하여 PL/pgSQL 함수가 이를 반복할 수 있도록 할 수 있습니다.
LOOP
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
LOOP는 EXIT 또는 RETURN 구문에 의해 종료될 때까지 무한히 반복되는 무조건 루프를 정의합니다. 선택적 label 은 중첩 루프 내의 EXIT 및 CONTINUE 구문이 해당 구문이 참조하는 루프를 지정하는 데 사용됩니다.
EXIT
EXIT [ label ] [ WHEN boolean-expression ];
label 이 지정되지 않으면, 가장 안쪽 루프가 종료되고 END LOOP 뒤의 구문이 다음으로 실행됩니다. 주어진 label 은 현재 또는 중첩 루프나 블록의 일부 외부 레벨의 레이블이어야 합니다. 그러면 명명된 루프 또는 블록이 종료되고, 구문은 해당 루프/블록의 END 뒤에서 계속됩니다.
WHEN이 지정되면, boolean-expression 이 true인 경우에만 루프가 종료됩니다. 그렇지 않으면 제어가 EXIT 뒤의 구문으로 넘어갑니다.
EXIT는 모든 유형의 루프에서 사용할 수 있습니다. 무조건 루프에서의 사용에만 국한되지 않습니다.
BEGIN 블록과 함께 사용하면, EXIT는 블록이 종료된 후 다음 구문으로 제어를 넘깁니다. 이 목적을 위해서는 label을 사용해야 합니다. label이 없는 EXIT는 BEGIN 블록과 일치하는 것으로 간주되지 않습니다.
다음은 예입니다.
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
<<ablock>>
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT ablock; -- causes exit from the BEGIN block
END IF;
-- computations here will be skipped when stocks > 100000
END;
CONTINUE
CONTINUE [ label ] [ WHEN boolean-expression ];
label 이 주어지지 않으면, 가장 안쪽 루프의 다음 반복이 시작됩니다. 즉, 루프 본문의 나머지 모든 구문을 건너뛰고 다른 루프 반복이 필요한지 결정하기 위해 루프 제어가 (있는 경우) 반환됩니다. label 이 있으면, 계속할 루프의 label을 지정합니다.
WHEN이 지정되면, boolean-expression 이 true인 경우에만 루프의 다음 반복이 시작됩니다. 그렇지 않으면 제어가 CONTINUE 뒤의 구문으로 넘어갑니다.
CONTINUE는 모든 유형의 루프에서 사용할 수 있습니다. 무조건 루프에서의 사용에만 국한되지 않습니다.
다음은 예입니다.
LOOP
-- some computations
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;
WHILE
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
WHILE 구문은 boolean-expression 이 true로 평가되는 동안 일련의 구문을 반복합니다. 표현식은 루프 본문의 각 항목 바로 직전에 검사됩니다.
다음은 예입니다.
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
FOR(Integer Variant)
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
이 유형의 FOR는 정수 범위를 반복하는 루프를 생성합니다. 변수 names 는 자동으로 정수 타입으로 정의되며 루프 내부에서만 존재합니다(변수 이름의 기존 정의는 루프에서 무시됩니다). 범위의 상한과 하한을 나타내는 두 표현식은 루프에 진입할 때 한 번 평가됩니다. BY 절이 지정되지 않으면 반복 단계는 1이며, 지정되면 BY 절에 지정된 값이 루프 진입 시 한 번 평가됩니다. REVERSE가 지정되면 단계 값이 더해지지 않고 각 반복마다 차감됩니다.
정수 FOR 루프의 몇 가지 예입니다.
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i will take on the values 10,8,6,4,2 within the loop
END LOOP;
하한이 상한보다 크면(또는 REVERSE에서 하한이 더 작으면), 루프 본문은 절대 실행되지 않습니다. 오류는 발생하지 않습니다.
label 이 FOR 루프에 연결되면, 해당 label 을 사용하여 지정된 이름으로 정수 루프 변수를 참조할 수 있습니다.
쿼리 결과를 통한 루프
다른 유형의 FOR 루프를 사용하면 쿼리 결과를 반복하고 그에 따라 데이터를 조작할 수 있습니다. 구문은 다음과 같습니다.
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
target 은 레코드 변수, 행 변수, 또는 쉼표로 구분된 스칼라 변수 목록입니다. target 은 쿼리 결과의 각 행에 연속적으로 할당되며, 각 행에 대해 루프 본문이 실행됩니다. 다음은 예입니다.
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
RAISE NOTICE 'Refreshing materialized views...';
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- Now "mviews" has one record from cs_materialized_views
RAISE NOTICE 'Refreshing materialized view %s ...', quote_ident(mviews.mv_name);
EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
END LOOP;
RAISE NOTICE 'Done refreshing materialized views.';
RETURN 1;
END;
$$ LANGUAGE plpgsql;
루프가 EXIT 구문에 의해 종료되더라도, 루프 이후에 마지막으로 할당된 행 값에 여전히 접근할 수 있습니다.
FOR 구문 유형에 사용되는 query 는 호출자에게 행을 반환하는 모든 SQL 명령일 수 있습니다. SELECT가 가장 일반적인 경우이지만, RETURNING을 사용하는 INSERT, UPDATE, DELETE도 사용할 수 있습니다. EXPLAIN과 같은 일부 유틸리티 명령도 동작합니다.
PL/pgSQL 변수는 쿼리 텍스트로 대체되며 쿼리 계획은 재사용 가능성을 위해 캐시됩니다.
FOR-IN-EXECUTE 구문은 행을 반복하는 또 다른 방법입니다.
[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
statements
END LOOP [ label ];
이 구문은 소스 쿼리가 FOR 루프의 각 항목에서 평가되고 다시 계산되는 문자열 표현식으로 지정된다는 점을 제외하면 이전 형식과 동일합니다. 이를 통해 프로그래머는 일반 EXECUTE 구문과 마찬가지로 사전 계획된 쿼리의 속도나 동적 쿼리의 유연성을 선택할 수 있습니다. EXECUTE와 마찬가지로, 매개변수 값을 USING을 통해 동적 명령에 삽입할 수 있습니다.
결과를 반복해야 하는 쿼리를 지정하는 또 다른 방법은 이를 커서로 선언하는 것입니다.
배열을 통한 루프
FOREACH 루프는 FOR 루프와 매우 유사하지만, SQL 쿼리가 반환한 행을 반복하는 대신 배열 값의 요소를 반복합니다. 일반적으로 FOREACH는 복합 값 표현식의 구성 요소를 반복하는 데 사용됩니다. 배열 이외의 복합체를 반복하는 변형은 나중에 추가될 수 있습니다.
배열을 반복하는 FOREACH 구문은 다음과 같습니다.
[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];
SLICE가 없거나 SLICE0이 지정되면, 루프는 expression 을 평가하여 생성된 배열의 개별 요소를 반복합니다. target 변수에 각 요소 값이 순서대로 지정되고, 각 요소에 대해 루프 본문이 실행됩니다. 다음은 배열 요소를 반복하는 예입니다.
CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
s int8 := 0;
x int;
BEGIN
FOREACH x IN ARRAY $1
LOOP
s := s + x;
END LOOP;
RETURN s;
END;
$$ LANGUAGE plpgsql;
요소는 배열의 차원 수에 관계없이 저장 순서대로 저장됩니다. target 은 일반적으로 단일 변수이지만, 복합 값 배열을 반복할 때는 변수 목록일 수 있습니다. 이러한 경우 각 배열 요소에 대해 변수가 복합 값의 연속된 열에 할당됩니다.
SLICE 값이 양수이면, FOREACH는 단일 요소가 아니라 배열의 슬라이스를 반복합니다. SLICE 값은 배열의 차원 수보다 크지 않은 정수 상수여야 합니다. target 변수는 배열이어야 하며 배열 값의 연속된 슬라이스를 받습니다. 각 슬라이스는 SLICE로 지정된 차원 수입니다. 다음은 1차원 슬라이스를 반복하는 예입니다.
CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
NOTICE: row = {7,8,9}
NOTICE: row = {10,11,12}
커서(Cursors)
전체 쿼리를 한 번에 실행하는 대신, 커서(cursors) 를 설정하여 쿼리를 캡슐화한 다음 쿼리 결과를 한 번에 몇 줄씩 읽을 수 있습니다. 이렇게 하는 이유는 결과에 많은 수의 행이 포함될 때 메모리 오버런을 피하기 위해서입니다. (다만, FOR 루프는 메모리 문제를 피하기 위해 내부적으로 커서를 사용하므로 PL/pgSQL 사용자는 일반적으로 이에 대해 걱정할 필요가 없습니다.) 더 흥미로운 사용 방법은 함수가 생성된 커서에 대한 참조를 반환하여 호출자가 행을 읽을 수 있도록 하는 것입니다. 이렇게 하면 함수에서 큰 행 집합을 반환하는 효율적인 방법을 제공합니다.
커서 변수 선언하기
PL/pgSQL 커서에 대한 모든 접근은 항상 특수 데이터 타입 refcursor인 커서 변수를 통해 수행됩니다. 커서 변수를 생성하는 한 가지 방법은 이를 refcursor 타입의 변수로 선언하는 것입니다. 또 다른 방법은 다음 커서 선언 구문을 사용하는 것입니다.
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
SCROLL이 지정되면, 커서는 뒤로 스크롤할 수 있습니다. NO SCROLL이 지정되면, 역방향 페치가 거부됩니다. 이 옵션이 지정되지 않으면, 역방향 페치가 허용되는지 여부는 쿼리에 따라 달라집니다. argument 가 지정되면, 이는 쉼표로 구분된 이름 데이터 타입 쌍 의 목록이며, 지정된 쿼리에서 매개변수 값으로 대체될 이름을 정의합니다. 이 이름을 대체할 실제 값은 나중에 커서가 열릴 때 지정됩니다.
다음은 예입니다.
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
이 세 변수는 모두 refcursor 데이터 타입을 갖지만, 첫 번째 쿼리는 모든 쿼리와 함께 사용할 수 있고, 두 번째 쿼리는 완전히 지정된 쿼리에 바인딩되며, 마지막 쿼리는 매개변수화된 쿼리에 바인딩(bound) 됩니다. (커서가 열릴 때 key는 정수 매개변수 값으로 대체됩니다.) 변수 curs1은 특정 쿼리에 바인딩되지 않았기 때문에 비바인딩(unbound) 이라고 합니다.
커서 열기
커서를 사용하여 행을 검색하려면, 커서가 이미 열려(open) 있어야 합니다. PL/pgSQL은 세 가지 유형의 OPEN 구문을 지원하며, 그중 두 가지는 비바인딩 커서 변수를 사용하고 세 번째는 바인딩 커서 변수를 사용합니다.
참고: 바인딩 커서 변수는 커서를 명시적으로 열지 않고도 사용할 수 있습니다.
OPEN FOR query
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;
커서 변수가 열리고 지정된 쿼리가 실행됩니다. 커서는 더 이상 열 수 없으며, 비바인딩 커서 변수(즉, 단순 refcursor 변수)로 선언되어야 합니다. 쿼리는 SELECT이거나 행을 반환하는 다른 것(예: EXPLAIN)이어야 합니다. 쿼리는 다른 PL/pgSQL SQL 명령과 동일한 방식으로 처리됩니다. PL/pgSQL 변수 이름이 대체되고, 쿼리 계획은 재사용 가능성을 위해 캐시됩니다. PL/pgSQL 변수가 커서 쿼리로 대체될 때, 대체될 값은 OPEN 시점의 값입니다. 변수의 후속 변경 사항은 커서의 동작에 영향을 주지 않습니다. SCROLL 및 NO SCROLL 옵션은 바인딩 커서에서와 동일한 의미를 갖습니다.
다음은 예입니다.
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN FOR EXECUTE
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
[ USING expression [, ... ] ];
커서 변수가 열리고 지정된 쿼리가 실행됩니다. 커서는 더 이상 열 수 없으며, 비바인딩 커서 변수(즉, 단순 refcursor 변수)로 선언되어야 합니다. 쿼리는 EXECUTE에서와 동일한 방식으로 문자열 표현식으로 지정됩니다. 늘 그렇듯이, 이는 유연성을 제공합니다. 즉, 쿼리 계획이 실행할 때마다 달라질 수 있으며 명령 문자열에서 변수 대체가 수행되지 않습니다. EXECUTE와 마찬가지로, 매개변수 값을 format()과 USING을 통해 동적 명령에 삽입할 수 있습니다. SCROLL 및 NO SCROLL 옵션은 바인딩 커서와 동일한 의미를 갖습니다.
다음은 예입니다.
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
이 예제에서는 테이블 이름이 format()을 통해 쿼리에 삽입됩니다. col1의 비교 값이 USING을 통해 삽입되므로 따옴표가 필요하지 않습니다.
바인딩 커서 열기
OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
이 OPEN 형식은 쿼리가 바인딩된 커서 변수를 열도록 쿼리가 선언될 때 사용됩니다. 커서는 더 이상 열 수 없습니다. 실제 인수 값 표현식의 목록은 커서가 인수를 받도록 선언된 경우에만 사용할 수 있습니다. 이러한 값은 쿼리에서 대체됩니다.
바인딩 커서에 대한 쿼리 계획은 항상 캐시 가능한 것으로 간주됩니다. 이 경우 EXECUTE는 없습니다. 커서의 스크롤 동작이 이미 결정되었기 때문에 OPEN에 대해 SCROLL 및 NO SCROLL을 지정할 수 없습니다.
인수 값은 위치(positional) 또는 명명(named) 표기법을 사용하여 전달할 수 있습니다. 위치 표기법에서는 모든 인수가 순서대로 지정됩니다. 명명 표기법에서는 각 인수의 이름이 지정되며 := 를 사용하여 인수 표현식과 구분됩니다. 호출 함수와 마찬가지로, 위치 표기법과 명명 표기법을 함께 사용할 수 있습니다.
다음은 예입니다(위의 커서 선언 예제를 사용함).
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
바인딩 커서의 쿼리에서 변수 대체가 수행되며, 이는 커서에 값을 전달하는 두 가지 방법이 있음을 의미합니다. OPEN 인수를 사용하여 명시적으로 전달하거나, 쿼리에서 PL/pgSQL 변수를 참조하여 암시적으로 값을 전달하는 것입니다. 다만, 바인딩 커서 선언 이전에 선언된 변수만 변수로 대체할 수 있습니다. 두 경우 모두 전달할 값은 OPEN 시점에 결정됩니다. 예를 들어, 위의 curs3 예제와 동일한 효과를 얻는 또 다른 방법은 다음과 같습니다.
DECLARE
key integer;
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
key := 42;
OPEN curs4;
커서 사용하기
커서가 열리면, 아래에 설명된 구문을 사용하여 이를 조작할 수 있습니다.
이 조작은 커서가 처음 열렸을 때와 동일한 방식으로 동작하므로 수행할 필요가 없습니다. 함수는 refcursor 값을 반환하여 호출자가 커서에서 작업하도록 할 수 있습니다. (내부적으로 refcursor 값은 단순히 커서에 대한 활성 쿼리를 포함하는 이른바 포털(portal)의 문자열 이름이며, 다른 refcursor 변수에 할당될 수 있습니다.)
모든 포털은 트랜잭션이 끝날 때 암시적으로 닫힙니다. 따라서 refcursor 값은 트랜잭션이 끝날 때까지 열린 커서를 참조하는 데 사용할 수 있습니다.
FETCH
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH는SELECT INTO와 같이 행 변수, 레코드 변수, 또는 쉼표로 구분된 단순 변수 목록에 다음 행을 커서의 대상으로 페치합니다. 다음 행이 없으면 대상은 NULL로 설정됩니다.SELECT INTO와 마찬가지로, 특수 변수FOUND를 확인하여 행을 얻었는지 확인할 수 있습니다.direction 절은 단일 행을 페치할 수 있다는 점을 제외하면 SQL FETCH에서 허용하는 변형 중 하나일 수 있습니다(즉,
NEXT,PRIOR,FIRST,LAST,ABSOLUTEcount,RELATIVEcount,FORWARD,BACKWARD). direction 을 생략하는 것은NEXT를 지정하는 것과 동일합니다. 커서가SCROLL옵션으로 선언되거나 열리지 않은 경우, 뒤로 이동하는 direction 값은 실패할 수 있습니다.cursor 는 열린 커서 포털을 참조하는
refcursor변수의 이름이어야 합니다.다음은 예입니다.
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
MOVE [ direction { FROM | IN } ] cursor;
MOVE는 데이터를 검색하지 않고 커서를 재배치합니다.MOVE는 커서만 재배치하고 이동된 행을 반환하지 않는다는 점을 제외하면FETCH명령과 동일하게 동작합니다.SELECT INTO와 마찬가지로, 특수 변수FOUND를 확인하여 이동할 다음 행이 있는지 확인할 수 있습니다.direction 절은 SQL FETCH 명령에서 사용되는 것과 동일한 방식으로 사용됩니다(예:
NEXT,PRIOR,FIRST,LAST,ABSOLUTEcount,RELATIVEcount,ALL,FORWARD[count|ALL],BACKWARD[count|ALL]). direction 을 생략하는 것은NEXT를 지정하는 것과 동일합니다. 커서가SCROLL옵션으로 선언되거나 열리지 않은 경우, 뒤로 이동하는 direction 값은 실패할 수 있습니다.
다음은 예입니다.MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;
UPDATE/DELETE WHERE CURRENT OF
UPDATE table SET ... WHERE CURRENT OF cursor; DELETE FROM table WHERE CURRENT OF cursor;
커서가 테이블 행에 위치하면, 커서로 해당 행을 식별하여 업데이트하거나 삭제할 수 있습니다. 커서의 쿼리에는 제한이 있으며(특히 그룹화되지 않은 쿼리), 커서에
FOR UPDATE를 사용하는 것이 가장 좋습니다.다음은 예입니다.
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
CLOSE
CLOSE cursor;
CLOSE는 열린 커서의 기본 포털을 닫습니다. 트랜잭션이 끝나기 전에 리소스를 해제하거나 다시 열 수 있도록 커서 변수를 해제하는 데 사용할 수 있습니다.다음은 예입니다.
CLOSE curs1;
커서 반환하기
PL/pgSQL 함수는 호출자에게 커서를 반환할 수 있습니다. 이 방법은 여러 행이나 열을 반환할 때, 특히 매우 큰 결과 집합을 반환할 때 유용합니다. 이를 위해 함수는 커서를 열고 커서 이름을 호출자에게 반환합니다(또는 단순히 호출자의 이름이나 포털의 이름을 사용하여 커서를 엽니다). 호출자는 커서에서 행을 페치할 수 있습니다. 커서는 호출자에 의해 닫히거나 닫힐 때 자동으로 닫힙니다.커서에 사용되는 포털의 이름은 프로그래머가 지정하거나 자동으로 생성될 수 있습니다. 포털 이름을 지정하려면,
refcursor변수가 열리기 전에 먼저 문자열을 할당해야 합니다.refcursor변수의 문자열 값은 OPEN에서 기본 포털의 이름으로 사용됩니다. 다만,refcursor변수가 null인 경우,OPEN은 기존 포털과 충돌하지 않는 이름을 자동으로 생성하여refcursor변수에 할당합니다.
참고: 바인딩 커서 변수는 이름을 나타내는 문자열 값으로 초기화되므로, 프로그래머가 커서를 열기 전에 할당을 통해 이를 무시하지 않는 한 포털 이름은 커서 변수 이름과 동일합니다. 다만, 비바인딩 커서 변수의 기본값은 처음에 null로 설정되므로, 무시되지 않는 한 고유하게 자동 생성된 이름을 갖게 됩니다.
다음 예제는 호출자가 커서 이름을 제공하는 방법입니다.
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
다음 예제는 자동 커서 이름 생성을 사용합니다.
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
다음 예제는 단일 함수에서 여러 커서를 반환하는 방법입니다.
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
커서 결과를 통한 루프
커서가 반환한 행을 반복할 수 있는 FOR 구문(아래 예제)이 있습니다.
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
statements
END LOOP [ label ];
커서 변수는 선언될 때 어떤 쿼리에 바인딩되어야 하며 이미 열려 있어서는 안 됩니다. FOR 구문은 커서를 자동으로 열고 루프가 끝나면 닫습니다. 실제 인수 값의 목록은 커서가 인수를 받도록 선언된 경우에만 나타나야 합니다. 이 값은 OPEN과 동일한 방식으로 쿼리에서 대체됩니다.
recordvar 변수는 자동으로 record 타입으로 정의되며 루프 내에서만 존재합니다(변수 이름의 기존 정의는 루프에서 무시됩니다). 커서가 반환한 각 행은 이 레코드 변수에 할당되고 루프 본문이 실행됩니다.
오류 및 메시지
오류 및 메시지 보고하기
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 설정 변수로 제어할 수 있습니다.
level 이 있으면, format 을 만들 수 있습니다(이는 표현식이 아닌 단순 문자열 리터럴이어야 합니다). 형식 문자열은 보고할 오류 메시지 텍스트를 지정합니다. 형식 문자열 뒤에는 메시지 다음에 선택적 인수 표현식을 삽입할 수 있습니다. 형식 문자열 내에서 %는 다음 선택적 인수 값의 문자열 표현식으로 대체됩니다. 리터럴 %를 출력하려면 %%를 작성해야 합니다. 인수의 개수는 형식 문자열의 % 자리 표시자 개수와 일치해야 하며, 그렇지 않으면 함수를 컴파일하는 동안 오류가 발생합니다.
이 예제에서는 v_job_id의 값이 문자열의 %를 대체합니다.
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
USING 뒤에 option = expression 항목을 작성하여 오류 보고에 추가 정보를 첨부할 수 있습니다. 각 expression 은 문자열 값 표현식일 수 있습니다. 허용되는 option 키워드는 다음과 같습니다.
MESSAGE
오류 메시지 텍스트를 설정합니다. 이 옵션은USING앞에 형식 문자열을 포함하는RAISE형식에서는 사용할 수 없습니다.DETAIL
상세한 오류 메시지를 제공합니다.HINT
힌트 메시지를 제공합니다.ERRCODE
조건 이름으로 또는 5자리 SQLSTATE 코드로 직접 보고할 오류 코드(SQLSTATE)를 지정합니다.COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA
관련 객체의 이름을 제공합니다.
이 예제는 주어진 오류 메시지와 힌트로 트랜잭션을 중단합니다.
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
다음 두 예제는 SQLSTATE를 설정하는 동일한 방법을 보여줍니다.
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
주요 인수가 보고할 조건 이름 또는 SQLSTATE인 두 번째 RAISE 구문이 있습니다. 예를 들어 다음과 같습니다.
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
또 다른 방법은 RAISE USING 또는 RAISE level USING을 만들고 나머지 모든 것을 USING 목록에 넣는 것입니다.
RAISE의 마지막 변형은 매개변수가 전혀 없습니다. 이 형식은 BEGIN 블록의 EXCEPTION 절 내에서만 사용할 수 있습니다. 현재 처리 중인 오류를 다시 발생시킵니다.
RAISE_EXCEPTION (P0001) 명령에 조건 이름이나 SQLSTATE가 지정되지 않으면, 기본적으로 RAISE_EXCEPTION (P0001)이 사용됩니다. 메시지 텍스트가 지정되지 않으면, 기본적으로 메시지 이름이 조건 이름 또는 SQLSTATE가 됩니다.
참고: SQLSTATE 코드를 오류 코드로 지정하는 경우, 미리 정의된 오류 코드에 국한되지 않고 5자리 숫자 및/또는 대문자 ASCII 문자(단,
00000제외)로 구성된 오류 코드를 선택할 수 있습니다. 세 개의 0으로 끝나는 오류 코드는 카테고리 코드이므로, 전체 카테고리를 트래핑해야만 트래핑할 수 있는 경우 오류 코드가 생성됩니다.
어서션(assertion) 검증하기
ASSERT 구문은 PL/pgSQL 함수에 디버깅 검사를 삽입하기 위한 편리한 축약 표현입니다.
ASSERT condition [ , message ];
condition 은 항상 true로 단언될 것으로 기대되는 불리언 표현식입니다. true이면 ASSERT 구문이 실행되지 않으며, false 또는 null이면 ASSERT_FAILURE 예외가 발생합니다. (condition 을 단언하는 동안 오류가 발생하면 일반 오류로 보고됩니다.)
선택적 message 가 주어진 경우, condition 이 실패하면 그 결과(null이 아닌 경우)가 기본 오류 메시지 텍스트인 “assertion failed.”를 대체합니다. 어서션이 성공하면 *message* 표현식은 수행되지 않습니다.
ASSERT 테스트는 불리언 값을 사용하는 설정 매개변수 plpgsql.check_asserts를 통해 활성화하거나 비활성화할 수 있으며, 기본값은 on입니다. 이 매개변수가 off이면 ASSERT 구문은 아무것도 하지 않습니다.
ASSERT는 일반적인 오류 조건을 보고하기 위한 것이 아니라 프로그램 버그를 감지하기 위한 것입니다. 오류 보고에는 위에서 설명한 RAISE 구문을 사용하십시오.
트리거 프로시저
PL/pgSQL은 데이터 변경이나 데이터베이스 이벤트에 대한 트리거 프로시저를 정의하는 데 사용할 수 있습니다. 트리거 프로시저는 CREATE FUNCTION(명령)으로 생성되며, 인수가 없고 반환 타입이 trigger(데이터 변경 트리거의 경우) 또는 event_trigger(데이터베이스 이벤트 트리거의 경우)로 선언됩니다. 호출을 발생시킨 조건을 설명하기 위해 PG_something 이라는 특수 로컬 변수가 자동으로 정의됩니다.
데이터 변경 트리거
데이터 변경 트리거는 인수가 없고 반환 타입이 trigger인 함수로 선언됩니다. CREATE TRIGGER에 지정된 인수를 받을 것으로 예상되더라도 함수는 인수 없이 선언되어야 합니다. 이러한 인수는 아래에 설명된 대로 TG_ARGV를 통해 전달됩니다.
PL/pgSQL 함수가 트리거에 의해 호출되면, 최상위 블록에 여러 특수 변수가 자동으로 생성됩니다. 생성되는 항목은 다음과 같습니다.
NEW
데이터 타입RECORD. 행 수준 트리거의INSERT/UPDATE작업 시 새 데이터베이스 행을 보유하는 변수입니다. 이 변수는 구문 수준 트리거와DELETE작업에서는 지정되지 않습니다.OLD
데이터 타입RECORD. 행 수준 트리거의UPDATE/DELETE작업 시 이전 데이터베이스 행을 보유하는 변수입니다. 이 변수는 구문 수준 트리거와INSERT작업에서는 지정되지 않습니다.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입니다.TG_TABLE_NAME
데이터 타입name. 트리거 호출을 발생시킨 테이블의 이름입니다.TG_TABLE_SCHEMA
데이터 타입name. 트리거 호출을 발생시킨 테이블의 스키마 이름입니다.TG_NARGS
데이터 타입integer.CREATE TRIGGER구문에서 트리거 프로시저에 주어진 인수의 개수입니다.TG_ARGV[]
데이터 타입text.CREATE TRIGGER구문의 인수 인덱스는 0부터 시작합니다. 유효하지 않은 인덱스(0보다 작거나tg_nargs이상)는 NULL 값을 반환합니다.
트리거 함수는 NULL 또는 트리거가 실행된 테이블의 구조와 정확히 일치하는 레코드/행 값을 반환해야 합니다.
BEFORE에 의해 발생한 행 수준 트리거는 null을 반환하여 트리거 관리자에게 이 행에 대한 나머지 작업을 건너뛰도록 신호를 보낼 수 있습니다(즉, 후속 트리거가 실행되지 않으며 이 행의 INSERT/UPDATE/DELETE가 발생하지 않습니다). null이 아닌 값이 반환되면 작업은 해당 행 값으로 진행됩니다. NEW의 원래 값과 다른 행 값을 반환하면 삽입하거나 업데이트할 행이 변경됩니다. 따라서 트리거 함수가 행 값을 변경하지 않고 트리거 작업이 정상적으로 성공하기를 원한다면, NEW(또는 그에 해당하는 값)를 반환해야 합니다. NEW에서 직접 단일 값을 수정하고 수정된 NEW를 반환하거나, 반환할 완전히 새로운 레코드/행을 생성하여 저장할 행을 변경할 수 있습니다. DELETE의 before 트리거의 경우, 반환 값은 직접적인 영향을 미치지 않지만, 트리거 작업을 계속하려면 null이 아니어야 합니다. DELETE 트리거에서는 NEW가 null이므로 일반적으로 반환은 의미가 없습니다. DELETE 트리거에 대한 일반적인 관용구는 OLD를 반환하는 것입니다.
INSTEAD OF 트리거(항상 행 수준 트리거이며 뷰에서만 사용할 수 있음)는 null을 반환하여 업데이트가 수행되지 않았음을 나타낼 수 있으며, 이 행에 대한 나머지 작업을 건너뛸 수 있어야 합니다(예: 주변의 INSERT/UPDATE/DELETE에 의해 영향을 받을 때 트리거가 시작되지 않고 행이 계산되지 않음). 그렇지 않으면, 트리거가 요청된 작업을 수행했음을 나타내기 위해 null이 아닌 값을 반환해야 합니다. INSERT 및 UPDATE 작업의 경우 반환 값은 NEW여야 하며, 트리거 함수를 수정하여 INSERT RETURNING 및 UPDATE RETURNING을 지원할 수 있습니다(이는 후속 트리거에 전달되는 행 값에 영향을 주거나, INSERT 구문의 ON CONFLICT DO UPDATE 절에 포함된 특정 EXCLUDED 별칭 참조에 의해 전달됩니다). DELETE 작업의 경우 반환 값은 OLD여야 합니다.
BEFORE 또는 AFTER 이후에 실행되는 행 수준 트리거의 반환 값은 항상 무시됩니다. null일 수 있습니다. 다만, 이러한 유형의 트리거 중 하나라도 실패하면 전체 작업이 중단될 수 있습니다.
다음 예제는 PL/pgSQL의 트리거 프로시저 예를 보여줍니다.
아래 예제의 이 트리거는 테이블에 행이 삽입되거나 업데이트될 때마다 현재 사용자 이름과 시간이 행에 기록되도록 합니다. 직원의 이름이 주어지고 급여가 양수(숫자)인지 확인하십시오.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
테이블 변경 사항을 기록하는 또 다른 방법은 각 삽입, 업데이트, 삭제에 대한 행을 보유하는 새 테이블을 생성하는 것입니다. 이 접근 방식은 테이블의 모든 변경 사항을 감사(auditing)하는 것으로 생각할 수 있습니다. 다음 예제는 PL/pgSQL의 감사 트리거 프로시저 예를 보여줍니다.
이 예제 트리거는 emp 테이블의 행 삽입, 업데이트, 삭제가 emp_audit 테이블에 기록되도록 합니다. 수행된 작업 유형과 함께 현재 시간과 사용자 이름이 행에 기록됩니다.
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
이전 예제의 변형은 메인 테이블을 감사 테이블에 조인하는 뷰를 사용하여 각 항목이 마지막으로 수정된 시점을 나타냅니다. 이 접근 방식은 여전히 테이블 변경 사항의 전체 감사 추적을 기록하지만, 각 항목에 대해 감사 추적에서 도출된 마지막 수정 타임스탬프만 표시하여 감사 추적의 간략한 뷰를 제공합니다. 다음 예제는 PL/pgSQL의 뷰에 대한 감사 트리거 예를 보여줍니다.
이 예제는 뷰가 트리거를 사용하여 뷰를 업데이트하고, 뷰의 행을 삽입, 업데이트, 삭제하는 작업이 emp_audit 테이블에 기록되도록 합니다. 수행된 작업 유형과 함께 현재 시간과 사용자 이름이 기록되며, 뷰는 각 행의 마지막 수정 시간을 표시합니다.
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- Perform the required operation on emp, and create a row in emp_audit
-- to reflect the change made to emp.
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE PROCEDURE update_emp_view();
트리거의 용도 중 하나는 다른 테이블의 요약 테이블을 유지하는 것입니다. 결과 요약은 특정 쿼리에 대해 원래 테이블 대신 사용할 수 있습니다(일반적으로 훨씬 짧은 실행 시간이 필요합니다). 이 기법은 측정되거나 관찰된 데이터 테이블(팩트 테이블이라고 함)이 매우 클 수 있는 데이터 웨어하우징에서 일반적으로 사용됩니다. 다음 예제는 데이터 웨어하우스의 팩트 테이블에 대한 요약 테이블을 유지하는 PL/pgSQL 트리거 프로시저 예를 보여줍니다.
여기에 설명된 스키마는 부분적으로 Ralph Kimball의 The Data Warehouse Toolkit 에 포함된 grocery store 예제를 기반으로 합니다.
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
이벤트 트리거
PL/pgSQL은 이벤트 트리거를 정의하는 데 사용할 수 있습니다. AgensGraph에서 이벤트 트리거로 호출될 프로시저는 인수가 없으며 반환 타입으로 event_trigger를 선언해야 합니다.
PL/pgSQL 함수가 이벤트 트리거와 함께 호출되면, 최상위 블록에 여러 특수 변수가 자동으로 생성됩니다.
TG_EVENT
데이터 타입text. 트리거가 실행될 이벤트를 나타내는 문자열입니다.TG_TAG
데이터 타입text. 트리거가 실행되는 명령 태그를 포함하는 변수입니다.
아래 예제의 트리거는 지원되는 명령이 실행될 때마다 NOTICE 메시지를 생성합니다.
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();
PL/pgSQL 개발 팁
PL/pgSQL에서 개발하는 좋은 방법은 원하는 텍스트 편집기를 사용하여 함수를 작성하고 다른 창에서 psql을 사용하여 로드하고 테스트하는 것입니다. 이런 방식으로 작업하는 경우, CREATE OR REPLACE FUNCTION을 사용하여 함수를 작성하는 것이 좋습니다. 그러면 파일을 다시 로드하여 함수 정의를 업데이트할 수 있습니다. 예는 다음과 같습니다.
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE plpgsql;
psql을 실행하는 동안 다음과 같이 함수 정의 파일을 로드하거나 다시 로드할 수 있습니다.
\i filename.sql
PL/pgSQL에서 개발하는 또 다른 좋은 방법은 절차적 언어를 사용한 개발을 용이하게 하는 GUI 데이터베이스 접근 도구를 사용하는 것입니다. 이러한 도구는 종종 편리한 기능을 제공합니다(예: 작은따옴표 제거, 더 쉬운 재실행).
따옴표 처리
PL/pgSQL 함수의 코드는 CREATE FUNCTION에서 문자열 리터럴로 지정됩니다. 일반적인 방식으로 문자열을 작은따옴표로 묶는 경우, 함수 본문의 작은따옴표를 이중으로 처리해야 합니다. 마찬가지로 백슬래시도 두 개를 사용해야 합니다(이스케이프 문자열 구문이 사용된다고 가정함). 따옴표를 두 번 사용하면 코드를 이해하기 어려워질 수 있습니다. 이는 인접한 따옴표가 많이 필요하다는 것을 사용자가 쉽게 알 수 있기 때문입니다.
따라서 “달러 인용” 문자열 리터럴을 사용하여 함수 본문을 작성하는 것이 좋습니다. 달러 인용 방식에서는 따옴표를 두 번 사용해서는 안 되며, 대신 필요한 중첩 레벨마다 서로 다른 달러 인용 구분자를 선택해야 합니다. 예를 들어, 다음과 같이 CREATE FUNCTION 명령을 생성할 수 있습니다.
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
이 경우, SQL 명령에서 단순 리터럴 문자열 주위에 따옴표를 사용하고 $$를 사용하여 문자열로 조립할 SQL 명령을 구분할 수 있습니다. $$를 포함하는 텍스트를 인용해야 하는 경우 $Q$를 사용할 수 있습니다.
다음 표는 달러 인용 부호 없이 따옴표를 작성할 때 무엇을 해야 하는지 보여줍니다. 이는 달러 인용 부호를 더 이해하기 쉬운 것으로 변환할 때 유용할 수 있습니다.
따옴표 1개
함수 본문을 시작하고 끝내려면 다음과 같이 합니다.
CREATE FUNCTION foo() RETURNS integer AS '
....
' LANGUAGE plpgsql;
작은따옴표로 묶인 함수 본문 내에서는 따옴표가 쌍으로 나타나야 합니다.
따옴표 2개
함수 본문 내부의 문자열 리터럴을 표현하는 데 사용됩니다. 예를 들어 다음과 같습니다.
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
달러 인용 방식에서는 다음과 같이 작성할 수 있습니다.
a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';
PL/pgSQL 파서는 두 경우 모두 정확히 파악합니다.
따옴표 4개
함수 본문 내부의 문자열 상수가 작은따옴표를 필요로 할 때 사용됩니다. 예를 들어 다음과 같습니다.
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
a_output에 실제로 추가된 값: AND name LIKE 'foobar'AND xyz.
달러 인용 방식에서는 다음과 같이 작성할 수 있습니다.
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
따옴표 6개
함수 본문 내부 문자열의 작은따옴표가 문자열 상수의 끝에 인접해 있을 때 사용됩니다. 예를 들어 다음과 같습니다.
a_output := a_output || '' AND name LIKE ''''foobar''''''
a_output에 추가된 값: AND name LIKE 'foobar'.
달러 인용 방식에서는 다음과 같이 작성할 수 있습니다.
a_output := a_output || $$ AND name LIKE 'foobar'$$
따옴표 10개
문자열 상수에 두 개의 작은따옴표가 필요하고(따옴표 8개를 나타냄) 문자열 상수의 끝에 인접한 경우(두 개 이상)입니다. 이는 다른 함수를 생성하는 함수를 작성하는 경우에만 필요합니다.
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
a_output의 값은 다음과 같습니다.
if v_... like ''...'' then return ''...''; end if;
달러 인용 방식에서는 다음과 같이 작성할 수 있습니다.
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
|| referrer_keys.key_string || $$'
then return '$$ || referrer_keys.referrer_type
|| $$'; end if;$$;
PL/Python
PL/Python 함수
PL/Python의 함수는 표준 CREATE FUNCTION 구문을 사용하여 선언됩니다.
CREATE FUNCTION funcname (argument-list)
RETURNS return-type
AS $$
# PL/Python function body
$$ LANGUAGE plpythonu;
이 함수의 본문은 단순한 python 스크립트입니다. 함수가 호출되면, 인수는 리스트 args에 전달되며, 명명된 인수도 일반 변수로 Python 스크립트에 전달됩니다. 이러한 명명된 인수는 일반적으로 가독성이 더 좋습니다. 결과는 Python 코드에서 return 또는 (결과 집합 구문의 경우) yield로 반환됩니다. 반환 값을 제공하지 않으면, Python은 기본값(None)을 반환합니다. PL/Python은 Python의 None을 SQL의 Null로 변환합니다.
예를 들어, 두 정수 중 더 큰 값을 반환하는 함수는 다음과 같이 정의할 수 있습니다.
CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
함수 정의의 본문으로 주어진 Python 코드는 Python 함수로 변환됩니다. 예를 들어, 위의 코드는 다음과 같이 변환됩니다.
def __plpython_procedure_pymax_23456():
if a > b:
return a
return b
23456이 함수에 할당된 OID라고 가정합니다.
인수는 전역 변수로 설정됩니다. 변수가 블록에서 전역으로 선언되지 않는 한, Python의 스코핑 규칙에 따라 변수 이름 자체를 포함하는 표현식 값으로 함수 내에서 인수 변수를 다시 할당할 수 없습니다. 예를 들어, 다음은 동작하지 않을 수 있습니다.
CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
x = x.strip() # error
return x
$$ LANGUAGE plpythonu;
x에 할당하면, x는 전체 블록에 대한 로컬 변수가 됩니다. 따라서 PL/Python 함수 매개변수가 아닌 오른쪽의 x는 아직 할당되지 않은 로컬 변수 x만 참조합니다. global 구문을 사용하여 다음을 수행할 수 있습니다.
CREATE FUNCTION pystrip(x text)
RETURNS text
AS $$
global x
x = x.strip() # ok now
return x
$$ LANGUAGE plpythonu;
다만, PL/Python의 세부 구현에 의존하지 않는 것이 좋습니다. 함수 매개변수는 읽기 전용으로 사용하는 것이 권장됩니다.
데이터 값
일반적으로 PL/Python의 목적은 AgensGraph와 Python 사이에 “자연스러운” 매핑을 제공하는 것입니다. 데이터 매핑 규칙에 대한 정보는 아래에 설명되어 있습니다.
데이터 타입 매핑
PL/Python 함수가 호출되면, 인수가 AgensGraph 데이터 타입에서 해당 Python 타입으로 변환됩니다.
AgensGraph
boolean은 Pythonboolean으로 변환됩니다.AgensGraph
smallint와int는 Pythonint로 변환됩니다. AgensGraphbigint와oid는 Python2에서는long으로, Python3에서는int로 변환됩니다.AgensGraph
real과double은 Pythonfloat로 변환됩니다.AgensGraph
numeric은 PythonDecimal로 변환됩니다. 이 타입을 사용할 수 있는 경우cdecimal패키지에서 가져옵니다. 그렇지 않으면 표준 라이브러리decimal.Decimal이 사용됩니다.cdecimal은decimal보다 상당히 빠릅니다. Python3.3 이상 버전에서는cdecimal이decimal이라는 이름으로 표준 라이브러리에 통합되어 있으므로 둘 사이에 차이가 없습니다.AgensGraph
bytea는 Python2에서는str로, Python3에서는bytes로 변환됩니다. Python2에서는 문자 인코딩 없이 문자열을 바이트 시퀀스로 처리해야 합니다.AgensGraph 문자열 형식을 포함한 다른 모든 데이터 타입은 Python
str로 변환됩니다. Python2에서 이 문자열은 AgensGraph 서버 인코딩으로 되어 있습니다. Python3에서는 모든 문자열과 동일한 유니코드 문자열이 됩니다.비스칼라 데이터 타입의 경우 아래를 참고하십시오.
PL/Python 함수가 반환될 때, 반환 값은 다음과 같이 함수에 선언된 AgensGraph 반환 데이터 타입으로 변환됩니다.
AgensGraph 반환 타입이
boolean인 경우, 반환 값은 Python 규칙에 따라 true인지 평가됩니다. 즉, 0과 빈 문자열은 false인 반면, ‘f’는 true입니다.AgensGraph 반환 타입이
bytea인 경우, 반환 값은 각 Python 내장 함수를 사용하여 문자열(Python2) 또는 바이트(Python3)로 변환되고, 그 결과가 bytea로 변환됩니다.다른 모든 AgensGraph 반환 타입의 경우, 변환 값은 Python 내장
str을 사용하여 문자열로 변환되고, 그 결과가 AgensGraph 데이터 타입의 입력 함수로 전달됩니다. (Python 값이float인 경우, 정밀도 손실을 피하기 위해str대신repr내장을 사용합니다.)
Python2에서는 문자열을 AgensGraph에 전달할 때 AgensGraph 서버 인코딩으로 되어 있어야 합니다. 현재 서버 인코딩에서 유효하지 않은 문자열은 오류를 발생시키며, 모든 인코딩 불일치를 감지할 수 있는 것은 아니므로 이를 올바르게 처리하지 않으면 가비지 값이 계속 발생할 수 있습니다. 유니코드 문자열은 자동으로 올바른 인코딩으로 변환되므로, 이를 사용하는 것이 더 안전하고 편리할 수 있습니다. Python3에서는 모든 문자열이 유니코드 문자열입니다.
*비스칼라 데이터 타입에 대한 자세한 내용은 아래를 참고하십시오.
선언된 AgensGraph 반환 타입과 실제 반환 객체의 Python 데이터 타입 사이의 논리적 불일치는 표시되지 않으며, 어떤 경우든 값이 반환됩니다.
Null, None
SQL null이 함수에 전달되면, Python의 인수 값은 none으로 표시됩니다. 예를 들어, [PL/Python 함수]의 함수 정의 pymax는 null 입력에 대해 잘못된 답을 반환합니다. 함수 정의에 STRICT를 추가하여 작업을 더 합리적으로 만들 수 있습니다. null이 전달되면, 함수는 전혀 호출되지 않으며 자동으로 null을 반환합니다. 함수 본문에서 null 입력을 확인할 수도 있습니다.
CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$
if (a is None) or (b is None):
return None
if a > b:
return a
return b
$$ LANGUAGE plpythonu;
위에서 보인 것처럼, PL/Python 함수에서 SQL null 값을 반환하려면 값 None을 반환합니다. 이는 함수가 strict인지 여부에 관계없이 수행할 수 있습니다.
배열, 리스트
SQL 배열 값은 Python 리스트로 PL/Python에 전달됩니다. PL/Python 함수에서 SQL 배열 값을 반환하려면 Python 시퀀스(예: 리스트 또는 튜플)를 반환합니다.
CREATE FUNCTION return_arr()
RETURNS int[]
AS $$
return (1, 2, 3, 4, 5)
$$ LANGUAGE plpythonu;
SELECT return_arr();
return_arr
-------------
{1,2,3,4,5}
(1 row)
Python에서 문자열은 시퀀스입니다. 이에 익숙하지 않은 Python 프로그래머는 바람직하지 않은 결과를 얻을 수 있습니다.
CREATE FUNCTION return_str_arr()
RETURNS varchar[]
AS $$
return "hello"
$$ LANGUAGE plpythonu;
SELECT return_str_arr();
return_str_arr
----------------
{h,e,l,l,o}
(1 row)
복합 타입
복합 타입 인수는 Python 매핑으로 함수에 전달됩니다. 매핑의 요소 이름은 복합 타입의 속성 이름입니다. 전달된 행의 속성이 null 값을 가지면, 매핑에서 None 값을 갖습니다. 다음은 예입니다.
CREATE TABLE employee (
name text,
salary integer,
age integer
);
CREATE FUNCTION overpaid (e employee)
RETURNS boolean
AS $$
if e["salary"] > 200000:
return True
if (e["age"] < 30) and (e["salary"] > 100000):
return True
return False
$$ LANGUAGE plpythonu;
Python 함수에서 행 또는 복합 타입을 반환하는 방법은 여러 가지가 있습니다. 복합 타입 결과 예제를 실행하려면, 아래와 같이 TYPE을 생성합니다.
CREATE TYPE named_value AS (
name text,
value integer
);
복합 결과는 다음으로 반환될 수 있습니다.
시퀀스 타입(튜플 또는 리스트, 단 인덱싱이 불가능하므로 셋은 제외) 반환된 시퀀스 객체는 복합 결과 타입이 가진 필드 수와 동일한 수의 항목을 가져야 합니다. 인덱스 0의 항목은 복합 타입의 첫 번째 필드에 할당되고, 1은 두 번째 필드에 할당되며, 이런 식으로 계속됩니다. 예를 들어 다음과 같습니다.
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ return [ name, value ] # or alternatively, as tuple: return ( name, value ) $$ LANGUAGE plpythonu;
어떤 열에 대해 SQL null을 반환하려면 해당 위치에
none을 삽입합니다.매핑(딕셔너리)
각 결과 타입 열의 값은 열 이름을 키로 사용하여 매핑에서 검색됩니다. 예:
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ return { "name": name, "value": value } $$ LANGUAGE plpythonu;
추가 딕셔너리 키/값 쌍은 무시됩니다. 누락된 키는 오류로 처리됩니다. 어떤 열에 대해 SQL null 값을 반환하려면 해당 열 이름을 키로 하여
None을 삽입합니다.객체(
__getattr__메서드를 제공하는 모든 객체)이는 매핑과 동일하게 동작합니다. 다음은 예입니다.
CREATE FUNCTION make_pair (name text, value integer) RETURNS named_value AS $$ class named_value: def __init__ (self, n, v): self.name = n self.value = v return named_value(name, value) # or simply class nv: pass nv.name = name nv.value = value return nv $$ LANGUAGE plpythonu;
OUT매개변수가 있는 함수도 지원됩니다. 다음은 예입니다.CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ return (1, 2) $$ LANGUAGE plpythonu; SELECT * FROM multiout_simple();
집합 반환 함수
PL/Python 함수는 스칼라 또는 복합 타입의 집합도 반환할 수 있습니다. 반환된 객체가 내부적으로 이터레이터로 변환되므로 이를 달성하는 방법은 여러 가지가 있습니다. 다음 예제는 복합 타입이 있다고 가정합니다.
CREATE TYPE greeting AS (
how text,
who text
);
집합 결과는 다음에서 반환될 수 있습니다.
시퀀스 타입(튜플, 리스트, 셋)
CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ # return tuple containing lists as composite types # all other combinations work also return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] ) $$ LANGUAGE plpythonu;
이터레이터(
__iter__및next메서드를 제공하는 모든 객체)CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ class producer: def __init__ (self, how, who): self.how = how self.who = who self.ndx = -1 def __iter__ (self): return self def next (self): self.ndx += 1 if self.ndx == len(self.who): raise StopIteration return ( self.how, self.who[self.ndx] ) return producer(how, [ "World", "PostgreSQL", "PL/Python" ]) $$ LANGUAGE plpythonu;
제너레이터(
yield)CREATE FUNCTION greet (how text) RETURNS SETOF greeting AS $$ for who in [ "World", "PostgreSQL", "PL/Python" ]: yield ( how, who ) $$ LANGUAGE plpythonu;
OUT 매개변수가 있는 집합 반환 함수(RETURNS SETOF record 사용)도 지원됩니다. 다음은 예입니다.
CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer)
RETURNS SETOF record
AS $$
return [(1, 2)] * n
$$ LANGUAGE plpythonu;
SELECT * FROM multiout_simple_setof(3);
데이터 공유
전역 딕셔너리 SD는 함수 호출 간에 데이터를 저장하는 데 사용할 수 있습니다. 이 변수는 개별 정적 데이터입니다. 전역 딕셔너리 GD는 세션의 모든 Python 함수에서 사용할 수 있는 공용 데이터이므로 주의해야 합니다.
각 함수는 Python 인터프리터에서 자체 실행 환경을 가지므로, myfunc의 전역 데이터와 함수 인수는 myfunc2에서 사용할 수 없습니다. 위에서 언급한 것처럼, GD 딕셔너리의 데이터는 예외입니다.
익명 코드 블록
PL/Python은 DO 구문과 함께 호출되는 익명 코드 블록도 지원합니다.
DO $$
# PL/Python code
$$ LANGUAGE plpythonu;
익명 코드 블록은 인수를 받지 않으며 반환된 모든 값을 폐기합니다. 그 외에는 함수처럼 동작합니다.
트리거 함수
함수가 트리거로 사용되면, 딕셔너리 TD에 트리거 관련 값이 포함됩니다.
TD[“event”]
이벤트를 문자열(INSERT, UPDATE, DELETE, TRUNCATE)로 포함합니다.
TD[“when”]
BEFORE, AFTER, INSTEAD OF 중 하나를 포함합니다.
TD[“level”]
ROW 또는 STATEMENT를 포함합니다.
TD[“new”], TD[“old”]
행 수준 트리거의 경우, 필드의 하나 또는 두 트리거 이벤트에 따라 각 트리거 행을 포함합니다.
TD[“name”]
트리거 이름을 포함합니다.
TD[“table_name”]
트리거가 발생한 테이블의 이름을 포함합니다.
TD[“table_schema”]
트리거가 발생한 테이블의 스키마를 포함합니다.
TD[“relid”]
트리거가 발생한 테이블의 OID를 포함합니다.
TD[“args”]
인수가 있는 CREATE TRIGGER(명령)의 경우, TD["args"][0]부터 TD["args"][n-1]까지 사용할 수 있습니다.
TD ["when"]이 BEFORE 또는 INSTEAD OF이고 TD ["level"]이 ROW인 경우, Python 함수는 None 또는 “OK”를 반환하여 행이 변경되지 않았음을 나타낼 수 있습니다. “SKIP”은 이벤트를 중단합니다. TD ["event"]가 INSERT 또는 UPDATE에 적용되는 경우, “MODIFY”를 반환하여 새 행을 수정할 수 있습니다. 그 외에는 반환 값이 무시됩니다.
데이터베이스 접근
PL/Python 언어 모듈은 plpy라는 Python 모듈을 자동으로 임포트합니다. 이 모듈의 함수와 상수는 Python 코드에서 plpy.foo로 사용할 수 있습니다.
데이터베이스 접근 함수
plpy 모듈은 데이터베이스 명령을 실행하기 위한 여러 함수를 제공합니다.
plpy.execute(query [, max-rows])
쿼리 문자열과 함께 plpy.execute를 호출하고 선택적 행 제한 인수를 선택하면, 해당 쿼리가 실행되고 결과가 결과 객체로 반환됩니다.
결과 객체는 리스트 또는 딕셔너리 객체를 모방합니다. 결과 객체는 행 번호와 열 이름으로 접근할 수 있습니다. 예를 들어 다음과 같습니다.
rv = plpy.execute("SELECT * FROM my_table", 5)
my_table에서 최대 5개의 행을 반환합니다. my_table에 my_column 열이 있으면, 다음과 같이 접근할 수 있습니다.
foo = rv[i]["my_column"]
반환된 행의 수는 내장 len 함수를 사용하여 얻을 수 있습니다.
결과 객체에는 다음과 같은 추가 메서드가 있습니다.
nrows() 명령에 의해 처리된 행의 수를 반환합니다. 이는 반드시 반환된 행의 수와 동일하지는 않습니다. 예를 들어,
UPDATE명령은 이 값을 설정하지만 (RETURNING이 사용되지 않는 한) 어떤 행도 반환하지 않습니다.status()
SPI_execute()값을 반환합니다.colnames(), coltypes(), coltypmods() 각각 열 이름 목록, 열 타입 OID 목록, 열에 대한 타입별 타입 한정자 목록을 반환합니다. 이러한 메서드는 결과 집합을 생성하지 않은 명령(예:
RETURNING이 없는UPDATE또는DROP TABLE)의 결과 객체에서 호출될 때 예외를 발생시킵니다. 다만, 0개의 행을 포함하는 결과 집합에서 이러한 메서드를 사용하는 것은 괜찮습니다.str() 표준
__str__메서드가 정의되어 있어, 예를 들어plpy.debug(rv)를 사용하여 쿼리 실행 결과를 디버그할 수 있습니다.
결과 객체는 수정할 수 있습니다.
plpy.execute를 호출하면 전체 결과 집합이 메모리로 읽혀집니다. 결과 집합이 비교적 작을 때만 이 함수를 사용하십시오. 큰 결과를 페치할 때 과도한 메모리 사용의 위험을 감수하고 싶지 않다면, plpy.execute 대신 plpy.cursor를 사용하십시오.
plpy.prepare(query [, argtypes]) plpy.execute(plan [, arguments [, max-rows]])
plpy.prepare는 쿼리에 대한 실행 계획을 준비합니다. 쿼리에 매개변수 참조가 있는 경우, 쿼리 문자열과 매개변수 타입 목록과 함께 호출됩니다. 다음은 예입니다.
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
text는 $1에 대해 전달할 변수의 타입입니다. 쿼리에 어떤 매개변수도 전달하고 싶지 않다면 두 번째 인수는 선택 사항입니다. 구문을 준비한 후에는 함수 plpy.execute의 변형을 사용하여 실행합니다.
rv = plpy.execute(plan, ["name"], 5)
첫 번째 인수로 (쿼리 문자열 대신) 계획을 전달하고, 두 번째 인수로 쿼리에 대체할 값의 목록을 전달합니다. 쿼리가 어떤 매개변수도 기대할 수 없는 경우 두 번째 인수는 선택 사항입니다. 세 번째 인수는 이전과 같이 선택적 행 제한입니다.
쿼리 매개변수와 결과 행 필드는 데이터 값에 설명된 대로 PostgreSQL과 Python 데이터 타입 사이에서 변환됩니다.
PL/Python 모듈을 사용하여 계획을 준비하면 자동으로 저장됩니다. 자세한 내용은 이 링크를 참고하십시오. 이 기능을 통해 이 함수를 효과적으로 사용하려면, 영구 저장소 딕셔너리 SD 또는 GD 중 하나를 사용해야 합니다([데이터 공유] 참고). 다음은 예입니다.
CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
if "plan" in SD:
plan = SD["plan"]
else:
plan = plpy.prepare("SELECT 1")
SD["plan"] = plan
# rest of function
$$ LANGUAGE plpythonu;
plpy.cursor(query) plpy.cursor(plan [, arguments])
plpy.cursor 함수는 (행 제한을 제외하고) plpy.execute와 동일한 인수를 받으며 커서 객체를 반환하는데, 이를 통해 큰 결과 집합을 더 작은 청크 단위로 처리할 수 있습니다. plpy.execute와 마찬가지로, 쿼리 문자열이나 인수 목록과 함께 계획 객체를 사용할 수 있습니다.
커서 객체는 정수 매개변수를 받고 결과 객체를 반환하는 fetch 메서드를 제공합니다. fetch를 호출할 때마다, 반환된 객체에는 매개변수 값보다 크지 않은 다음 행 배치가 포함됩니다. 모든 행이 사용되면, fetch는 빈 결과 객체를 반환하기 시작합니다. 커서 객체는 이터레이터 인터페이스도 제공하여, 모든 행이 소진될 때까지 한 번에 한 행씩 생성합니다. 그러한 방식으로 페치된 데이터는 결과 객체가 아니라 딕셔너리로 반환되며, 각 딕셔너리는 단일 결과 행에 해당합니다.
큰 테이블에서 데이터를 처리하는 두 가지 방법의 예입니다.
CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
odd = 0
for row in plpy.cursor("select num from largetable"):
if row['num'] % 2:
odd += 1
return odd
$$ LANGUAGE plpythonu;
CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("select num from largetable")
while True:
rows = cursor.fetch(batch_size)
if not rows:
break
for row in rows:
if row['num'] % 2:
odd += 1
return odd
$$ LANGUAGE plpythonu;
CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
odd = 0
plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
rows = list(plpy.cursor(plan, [2]))
return len(rows)
$$ LANGUAGE plpythonu;
커서는 자동으로 폐기됩니다. 다만, 커서가 보유한 모든 리소스를 명시적으로 해제하려면 close 메서드를 사용하십시오. 일단 닫히면 커서에서 더 이상 페치할 수 없습니다.
오류 트래핑
데이터베이스에 접근하는 함수는 오류를 만날 수 있으며, 이로 인해 함수가 중단되고 예외가 발생합니다. plpy.execute와 plpy.prepare는 모두 plpy.SPIError의 하위 클래스 인스턴스를 발생시킬 수 있으며, 기본적으로 이는 함수를 종료시킵니다. 이 오류는 try/except 구문을 사용하여 다른 Python 예외와 마찬가지로 처리할 수 있습니다. 예를 들어 다음과 같습니다.
CREATE FUNCTION try_adding_joe() RETURNS text AS $$
try:
plpy.execute("INSERT INTO users(username) VALUES ('joe')")
except plpy.SPIError:
return "something went wrong"
else:
return "Joe added"
$$ LANGUAGE plpythonu;
발생하는 예외의 실제 클래스는 오류를 발생시킨 특정 조건에 해당합니다. 가능한 조건 목록은 이 링크를 참고하십시오. plpy.spiexceptions 모듈은 각 조건에 대한 예외 클래스를 정의하며, 그 이름은 조건 이름에서 도출됩니다. 예를 들어, division_by_zero는 DivisionByZero가 되고, unique_violation은 UniqueViolation이 되며, fdw_error는 FdwError가 되는 식입니다. 이러한 각 예외 클래스는 SPIError를 상속합니다. 이러한 분리는 특정 오류를 더 쉽게 처리할 수 있게 해줍니다. 예를 들어 다음과 같습니다.
CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
from plpy import spiexceptions
try:
plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
plpy.execute(plan, [numerator, denominator])
except spiexceptions.DivisionByZero:
return "denominator cannot equal zero"
except spiexceptions.UniqueViolation:
return "already have that fraction"
except plpy.SPIError, e:
return "other error, SQLSTATE %s" % e.sqlstate
else:
return "fraction inserted"
$$ LANGUAGE plpythonu;
plpy.spiexceptions 모듈의 모든 예외가 SPIError를 상속하므로, 이를 처리하는 except 절은 모든 데이터베이스 접근 오류를 잡아냅니다.
서로 다른 오류 조건을 처리하는 대안적인 방법으로, SPIError 예외를 잡고 예외 객체의 sqlstate 속성을 살펴봄으로써 except 블록 내에서 특정 오류 조건을 판별할 수 있습니다. 이 속성은 “SQLSTATE” 오류 코드를 포함하는 문자열 값입니다. 이 접근 방식은 거의 동일한 기능을 제공합니다.
명시적 하위 트랜잭션
[오류 트래핑]에서 설명한 것처럼 데이터베이스 접근으로 인한 오류에서 복구하면, 일부 작업이 성공한 후 그중 하나가 실패하는 바람직하지 않은 상황이 발생할 수 있으며, 그 오류에서 복구한 후에는 데이터가 일관성 없는 상태로 남게 됩니다. PL/Python은 명시적 하위 트랜잭션의 형태로 이 문제에 대한 해결책을 제공합니다.
하위 트랜잭션 컨텍스트 관리자
두 계좌 간의 이체를 구현하는 함수를 생각해 봅시다.
CREATE FUNCTION transfer_funds() RETURNS void AS $$
try:
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError, e:
result = "error transferring funds: %s" % e.args
else:
result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpythonu;
두 번째 UPDATE 구문에서 예외가 발생하면, 이 함수는 오류를 보고하지만 첫 번째 UPDATE의 결과는 그럼에도 불구하고 커밋됩니다. 즉, 자금이 Joe의 계좌에서 인출되지만 Mary의 계좌로 이체되지는 않습니다.
이러한 문제를 피하기 위해, plpy.execute 호출을 명시적 하위 트랜잭션으로 감쌀 수 있습니다. plpy 모듈은 plpy.subtransaction() 함수로 생성되는, 명시적 하위 트랜잭션을 관리하기 위한 헬퍼 객체를 제공합니다. 이 함수로 생성된 객체는 컨텍스트 관리자 인터페이스를 구현합니다. 명시적 하위 트랜잭션을 사용하면 함수를 다음과 같이 다시 작성할 수 있습니다.
CREATE FUNCTION transfer_funds2() RETURNS void AS $$
try:
with plpy.subtransaction():
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except plpy.SPIError, e:
result = "error transferring funds: %s" % e.args
else:
result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpythonu;
여전히 try/catch의 사용이 필요합니다. 그렇지 않으면 예외가 Python 스택의 최상위로 전달되어 AgensGraph 오류로 인해 전체 함수가 중단되며, 그 결과 operations 테이블에 어떤 행도 삽입되지 않습니다. 하위 트랜잭션 컨텍스트 관리자는 오류를 트래핑하지 않습니다. 단지 그 범위 내에서 실행되는 모든 데이터베이스 작업이 원자적으로 커밋되거나 롤백되도록 보장할 뿐입니다. 하위 트랜잭션 블록의 롤백은 데이터베이스 접근에서 비롯된 오류로 인한 경우뿐만 아니라 모든 종류의 예외 종료 시 발생합니다. 명시적 하위 트랜잭션 블록 내에서 발생한 일반 Python 예외도 하위 트랜잭션을 롤백하게 합니다.
이전 Python 버전
WITH 키워드를 사용하는 컨텍스트 관리자 구문은 Python 2.6에서 기본적으로 사용할 수 있습니다. 이전 Python 버전에서 PL/Python을 사용하는 경우, 그만큼 투명하지는 않지만 여전히 명시적 하위 트랜잭션을 사용할 수 있습니다. enter와 exit 편의 별칭을 사용하여 하위 트랜잭션 관리자의 __enter__ 및 __exit__ 함수를 호출할 수 있습니다. 자금을 이체하는 예제 함수는 다음과 같이 작성할 수 있습니다.
CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
try:
subxact = plpy.subtransaction()
subxact.enter()
try:
plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
except:
import sys
subxact.exit(*sys.exc_info())
raise
else:
subxact.exit(None, None, None)
except plpy.SPIError, e:
result = "error transferring funds: %s" % e.args
else:
result = "funds transferred correctly"
plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
plpy.execute(plan, [result])
$$ LANGUAGE plpythonu;
유틸리티 함수
plpy 모듈은 다음 함수들도 제공합니다.
plpy.debug(msg, **kwargs)
plpy.log(msg, **kwargs)
plpy.info(msg, **kwargs)
plpy.notice(msg, **kwargs)
plpy.warning(msg, **kwargs)
plpy.error(msg, **kwargs)
plpy.fatal(msg, **kwargs)
plpy.error와 plpy.fatal은 실제로 호출되지 않는 Python 예외를 발생시켜 현재 트랜잭션 또는 하위 트랜잭션을 중단시킵니다. raise plpy.Error(msg)와 raise plpy.Fatal(msg)는 각각 plpy.error(msg)와 plpy.fatal(msg)를 호출하는 것과 동일하지만, raise 형식에서는 키워드 인수를 전달할 수 없습니다. 다른 함수들은 서로 다른 우선순위 레벨의 메시지만 생성합니다. 특정 우선순위의 메시지가 클라이언트에 보고되어야 하는지, 서버 로그에 기록되어야 하는지, 또는 둘 다인지는 log_min_messages 및 client_min_messages 설정 변수로 제어할 수 있습니다. 자세한 내용은 이 링크를 참고하십시오.
msg 인수는 위치 인수로 제공됩니다. 하위 호환성을 위해 두 개 이상의 위치 인수가 제공될 수 있습니다. 이 경우, 위치 인수의 튜플 문자열 표현식이 클라이언트에 보고되는 메시지입니다.
다음 키워드별 인수가 허용됩니다.
detail
hint
sqlstate
schema_name
table_name
column_name
datatype_name
키워드별 인수로 전달된 객체의 문자열 표현식은 클라이언트에 보고되는 메시지를 보강하는 데 사용됩니다. 다음은 예입니다.
CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
plpy.error("custom exception message",
detail="some info about exception",
hint="hint for users")
$$ LANGUAGE plpythonu;
=# SELECT raise_custom_exception();
ERROR: plpy.Error: custom exception message
DETAIL: some info about exception
HINT: hint for users
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_custom_exception", line 4, in <module>
hint="hint for users")
PL/Python function "raise_custom_exception"
또 다른 유틸리티 함수 집합은 plpy.quote_literal(string), plpy.quote_nullable(string),
plpy.quote_ident(string)입니다. 이들은 내장 인용 함수와 동일합니다. 임시(ad-hoc) 쿼리를 구성할 때 유용합니다. 동적 PL/Python은 다음과 같습니다.
plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
plpy.quote_ident(colname),
plpy.quote_nullable(newvalue),
plpy.quote_literal(keyvalue)))
환경 변수
Python 인터프리터가 받아들이는 일부 환경 변수는 PL/Python 동작에 영향을 줄 수 있습니다. 이러한 변수는 예를 들어 시작 스크립트와 같은 메인 AgensGraph 서버 프로세스의 환경에 설정되어야 합니다. 사용 가능한 환경 변수는 Python 버전에 따라 다릅니다. 자세한 내용은 Python 매뉴얼을 참고하십시오.
PYTHONHOME
PYTHONPATH
PYTHONY2K
PYTHONOPTIMIZE
PYTHONDEBUG
PYTHONVERBOSE
PYTHONCASEOK
PYTHONDONTWRITEBYTECODE
PYTHONIOENCODING
PYTHONUSERBASE
PYTHONHASHSEED
(python man 페이지에 나열된 일부 환경 변수가 명령줄 인터프리터에서만 유효하고 임베디드 Python 인터프리터에서는 유효하지 않다는 것은 PL/Python의 제어를 벗어난 Python 구현 세부 사항으로 보입니다.)