· KLDP.org · KLDP.net · KLDP Wiki · KLDP BBS ·
Linuxdoc Sgml/PgSQL_Extension-KLDP

You are not allowed to 'diff'


PostgreSQL¼³¸í°ú ¿¹Á¦

PostgreSQL¼³¸í°ú ¿¹Á¦

ÁÖÈ¿½Ä nogadax@chollian.net

2000/03/23
ÀÌ ¹®¼­´Â Æ÷½ºÆ®±×·¹½ºÀÇ Index,function,trigger,grant,revoke,Large Object¸¦ ¿¹Á¦¿Í ÇÔ²² ¼³¸íÇÑ ±ÛÀÌ´Ù.

1. INDEX, SEQUENCE, FUNCTION(1)

1.1 Create Index

INDEX ´Â µ¥ÀÌŸº£À̽º³»ÀÇ relation(Å×À̺í)¿¡ ´ëÇÑ °Ë»öÀÇ ¼º´ÉÀ» ³ô¿©ÁØ´Ù.


  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¸¦ Áö¿øÇÑ´Ù. 

example

   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;

1.2 Create SEQUENCE

SEQUENCE ´Â ¼øÂ÷ÀûÀÎ ¼ýÀÚ ¹ß»ý±âÀÌ´Ù.

 
  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     : ÃÖ°í°ªÀ¸·Î Áõ°¡µÇ¸é ´Ù½Ã ÃÖ¼Ò°ªÀ¸·Î ¼øȯÇÏ°Ô ÇÑ´Ù. 

