Next Previous Contents

5. 중급편

5.1 primary,foreign key 예제 1 


create sequence test1_seq ;

create table test1 ( 
  t_1_id   int4  default  nextval('test1_seq') ,
  t_1_name text  default  'N/A',
  primary key(t_1_id)
  );

create table test2 (
  t_2_id   int4 ,
  t_2_name text default 'N/A',
  t_2_item text default 'N/A', 
  CONSTRAINT const_test2
  FOREIGN KEY(t_2_id) REFERENCES test1
  ON DELETE CASCADE
  ON UPDATE CASCADE
  );

insert into test1(t_1_name) values('jone');
insert into test2 values(currval('test1_seq'),'jone','A-100');
insert into test1(t_1_name) values('sam');
insert into test2 values(currval('test1_seq'),'sam','B-500');

select * from test1;

    t_1_id | t_1_name 
   --------+----------
         1 | jone
         2 | sam
   (2 rows)


select * from test2;

   t_2_id | t_2_name | t_2_item 
  --------+----------+----------
        1 | jone     | A-100
        2 | sam      | B-500
  (2 rows)


delete from test1 where t_1_id=1;

select * from test1;

   t_1_id | t_1_name 
  --------+----------
        2 | sam
  (1 row)


select * from test2;

   t_2_id | t_2_name | t_2_item 
  --------+----------+----------
        2 | sam      | B-500
  (1 row)


update test1 set t_1_id=10 , t_1_name='abc'  where t_1_id=2;

select * from test1;

   t_1_id | t_1_name 
  --------+----------
       10 | abc
  (1 row)

select * from test2;

   t_2_id | t_2_name | t_2_item 
  --------+----------+----------
       10 | sam      | B-500
  (1 row)

다음의 예제는 위의 Example 1 예제와 거의 비슷합니다. 단지, Primary, Foreign Key에 두개의 column을 사용한 것만 빼고요.. 

----Example 2


create sequence test1_seq ;

create table test1 ( 
  t_1_id   int4  default  nextval('test1_seq') ,
  t_1_name text  default  'N/A',
  primary key(t_1_id, t_1_name)
  );

create table test2 (
  t_2_id   int4 ,
  t_2_name text default 'N/A',
  t_2_item text default 'N/A', 
  CONSTRAINT const_test2
  FOREIGN KEY(t_2_id, t_2_name) REFERENCES test1
  ON DELETE CASCADE
  ON UPDATE CASCADE
  );

insert into test1(t_1_name) values('jane');
insert into test2 values(currval('test1_seq'),'jane','A-100');
insert into test1(t_1_name) values('sam');
insert into test2 values(currval('test1_seq'),'sam','B-500');
insert into test1(t_1_name) values('tom');
insert into test2 values(currval('test1_seq'),'tom','C-150');

select * from test1;

    t_1_id | t_1_name 
   --------+----------
         1 | jane
         2 | sam
         3 | tom
   (3 rows)

select * from test2;

    t_2_id | t_2_name | t_2_item 
   --------+----------+----------
         1 | jane     | A-100
         2 | sam      | B-500
         3 | tom      | C-150
   (3 rows)

delete from test1 where t_1_id=1;

select * from test1;

    t_1_id | t_1_name 
   --------+----------
         2 | sam
         3 | tom
   (2 rows)

select * from test2;

    t_2_id | t_2_name | t_2_item 
   --------+----------+----------
         2 | sam      | B-500
         3 | tom      | C-150
   (2 rows)

update test1 set t_1_id=10 , t_1_name='abc'  where t_1_id=3;

select * from test1;

    t_1_id | t_1_name 
   --------+----------
         2 | sam
        10 | abc
   (2 rows)

select * from test2;

    t_2_id | t_2_name | t_2_item 
   --------+----------+----------
         2 | sam      | B-500
        10 | abc      | C-150
   (2 rows)

5.2 primary,foreign key 예제 2 

