  PostgreSQL 

  ȿnogadax@chollian.net
  2000/03/23

    Ʈ׷ Index,function,trigger,grant,revoke,Large
  Object  Բ  ̴.
  ______________________________________________________________________

  

  1. INDEX, SEQUENCE, FUNCTION(1)
     1.1 Create Index
        1.1.1 example
     1.2 Create  SEQUENCE
        1.2.1 example
     1.3 Create FUNCTION
        1.3.1 example
     1.4 

  2. GRANT and REVOKE
     2.1 GRANT
     2.2 REVOKE

  3. TRIGGER
     3.1 TRIGGER 1
     3.2 TRIGGER   PL/pgSQL 
     3.3 TRIGGER  1
     3.4 TRIGGER 2
     3.5 TRIGGER  2

  4. PL/pgSQL
     4.1 PL/pgSQL 1 (PL/pgSQLó )
     4.2 PL/pgSQL 2
     4.3 

  5. Large Object with Transaction
     5.1 Large Object 
     5.2 TRANSACTION
        5.2.1 Ʈ (ACID)
        5.2.2 Ʈ  SQL ɾ 

  6.   Ÿ

  ______________________________________________________________________

  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 Ѵ.

  1.1.1.  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     : ְ Ǹ ٽ ּҰ ȯϰ Ѵ.

  1.2.1.  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  ִ.

  1.3.1.  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;

  CREATE FUNCTION tab2_func() RETURNS opaque
  AS '
     BEGIN
        INSERT  INTO  test3 values( new.tab2_id);
        RETURN new;
     END;

  CREATE FUNCTION tab3_func() RETURNS opaque
  AS '
     BEGIN
        INSERT  INTO  test4 values( new.tab3_id);
        RETURN new;
     END;

  --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 ƴ  Ʈ ۵Ǳ commit Ÿ
  ٷ ִ.

  OID  ü Ʈ׷ ý  ĺ̴.

  lo_import(о Ÿ PATH);  Ÿ о̴ Large Object 
   Լ̴.

  lo_export( OID , Ÿ  ý PATH);  Ÿ о 
  Large Object  Լ̴.

  COMMIT WORK;  Ʈ ϷḦ ǹѴ. ̷   ̳ 
  ̷.

  5.2.  TRANSACTION

  5.2.1.  Ʈ (ACID)

       ڼ       : ϳ Ʈ ټ query  ̴  ϳ
       (ATOMIC)       query ξ Ǿ Ѵ.

       ϰ       : Ʈ ࿡  Ÿ̽ Ÿ ϰ
       (CONSISTENT)   Ǿ Ѵ.

       и         :  Ʈ иǾ ٸ Ʈ߿ ؼ ȵȴ.
       (ISOLATABLE)   ̴  (CONCURRENCY)   Ÿ̽ Ƽ
                       ȯ  Ƿ   Ʈ ϰ ̷
                       Ѵ.

              : Ʈ  commit  Ÿ  Ǿ
       (DURABLE)      Ѵ.

  5.2.2.  Ʈ  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

  Ŭ  .

     ǿܷ Ʈ׷ Ŭ  κ .
       ذ     ϸ鼭 Ʈ׷
     ϸ  ̴ϴ.