example

    
    CREATE  SEQUENCE  seq_name1 START 101;
    SELECT  NEXTVAL('seq_name1);


    °á°ú 
    
    nextval
    -------
        114

1.3 Create FUNCTION

FUNCTION Àº »õ·Î¿î ÇÔ¼ö¸¦ Á¤ÀÇÇÑ´Ù.


 CREATE  FUNCTION  func_name([type[,...]])
    RETURNS  return_type [with (attribute [,...])]
    AS ' definition '
    LANGUAGE 'language_name';


 LANGUAGE : sql, pgsql, c µîÀÌ ÀÖ´Ù.
 

example

   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 °ü·Ã ³»ÀåÇÔ¼öÀÌ´Ù. 

1.4 ¿¹Á¦

´ÙÀ½ ¿¹Á¦ÀÇ À̸§Àº test.sql ÀÔ´Ï´Ù. ´ÙÀ½ ¿¹Á¦¸¦ È­ÀÏ·Î ¸¸µé¾î ´ÙÀ½Ã³·³ ½ÇÇàÇÏ½Ã¸é µË´Ï´Ù.

nogadax=> \i /usr/local/src/test.sql
   -------------------------------------------------------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 !!
   

2. GRANT and REVOKE

2.1 GRANT

GRANT´Â user,group ȤÀº ¸ðµç userµé¿¡°Ô ÇØ´ç °´Ã¼¿¡ ´ëÇÑ »ç¿ë±ÇÇÑÀ» ½ÂÀÎÇÑ´Ù. REVOKE´Â user,group ȤÀº ¸ðµç user·ÎºÎÅÍ °´Ã¼¿¡ ´ëÇÑ »ç¿ë±ÇÇÑÀ» ¹«È¿È­ÇÑ´Ù.


GRANT privilege [,...] ON object [,...]
    TO { PUBLIC | GROUP group | username}
privilege
SELECT : ƯÁ¤ TABLE/VIEW ÀÇ column¿¡ ´ëÇÑ access À» ½ÂÀÎ
INSERT : ƯÁ¤ TABLEÀÇ ¸ðµç column ¿¡ µ¥ÀÌŸÀÇ »ðÀÔ¿¡ ´ëÇÑ ±ÇÇÑ ½ÂÀÎ
UPDTAE : ƯÁ¤ TABLEÀÇ ¸ðµç column ÀÇ °»½Å¿¡ ´ëÇÑ ±ÇÇÑ ½ÂÀÎ
DELETE : ƯÁ¤ TABLE ÀÇ row ÀÇ »èÁ¦¿¡ ´ëÇÑ ±ÇÇÑ ½ÂÀÎ
RULE : ƯÁ¤ TABLE/VIEW¿¡ ´ëÇÑ rule À» Á¤ÀÇÇÏ´Â ±ÇÇÑ¿¡ ´ëÇÑ ½ÂÀÎ
ALL : ¸ðµç ±ÇÇÑÀ» ½ÂÀÎÇÑ´Ù.
object
access ¸¦ ½ÂÀÎÇÏ´Â °´Ã¼ÀÇ À̸§À¸·Î¼­ ´ÙÀ½°ú °°Àº °´Ã¼µéÀÌ ÀÖ´Ù.

Table
Sequence
View
Index
PUBLIC
¸ðµç À¯Àú¸¦ ½ÂÀÎ
GROUP group
»ç¿ë ±ÇÇÑÀ» ȹµæÇÒ groupÀ» ÁöÁ¤, group À» ¸í½ÃÀûÀ¸·Î »ý¼ºµÇ¾îÁ® ÀÖ¾î¾ß ÇÔ.
username
»ç¿ë±ÇÇÑÀ» ȹµæÇÒ »ç¿ëÀÚ¸í. PUBLIC Àº ¸ðµç À¯Àú¿¡ ´ëÇؼ­ Àû¿ëµÈ´Ù.
Description
GRANT ´Â °´Ã¼ (object) ¸¦ »ý¼ºÇÑ À¯Àú°¡ ¸ðµç À¯Àú, ȤÀº °³ÀÎ À¯Àú, ȤÀº ±×·ì¿¡ ´ëÇØ
ÇØ´ç °´Ã¼ÀÇ »ç¿ë ±ÇÇÑÀ» Çã¿ëÇϵµ·Ï ÇÑ´Ù. °´Ã¼¸¦ »ý¼ºÇÑ À¯Àú°¡ ¾Æ´Ñ ´Ù¸¥ À¯ÀúµéÀº ±×
°´Ã¼¿¡ ´ëÇÑ »ç¿ë±ÇÇÑÀÌ ¾ø¾î¼­ »ç¿ëÇÒ ¼ö°¡ ¾ø´Ù. ´ÜÁö ±× ÇØ´ç °´Ã¼¸¦ »ý¼ºÇÑ À¯Àú¸¸ÀÌ
À̸¦ Çã¿ëÇÒ ¼ö °¡ Àִµ¥ ÀÌ´Â GRANT ¸¦ »ç¿ëÇÔÀ¸·Î¼­ ´Ù¸¥ À¯ÀúµéÀÌ »ç¿ëÇÒ ¼ö ÀÖµµ·Ï
Çã¿ëÇÑ´Ù. ¾î¶² °´Ã¼¸¦ »ý¼ºÇÑ À¯Àú´Â ÀÚµ¿ÀûÀ¸·Î ¸ðµç ±ÇÇÑÀ» °¡Áö¸ç ÀÌ ±ÇÇѵéÀº SELECT
INSERT, UPDATE, DELETE, RULE µîÀÌ¸ç ¶ÇÇÑ ±× °´Ã¼ ÀÚü¸¦ »èÁ¦ÇÒ ¼ö ÀÖ´Ù.
Notes
psql ¿¡¼­ "\z" ¸¦ »ç¿ëÇÏ¿© Á¸ÀçÇÏ´Â °´Ã¼¿¡ ´ëÇÑ permission µîÀ» ÂüÁ¶ÇÒ ¼ö ÀÖ´Ù.
permission Á¤º¸ÀÇ Çü½Ä
username=arwR : À¯Àú¿¡°Ô ½ÂÀÎµÈ »ç¿ë±ÇÇÑ
group gname=arwR : GROUP ¿¡°Ô ½ÂÀÎµÈ »ç¿ë±ÇÇÑ 
=arwR : ¸ðµç À¯Àú¿¡°Ô ½ÂÀÎµÈ »ç¿ë±ÇÇÑ

a : INSERT privilege
r : SELECT privilege
w : UPDATE/DELETE privilege
R : RULE privilege
arwR : ALL privilege
USAGE(»ç¿ë¿¹)
GRANT INSERT ON imsi_table TO PUBLIC

GRANT ALL ON imsi_table TO nogadax

2.2 REVOKE

À¯Àú,±×·ì, ȤÀº ¸ðµç À¯Àú·ÎºÎÅÍ access privilege ¸¦ Ãë¼Ò


   REVOKE privilege [,...]
    ON object [,...]
    FROM { PUBLIC | GROUP gname | username }
privilege
SELECT ,INSERT ,UPDATE, DELETE, RULE, ALL
object
Àû¿ëµÉ ¼ö ÀÖ´Â °´Ã¼ : table, view, sequence, index
group
privilege ¸¦ Ãë¼ÒÇÒ ±×·ì¸í
username
PUBLIC
Description
REVOKE ´Â °´Ã¼ÀÇ »ý¼ºÀÚ°¡ ¸ðµç À¯Àú, À¯Àú, ±×·ìµé·ÎºÎÅÍ Àü¿¡ ½ÂÀÎÇß´ø Æ۹̼ÇÀ»
ÇØÁ¦ÇÑ´Ù.
USAGE(»ç¿ë¿¹)
REVOKE INSERT ON imsi_table FROM PUBLIC

REVOKE ALL ON imsi_table FROM nogadax

3. TRIGGER

3.1 TRIGGER 1


     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) ·Î 
     º¸°í ÀÌ »ç°Ç¿¡ Á¤ÀÇµÈ ¾î¶² ÇൿÀ¸·Î ¹ÝÀÀÇÏ´Â °ÍÀÌ´Ù.¡