--고객과 상품에는 판매라는 관계가 성립한다. 

   고객  TABLE                 상품 TABLE      
  +-----------+              +------------+ Primary Key     
  | 고객_번호 +--+ Primary   | 상품_번호  +---+   
  +-----------+  |           +------------+   |
  | 고객_성별 |  |           | 상품_이름  |   |
  +-----------+  |           +------------+   |
  | 고객_전화 |  |           | 상품_회사  |   |
  +-----------+  |           +------------+   |
  | 고객_주소 |  |           | 상품_가격  |   |
  +-----------+  |           +------------+   |
                 |                            |
                 |                            |
                 |                            |
                 |      판매 TABLE            |
                 |    +----------------+      |
                 |    | 판매_번호      |      |
                 |    +----------------+      |
                 +----+ 판매_고객_번호 |      |
              Foreign +----------------+      |
                      | 판매_상품_번호 +------+ 
                      +----------------+ Foreign Key    
                      | 판매_날짜      |   
                      +----------------+   
                      | 판매_개수      |   
                      +----------------+

다음은 위의 그림에 대한 예제입니다. 

create table 고객 (
 고객_번호   text , 
 고객_이름   text,
 고객_성별   bool,
 고객_전화   text, 
 고객_주소   text,
 primary key(고객_번호)
 );

create table 상품 (
 상품_번호  text, 
 상품_이름  text,
 상품_회사  text,
 상품_가격  int4,
 primary key(상품_번호)
);

create table 판매 (
 판매_번호         text, 
 판매_고객_번호    text,  
 판매_상품_번호    text,
 판매_날짜         date, 
 판매_개수         int4,

 constraint 고객_con 
 foreign key(판매_고객_번호) references 고객(고객_번호)  ,

 constraint 상품_con 
 foreign key(판매_상품_번호)  references 상품(상품_번호)
);


insert into 고객 values (
         '해운대구-001-남-30', 'nogadax', 't', 
         '051-729-7777', '부산시 해운대구 해운대 동백섬 1/1'
);

insert into 고객 values (
         '남구-002-남-27', 'ogler', 't', 
         '051-629-7887', '부산시 남구 용호동 2/1'
);

insert into 고객 values (
         '남구-003-남-32', 'winob', 't', 
         '051-629-2111', '부산시 남구 대연동 8/8'
);


insert into 상품 values 
     ('co-001-e-01' , 'TV'    , 'NGD co.' , 90000);
insert into 상품 values 
     ('co-008-e-02' , 'RADIO' , 'NGD co.' , 20000);
insert into 상품 values 
     ('co-011-e-01' , 'AUDIO' , 'NGD co.' , 70000);


select * from 고객;

    고객_번호      | 고객_이름 |..|             고객_주소             
-------------------+-----------+..+-----------------------------------
해운대구-001-남-30 | nogadax   |..| 부산시 해운대구 해운대 동백섬 1/1
남구-002-남-27     | ogler     |..| 부산시 남구 용호동 2/1
남구-003-남-32     | winob     |..| 부산시 남구 대연동 8/8
(3 rows)


select * from 상품;


  상품_번호  | 상품_이름 | 상품_회사 | 상품_가격 
-------------+-----------+-----------+-----------
 co-001-e-01 | TV        | NGD co.   |     90000
 co-008-e-02 | RADIO     | NGD co.   |     20000
 co-011-e-01 | AUDIO     | NGD co.   |     70000
(3 rows)


--integrity(무결성)에 문제가 없으므로.. 입력이 이루어진다. 

insert into 판매 values (
      '판매_001_S_Branch' , '남구-002-남-27', 
      'co-001-e-01' ,'2001-03-27',2
);


--integrity(무결성) error ..입력이 되지 않는다.  
--"남구-003-남-27"이란 값은  "고객" 테이블의 "고객_번호"에 없음.
--"co-001-e-02"   이란 값은  "상품" 테이블의 "상품_번호"에 없음

insert into 판매 values ('판매_001_S_Branch' , '남구-003-남-27', 
           'co-011-e-01' ,'2001-03-28',1
);

insert into 판매 values ('판매_001_S_Branch' , '남구-003-남-32', 
           'co-001-e-02' ,'2001-03-29',3
);

