Postgres 6.3 버젼 부터 PostgreSQL은 procedural Language(PL)를 지원하기 시작했다. 이것은 PostgreSQL만의 특별한 경우로서 oracle 의 PL/SQL과 비유될 수 있다. 하지만 특수한 언어인 PostgreSQL의 PL은 PostgreSQL에 내장된것 아니고 모듈화된 Handler를 다시 데이타베이스에 등록을 해주어야 한다. 그렇지 않으면 데이타베이스는 PL 로 쓰 여진 function의 내용을 이해할 수 없을 것이다. 결론적으로 처리기는 공유객체로서 컴파일되며 동적으로 Load 되는 특별한 언어 처리 기능이다.
여기서는 PL Handler의 등록의 예로서 PL의 한종류인 PL/pgSQL 언어를 등록하겠다.
공유객체인 처리기는 컴파일된 후 인스톨되어야 하는데 디폴트로 PL/pgSQL 은 PostgreSQL 설치시 자동으로 컴파일된후 라이브러리 디렉토리에 놓여진다. 다 른 처리기인 PL/Tcl 은 PostgreSQL 컴파일시 명시적으로 설정되어야지만 컴파 일되며 라이브러리 디렉토리에 놓여진다. 라이브러리 디렉토리는 설치되어질 PostgreSQL의 바로 밑의 lib 이다. 예를 들어 PostgreSQL의 절대경로가 다음과 같다고 하자.
/usr/local/pgsql
그러면 라이브러리 디렉토리의 절대경로는 다음과 같다.
/usr/local/pgsql/lib
PL/pgSQL 언어 처리기를 설치하기 위해서는 먼저 위의 라이브러리 디렉토리에서 "plpgsql.so" 를 먼저 확인하여야 한다.
확인 후 CREATE FUNCTION 와 CREATE PROCEDURAL LANGUAGE 에 의해 각 데이타베이스 에서 등록을 하여야 한다. 각 데이타베이스에서 등록을 하지않고 일괄적으로 처리하고 싶다면 PostgreSQL 의 특별힌 데이타베이스인 "template1" 에서 등록을 하면된다. template1 에서 등록이 되었다면 차후 생성되는 데이타베이스에는 자동적으로 처리기가 등록된다.
[postgres@nogadax postgresql]psql template1
template1=>
template1=> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE
template1-> AS ' /usr/local/pgsql/lib/plpgsql.so '
template1-> LANGUAGE 'C' ;
template1=>
template1=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
template1-> HANDLER plpgsql_call_handler
template1-> LANCOMPILER 'PL/pgSQL' ;
template1=>
template1=> CREATE DATABASE nogadax ;
template1=>
혹은 위의 문을 화일로 저장한후 \i 를 사용할 수 있다.
template1=> \i /usr/local/src/plsql_inst.sql
"CREATE TRUSTED PROCEDURAL LANGUAGE" 에서 TRUSTED 키워드는 PostgreSQL 의 슈퍼유저 권한이 없는 일반 유저가 "CREATE FUNCTION" 이나 "CREATE TRIGGER" 문을 사용할 때 등록 된 procedure language(PL) 를 사용할 수 있도록 해준다.
PL/pgSQL 은 PostgreSQL 데이타베이스 시스템에서 "Loadable Procedural Language"이다. 이 패키지는 Jan Wieck 에의해 작성되었다.
1. PL/pgSQL 은 function 이나 trigger procedure 를 만드는데 사용되어 질 수 있다.
2. SQL 문에 제어 구조를 추가할 수 있다.
3. 복잡한 계산을 구현할 수 있다.
4. user가 정의한 Type, Function, Operation을 상속할 수 있다.
5. Server 에 의해 Trusted(Authentication 관련의 뜻)된것을 정의할 수 있다.
6. 사용하기 쉽다.
PL/pgSQL 은 대소문자의 구분이 없으므로 키워드나 Identifier 들은 대소문자
구분없이 혼용되어 쓰일 수 있다.
PL/pgSQL 은 블럭 지향언어이다. 블럭은 다음처럼 정의되어진다.
[ Label ]
[ DECLARE declarations ]
BEGIN
statements
END;
블럭의 statements구역내에 여러개의 sub-block이 생성될 수 있으며 이는 서브블럭내의 변수 들을 서브블럭 외부로부터 숨기기 위해 사용되어질수 있다. 블럭 앞부분의 declarations구역 에서 선언된 변수는 function 이 호출될 때 단지 한번 초기화가 되는 것이 아니라 블럭으로 진입할 때마다 매번 디폴트 값으로 초기화된다.
PL/pgSQL 의 BEGIN/END 와 Transaction(BEGIN; END;)을 위한 데이타베이스의 명령문과는 다르다는것을 이해해야 한다. 또한 Function과 Trigger Procedure 에서는 트랜잭션을 시작 하거나 commit 을 할 수 없고 Postgres는 중첩된 트랜잭션을 가질 수 없다.
-- : 한 라인의 주석처리
/* */ : 블럭 단위 주석 처리
CREATE FUNCTION logfunc2(text,text,text) RETURNS datetime
AS '
DECLARE logtxt1 ALIAS FOR $1;
logtxt2 ALIAS FOR $2;
logtxt2 ALIAS FOR $3;
curtime datetime;
BEGIN
curtime :=''now'';
INSERT INTO logtable VALUES (logtxt1,logtxt2,logtxt3,curtime);
RETURN curtime;
END;
' LANGUAGE 'plpgsql';
$1,$2,$3 은 함수의 인자들로서 나열된 순서로서 참조되어진다. DECLARE 의 ALIAS FOR 변수 $1 에 대한 별명을 설정한다. 이로서 $1 에 대한 가독성이 높아질수 있다. curtime := ''now''; 는 변수 curtime에 현재의 시각값을(''now'') 할당한다.":=" 은 변수에 값을 할당할때 쓰인다. 마지막으로 위의 함수의 리턴값이 datetime 이므로 datetime 타입의 변수 curtime 을 리턴하게 된다.
다음은 예제입니다. 적당히 화일로 복사해서 실행을 하면 됩니다.
DROP FUNCTION test1();
DROP TABLE tab1;
CREATE TABLE tab1 (
id int4,
name text
);
CREATE FUNCTION test1() RETURNS int4
AS '
DECLARE
var1 tab1.id%TYPE:=1;
var2 tab1.name%TYPE;
var3 var2%TYPE:=''nogada'';
BEGIN
INSERT INTO tab1(id,name) VALUES(var1,var3);
RETURN var1;
END;
' LANGUAGE 'plpgsql';
SELECT test1();
SELECT * FROM tab1;
위의 예제는 DROP명령문으로부터 시작한다. 별다른 이유는 없고 안전한 테스트를 위해 기존에 있을지 모를 function이나 table 을 먼저 삭제한다. DROP 명령어로 인한 에러는 무시해도 된다.Function의 DECLARE 부분은 변수의 선언 구역으로 보면 되겠다. var1 , var2,var3은 변수명이다. tab.id%TYPE 은 var1 의 변수형으로서 tab.id 의 속성을 참조하며 이속성의 바로 뒤의 %TYPE 에의해 지정된다. 또한 %TYPE 은 앞전에 선언된 변수의 자룔형을 참조 할 수 있다. var3 는 바로 전에 선언된 var2 의 자료형을 참조한다.
PL/pgSQL 은 트리거 프로시져를 정의하는데 사용되어질 수 있는데 CREATE FUNCTION문을 사용 하여 생성되어진다. 생성될 트리거 프로시져는 대체로 인자가없고 opaque형을 리턴하는 함수 로서 생성되어진다.
트리거 프로시져로서 생성된 함수에는 약간의 특수한 변수를 가지며 이는 자동으로 생성되어 지며 다음과 같다.
NEW : ROW 레벨 트리거상에서 INSERT/UPDATE 로 인해 새로리 생성된
ROW 를 유지하는 변수로서 데이타타입은 RECORD 이다. RECORD
형은 미리 구조화되지 않은 ROWTYPE로서 selection이나 insert
,update 시 결과로 생성된 하나의 row 를 유지하는 형이다.
OLD : new 와 대조되는 변수로서 UPDATE나 DELETE형 으로 인해 변경
되기 전의 ROW를 유지하는 변수이다.
TG_NAME : 데이타 타입은 NAME 이고 실제로 기동된 트리거의 이름에 대한
변수이다.
TG_WHEN : text형이고 BEFORE나 AFTER를 가진다.
TG_LEVEL : text형이고 ROW나 STATEMENT를 가진다.
TG_OP : text형이고 INSERT나 UPDATE나 DELETE 를 가진다.
TG_RELID : oid형이고(Object ID) 트리거를 기동시키는 테이블의 Object ID
이다.
TG_RELNAME : name형이고 트리거를 기동시키는 테이블의 name 을 가지는 변수
이다.
TG_NARGS : Integer형이고 트리거 프로시져에 주어지는 인자의 개수이다.
TG_ARGV[] : array of text 형이고 트리거 프로시져에 주어지는 인자들을
값으로 가지는 텍스트 배열형의 변수이다.
다음을 화일로 만들어 실행해보세요.
-------------------------------------------------------Cut here !!
DROP TRIGGER emp_stamp ON emp;
DROP FUNCTION emp_stamp() ;
DROP TABLE emp;
CREATE TABLE emp(
empname text,
salary int4,
last_date datetime,
last_user name
);
CREATE FUNCTION emp_stamp() RETURNS OPAQUE
AS '
BEGIN
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname ;
END IF;
IF NEW.salary < 0 THEN
RAISE NOTICE ''% cannot have a negative salary'', NEW.empname ;
END IF;
--NOTICE TEST
RAISE NOTICE ''TRIGGER NAME : %'',TG_NAME ;
RAISE NOTICE ''TRIGGER LEVEL : % TRIGGER OPERATION : %'',TG_LEVEL , TG_OP;
--EXCEPTION TEST
RAISE EXCEPTION ''TRIGGER WHEN : %'',TG_WHEN;
RAISE NOTICE ''TRIGGER LEVEL : % TRIGGER OPERATION : %'',TG_LEVEL , TG_OP;
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp AFTER INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
INSERT INTO emp(empname,salary) VALUES('nogadax',20);
INSERT INTO emp(empname) VALUES('winob');
INSERT INTO emp(salary) VALUES(10);
INSERT INTO emp(empname,salary) VALUES('diver',30);
INSERT INTO emp(salary) VALUES(-20);
SELECT * FROM emp;
--------------------------------------------------------------------------End !!
RAISE는 메시지를 던지는 것입니다. EXCEEPTION은 포스트그레스의 DEBUG레벨로서 데이타베이스에 log 를 남기고 트랜잭션을 중지한다. 다른 키워드로 NOTICE가 있 는데 이것은 데이타베이스를 작성하고 이를 클라이언트측으로도 전송한다.
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname ;
위의 문에서 '' 와 '' 사이의 % 는 NEW.empname 의 값을 출력한다.
CREATE TRIGGER emp_stamp AFTER INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
NEW.empname의 NEW는 RECORD타입으로서 트리거의 이벤트의 원인이 되는 emp 의 구조를 가지며 NEW 의 값은 event의 결과로서 추가되거나 변경된 행을 가진다.
example )
DECLARE
var_a int4 DEFAULT 5;
--var_a 변수의 DEFAULT의 값은 5이다.
example )
DECLARE
var_a test_table%ROWTYPE;
var_a 변수는 test_table 의 테이블 구조를 가진다.
example )
DECLARE
var_a RECORD ;
특정 테이블의 구조를 가지지 않고 selection 의 결과에
대한 구조를 가질 수 있다.(NEW,OLD)�
$n 에 대한 별칭
oldname를 newname로 바꿈
Postgres-BaseType : 포스트 그레스의 기본 자료형( int4,integer,text,char,..)
variable%TYPE
class.field%TYPE
Select expression
identifier :=expression;
SELECT expressions INTO target FROM ...
PERFORM query : Calling another function
RETURN expression;
RAISE [NOTICE | EXCEPTION] '' message % '',variable
IF expression THEN
statements
[ELSE statements]
END IF;
[label]
LOOP statements END LOOP;
[label]
WHILE expression LOOP statements END LOOP;
[label]
FOR name IN [REVERSE] expression LOOP statements END LOOP;
[label]
FOR record | row IN select_clause LOOP statement
END LOOP;
EXIT [label] [WHEN expression];