example
     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 ÀÌ´Ù.
plpgsql ¸Àº¸±â
     CREATE  FUNCTION  func_name() RETURNS type
     AS '
        [DECLARE  declarations]
        BEGIN
           statements
        END;
     'LANGUAGE 'plpgsql';
example
     CREATE  FUNCTION  pgsql_test() RETURNS  datetime
     AS '
       DECLARE  curtime  datetime;
       BEGIN 
         curtime:= ''now'';
         return  curtime;
       END;
     ' LANGUAGE 'plpgsql';

3.2 TRIGGER ½ÇÇàÀ» À§ÇÑ PL/pgSQLµî·Ï ¹æ¹ý

´ÙÀ½Àº ½ÇÇà ¿¹Á¦ÀÌ´Ù. ¿ì¼± ´ÙÀ½ ¿¹Á¦¸¦ ÀûÀýÈ÷ º¹»ç¸¦ Çؼ­ ÆÄÀÏ·Î ¸¸µç´Ù.(¿¹ : trg.sql) ´ÙÀ½ ¿¹Á¦¸¦ ½ÇÇà¿¡ ¾Õ¼­ ¸ÕÀú ÇؾßÇÒ ÀÏÀÌ Àִµ¥ ±×°ÍÀº Procedural Languages ¸¦ µî·ÏÇÏ´Â °ÍÀÌ´Ù. ´ÙÀ½¿¹Á¦¿¡´Â PL/pgSQL À» ÀÌ¿ëÇÑ ÇÔ¼ö¸¦ »ç¿ëÇϹǷΠÀÌ°ÍÀ» µî·ÏÇÏ´Â °ÍÀº ÇʼöÀÌ´Ù.

¹æ¹ýÀº µÎ°¡Áö°¡ ÀÖ´Ù.

1. template1 µ¥ÀÌŸ º£À̽º¿¡ µî·ÏÇÏ´Â °ÍÀÌ´Ù. ÀÌ µ¥ÀÌŸº£À̽º¿¡ µî·ÏÀÌ µÈÈÄ template1 µ¥ÀÌŸº£À̽º¿¡¼­ create database ¸í·ÉÀ¸·Î µ¥ÀÌŸº£À̽º¸¦ »ý¼ºÇϸé ÀÚµ¿ÀûÀ¸·Î »ý¼ºµÈ µ¥ÀÌŸº£À̽º¿¡ PL/pgSQLÀÌ µî·ÏÀÌ µÇ¹Ç·Î Æí¸®ÇÏ´Ù.

µî·Ï¿¡ ¾Õ¼­ ´ÙÀ½À» È®ÀÎÇ϶ó.