select * from 판매;

     판매_번호     | 판매_고객_번호 | 판매_상품_번호 | 판매_날짜  | 판매_개수 
-------------------+----------------+----------------+------------+-----------
 판매_001_S_Branch | 남구-002-남-27 | co-001-e-01    | 2001-03-27 |         2
(1 row)

다음은 위 예제의 Full SQL source이다. 

-------------------------------Cut here!!
drop index 고객_pkey;
drop index 상품_pkey;
drop table 고객; 
drop table 상품; 
drop table 판매; 

create table 고객 (
 고객_번호   text , 
 고객_이름   text,
 고객_성별   bool,
 고객_전화   text, 
 고객_주소   text,
 primary key(고객_번호)
 );

create table 상품 (
 상품_번호  text, 
 상품_이름  text,
 상품_회사  text,
 상품_가격  int4,
 primary key(상품_번호)
);

create table 판매 (
 판매_번호         text, 
 판매_고객_번호    text,  
 판매_상품_번호    text,
 판매_날짜         date, 
 판매_개수         int4,

 constraint 고객_con 
 foreign key(판매_고객_번호) references 고객(고객_번호)  ,
 constraint 상품_con 
 foreign key(판매_상품_번호)  references 상품(상품_번호)
);

insert into 고객 values (
         '해운대구-001-남-30', 'nogadax', 't', 
         '051-729-7777', '부산시 해운대구 해운대 동백섬 1/1'
);
insert into 고객 values (
         '남구-002-남-27', 'ogler', 't', 
         '051-629-7887', '부산시 남구 용호동 2/1'
);
insert into 고객 values (
         '남구-003-남-32', 'winob', 't', 
         '051-629-2111', '부산시 남구 대연동 8/8'
);


insert into 상품 values 
     ('co-001-e-01' , 'TV'    , 'NGD co.' , 90000);
insert into 상품 values 
     ('co-008-e-02' , 'RADIO' , 'NGD co.' , 20000);
insert into 상품 values 
     ('co-011-e-01' , 'AUDIO' , 'NGD co.' , 70000);

select * from 고객;
select * from 상품;


--integrity(무결성)에 문제가 없으므로.. 입력이 이루어진다. 

insert into 판매 values (
      '판매_001_S_Branch' , '남구-002-남-27', 
      'co-001-e-01' ,'2001-03-27',2
);


--integrity(무결성) error ..입력이 되지 않는다.  
--"남구-003-남-27"이란 값은  "고객" 테이블의 "고객_번호"에 없음.
--"co-001-e-02"   이란 값은  "상품" 테이블의 "상품_번호"에 없음

insert into 판매 values ('판매_001_S_Branch' , '남구-003-남-27', 
           'co-011-e-01' ,'2001-03-28',1
);
insert into 판매 values ('판매_001_S_Branch' , '남구-003-남-32', 
           'co-001-e-02' ,'2001-03-29',3
);

select * from 판매;
-----------------------------End ..

5.3 serial,sequence

다음은 serial 에제이다. 

--SERIAL (Auto-Sequence type)

drop sequence table_name1_colm_id_seq;
drop index table_name1_colm_id_key;
drop table table_name1;

DROP
DROP
DROP

create table table_name1(
 colm_id serial , 
 colm_name text
);

CREATE

insert into table_name1(colm_name) values('xxx');
insert into table_name1(colm_name) values('yyy');
insert into table_name1(colm_name) values('zzz');

INSERT 22729 1
INSERT 22730 1
INSERT 22731 1

select * from table_name1;

 colm_id | colm_name 
---------+-----------
       1 | xxx
       2 | yyy
       3 | zzz
(3 rows)

다음은 sequence 예제이다. 

drop sequence table_name2_colm_id_seq;
drop index table_name2_colm_id_key;
drop table table_name2;

DROP
DROP
DROP

create sequence table_name2_colm_id_seq;
create table table_name2(
 colm_id int4 default nextval('table_name2_colm_id_seq'), 
 colm_name text
);
create unique index table_name2_colm_id_key on table_name2(colm_id);

CREATE
CREATE
CREATE

