반응형
24.3. Trigger Procedures
PL/pgSQL는 trigger procedure를 정의 할 수 있다. 이를 생성하는 방법은 일반적인 것과 같이 CREATE FUNCTION를 사용하고 그 함수에 매개변수없이 리턴타입을 OPAQUE로 준다.
trigger procedure로 함수를 사용하는데는 Postgres에서만의 몇가지 세부사항이 있다.
첫째는 최상위 선언부(declare section)에 자동으로 생성되는 몇가지 변수들이 있다.
NEW
자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 새로운 행의 값을 갖는다.
OLD
자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 과거 행의 값을 갖는다.
TG_NAME
자료형이 name인 변수로 실제 작동할 trigger의 이름을 갖고 있다.
TG_WHEN
자료형이 test인 문자열로 trigger의 정의에 따라 "BEFORE" 또는 "AFTER"를 갖는다.
TG_LEVEL
자료형이 test인 문자열로 trigger의 정의에 따라 "ROW" 또는 "STATEMENT"를 갖는다.
TG_OP
자료형이 test인 문자열로 trigger가 실제 수행하는 작업을 나타내는데 "INSERT", "UPDATE" 또는 "DELETE"를 갖는다.
TG_RELID
자료형이 oid인 변수로 trigger를 촉발시킨 테이블의 Object ID를 갖는다.
TG_RELNAME
자료형이 name으로 trigger를 촉발시킨 테이블의 이름을 갖는다.
TG_NARGS
자료형이 integer으로 CREATE TRIGGER 문으로 생성된 trigger procedure로 넘겨진 매개변수의 숫자이다.
TG_ARGV[]
자료형이 text인 배열로 CREATE TRIGGER 문의 실제 매개변수 값이다. 배열의 첫째 요소는 0번재 배열에 있고, 만일 배열의 인덱스 값이 0보다 작거나 tg_nargs보다 크면 해당 배열은 NULL을 반환한다.
두번째는 trigger procedure는 NULL 또는 trigger를 동작시킨 테이블과 동인한 구조의 레코드/행을 반납해야한다. "AFTER"로 동작된 trigger는 항상 NULL값을 반납한다. "BEFORE"로 동작한 trigger는 NULL을 반환하면 이 실제의 행에 대한 동작을 건너 뛰도록 신호를 보낸다. 그렇지 않으면 반환된 레코드/행의 값이 삽입되거나 갱신된 행의 값을 덮어쒸울 것이다. signal값을 직접적으로 "NEW"에 기록해서 반환하거나 완전히 새로운 레코드/행을 만들어서 반환하는 것이 가능하다.
Example 24/1. PL/pgSQL Trigger Procedure 예
이 trigger는 테이블에 행 하나가 삽입되거나 갱신될 때마다 현재 사용자 이름과 행에 있는 시간을 확인한다. 그리고 주어진 종업원의 이름과 월급이 양수값인지 확인한다.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
// 주어진 empname과 salary를 확인
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname은 NULL값이 될 수 없다.'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% 는 NULL로 된 salary를 가질 수 없다'', NEW.empname;
END IF;
// 월급을 받고 있는 사람 중에 종업원인가?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% 는 음수의 salary를 가질 수 없다.'', NEW.empname;
END IF;
// 언제 누가 월급을 조정했나?
NEW.last_date := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE on emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
24.4. 예제
여기에 PL/pgSQL로 함수를 만드는 것이 얼마나 쉬운지를 보여주는 예가 있다.
PL/pgSQL를 사용하여 함수를 만들 때 한가지 귀찮은 것은 작은 따옴표를 사용하는 것이다. CREATE FUNCTION에 사용하는 함수의 소스는 문자열형이어야 한다. 그렇기 때문에 문자열형 내부의 작은 따옴표는 두번을 사용하던지 ('') 아니면 역슬레쉬에 이은 작은 따옴표 (')이어야 한다. PL/pgSQL개발자는 여전히 좀더 우아한 방법을 찾고 있고 그동안은 (괴롭겠지만) 아래의 예와 같이 두개의 작은 따옴표를 써야한다. 미래에 어떤 해결점이 나오던지 간에 현재의 사용하는 형태는 미래에도 호환이 될것이다.
다른 상황에서 작은 따옴표에서 벗어나는 방법에 대한 자세한 설명은 24.5.1.1에 있으니 참고 바란다.
Example 24/2. 정수를 증가시키기 위한 간단한 PL/pgSQL 함수
이 함수는 정수를 받아서 1을 증가시키고, 그 증가된 값을 반환한다.
CREATE FUNCTION add_one (integer) RETURNS integer AS '
BEGIN
RETURN $1 + 1;
END;
' LANGUAGE 'plpgsql';
Example 24/3. 문자열을 합치는 간단한 PL/pgSQL 함수
이 함수는 두 문자열을 받아들여서 하나로 합친다음 그 것을 반환한다.
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
BEGIN
RETURN $1 || $2;
END;
' LANGUAGE 'plpgsql';
Example 24/4. 복합형의 PL/pgSQL 함수
이 예에서 EMP라는 테이블과 매개변수로 정수를 받아서 참 또는 거짓을 반환한다. 만약에 EMP테이블의 salary 필드가 NULL이면 "f"를 반한하고 그렇지 않으면 그 필드의 값과 매개변수로 받은 정수값을 비교하여 그 결과값을 반환한다. ( t 또는 f )
CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS '
DECLARE
emprec ALIAS FOR $1;
sallim ALIAS FOR $2;
BEGIN
IF emprec.salary ISNULL THEN
RETURN ''f'';
END IF;
RETURN emprec.salary > sallim;
END;
' LANGUAGE 'plpgsql';
24.5. Oracle PL/SQL에서 PG/pgSQL로 변환
글쓴이: Roberto Mello (<rmello@fslc.usu.edu>)
이 장은 Oracle의 PL/SQL과 PostgreSQL의 PL/pgSQL 사이에 다른 점을 설명하고자 한다. Oracle PL/SQL에서 PG/pgSQL로 변환하고자 하는 개발자들에게 도움이 되었으면한다. 여기있는 대부분의 코드는 Roberto Mello가 2000년 여름에 OpenForce Inc.와 internship을 가졌을 때 PostgreSQL로 변환한 ArsDigita Clickstream에서 거의 가져왔다.
PL/pgSQL는 PL/SQL과 외양면에서 상당히 비슷한다. 블록으로 구조화 되어있고 엄격한 언어이다. (모든 변수는 반드시 선언되어져야한다.) PL/SQL은 PostgreSQL보다 더 많은 기능들을 가지고 있긴 하지만, PL/pgSQL는 우수한 기능을 가지고 있고 꾸준하게 향상되어왔다.
24.5.1. 주요 차이점
변환을 하고자 할때 유념해야 할 것은...
/ PostgreSQL에는 기본 parameter가 없다.
/ PostgreSQL에서는 함수를 loverload할 수 있다. 이 것은 종종 기본 parameter가
없는 것에 대한 대안으로 사용된다.
/ 할당(대입), 반복문 그리고 조건문은 비슷하다.
/ PostgreSQL에서는 커서를 필요로 하지 않는다. 그저 쿼리를 FOR 문에 넣으면 된다.
(아래의 예제 참조)
/ PostgreSQL에서는 작은 따옴표 사용에 주의 해야한다.
(See Section 24.5.1.1)
24.5.1.1. 작은 따옴표를 잘 사용하는 법
PostgreSQL에서 함수를 선언할 때 작은 따옴표를 잘 사용해야한다. 때때로 이러한 작은 따옴표는 웃기는(?) 코드를 만들기도 하는 데 특히 다른 함수를 만드는 함수를 만들때 더욱 그렇다. (Example 24/6을 참조바람) 작은 따옴표를 쓸때 한가지 명심할 것은 오로지 시작하고 끝맺을 때 쓴 작은 따옴표를 빼고는 다른 모든 것들이 짝수로 온다는 것이다.
이런 맥락에서 Table 24/1는 편리함을 제공할 것이다. (당신은 이 작은 도표를 사랑할 껄...)
Table 24/1. 작은 따옴표를 잘 사용하는 방법
작은 따옴표 갯수: 1개
/목적 : 함수의 몸체를 시작하고 끝낼 때
/ 예:
CREATE FUNCTION foo() RETURNS INTEGER AS '...'
LANGUAGE 'plpgsql';
/ 결과:
예와 똑같이 처리됨.
작은 따옴표 갯수: 2개
/목적 : 대입, SELECT, 문자열을 만들 때 등등...
/예:
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
/결과:
SELECT * FROM users WHERE f_name='foobar';
작은 따옴표 갯수: 4개
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의 중간에)
/예:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND ...''
/결과:
AND name LIKE 'foobar' AND ...
작은 따옴표 갯수: 6개
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의의 끝에서)
/예:
a_output := a_output || '' AND name LIKE ''''foobar''''''
/결과:
AND name LIKE 'foobar' AND ...
작은 따옴표 갯수: 10개 (아~~ 미치겠다..)
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의의 끝에서)
아마도 다른 함수를 생성할 때만 적용되는... 경우..
/예:
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
/결과:
if v_<...> like ''<...>'' then return ''<...>''; end if;
/////////////////////////////////////////////////////////////////////////////
휴~~~ 다음회가 마지막이 되겠습니다....
PL/pgSQL는 trigger procedure를 정의 할 수 있다. 이를 생성하는 방법은 일반적인 것과 같이 CREATE FUNCTION를 사용하고 그 함수에 매개변수없이 리턴타입을 OPAQUE로 준다.
trigger procedure로 함수를 사용하는데는 Postgres에서만의 몇가지 세부사항이 있다.
첫째는 최상위 선언부(declare section)에 자동으로 생성되는 몇가지 변수들이 있다.
NEW
자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 새로운 행의 값을 갖는다.
OLD
자료형이 RECORD인 변수로 ROW level trigger에서 INSERT/UPDATE 작업 할 때 데이터베이스의 과거 행의 값을 갖는다.
TG_NAME
자료형이 name인 변수로 실제 작동할 trigger의 이름을 갖고 있다.
TG_WHEN
자료형이 test인 문자열로 trigger의 정의에 따라 "BEFORE" 또는 "AFTER"를 갖는다.
TG_LEVEL
자료형이 test인 문자열로 trigger의 정의에 따라 "ROW" 또는 "STATEMENT"를 갖는다.
TG_OP
자료형이 test인 문자열로 trigger가 실제 수행하는 작업을 나타내는데 "INSERT", "UPDATE" 또는 "DELETE"를 갖는다.
TG_RELID
자료형이 oid인 변수로 trigger를 촉발시킨 테이블의 Object ID를 갖는다.
TG_RELNAME
자료형이 name으로 trigger를 촉발시킨 테이블의 이름을 갖는다.
TG_NARGS
자료형이 integer으로 CREATE TRIGGER 문으로 생성된 trigger procedure로 넘겨진 매개변수의 숫자이다.
TG_ARGV[]
자료형이 text인 배열로 CREATE TRIGGER 문의 실제 매개변수 값이다. 배열의 첫째 요소는 0번재 배열에 있고, 만일 배열의 인덱스 값이 0보다 작거나 tg_nargs보다 크면 해당 배열은 NULL을 반환한다.
두번째는 trigger procedure는 NULL 또는 trigger를 동작시킨 테이블과 동인한 구조의 레코드/행을 반납해야한다. "AFTER"로 동작된 trigger는 항상 NULL값을 반납한다. "BEFORE"로 동작한 trigger는 NULL을 반환하면 이 실제의 행에 대한 동작을 건너 뛰도록 신호를 보낸다. 그렇지 않으면 반환된 레코드/행의 값이 삽입되거나 갱신된 행의 값을 덮어쒸울 것이다. signal값을 직접적으로 "NEW"에 기록해서 반환하거나 완전히 새로운 레코드/행을 만들어서 반환하는 것이 가능하다.
Example 24/1. PL/pgSQL Trigger Procedure 예
이 trigger는 테이블에 행 하나가 삽입되거나 갱신될 때마다 현재 사용자 이름과 행에 있는 시간을 확인한다. 그리고 주어진 종업원의 이름과 월급이 양수값인지 확인한다.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
// 주어진 empname과 salary를 확인
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname은 NULL값이 될 수 없다.'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% 는 NULL로 된 salary를 가질 수 없다'', NEW.empname;
END IF;
// 월급을 받고 있는 사람 중에 종업원인가?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% 는 음수의 salary를 가질 수 없다.'', NEW.empname;
END IF;
// 언제 누가 월급을 조정했나?
NEW.last_date := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE on emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
24.4. 예제
여기에 PL/pgSQL로 함수를 만드는 것이 얼마나 쉬운지를 보여주는 예가 있다.
PL/pgSQL를 사용하여 함수를 만들 때 한가지 귀찮은 것은 작은 따옴표를 사용하는 것이다. CREATE FUNCTION에 사용하는 함수의 소스는 문자열형이어야 한다. 그렇기 때문에 문자열형 내부의 작은 따옴표는 두번을 사용하던지 ('') 아니면 역슬레쉬에 이은 작은 따옴표 (')이어야 한다. PL/pgSQL개발자는 여전히 좀더 우아한 방법을 찾고 있고 그동안은 (괴롭겠지만) 아래의 예와 같이 두개의 작은 따옴표를 써야한다. 미래에 어떤 해결점이 나오던지 간에 현재의 사용하는 형태는 미래에도 호환이 될것이다.
다른 상황에서 작은 따옴표에서 벗어나는 방법에 대한 자세한 설명은 24.5.1.1에 있으니 참고 바란다.
Example 24/2. 정수를 증가시키기 위한 간단한 PL/pgSQL 함수
이 함수는 정수를 받아서 1을 증가시키고, 그 증가된 값을 반환한다.
CREATE FUNCTION add_one (integer) RETURNS integer AS '
BEGIN
RETURN $1 + 1;
END;
' LANGUAGE 'plpgsql';
Example 24/3. 문자열을 합치는 간단한 PL/pgSQL 함수
이 함수는 두 문자열을 받아들여서 하나로 합친다음 그 것을 반환한다.
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
BEGIN
RETURN $1 || $2;
END;
' LANGUAGE 'plpgsql';
Example 24/4. 복합형의 PL/pgSQL 함수
이 예에서 EMP라는 테이블과 매개변수로 정수를 받아서 참 또는 거짓을 반환한다. 만약에 EMP테이블의 salary 필드가 NULL이면 "f"를 반한하고 그렇지 않으면 그 필드의 값과 매개변수로 받은 정수값을 비교하여 그 결과값을 반환한다. ( t 또는 f )
CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS '
DECLARE
emprec ALIAS FOR $1;
sallim ALIAS FOR $2;
BEGIN
IF emprec.salary ISNULL THEN
RETURN ''f'';
END IF;
RETURN emprec.salary > sallim;
END;
' LANGUAGE 'plpgsql';
24.5. Oracle PL/SQL에서 PG/pgSQL로 변환
글쓴이: Roberto Mello (<rmello@fslc.usu.edu>)
이 장은 Oracle의 PL/SQL과 PostgreSQL의 PL/pgSQL 사이에 다른 점을 설명하고자 한다. Oracle PL/SQL에서 PG/pgSQL로 변환하고자 하는 개발자들에게 도움이 되었으면한다. 여기있는 대부분의 코드는 Roberto Mello가 2000년 여름에 OpenForce Inc.와 internship을 가졌을 때 PostgreSQL로 변환한 ArsDigita Clickstream에서 거의 가져왔다.
PL/pgSQL는 PL/SQL과 외양면에서 상당히 비슷한다. 블록으로 구조화 되어있고 엄격한 언어이다. (모든 변수는 반드시 선언되어져야한다.) PL/SQL은 PostgreSQL보다 더 많은 기능들을 가지고 있긴 하지만, PL/pgSQL는 우수한 기능을 가지고 있고 꾸준하게 향상되어왔다.
24.5.1. 주요 차이점
변환을 하고자 할때 유념해야 할 것은...
/ PostgreSQL에는 기본 parameter가 없다.
/ PostgreSQL에서는 함수를 loverload할 수 있다. 이 것은 종종 기본 parameter가
없는 것에 대한 대안으로 사용된다.
/ 할당(대입), 반복문 그리고 조건문은 비슷하다.
/ PostgreSQL에서는 커서를 필요로 하지 않는다. 그저 쿼리를 FOR 문에 넣으면 된다.
(아래의 예제 참조)
/ PostgreSQL에서는 작은 따옴표 사용에 주의 해야한다.
(See Section 24.5.1.1)
24.5.1.1. 작은 따옴표를 잘 사용하는 법
PostgreSQL에서 함수를 선언할 때 작은 따옴표를 잘 사용해야한다. 때때로 이러한 작은 따옴표는 웃기는(?) 코드를 만들기도 하는 데 특히 다른 함수를 만드는 함수를 만들때 더욱 그렇다. (Example 24/6을 참조바람) 작은 따옴표를 쓸때 한가지 명심할 것은 오로지 시작하고 끝맺을 때 쓴 작은 따옴표를 빼고는 다른 모든 것들이 짝수로 온다는 것이다.
이런 맥락에서 Table 24/1는 편리함을 제공할 것이다. (당신은 이 작은 도표를 사랑할 껄...)
Table 24/1. 작은 따옴표를 잘 사용하는 방법
작은 따옴표 갯수: 1개
/목적 : 함수의 몸체를 시작하고 끝낼 때
/ 예:
CREATE FUNCTION foo() RETURNS INTEGER AS '...'
LANGUAGE 'plpgsql';
/ 결과:
예와 똑같이 처리됨.
작은 따옴표 갯수: 2개
/목적 : 대입, SELECT, 문자열을 만들 때 등등...
/예:
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
/결과:
SELECT * FROM users WHERE f_name='foobar';
작은 따옴표 갯수: 4개
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의 중간에)
/예:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND ...''
/결과:
AND name LIKE 'foobar' AND ...
작은 따옴표 갯수: 6개
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의의 끝에서)
/예:
a_output := a_output || '' AND name LIKE ''''foobar''''''
/결과:
AND name LIKE 'foobar' AND ...
작은 따옴표 갯수: 10개 (아~~ 미치겠다..)
/목적 : 결과 문자열 안에 2개의 작은 따옴표가 필요할 때(문자열 정의의 끝에서)
아마도 다른 함수를 생성할 때만 적용되는... 경우..
/예:
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
/결과:
if v_<...> like ''<...>'' then return ''<...>''; end if;
/////////////////////////////////////////////////////////////////////////////
휴~~~ 다음회가 마지막이 되겠습니다....
반응형