postgreSQLÀÇ PATH : ¿©±â¼­ÀÇ PATH ´Â /usr/local/pgsql ÀÌ´Ù. ¶ÇÇÑ pgsql µð·ºÅ丮 ¹ØÀÇ lib µð·ºÅ丮¿¡¼­ plpgsql.so ¸¦ È®ÀÎÇ϶ó. ¾Æ¸¶µµ ÀÌ È­ÀÏÀº ´Ù Á¸ÀçÇÒ °ÍÀÌ´Ù.


µî·Ï °úÁ¤

[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]#


ÀÌÁ¦´Â À§ÀÇ µÎ°¡Áö ¹æ¹ýÁß Çϳª¸¦ ¼±ÅÃÇÏ¿© µî·ÏÀ» ÇÏ¸é µÈ´Ù.

3.3 TRIGGER ¿¹Á¦ 1

´ÙÀ½Àº ¾Æ·¡ ¿¹Á¦¸¦ ½ÇÇàÇÏ´Â ¹æ¹ýÀÌ´Ù.


[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 !!

3.4 TRIGGER 2

CREATE TRIGGER
  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 ¶ó´Â
   ¸Þ½ÃÁö°¡ Ãâ·ÂµÈ´Ù.
DESCRIPTION
 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Àº ÀÌ·± Æ®¸®°Å¿¡ ´ë
 ÇÑ Æ©ÇÃÀÇ µ¿±âÈ­°¡ Áö¿øµÇÁö ¾ÊÀ¸¹Ç·Î ÁÖÀǸ¦ ÇÏ¿©¾ß ÇÒ °ÍÀÌ´Ù.
NOTES
   
 CREATE TRIGGER Àº PostgreSQLÀÇ È®ÀåµÈ ±â´ÉÀÌ´Ù.
 ´ÜÁö ¸±·¹À̼Ç(Table) ÀÇ ¼ÒÀ¯ÀÚ¸¸ÀÌ ±× ¸±·¹À̼ǿ¡ Æ®¸®°Å¸¦ »ý¼ºÇÒ ¼ö ÀÖ´Ù.
 ¹öÁ¯ 6.4¿¡¼­ STATEMENT ´Â ±¸ÇöµÇÁö ¾Ê¾Ò´Ù.

3.5 TRIGGER ¿¹Á¦ 2

CASCADING TRIGGER SAMPLE


----------------------------------------------------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 !!

4. PL/pgSQL

4.1 PL/pgSQL 1 (PL/pgSQL󸮱⠵î·Ï)

1. Procedure Language

Postgres 6.3 ¹öÁ¯ ºÎÅÍ PostgreSQLÀº procedural Language(PL)¸¦ Áö¿øÇϱ⠽ÃÀÛÇß´Ù. ÀÌ°ÍÀº PostgreSQL¸¸ÀÇ Æ¯º°ÇÑ °æ¿ì·Î¼­ oracle ÀÇ PL/SQL°ú ºñÀ¯µÉ ¼ö ÀÖ´Ù. ÇÏÁö¸¸ Ư¼öÇÑ ¾ð¾îÀÎ PostgreSQLÀÇ PLÀº PostgreSQL¿¡ ³»ÀåµÈ°Í ¾Æ´Ï°í ¸ðµâÈ­µÈ Handler¸¦ ´Ù½Ã µ¥ÀÌŸº£À̽º¿¡ µî·ÏÀ» ÇØÁÖ¾î¾ß ÇÑ´Ù. ±×·¸Áö ¾ÊÀ¸¸é µ¥ÀÌŸº£À̽º´Â PL ·Î ¾² ¿©Áø functionÀÇ ³»¿ëÀ» ÀÌÇØÇÒ ¼ö ¾øÀ» °ÍÀÌ´Ù. °á·ÐÀûÀ¸·Î 󸮱â´Â °øÀ¯°´Ã¼·Î¼­ ÄÄÆÄÀÏµÇ¸ç µ¿ÀûÀ¸·Î Load µÇ´Â Ưº°ÇÑ ¾ð¾î ó¸® ±â´ÉÀÌ´Ù.

¿©±â¼­´Â PL HandlerÀÇ µî·ÏÀÇ ¿¹·Î¼­ PLÀÇ ÇÑÁ¾·ùÀÎ PL/pgSQL ¾ð¾î¸¦ µî·ÏÇÏ°Ú´Ù.

Installing Procedural Languages

°øÀ¯°´Ã¼ÀΠ󸮱â´Â ÄÄÆÄÀÏµÈ ÈÄ ÀνºÅçµÇ¾î¾ß Çϴµ¥ µðÆúÆ®·Î 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 ¿¡¼­ µî·ÏÀÌ µÇ¾ú´Ù¸é Â÷ÈÄ »ý¼ºµÇ´Â µ¥ÀÌŸº£À̽º¿¡´Â ÀÚµ¿ÀûÀ¸·Î 󸮱Ⱑ µî·ÏµÈ´Ù.

PL/pgSQL 󸮱⠵î·Ï ¿¹Á¦
 
 [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) ¸¦ »ç¿ëÇÒ ¼ö ÀÖµµ·Ï ÇØÁØ´Ù.

2. PL/pgSQL

PL/pgSQL Àº PostgreSQL µ¥ÀÌŸº£À̽º ½Ã½ºÅÛ¿¡¼­ "Loadable Procedural Language"ÀÌ´Ù. ÀÌ ÆÐÅ°Áö´Â Jan Wieck ¿¡ÀÇÇØ ÀÛ¼ºµÇ¾ú´Ù.

OVERVIEW
   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´Â ÁßøµÈ Æ®·£Àè¼ÇÀ» °¡Áú ¼ö ¾ø´Ù.

   --       : ÇÑ ¶óÀÎÀÇ ÁÖ¼®Ã³¸®
   /*   */  : ºí·° ´ÜÀ§ ÁÖ¼® ó¸®
example
   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 À» ¸®ÅÏÇÏ°Ô µÈ´Ù.

4.2 PL/pgSQL 2

example 1

´ÙÀ½Àº ¿¹Á¦ÀÔ´Ï´Ù. Àû´çÈ÷ È­ÀÏ·Î º¹»çÇؼ­ ½ÇÇàÀ» ÇÏ¸é µË´Ï´Ù.

  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 ÀÇ ÀÚ·áÇüÀ» ÂüÁ¶ÇÑ´Ù.

Trigger Procedure

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 ÇüÀÌ°í Æ®¸®°Å ÇÁ·Î½ÃÁ®¿¡ ÁÖ¾îÁö´Â ÀÎÀÚµéÀ» 
              °ªÀ¸·Î °¡Áö´Â ÅؽºÆ® ¹è¿­ÇüÀÇ º¯¼öÀÌ´Ù.

4.3 ¿¹Á¦

´ÙÀ½À» È­ÀÏ·Î ¸¸µé¾î ½ÇÇàÇغ¸¼¼¿ä.

-------------------------------------------------------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ÀÇ °á°ú·Î¼­ Ãß°¡µÇ°Å³ª º¯°æµÈ ÇàÀ» °¡Áø´Ù.

Á¤¸®
Declaration : ¼±¾ðºÎ
name [CONSTANT] type [NOT NULL] [DEFAULT | :=value]
      example )
         DECLARE 
             var_a  int4  DEFAULT  5;

    --var_a  º¯¼öÀÇ  DEFAULTÀÇ °ªÀº  5ÀÌ´Ù.
             