insert into table_name2(colm_name) values('aaa');
insert into table_name2(colm_name) values('bbb');
insert into table_name2(colm_name) values('ccc');

INSERT 22763 1
INSERT 22764 1
INSERT 22765 1

select * from table_name2;

 colm_id | colm_name 
---------+-----------
       1 | aaa
       2 | bbb
       3 | ccc
(3 rows)

5.4 Function 


CREATE TABLE FUNCTION_NAME([PARAM1,PARAM2,..]) RETURNS [SETOF] DATA_TYPE
AS ' 
   USER_DEFINITION 
' LANGUAGE 'lang';

위의 []는 생략 가능함을 의미한다. lang에는 sql,plpgsql,c 등이 올수 있다.

다음은 앞부분(기초편1)에서 언급된 function 예이다. 실행 결과는 앞부분을 참조하라. 

 CREATE FUNCTION SQL_FUNC_1(int4) RETURNS text AS '
 SELECT
  CASE
     WHEN NOGADA_TAB.id_ngd = $1
       THEN ''있군요..''::text
       ELSE ''없군요..''::text
  END
 ' LANGUAGE 'sql';

  SELECT SQL_FUNC_1(1) ;

다음은 포스트그레스의 function으로 간단한 예와 그 결과이다. 

    create function func1() returns text
    as ' select \'nogadax\'::text \; ' 
    language 'sql';


    select func1() as name;

      name 
   -------
   nogadax
   (1 row)

returns 에 예약어 setof 를 사용하면 한번에 여러 데이타를 받을 수 있다. 리턴형에는 일반 자료형뿐만아니라 테이블이나 뷰도 포함된다. 

    create table tablex (id_t int4, name text);
       
    insert into tablex values ( 1, 'abc'); 
    insert into tablex values ( 2, 'bcd');        
    insert into tablex values ( 3, 'cde');        
    insert into tablex values ( 4, 'def');        

    create function func2(int4) returns setof tablex
    as ' select * from tablex where id_t > $1 '
    language 'sql';

    select id_t(func2(1)) as id ;
        
      id
     ---
       2
       3
       4
   (3 rows)

    select id_t(func2(2)) as id , name(func2(2)) as name;

    id  |   name
   -----+-------- 
     3  |    cde
     4  |    def
  (2 rows)

5.5 PL/pgSQL

PL/pgSQL은 PGLIB의 plpgsql.so를 필요로 하며 이 파일을 핸들러 혹은 처리기라 한다. PL/pgSQL을 사용하기 위해서는 핸들러를 데이타베이스에 등록하여야 한다. 참고로 트리거에서 PL/pgSQL 이 사용될때는 트리거 프로시져라 한다. 다음은 등록 예이다. 

 $ su - postgres
 $ createlang plpgsql template1
 $

다음은 PL/pgSQL의 예이다. 

create function test(int4)returns int4
as ' 
  begin 
    return $1;
   end;
' language 'plpgsql';

 select test(2) ;

  test
  ----
    2
  (1 rows)

5.6 트리거 프로시져

트리거 프로시져란 트리거에서 사용되는 FUNCTION 을 의미하며 PL/pgSQL이 트리거에서 사용될 경우도 마찬가지로 트리거 프로시져라 한다.

PL/pgSQL 이 트리거 프로시져로 시용될 때 특별히 사용되는 변수들이 있다. 다음은 트리거 프로시져 변수들의 예이다.

기타 자세한 것은 관련 문서를 찾아보기 바란다.

5.7 트리거

트리거는 하나의 ROW에 대해 동작한다(FOR EACH ROW). 즉, 동시에 여러 ROW에 대해 처리가 이루어지지 않는다. 이는 RULE과의 차이점이다. 다음은 트리거 예제이다. 

CREATE TABLE test_table1 (test_id int, test_name text);
CREATE TABLE test_table2 (test_id int, test_name text);

insert into test_table1 values(1,'jini1');
insert into test_table2 values(1,'jini1');
insert into test_table1 values(2,'jini2');
insert into test_table2 values(2,'jini2');

insert into test_table1 values(10,'jini10');
insert into test_table1 values(15,'jini15');
insert into test_table1 values(19,'jini19');
insert into test_table1 values(20,'jini20');

