PostgreSQL설명과 예제 <author>주효식<htmlurl url="mailto:nogadax@chollian.net" name="nogadax@chollian.net"> <date>2000/03/23 <abstract>이 문서는 포스트그레스의 Index,function,trigger,grant,revoke,Large Object를 예제와 함께 설명한 글이다.</abstract> <toc> <sect>INDEX, SEQUENCE, FUNCTION(1) <sect1>Create Index <p>INDEX 는 데이타베이스내의 relation(테이블)에 대한 검색의 성능을 높여준다. <tscreen><verb> CREATE [UNIQUE] INDEX index_name ON table_name (name_of_attribute); CREATE [UNIQUE] INDEX index_name ON table_name [USING acc_name] (column [ops_name] [,...]); CREATE [UNIQUE] INDEX index_name ON table_name [USING acc_name] (func_name() ops_name ); acc_name : ACCESS METHOD . 디폴트는 BTREE 이다.(BTREE,RTREE,HASH) func_name : 사용자 정의 함수. ops_name : operator class (int4_ops,int24_ops,int42_ops) btree(sid int4_ops) 는 btree 를 이용한 INDEX 자료형이 4-BYTE 정수임. 디폴트 operator class 는 Field Type 이다. 현재 btree 는 7개까지의 Multi-Column INDEX를 지원한다. </verb></tscreen> <sect2>example<p> <tscreen><verb> example 1) INDEX 생성 1 CREATE INDEX indx1 ON supplier(sid); supplier relation(테이블) 의 sname attribute(column) 을 INDEX 로 지정 example 2) INDEX 생성 2 CREATE INDEX indx2 ON supplier USING btree(sid int4_pos); example 3) INDEX 생성 3 CREATE INDEX indx3 ON supplier USING btree(sid int8_ops); example 4) INDEX 생성 4 CREATE INDEX indx4 ON supplier USING btree(sid, tid); example 5) INDEX 삭제 DROP INDEX indx1; DROP INDEX indx2; DROP INDEX indx3; DROP INDEX indx4; </verb></tscreen> <sect1>Create SEQUENCE <p>SEQUENCE 는 순차적인 숫자 발생기이다. <tscreen><verb> CREATE SEQUENCE seq_name [INCREMENT increment] [MINVALUE minvalue] [MANVALUE maxvalue] [START start] [CACHE cache] [CYCLE] INCREMENT : 이값이 -1 이면 -1 만큼 감소 , 3 이면 3씩 증가, 디폴트는 1 이다. MAXVALUE : optional clause , 증가할수 있는 최고값을 명시적으로 지정 START : 시작값 CACHE : sequence 값을 먼저 메모리에 할당하여 빠른 ACCESS 를 가능케 한다. CYCLE : 최고값으로 증가되면 다시 최소값으로 순환하게 한다. </verb></tscreen> <sect2>example<p> <tscreen><verb> CREATE SEQUENCE seq_name1 START 101; SELECT NEXTVAL('seq_name1); 결과 nextval ------- 114 </verb></tscreen> <sect1>Create FUNCTION <p>FUNCTION 은 새로운 함수를 정의한다. <tscreen><verb> CREATE FUNCTION func_name([type[,...]]) RETURNS return_type [with (attribute [,...])] AS ' definition ' LANGUAGE 'language_name'; LANGUAGE : sql, pgsql, c 등이 있다. </verb></tscreen> <sect2> example<p> <tscreen><verb> CREATE FUNCTION test() RETURNS int4 AS ' SELECT 1 ' LANGUAGE 'sql'; 실행 SELECT test() AS answer; 결과 answer ------ 1 AS ' 와 ' 사이에 함수의 본문을 기입하면 된다. 참고로 문자열일 경우, 'seq_test1' 와 같은 경우 다음처럼 한다. CREATE FUNCTION test() RETURNS int4 AS ' SELECT NEXTVAL(''seq_test1'') ' LANGUAGE 'sql'; 여기서 NEXTVAL 은 SEQUENCE 관련 내장함수이다. </verb></tscreen> <sect1>예제 <p>다음 예제의 이름은 test.sql 입니다. 다음 예제를 화일로 만들어 다음처럼 실행하시면 됩니다. <descrip><tag> nogadax=> \i /usr/local/src/test.sql</tag><p></descrip> <tscreen><verb> -------------------------------------------------------cut here!! --code By nogadax@chollian.net /2000/02/18 --drop all object for safe_test DROP SEQUENCE seq_test1; DROP SEQUENCE seq_test2; DROP SEQUENCE seq_test3; DROP INDEX ind_test1; DROP INDEX ind_test2; DROP INDEX ind_test3; DROP TABLE tab_test1; DROP TABLE tab_test2; DROP TABLE tab_test3; DROP FUNCTION func_test(); --create sequence seq_test1,seq_test2,seq_test3 CREATE SEQUENCE seq_test1 START 101; CREATE SEQUENCE seq_test2 START 1; CREATE SEQUENCE seq_test3 START 1; --create table tab_test1,tab_test2,tab_test3 CREATE TABLE tab_test1( tab1_id int4 NOT NULL, tab1_name text, tab1_tel text, teb1_memo text ); CREATE TABLE tab_test2( tab2_id int4 NOT NULL, tab2_name text, tab2_tel text, teb2_memo text ); CREATE TABLE tab_test3( tab3_id int4 DEFAULT nextval('seq_test3') NOT NULL, tab3_name text, tab3_tel text, tab3_memo text ); --craete index CREATE UNIQUE INDEX ind_test1 ON tab_test1(tab1_id); CREATE UNIQUE INDEX ind_test2 ON tab_test2(tab2_id); CREATE UNIQUE INDEX ind_test3 ON tab_test3 USING btree(tab3_id int4_ops); --FUNCTION func_test() CREATE FUNCTION func_test() RETURNS INT4 AS ' SELECT NEXTVAL(''seq_test1'') ' LANGUAGE 'sql'; --transaction 1 BEGIN; INSERT INTO tab_test1 VALUES (func_test(),'jini1','000-0000','No_Memo1'); INSERT INTO tab_test2 VALUES (nextval('seq_test2'),'winob1','000-0001','No_Memo1'); INSERT INTO tab_test3 (tab3_name,tab3_tel,tab3_memo) VALUES ('nogadax1','000-0003','No_Memo1'); INSERT INTO tab_test1 VALUES (func_test(),'jini2','100-0000','No_Memo2'); INSERT INTO tab_test2 VALUES (nextval('seq_test2'),'winob2','100-0001','No_Memo2'); INSERT INTO tab_test3 (tab3_name,tab3_tel,tab3_memo) VALUES ('nogadax2','100-0003','No_Memo2'); INSERT INTO tab_test1 VALUES (func_test(),'jini3','200-0000','No_Memo3'); INSERT INTO tab_test2 VALUES (nextval('seq_test2'),'winob3','200-0001','No_Memo3'); INSERT INTO tab_test3 (tab3_name,tab3_tel,tab3_memo) VALUES ('nogadax3','200-0003','No_Memo3'); INSERT INTO tab_test1 VALUES (func_test(),'jini4','300-0000','No_Memo4'); INSERT INTO tab_test2 VALUES (nextval('seq_test2'),'winob4','300-0001','No_Memo4'); INSERT INTO tab_test3 (tab3_name,tab3_tel,tab3_memo) VALUES ('nogadax4','300-0003','No_Memo4'); INSERT INTO tab_test1 VALUES (func_test(),'jini5','400-0000','No_Memo5'); INSERT INTO tab_test2 VALUES (nextval('seq_test2'),'winob5','400-0001','No_Memo5'); INSERT INTO tab_test3 (tab3_name,tab3_tel,tab3_memo) VALUES ('nogadax5','400-0003','No_Memo5'); END; --transaction 2 BEGIN; SELECT * FROM tab_test1; SELECT * FROM tab_test2; SELECT * FROM tab_test3; VACUUM VERBOSE ANALYZE tab_test1; VACUUM VERBOSE ANALYZE tab_test2; VACUUM VERBOSE ANALYZE tab_test3; END; -------------------------------------------------------------------End !! </verb></tscreen> <sect>GRANT and REVOKE <sect1>GRANT <p>GRANT는 user,group 혹은 모든 user들에게 해당 객체에 대한 사용권한을 승인한다. REVOKE는 user,group 혹은 모든 user로부터 객체에 대한 사용권한을 무효화한다. <tscreen><verb> GRANT privilege [,...] ON object [,...] TO { PUBLIC | GROUP group | username} </verb></tscreen> <descrip><tag>privilege</tag><p></descrip> <tscreen><verb> SELECT : 특정 TABLE/VIEW 의 column에 대한 access 을 승인 INSERT : 특정 TABLE의 모든 column 에 데이타의 삽입에 대한 권한 승인 UPDTAE : 특정 TABLE의 모든 column 의 갱신에 대한 권한 승인 DELETE : 특정 TABLE 의 row 의 삭제에 대한 권한 승인 RULE : 특정 TABLE/VIEW에 대한 rule 을 정의하는 권한에 대한 승인 ALL : 모든 권한을 승인한다. </verb></tscreen> <descrip><tag>object</tag><p></descrip> <tscreen><verb> access 를 승인하는 객체의 이름으로서 다음과 같은 객체들이 있다. Table Sequence View Index </verb></tscreen> <descrip><tag>PUBLIC</tag><p></descrip> <tscreen><verb> 모든 유저를 승인 </verb></tscreen> <descrip><tag>GROUP group</tag><p></descrip> <tscreen><verb> 사용 권한을 획득할 group을 지정, group 을 명시적으로 생성되어져 있어야 함. </verb></tscreen> <descrip><tag>username</tag><p></descrip> <tscreen><verb> 사용권한을 획득할 사용자명. PUBLIC 은 모든 유저에 대해서 적용된다. </verb></tscreen> <descrip><tag>Description</tag><p></descrip> <tscreen><verb> GRANT 는 객체 (object) 를 생성한 유저가 모든 유저, 혹은 개인 유저, 혹은 그룹에 대해 해당 객체의 사용 권한을 허용하도록 한다. 객체를 생성한 유저가 아닌 다른 유저들은 그 객체에 대한 사용권한이 없어서 사용할 수가 없다. 단지 그 해당 객체를 생성한 유저만이 이를 허용할 수 가 있는데 이는 GRANT 를 사용함으로서 다른 유저들이 사용할 수 있도록 허용한다. 어떤 객체를 생성한 유저는 자동적으로 모든 권한을 가지며 이 권한들은 SELECT INSERT, UPDATE, DELETE, RULE 등이며 또한 그 객체 자체를 삭제할 수 있다. </verb></tscreen> <descrip><tag>Notes</tag><p></descrip> <tscreen><verb> psql 에서 "\z" 를 사용하여 존재하는 객체에 대한 permission 등을 참조할 수 있다. </verb></tscreen> <descrip><tag>permission 정보의 형식</tag><p></descrip> <tscreen><verb> username=arwR : 유저에게 승인된 사용권한 group gname=arwR : GROUP 에게 승인된 사용권한 =arwR : 모든 유저에게 승인된 사용권한 a : INSERT privilege r : SELECT privilege w : UPDATE/DELETE privilege R : RULE privilege arwR : ALL privilege </verb></tscreen> <descrip><tag>USAGE(사용예)</tag><p></descrip> <tscreen><verb> GRANT INSERT ON imsi_table TO PUBLIC GRANT ALL ON imsi_table TO nogadax </verb></tscreen> <sect1>REVOKE <p>유저,그룹, 혹은 모든 유저로부터 access privilege 를 취소 <tscreen><verb> REVOKE privilege [,...] ON object [,...] FROM { PUBLIC | GROUP gname | username } </verb></tscreen> <descrip><tag>privilege</tag><p></descrip> <tscreen><verb> SELECT ,INSERT ,UPDATE, DELETE, RULE, ALL </verb></tscreen> <descrip><tag>object</tag><p></descrip> <tscreen><verb> 적용될 수 있는 객체 : table, view, sequence, index </verb></tscreen> <descrip><tag>group</tag><p></descrip> <tscreen><verb> privilege 를 취소할 그룹명 </verb></tscreen> <descrip><tag>username</tag><p></descrip> <descrip><tag>PUBLIC</tag><p></descrip> <descrip><tag>Description</tag><p></descrip> <tscreen><verb> REVOKE 는 객체의 생성자가 모든 유저, 유저, 그룹들로부터 전에 승인했던 퍼미션을 해제한다. </verb></tscreen> <descrip><tag>USAGE(사용예)</tag><p></descrip> <tscreen><verb> REVOKE INSERT ON imsi_table FROM PUBLIC REVOKE ALL ON imsi_table FROM nogadax </verb></tscreen> <sect>TRIGGER <sect1>TRIGGER 1<p> <tscreen><verb> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ...]} ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE func_name ( ) event : INPUT , UPDATE , DELETE 등이 올 수 있다. func_name : 사용자 정의 함수이다. plpgsql 을 권장하고 싶다. 또한 이 함수에는 인자가 들어올 수 없다. 또한 이 함수의 RETURN TYPE 로는 OPAQUE 이어야 한다. 인수를 위해 TRIGGER 의 자체 변수를 사용하여야 한다. (new,old,...) TRIGGER 를 촌스럽게 표현하자면 방아쇠를 당기면 총알이 나가는 것에 비유할 수 있다. TRIGGER 은 TUPLE(ROW or RECORD) 에 대한 INSERT, UPDATE, DELETE 를 사건(event) 로 보고 이 사건에 정의된 어떤 행동으로 반응하는 것이다.� </verb></tscreen> <descrip><tag>example</tag><p></descrip> <tscreen><verb> CREATE TRIGGER trg_test BEFORE DELETE OR UPDATE ON supplier FOR EACH ROW EXECUTE PROCEDURE check_sup(); supplier 테이블에 DELETE, UPDATE 가 발생하면 이 동작이 행해지기 전에 check_sup() 를 실행하라. 이 TRIGGER 의 이름은 trg_test 이다. </verb></tscreen> <descrip><tag>plpgsql 맛보기</tag><p></descrip> <tscreen><verb> CREATE FUNCTION func_name() RETURNS type AS ' [DECLARE declarations] BEGIN statements END; 'LANGUAGE 'plpgsql'; </verb></tscreen> <descrip><tag>example</tag><p></descrip> <tscreen><verb> CREATE FUNCTION pgsql_test() RETURNS datetime AS ' DECLARE curtime datetime; BEGIN curtime:= ''now''; return curtime; END; ' LANGUAGE 'plpgsql'; </verb></tscreen> <sect1>TRIGGER 실행을 위한 PL/pgSQL등록 방법 <p>다음은 실행 예제이다. 우선 다음 예제를 적절히 복사를 해서 파일로 만든다.(예 : trg.sql) 다음 예제를 실행에 앞서 먼저 해야할 일이 있는데 그것은 Procedural Languages 를 등록하는 것이다. 다음예제에는 PL/pgSQL 을 이용한 함수를 사용하므로 이것을 등록하는 것은 필수이다. <p>방법은 두가지가 있다. <p>1. template1 데이타 베이스에 등록하는 것이다. 이 데이타베이스에 등록이 된후 template1 데이타베이스에서 create database 명령으로 데이타베이스를 생성하면 자동적으로 생성된 데이타베이스에 PL/pgSQL이 등록이 되므로 편리하다. <p>등록에 앞서 다음을 확인하라. <p>postgreSQL의 PATH : 여기서의 PATH 는 /usr/local/pgsql 이다. 또한 pgsql 디렉토리 밑의 lib 디렉토리에서 plpgsql.so 를 확인하라. 아마도 이 화일은 다 존재할 것이다. <tscreen><verb> 등록 과정 [postgres@nogadax postgres]# psql template1 template1=> template1=> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS template1-> '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'c'; template1=> template1=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' template1-> HANDLER plpgsql_call_handler template1-> LANCOMPILER 'PL/pgSQL'; template1=> CREATE DATABASE nogadax; template1=> \q [postgres@nogadax postgres]# 2. 다음 방법은 생성한 데이타베이스마다 하나하나 다 등록을 하는 것이다. 등록 과정 [postgres@nogadax postgres]# psql nogadax nogadax=> nogadax=> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS nogadax-> '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'c'; nogadax=> nogadax=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' nogadax-> HANDLER plpgsql_call_handler nogadax-> LANCOMPILER 'PL/pgSQL'; nogadax=> nogadax=> \q [postgres@nogadax postgres]# 이제는 위의 두가지 방법중 하나를 선택하여 등록을 하면 된다. </verb></tscreen> <sect1>TRIGGER 예제 1 <p>다음은 아래 예제를 실행하는 방법이다. <tscreen><verb> [podtgres@nogadax postgres]$ psql nogadax nogadax=> \i /home/postgres/trg.sql ..... ..... -------------------------------------------------------Cut here!! --coded BY NoGaDa-X 2000/02/19 --DROP all Object for safe_test DROP FUNCTION ins_row(); DROP TRIGGER trg_test ON test1; DROP TABLE test1; DROP TABLE test2; --Create Table CREATE TABLE test1( tab1_id int4, tab1_name text ); CREATE TABLE test2( tab2_id int4, tab2_memo text DEFAULT 'None' ); --Create Function CREATE FUNCTION ins_row() RETURNS OPAQUE AS ' BEGIN INSERT INTO test2(tab2_id) VALUES(new.tab1_id); RETURN new; END; ' LANGUAGE 'plpgsql'; --Create Trigger CREATE TRIGGER trg_test AFTER INSERT ON test1 FOR EACH ROW EXECUTE PROCEDURE ins_row(); --INSERT Transaction BEGIN; INSERT INTO test1 values(1,'nogadax'); INSERT INTO test1 values(2,'winob'); INSERT INTO test1 values(3,'diver708'); INSERT INTO test1 values(4,'jini'); INSERT INTO test1 values(5,'opensys'); INSERT INTO test1 values(6,'Linuz'); END; --SELECT TRACTION BEGIN; SELECT * FROM test1; SELECT * FROM test2; END; ----------------------------------------------------------End !! </verb></tscreen> <sect1>TRIGGER 2<p> <descrip><tag>CREATE TRIGGER</tag><p></descrip> <tscreen><verb> CREATE TRIGGER trigger_name { BEFORE | AFTER } { event [OR,...] } ON table FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE funcname (); trigger_name : TRIGGER 의 이름 table : Table 의 이름 event : INSERT , DELETE , UPDATE 들 중에서 하나 혹은 두세개를 TRIGGER 를 기동시키기 위한 event 로 봄 ( 예 : INSERT OR UPDATE ) func_name : user 가 제공한 함수. 이 함수는 트리거가 생성되기 전에 만들어져야 한다. 또한, 이 함수의 return 형은 opaque이며 인수가 없어야 한다. (이 부분은 PostgreSQL Programmer's Guide 에 나와 있는 부분인데 function 에서 왜 인수가 쓰이면 안되는지 그리고 opaque 형의 리턴 값만 되는지 를 정확히 설명한 부분이 없다.) "CREATE TRIGGER"로 인해 TRIGGER 가 성공적으로 생성되면 CREATE 라는 메시지가 출력된다. </verb></tscreen> <descrip><tag>DESCRIPTION</tag><p></descrip> <tscreen><verb> CREATE TRIGGER은 현재의 데이타베이스에 새로운 TRIGGER을 등록할 것이다.Trigger 은 테이블(릴레이션)과 연계되어서 미리 규정된 함수 func_name을 실행한다. 트리거는 트리거의 생성시 BEFORE키를 사용하여 Tuple(row,record)에 어떤 event가 발생하기 전에 기동되어 질수 있도록 규정되어질수 있으며 반대로 AFTER키를 사용하 여 event가 완료 후에 기동되게 할수도 있다. (다음은 부분적으로 Postgres data changes visibility rule 이 참조되었다.) 트리거가 BEFORE에 의해 event전에 기동되어 진다면, 트리거는 현재의 Tuple에 대한 event를 건너뛰게 한다. 특히 INSERT나 UPDATE의 event에 대해서는 삽입되어질 튜플 의 변화를 인지할 수 없다. 즉, BEFORE성격의 트리거는 변경되어질 튜플들에 대해서 "invisible"한 상태이다. 단지, 처리되어질 event 만 인식할 수 있다. 또한, 트리거가 AFTER 키에 의해 event후에 기동되어지면, 최근의 삽입,UPDATE,삭제 등이 트리거에 "visible" 이다. 즉, 변경된 부분을 트리거가 인지할 수 있다. event는 다중의 event 를 OR 에 의해 규정할 수 있다. 또한 동일한 릴레이션에 동일 한 event 를 지정하는 하나 이상의 트리거를 정의할 수 있으나, 이는 트리거의 기동 순서를 예측할 수 없게 된다. 트리거가 SQL 쿼리를 실행할때 다른 트리거들을 기동시킬 수 있으며 이를 CASCADE 트리거라 한다. 이러한 캐스캐이드 트리거의 레벨에는 제한이 없으므로 한번의 트리 거로 여러개의 다중의 트리거를 기동시킬 수 있다. 동일한 릴레이션에 대한 INSERT 트리거가 있다면 이 트리거는 다시 동일한 릴레이션 에 대한 트리거가 기동되어질 수 있다. 하지만 아직 PostgreSQL은 이런 트리거에 대 한 튜플의 동기화가 지원되지 않으므로 주의를 하여야 할 것이다. </verb></tscreen> <descrip><tag>NOTES</tag><p></descrip> <tscreen><verb> CREATE TRIGGER 은 PostgreSQL의 확장된 기능이다. 단지 릴레이션(Table) 의 소유자만이 그 릴레이션에 트리거를 생성할 수 있다. 버젼 6.4에서 STATEMENT 는 구현되지 않았다. </verb></tscreen> <sect1>TRIGGER 예제 2 <p>CASCADING TRIGGER SAMPLE <tscreen><verb> ----------------------------------------------------Cut here !! --coded by NoGaDa-X --cascading tigger DROP TRIGGER tab1_trg ON test1; DROP TRIGGER tab2_trg ON test2; DROP TRIGGER tab3_trg ON test3; DROP FUNCTION tab1_func(); DROP FUNCTION tab2_func(); DROP FUNCTION tab3_func(); DROP TABLE test1; DROP TABLE test2; DROP TABLE test3; DROP TABLE test4; --Create Table CREATE TABLE test1( tab1_id INT4 ); CREATE TABLE test2( tab2_id INT4 ); CREATE TABLE test3( tab3_id INT4 ); CREATE TABLE test4( tab4_id INT4 ); --Create Function CREATE FUNCTION tab1_func() RETURNS opaque AS ' BEGIN INSERT INTO test2 values( new.tab1_id); RETURN new; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION tab2_func() RETURNS opaque AS ' BEGIN INSERT INTO test3 values( new.tab2_id); RETURN new; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION tab3_func() RETURNS opaque AS ' BEGIN INSERT INTO test4 values( new.tab3_id); RETURN new; END; ' LANGUAGE 'plpgsql'; --Create Trigger CREATE TRIGGER tab1_trg AFTER INSERT OR UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE tab1_func(); CREATE TRIGGER tab2_trg AFTER INSERT OR UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE tab2_func(); CREATE TRIGGER tab3_trg AFTER INSERT OR UPDATE ON test3 FOR EACH ROW EXECUTE PROCEDURE tab3_func(); --transaction BEGIN; INSERT INTO test1 VALUES (1); SELECT * from test1; INSERT INTO test1 VALUES (2); SELECT * from test2; INSERT INTO test1 VALUES (3); SELECT * from test3; INSERT INTO test1 VALUES (4); SELECT * from test4; END; -----------------------------------------------End !! </verb></tscreen> <sect>PL/pgSQL <sect1>PL/pgSQL 1 (PL/pgSQL처리기 등록)<p> <descrip> <tag>1. Procedure Language</tag><p> </descrip> <p>Postgres 6.3 버젼 부터 PostgreSQL은 procedural Language(PL)를 지원하기 시작했다. 이것은 PostgreSQL만의 특별한 경우로서 oracle 의 PL/SQL과 비유될 수 있다. 하지만 특수한 언어인 PostgreSQL의 PL은 PostgreSQL에 내장된것 아니고 모듈화된 Handler를 다시 데이타베이스에 등록을 해주어야 한다. 그렇지 않으면 데이타베이스는 PL 로 쓰 여진 function의 내용을 이해할 수 없을 것이다. 결론적으로 처리기는 공유객체로서 컴파일되며 동적으로 Load 되는 특별한 언어 처리 기능이다. <p>여기서는 PL Handler의 등록의 예로서 PL의 한종류인 PL/pgSQL 언어를 등록하겠다. <descrip><tag>Installing Procedural Languages</tag><p></descrip> <p> 공유객체인 처리기는 컴파일된 후 인스톨되어야 하는데 디폴트로 PL/pgSQL 은 PostgreSQL 설치시 자동으로 컴파일된후 라이브러리 디렉토리에 놓여진다. 다 른 처리기인 PL/Tcl 은 PostgreSQL 컴파일시 명시적으로 설정되어야지만 컴파 일되며 라이브러리 디렉토리에 놓여진다. 라이브러리 디렉토리는 설치되어질 PostgreSQL의 바로 밑의 lib 이다. 예를 들어 PostgreSQL의 절대경로가 다음과 같다고 하자. <tscreen><verb>/usr/local/pgsql</verb></tscreen> <p> 그러면 라이브러리 디렉토리의 절대경로는 다음과 같다. <tscreen><verb>/usr/local/pgsql/lib</verb></tscreen> <p> PL/pgSQL 언어 처리기를 설치하기 위해서는 먼저 위의 라이브러리 디렉토리에서 "plpgsql.so" 를 먼저 확인하여야 한다. <p> 확인 후 CREATE FUNCTION 와 CREATE PROCEDURAL LANGUAGE 에 의해 각 데이타베이스 에서 등록을 하여야 한다. 각 데이타베이스에서 등록을 하지않고 일괄적으로 처리하고 싶다면 PostgreSQL 의 특별힌 데이타베이스인 "template1" 에서 등록을 하면된다. template1 에서 등록이 되었다면 차후 생성되는 데이타베이스에는 자동적으로 처리기가 등록된다. <descrip><tag>PL/pgSQL 처리기 등록 예제</tag><p></descrip> <tscreen><verb> [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=> </verb></tscreen> <p> 혹은 위의 문을 화일로 저장한후 \i 를 사용할 수 있다. <tscreen><verb> template1=> \i /usr/local/src/plsql_inst.sql</verb></tscreen> <p>"CREATE TRUSTED PROCEDURAL LANGUAGE" 에서 TRUSTED 키워드는 PostgreSQL 의 슈퍼유저 권한이 없는 일반 유저가 "CREATE FUNCTION" 이나 "CREATE TRIGGER" 문을 사용할 때 등록 된 procedure language(PL) 를 사용할 수 있도록 해준다. <descrip><tag>2. PL/pgSQL</tag><p></descrip> <p>PL/pgSQL 은 PostgreSQL 데이타베이스 시스템에서 "Loadable Procedural Language"이다. 이 패키지는 Jan Wieck 에의해 작성되었다. <descrip><tag>OVERVIEW</tag><p></descrip> <tscreen><verb> 1. PL/pgSQL 은 function 이나 trigger procedure 를 만드는데 사용되어 질 수 있다. 2. SQL 문에 제어 구조를 추가할 수 있다. 3. 복잡한 계산을 구현할 수 있다. 4. user가 정의한 Type, Function, Operation을 상속할 수 있다. 5. Server 에 의해 Trusted(Authentication 관련의 뜻)된것을 정의할 수 있다. 6. 사용하기 쉽다. </verb></tscreen> <descrip><tag>설명</tag><p></descrip> <tscreen><verb> PL/pgSQL 은 대소문자의 구분이 없으므로 키워드나 Identifier 들은 대소문자 구분없이 혼용되어 쓰일 수 있다. </verb></tscreen> <p>PL/pgSQL 은 블럭 지향언어이다. 블럭은 다음처럼 정의되어진다. <tscreen><verb> [ Label ] [ DECLARE declarations ] BEGIN statements END; </verb></tscreen> <p>블럭의 statements구역내에 여러개의 sub-block이 생성될 수 있으며 이는 서브블럭내의 변수 들을 서브블럭 외부로부터 숨기기 위해 사용되어질수 있다. 블럭 앞부분의 declarations구역 에서 선언된 변수는 function 이 호출될 때 단지 한번 초기화가 되는 것이 아니라 블럭으로 진입할 때마다 매번 디폴트 값으로 초기화된다. <p>PL/pgSQL 의 BEGIN/END 와 Transaction(BEGIN; END;)을 위한 데이타베이스의 명령문과는 다르다는것을 이해해야 한다. 또한 Function과 Trigger Procedure 에서는 트랜잭션을 시작 하거나 commit 을 할 수 없고 Postgres는 중첩된 트랜잭션을 가질 수 없다. <tscreen><verb> -- : 한 라인의 주석처리 /* */ : 블럭 단위 주석 처리 </verb></tscreen> <descrip><tag>example</tag><p></descrip> <tscreen><verb> 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'; </verb></tscreen> <descrip><tag>설명</tag><p></descrip> <p>$1,$2,$3 은 함수의 인자들로서 나열된 순서로서 참조되어진다. DECLARE 의 ALIAS FOR 변수 $1 에 대한 별명을 설정한다. 이로서 $1 에 대한 가독성이 높아질수 있다. curtime := ''now''; 는 변수 curtime에 현재의 시각값을(''now'') 할당한다.":=" 은 변수에 값을 할당할때 쓰인다. 마지막으로 위의 함수의 리턴값이 datetime 이므로 datetime 타입의 변수 curtime 을 리턴하게 된다. <sect1>PL/pgSQL 2<p> <descrip><tag>example 1</tag><p></descrip> <p>다음은 예제입니다. 적당히 화일로 복사해서 실행을 하면 됩니다. <tscreen><verb> 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; </verb></tscreen> <descrip><tag>설명</tag><p></descrip> <p>위의 예제는 DROP명령문으로부터 시작한다. 별다른 이유는 없고 안전한 테스트를 위해 기존에 있을지 모를 function이나 table 을 먼저 삭제한다. DROP 명령어로 인한 에러는 무시해도 된다.Function의 DECLARE 부분은 변수의 선언 구역으로 보면 되겠다. var1 , var2,var3은 변수명이다. tab.id%TYPE 은 var1 의 변수형으로서 tab.id 의 속성을 참조하며 이속성의 바로 뒤의 %TYPE 에의해 지정된다. 또한 %TYPE 은 앞전에 선언된 변수의 자룔형을 참조 할 수 있다. var3 는 바로 전에 선언된 var2 의 자료형을 참조한다. <descrip><tag>Trigger Procedure</tag><p></descrip> <p>PL/pgSQL 은 트리거 프로시져를 정의하는데 사용되어질 수 있는데 CREATE FUNCTION문을 사용 하여 생성되어진다. 생성될 트리거 프로시져는 대체로 인자가없고 opaque형을 리턴하는 함수 로서 생성되어진다. <p>트리거 프로시져로서 생성된 함수에는 약간의 특수한 변수를 가지며 이는 자동으로 생성되어 지며 다음과 같다. <tscreen><verb> 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 형이고 트리거 프로시져에 주어지는 인자들을 값으로 가지는 텍스트 배열형의 변수이다. </verb></tscreen> <sect1>예제 <p>다음을 화일로 만들어 실행해보세요. <tscreen><verb> -------------------------------------------------------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 !! </verb></tscreen> <descrip><tag>설명</tag><p></descrip> <p>RAISE는 메시지를 던지는 것입니다. EXCEEPTION은 포스트그레스의 DEBUG레벨로서 데이타베이스에 log 를 남기고 트랜잭션을 중지한다. 다른 키워드로 NOTICE가 있 는데 이것은 데이타베이스를 작성하고 이를 클라이언트측으로도 전송한다. <tscreen><verb> RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname ; </verb></tscreen> <p>위의 문에서 '' 와 '' 사이의 % 는 NEW.empname 의 값을 출력한다. <tscreen><verb> CREATE TRIGGER emp_stamp AFTER INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); </verb></tscreen> <p>NEW.empname의 NEW는 RECORD타입으로서 트리거의 이벤트의 원인이 되는 emp 의 구조를 가지며 NEW 의 값은 event의 결과로서 추가되거나 변경된 행을 가진다. <descrip><tag>정리</tag><p></descrip> <descrip><tag>Declaration : 선언부</tag><p></descrip> <descrip><tag>name [CONSTANT] type [NOT NULL] [DEFAULT | :=value]</tag><p></descrip> <tscreen><verb> example ) DECLARE var_a int4 DEFAULT 5; --var_a 변수의 DEFAULT의 값은 5이다. </verb></tscreen> <descrip><tag>name class%ROWTYPE</tag><p></descrip> <tscreen><verb> example ) DECLARE var_a test_table%ROWTYPE; var_a 변수는 test_table 의 테이블 구조를 가진다. </verb></tscreen> <descrip><tag>name RECORD</tag><p></descrip> <tscreen><verb> example ) DECLARE var_a RECORD ; 특정 테이블의 구조를 가지지 않고 selection 의 결과에 대한 구조를 가질 수 있다.(NEW,OLD)� </verb></tscreen> <descrip><tag>name ALIAS FOR $n;</tag><p></descrip> <tscreen><verb> $n 에 대한 별칭 </verb></tscreen> <descrip><tag>RENAME oldname TO newname</tag><p></descrip> <tscreen><verb> oldname를 newname로 바꿈 </verb></tscreen> <descrip><tag>Data Type : 자료형</tag><p></descrip> <tscreen><verb> Postgres-BaseType : 포스트 그레스의 기본 자료형( int4,integer,text,char,..) variable%TYPE class.field%TYPE </verb></tscreen> <descrip><tag>Expression</tag><p></descrip> <tscreen><verb> Select expression </verb></tscreen> <descrip><tag>Statement : 처리문장</tag><p></descrip> <descrip><tag>Assignment : 값의 할당</tag><p></descrip> <tscreen><verb> identifier :=expression; SELECT expressions INTO target FROM ... PERFORM query : Calling another function RETURN expression; RAISE [NOTICE | EXCEPTION] '' message % '',variable </verb></tscreen> <descrip><tag>제어문</tag><p></descrip> <tscreen><verb> 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]; </verb></tscreen> <sect>Large Object with Transaction <sect1>Large Object와 예제 <p>포스트그레스에서는 한 튜플의 사이즈가 8192 Byte (8k Bytes) 로 제한되어 있다. 하나의 레코드에 들어갈 수 있는 데이타의 총 크기가 제한되어 있으므로 이미지나 사이즈가 8K 를 넘는 문서들은 다르게 저장되어야 한다. <p>포스트그레스는 Large Object 라는 개념으로 이를 극복하려한다. 과거에 포스트그 레스는 이런 큰 사이즈의 데이타를 위해 3가지의 지원이 있었으나 사용자들사이의 잦은 혼란으로 하나만을 지원하게 되었고 그것은 단순히 데이타베이스안의 데이타로 서의 Large Object 를 지원한다. 이것은 액세스를 할때 느릴수 있지만 엄격한 데이타 무결성을 제공한다. <p>포스트그레스는 Large Object 를 쪼개어 이를 데이타베이스의 튜플들에 저장한다. B-tree 인덱스는 랜덤한 resd-write 액세스에 대한 빠른 검색을 보증한다. <tscreen><verb> 다음은 예제이다. -------------------------------------------------------------------- drop table image; BEGIN WORK; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; create table image( name text, raster oid ); insert into image (name , raster) values ('snapshot' , lo_import('/usr/local/src/snapshot01.gif') ); select lo_export (image.raster , '/tmp/snap.gif') from image where name='snapshot'; COMMIT WORK; ----------------------------------------------------------------------- </verb></tscreen> <descrip><tag>Large Object Note</tag><p></descrip> <p>위의 예제에서 명시적으로 트랜잭션내에서 Large Object 의 처리가 이루어지고 있다. 이는 포스트그레스 6.5 버젼대에서부터의 Large Object 처리에 대한 요구사항으로서 6.5 이전 버젼의 암시적인 트랜잭션 요구사항과는 달리 명시적인 트랜잭션을 요구한 다. 이 요구사항이 무시된다면, 즉 명시적인 트랜잭션문이 작성되지 않는다면 비정 성적인 결과를 만든다. <descrip><tag>설명</tag><p></descrip> <tscreen><verb> BEGIN WORK; 사용자 정의 트랜잭션 시작 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 트랜잭션 레벨 중 가장 강력한 SERIZABLE 레벨로 재 설정을 함. 포스트그레스의 트랜잭션의 디폴트 레벨은 "READ COMMITTED" 로서 트랜잭션 내의 query 는 이 query 가 실행되기전에 commit 된 데이타만 다룰 수 있다. SERIALIZABLE 는 포스트그레스의 가장 강력한 트랜잭션 레벨로서 트랜잭션내의 query는 query시작전이 아닌 그 트랜잭션이 시작되기전에 commit된 데이타만을 다룰수 있다. OID 는 객체에대한 포스트그레스의 시스템 관련 식별자이다. lo_import(읽어올 데이타의 PATH); 는 데이타를 읽어들이는 Large Object 관련 내장 함수이다. lo_export( OID , 데이타가 쓰여질 시스템의 PATH); 는 데이타를 읽어서 꺼내는 Large Object 관련 내장함수이다. COMMIT WORK; 는 트랜잭션의 완료를 의미한다. 이로 인해 실질적인 갱신이나 삭제등이 이루어진다. </verb></tscreen> <sect1> TRANSACTION<p> <sect2>트랜잭션의 성격(ACID)<p> <tscreen><verb> 원자성 : 하나의 트랜잭션은 다수의 query를 실행하지만 이는 단지 하나의 (ATOMIC) query 인양 실행되어야 한다. 일관성 : 트랜잭션의 수행에 대해 데이타베이스의 데이타들의 일관성은 (CONSISTENT) 유지되어야 한다. 분리 : 각 트랜잭션은 분리되어 다른 트랜잭션중에 간섭해서는 안된다. (ISOLATABLE) 이는 병렬 (CONCURRENCY) 제어의 개념으로 데이타베이스는 멀티 유저 환경일 수 있으므로 각 유저의 트랜잭션은 안전하게 이루 어져야 한다. 영구성 : 트랜잭션의 수행후 commit 된 데이타들은 영구적으로 유지되어야 (DURABLE) 한다. </verb></tscreen> <sect2>트랜잭션 관련 SQL 명령어 정리 <p> <descrip><tag>BEGIN [WORK | TRANSACTION]</tag><p></descrip> <tscreen><verb> BEGIN : 새로운 트랜잭션이 Chain Mode로 시작했음을 알린다. WORK , TRANCTION : Optional Keyword. They have no effect. </verb></tscreen> <descrip><tag>COMMIT [WORK | TRANSACTION]</tag><p></descrip> <tscreen><verb> 트랜잭션후 변경된 결과를 저장. </verb></tscreen> <descrip><tag>END [WORK | TRANCTION]</tag><p></descrip> <tscreen><verb> 현재 트랜잭션을 COMMIT. END는 포스트그레스 확장으로서 COMMIT 와 같은 의미이다. </verb></tscreen> <descrip><tag>LOCK [TABLE] name</tag><p> <tag>LOCK [TABLE] name IN [ROW | ACCESS] {SHARE | EXCLUSIVE} MODE</tag><p> <tag>LOCK [TABLE] name IN SHARE ROW EXCLUSIVE MODE</tag><p></descrip> <tscreen><verb> 명시적으로 트랜잭션 내의 테이블을 잠금. </verb></tscreen> <descrip><tag>ROLLBACK [WORK | TRANSACTION]</tag><p></descrip> <tscreen><verb> 현재 트랜잭션을 중지한다. </verb></tscreen> <descrip><tag>ABORT [WORK | TRANSACTION]</tag><p></descrip> <tscreen><verb> 현재 트랜잭션을 중지한다. ABORT 는 포스트그레스 확장으로 ROLLBACK와 같은 의미로서 쓰인다. </verb></tscreen> <descrip><tag>SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}</tag><p></descrip> <tscreen><verb> 현재 트랜잭션에 대한 분리 레벨을 설정한다. </verb></tscreen> <descrip><tag>설명</tag><p></descrip> <tscreen><verb> INSERT INTO tab VALUES('qwe','www',123); 위의 INSERT문 이 성공적으로 수행되었다면 commit 될것이다. 아니면 RollBack 될것이다. 다시 말해, 위의 문이 성공하면 데이타베이스에 그에 따른 데이타가 저장되고 그렇지 않고 INSERT 의 실행결과가 ERROR 이면 데이타는 저장되지 않는다. 이를 autocommit 라 하는데 또한 다른말로 unchained mode 라고도 한다. 포스트그레스에서의 일반적인 명령들의 실행은 unchained mode 이다. 그리고 이를 좀 더 그술적으로 서술하면 다음과 같다. "각각의 문장(statement)들은 암시적인 트랜잭션내에서 실행되어지고 그 문장의 끝부분에서 commit가 이루어지는데 실행이 성공적이면 commit 가 행해지고 반대로 실행이 성공적이지 않으면 rollback 되어진다." 결국은 개별적인 SQL 문들의 실행에 있어 사용자들은 자신도 모르게 트랜잭션내에서 수행하고 있고 또한 그 결과도 자신도 모르게 commit 이거나 rollback이 이루어진다. BEGIN 은 명시적으로 트랜잭션을 시작함을 의미하며 autocommit 이 되지 않는다(chained mode). 명시적인 commit 문이 올때까지 작업들의 결과들이 데이타베이스에 저장되지 않는다. BEGIN 문 바로 뒤에 SET 문을 사용하여 그 트랜잭션의 트랜잭션 분리 레벨 을 지정할 수 있다. SET 문의 예는 다음과 같다. BEGIN WORK; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE INSERT INTO tab VALUES(1,2,3); INSERT INTO tab VALUES(3,4,5); COMMIT WORK; 트랜잭션 분리 정책은 여러 유저의 동시성 에 대한 보다 강력한 제한이라 할 수 있겠다. 포스트그레스에의 디폴트 트랜잭션 분리레벨은 "READ COMMITTED" 이다. READ COMMITTED 보다 더욱더 엄격한 레벨이 SERIALIZABLE 이다. </verb></tscreen> <sect>참고 문서와 기타 <p>http://database.sarang.net <p>http://www.postgresql.org <p>postgresql 도큐먼트 <tscreen><verb> programmer's guide user's guide </verb></tscreen> <p>오라클 관련 도서. <tscreen><verb> 의외로 포스트그레스와 오라클이 비슷한 부분이 많더군요. 개념적으로 잘 이해가 되지 않을 때 참조를 하면서 포스트그레스를 공부하면 좋을 겁니다. </verb></tscreen> </article>