name class%ROWTYPE
      example ) 
         DECLARE 
              var_a  test_table%ROWTYPE;
              
   var_a º¯¼ö´Â test_table ÀÇ Å×ÀÌºí ±¸Á¶¸¦ °¡Áø´Ù.
name RECORD
      example ) 
         DECLARE
            var_a  RECORD ;
            
    ƯÁ¤ Å×À̺íÀÇ ±¸Á¶¸¦ °¡ÁöÁö ¾Ê°í selection ÀÇ °á°ú¿¡ 
    ´ëÇÑ ±¸Á¶¸¦ °¡Áú ¼ö ÀÖ´Ù.(NEW,OLD)§  
name ALIAS FOR $n;
             $n ¿¡ ´ëÇÑ º°Äª
         
RENAME oldname TO newname
            oldname¸¦  newname·Î ¹Ù²Þ
Data Type : ÀÚ·áÇü
      
Postgres-BaseType : Æ÷½ºÆ® ±×·¹½ºÀÇ ±âº» ÀÚ·áÇü( int4,integer,text,char,..)

variable%TYPE     

class.field%TYPE
Expression
      
      Select expression
Statement : 󸮹®Àå
Assignment : °ªÀÇ ÇÒ´ç
      
        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];

5. Large Object with Transaction