select * from test_table1;

 test_id | test_name 
---------+-----------
       1 | jini1
       2 | jini2
      10 | jini10
      15 | jini15
      19 | jini19
      20 | jini20
(6 rows)

select * from test_table2;


   test_id | test_name 
  ---------+-----------
         1 | jini1
         2 | jini2
  (2 rows)

--RAISE 는 예외 처리자이며 NOTICE, EXCEPTION 등이 올 수 있다. 
--RAISE 뒤의 EXCEPTION 은 예외 발생시 해당 트랜잭션을 중지한다.

CREATE FUNCTION test_func1() RETURNS OPAQUE AS '
 BEGIN
   IF NEW.test_id < 20  THEN 
    RAISE NOTICE ''너무 작아요. % %'', TG_RELNAME ,TG_OP;
   END IF;
   RETURN OLD;
 END; 
' language 'plpgsql';

CREATE TRIGGER test_trg1 AFTER INSERT ON test_table1
FOR EACH ROW EXECUTE PROCEDURE test_func1();


insert into test_table1 values(10,'jini10');

NOTICE: 너무 작아요. test_table1 INSERT
psql:test_trg:76 NOTICE:  너무 작아요. test_table2 INSERT
INSERT 21228 1


insert into test_table1 values(15,'jini15');

NOTICE: 너무 작아요. test_table1 INSERT
psql:test_trg:77 NOTICE:  너무 작아요. test_table2 INSERT
INSERT 21229 1


select * from test_table1;

   test_id | test_name 
  ---------+-----------
         1 | jini1
         2 | jini2
        10 | jini10
        15 | jini15
        19 | jini19
        20 | jini20
        10 | jini10
        15 | jini15
  (8 rows)

5.8 Rule examples

instead 있는 액션은 user command 실행안함. 여기서의 user command 는 rule1 을 기동하는 test1 에 대한 insert .. SQL 문이다. 즉, rule1 의 test1테이블에 대한 insert 명령은 실행되지 않고 do instead 뒤의 update문만 실행됨. instead 가 없다면 do 뒤의 action을 취하고 user command 가 실행됨 

create table test1(tid int4,tname text);
create table test2(tid int4,tname text);

insert into test1 values(1,'test1');
insert into test2 values(2,'test2');

create rule rule1 as on insert to test1
do instead update test1 set tname='t1';

create rule rule2 as on insert to test2
do update test2 set tname='t2';

insert into test1 values(3,'test1');
insert into test2 values(4,'test2');

select * from test1;


 tid | tname 
-----+-------
   1 | t1
(1 row)


select * from test2;

 tid | tname 
-----+-------
   2 | t2
   4 | test2
(2 rows)

create view 가 아닌 create rule를 사용하여 view 를 생성할 경우 해당 테이블에 대한 새로운 빈 테이블을 생성하고 그 테이블을 view로 사용한다.

아래의 예에서 "_RETtest3"은 특별한 rule 이름으로서 rule 로 view 를 생성할 경우의 예약어 구실을 한다. 즉, test3 에 대한 view 를 만들려는 rule의 경우 해당 view명은 "_RETtest3" 으로 정해야 한다. 

--view rule for test3

create table test3(tid int4,tname text);
create rule "_RETtest3" as on select to test3 
do instead select * from test1;

select * from test3;

 tid | tname 
-----+-------
   1 | t1
(1 row)

rule이 제대로 등록되었는지 확인코자 시스템 카탈로그인 pg_rewrite를 검색한다. 

select rulename from pg_rewrite;

    rulename    
----------------
 _RETpg_user
 _RETpg_rules
 _RETpg_views
 _RETpg_tables
 _RETpg_indexes
 rule1
 rule2
 rule3
 _RETtest3
(9 rows)

5.9 시스템 카탈로그 활용 예

다음은 시스템 카탈로그의 활용 예이다. 

--Select database_name

 SELECT pg_database.datname , pg_user.usename      
  FROM   pg_database, pg_user      
  WHERE  pg_database.datdba = pg_user.usesysid ;


  datname  | usename  