5.1 Large Object¿Í ¿¹Á¦

Æ÷½ºÆ®±×·¹½º¿¡¼­´Â ÇÑ Æ©ÇÃÀÇ »çÀÌÁî°¡ 8192 Byte (8k Bytes) ·Î Á¦ÇѵǾî ÀÖ´Ù. ÇϳªÀÇ ·¹Äڵ忡 µé¾î°¥ ¼ö ÀÖ´Â µ¥ÀÌŸÀÇ ÃÑ Å©±â°¡ Á¦ÇѵǾî ÀÖÀ¸¹Ç·Î À̹ÌÁö³ª »çÀÌÁî°¡ 8K ¸¦ ³Ñ´Â ¹®¼­µéÀº ´Ù¸£°Ô ÀúÀåµÇ¾î¾ß ÇÑ´Ù.

Æ÷½ºÆ®±×·¹½º´Â Large Object ¶ó´Â °³³äÀ¸·Î À̸¦ ±Øº¹ÇÏ·ÁÇÑ´Ù. °ú°Å¿¡ Æ÷½ºÆ®±× ·¹½º´Â ÀÌ·± Å« »çÀÌÁîÀÇ µ¥ÀÌŸ¸¦ À§ÇØ 3°¡ÁöÀÇ Áö¿øÀÌ ÀÖ¾úÀ¸³ª »ç¿ëÀÚµé»çÀÌÀÇ ÀæÀº È¥¶õÀ¸·Î Çϳª¸¸À» Áö¿øÇÏ°Ô µÇ¾ú°í ±×°ÍÀº ´Ü¼øÈ÷ µ¥ÀÌŸº£À̽º¾ÈÀÇ µ¥ÀÌŸ·Î ¼­ÀÇ Large Object ¸¦ Áö¿øÇÑ´Ù. ÀÌ°ÍÀº ¾×¼¼½º¸¦ ÇÒ¶§ ´À¸±¼ö ÀÖÁö¸¸ ¾ö°ÝÇÑ µ¥ÀÌŸ ¹«°á¼ºÀ» Á¦°øÇÑ´Ù.

Æ÷½ºÆ®±×·¹½º´Â Large Object ¸¦ ÂÉ°³¾î À̸¦ µ¥ÀÌŸº£À̽ºÀÇ Æ©Çõ鿡 ÀúÀåÇÑ´Ù. B-tree À妽º´Â ·£´ýÇÑ resd-write ¾×¼¼½º¿¡ ´ëÇÑ ºü¸¥ °Ë»öÀ» º¸ÁõÇÑ´Ù.


´ÙÀ½Àº ¿¹Á¦ÀÌ´Ù.

--------------------------------------------------------------------
   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;
-----------------------------------------------------------------------
Large Object Note

À§ÀÇ ¿¹Á¦¿¡¼­ ¸í½ÃÀûÀ¸·Î Æ®·£Àè¼Ç³»¿¡¼­ Large Object ÀÇ Ã³¸®°¡ ÀÌ·ç¾îÁö°í ÀÖ´Ù. ÀÌ´Â Æ÷½ºÆ®±×·¹½º 6.5 ¹öÁ¯´ë¿¡¼­ºÎÅÍÀÇ Large Object 󸮿¡ ´ëÇÑ ¿ä±¸»çÇ×À¸·Î¼­ 6.5 ÀÌÀü ¹öÁ¯ÀÇ ¾Ï½ÃÀûÀÎ Æ®·£Àè¼Ç ¿ä±¸»çÇ×°ú´Â ´Þ¸® ¸í½ÃÀûÀÎ Æ®·£Àè¼ÇÀ» ¿ä±¸ÇÑ ´Ù. ÀÌ ¿ä±¸»çÇ×ÀÌ ¹«½ÃµÈ´Ù¸é, Áï ¸í½ÃÀûÀÎ Æ®·£Àè¼Ç¹®ÀÌ ÀÛ¼ºµÇÁö ¾Ê´Â´Ù¸é ºñÁ¤ ¼ºÀûÀÎ °á°ú¸¦ ¸¸µç´Ù.

¼³¸í
  BEGIN  WORK;
    »ç¿ëÀÚ Á¤ÀÇ Æ®·£Àè¼Ç ½ÃÀÛ 

  SET  TRANSACTION  ISOLATION  LEVEL  SERIALIZABLE;
    Æ®·£Àè¼Ç ·¹º§ Áß °¡Àå °­·ÂÇÑ SERIZABLE ·¹º§·Î Àç ¼³Á¤À» ÇÔ. 

Æ÷½ºÆ®±×·¹½ºÀÇ Æ®·£Àè¼ÇÀÇ µðÆúÆ® ·¹º§Àº "READ COMMITTED" ·Î¼­ Æ®·£Àè¼Ç ³»ÀÇ 
query ´Â ÀÌ query °¡ ½ÇÇàµÇ±âÀü¿¡ commit µÈ µ¥ÀÌŸ¸¸ ´Ù·ê ¼ö ÀÖ´Ù.

SERIALIZABLE ´Â Æ÷½ºÆ®±×·¹½ºÀÇ °¡Àå °­·ÂÇÑ Æ®·£Àè¼Ç ·¹º§·Î¼­ Æ®·£Àè¼Ç³»ÀÇ 
query´Â query½ÃÀÛÀüÀÌ ¾Æ´Ñ ±× Æ®·£Àè¼ÇÀÌ ½ÃÀ۵DZâÀü¿¡ commitµÈ µ¥ÀÌŸ¸¸À» 
´Ù·ê¼ö ÀÖ´Ù.

OID ´Â °´Ã¼¿¡´ëÇÑ Æ÷½ºÆ®±×·¹½ºÀÇ ½Ã½ºÅÛ °ü·Ã ½Äº°ÀÚÀÌ´Ù. 

lo_import(Àоî¿Ã µ¥ÀÌŸÀÇ PATH); ´Â µ¥ÀÌŸ¸¦ ÀоîµéÀÌ´Â Large Object °ü·Ã 
³»Àå ÇÔ¼öÀÌ´Ù.

lo_export( OID , µ¥ÀÌŸ°¡ ¾²¿©Áú ½Ã½ºÅÛÀÇ PATH); ´Â µ¥ÀÌŸ¸¦ Àо ²¨³»´Â 
Large Object °ü·Ã ³»ÀåÇÔ¼öÀÌ´Ù.

COMMIT WORK; ´Â Æ®·£Àè¼ÇÀÇ ¿Ï·á¸¦ ÀǹÌÇÑ´Ù. ÀÌ·Î ÀÎÇØ ½ÇÁúÀûÀÎ °»½ÅÀ̳ª »èÁ¦µîÀÌ 
ÀÌ·ç¾îÁø´Ù.

5.2 TRANSACTION

Æ®·£Àè¼ÇÀÇ ¼º°Ý(ACID)

¿øÀÚ¼º       : ÇϳªÀÇ Æ®·£Àè¼ÇÀº ´Ù¼öÀÇ query¸¦ ½ÇÇàÇÏÁö¸¸ ÀÌ´Â ´ÜÁö ÇϳªÀÇ
(ATOMIC)       query ÀÎ¾ç ½ÇÇàµÇ¾î¾ß ÇÑ´Ù.  

ÀÏ°ü¼º       : Æ®·£Àè¼ÇÀÇ ¼öÇà¿¡ ´ëÇØ µ¥ÀÌŸº£À̽ºÀÇ µ¥ÀÌŸµéÀÇ ÀÏ°ü¼ºÀº 
(CONSISTENT)   À¯ÁöµÇ¾î¾ß ÇÑ´Ù. 