-----------+----------
 postgres  | postgres
 betty     | postgres
 nogada    | postgres
 jini      | postgres
 winob     | postgres
 template1 | postgres
 registdb  | webdb
 test2     | speedall
 test1     | lockmind
(9 rows)


--select system_relation

SELECT c.relname as Name, 'table'::text as Type, u.usename as Owner
  FROM pg_class c, pg_user u
  WHERE c.relowner = u.usesysid AND c.relkind = 'r'
    AND not exists (select 1 from pg_views where viewname = c.relname)
    AND c.relname ~ '^pg_';

      name      | type  |  owner   
----------------+-------+----------
 pg_type        | table | postgres
 pg_attribute   | table | postgres
 pg_proc        | table | postgres
 pg_class       | table | postgres
 pg_group       | table | postgres
 pg_database    | table | postgres
 pg_attrdef     | table | postgres
 pg_relcheck    | table | postgres
 pg_trigger     | table | postgres
 pg_inherits    | table | postgres
 pg_index       | table | postgres
 pg_statistic   | table | postgres
 pg_operator    | table | postgres
 pg_opclass     | table | postgres
 pg_am          | table | postgres
 pg_amop        | table | postgres
 pg_amproc      | table | postgres
 pg_language    | table | postgres
 pg_aggregate   | table | postgres
 pg_ipl         | table | postgres
 pg_inheritproc | table | postgres
 pg_rewrite     | table | postgres
 pg_listener    | table | postgres
 pg_description | table | postgres
 pg_shadow      | table | postgres
(25 rows)


--select normal_relation

SELECT c.relname as Name, 'table'::text as Type, u.usename as Owner
   FROM pg_class c, pg_user u
   WHERE c.relowner = u.usesysid AND c.relkind = 'r'
     AND not exists (select 1 from pg_views where viewname = c.relname)
     AND c.relname !~ '^pg_';

    name    | type  | owner 
------------+-------+-------
 regist_tab | table | webdb
(1 row)


-- * Get general table info 

SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
  FROM pg_class WHERE relname='regist_tab';

 relhasindex | relkind | relchecks | reltriggers | relhasrules 
-------------+---------+-----------+-------------+-------------
 f           | r       |         0 |           0 | f
(1 row)


-- * Get column info 

SELECT a.attname, t.typname,a.atttypmod-4 as Type_Length,t.typalign, 
          t.typlen ,a.attstorage,a.attnotnull, a.atthasdef, a.attnum
         
   FROM pg_class c, pg_attribute a, pg_type t
   WHERE c.relname = 'regist_tab'
   AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
   ORDER BY a.attnum;

 attname | typname | type_length |..| atthasdef | attnum 
---------+---------+-------------+..+-----------+--------
 a       | varchar |           2 |..| f         |      1  
 b       | varchar |           4 |..| f         |      2  
 c       | varchar |           6 |..| f         |      3
(3 rows)


--기타 시스템 카탈로그 SQL 문 

select tablename from pg_tables;
select relname,relhaspkey from pg_class;
select datname from pg_database;�

5.10 pg_shadow,pg_user

다음은 포스트그레스를 설치 후 포스트그레스의 슈퍼유저 postgres 유저에게 패스워드를 할당하는 예이며 패스워드를 할당 후 유저 정보를 살피는 예이다. "alter user .."는 기존의 사용자 정보를 수정한다. 각각의 명령은 슈퍼유저 "postgres"로 실행하였다. 

template1=# alter user postgres with password 'abxy912q';
ALTER USER

template1=# select * from pg_user;

 usename  | usesysid | usecreatedb |..| usecatupd |  passwd  | valuntil 
----------+----------+-------------+..+-----------+----------+----------
 postgres |      501 | t           |..| t         | ******** | 
(1 row)

template1=# select * from pg_shadow;

 usename  | usesysid | usecreatedb |..| usecatupd |  passwd  | valuntil 
----------+----------+-------------+..+-----------+----------+----------
 postgres |      501 | t           |..| t         | abxy912q | 
(1 row)

template1=#
Next Previous Contents