ºÐ¸®         : °¢ Æ®·£Àè¼ÇÀº ºÐ¸®µÇ¾î ´Ù¸¥ Æ®·£Àè¼ÇÁß¿¡ °£¼·Çؼ­´Â ¾ÈµÈ´Ù.
(ISOLATABLE)   ÀÌ´Â º´·Ä (CONCURRENCY) Á¦¾îÀÇ °³³äÀ¸·Î µ¥ÀÌŸº£À̽º´Â ¸ÖƼ
               À¯Àú ȯ°æÀÏ ¼ö ÀÖÀ¸¹Ç·Î °¢ À¯ÀúÀÇ Æ®·£Àè¼ÇÀº ¾ÈÀüÇÏ°Ô ÀÌ·ç
               ¾îÁ®¾ß ÇÑ´Ù. 

¿µ±¸¼º       : Æ®·£Àè¼ÇÀÇ ¼öÇàÈÄ commit µÈ µ¥ÀÌŸµéÀº ¿µ±¸ÀûÀ¸·Î À¯ÁöµÇ¾î¾ß 
(DURABLE)      ÇÑ´Ù.

Æ®·£Àè¼Ç °ü·Ã SQL ¸í·É¾î Á¤¸®

BEGIN [WORK | TRANSACTION]
   BEGIN : »õ·Î¿î Æ®·£Àè¼ÇÀÌ Chain Mode·Î ½ÃÀÛÇßÀ½À» ¾Ë¸°´Ù.
   WORK , TRANCTION : Optional Keyword. They have no effect.
COMMIT [WORK | TRANSACTION]
   Æ®·£Àè¼ÇÈÄ º¯°æµÈ °á°ú¸¦ ÀúÀå.
END [WORK | TRANCTION]
   ÇöÀç Æ®·£Àè¼ÇÀ» COMMIT.
   END´Â Æ÷½ºÆ®±×·¹½º È®ÀåÀ¸·Î¼­ COMMIT ¿Í °°Àº ÀǹÌÀÌ´Ù.
LOCK [TABLE] name
LOCK [TABLE] name IN [ROW | ACCESS] {SHARE | EXCLUSIVE} MODE
LOCK [TABLE] name IN SHARE ROW EXCLUSIVE MODE
  ¸í½ÃÀûÀ¸·Î Æ®·£Àè¼Ç ³»ÀÇ Å×À̺íÀ» Àá±Ý.
ROLLBACK [WORK | TRANSACTION]
   ÇöÀç Æ®·£Àè¼ÇÀ» ÁßÁöÇÑ´Ù.
 
ABORT [WORK | TRANSACTION]
   ÇöÀç Æ®·£Àè¼ÇÀ» ÁßÁöÇÑ´Ù. ABORT ´Â Æ÷½ºÆ®±×·¹½º È®ÀåÀ¸·Î ROLLBACK¿Í
   °°Àº Àǹ̷μ­ ¾²ÀδÙ.
 
SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}
   ÇöÀç Æ®·£Àè¼Ç¿¡ ´ëÇÑ ºÐ¸® ·¹º§À» ¼³Á¤ÇÑ´Ù.
   
¼³¸í
   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 ÀÌ´Ù.

6. Âü°í ¹®¼­¿Í ±âŸ

http://database.sarang.net

http://www.postgresql.org

postgresql µµÅ¥¸ÕÆ®

   programmer's guide
   user's guide

¿À¶óŬ °ü·Ã µµ¼­.

   ÀÇ¿Ü·Î Æ÷½ºÆ®±×·¹½º¿Í ¿À¶óŬÀÌ ºñ½ÁÇÑ ºÎºÐÀÌ ¸¹´õ±º¿ä.
   °³³äÀûÀ¸·Î Àß ÀÌÇØ°¡ µÇÁö ¾ÊÀ» ¶§ ÂüÁ¶¸¦ Çϸ鼭 Æ÷½ºÆ®±×·¹½º¸¦ 
   °øºÎÇϸé ÁÁÀ» °Ì´Ï´Ù.


ID
Password
Join
You are scrupulously honest, frank, and straightforward.


sponsored by andamiro
sponsored by cdnetworks
sponsored by HP

Valid XHTML 1.0! Valid CSS! powered by MoniWiki
last modified 2003-08-10 11:52:30
Processing time 0.0019 sec