
    : []Postgres95 User's Manual(1-10)                        
 ø : 긮(  )    97/08/24 04:46     :  20  ڷ 
 -----------------------------------------------------------------------------


#61      (Embryo  )
[] postgres 1.0 ޴ (1)               08/16 22:08   141 line

  : Postgres95 User Manual

  :  (1 -3 chapter) embryo@nownuri.net
        ѵ (4-11 chapter) ddoch@home.hite.net

۱: GPL ؼϸ   ǿҼ .


                         The POSTGRES95 User Manual 
                         
                       Version 1.0 (September 5, 1995)

1. Ұ

  Ŭ   ĶϾ п ߵ postgres95  ̽ ý
  ̴.  postgres95 postgres release 4.2    ΰ 
.  postgres  Ʈ Michale Stonebraker   ֵ Ǿ, 
  Ŀ ޾Ҵ.

- DRAPA      : Defence Advanced Research Projects Agency
- ARO        : Army Research Office
- NSF        : National Science Foundation ( )
- ELS

Ÿ 

1.1. postgres ΰ?

  ̽  ý۵  ̸ ü    
Ѵ. ̰ Ư  Ӽ ϰ ִ.    ýۿ 
 Ҽ  ġ, , , , ¥   ϴ.
׷ Ϲ ̷    ̷   ó α׷ 
   ִ. 

      𵨵 ٲپ  ִµ, 
  ܼ ̴. ׷ ̷ ܼ δ  α׷ 
ϴµ ־  ۿϱ⵵ Ѵ.  postgres  ڰ ý 
Ȯų ִ  ⺻    ϸ, ̰ Ͽ 
μ   ϰ ȴ.

- classes(Ŭ)
- inheritance(Ӽ)
- types()
- functions(Լ)

ΰ postgres  rule ý Ѵ.

)   ------------------------------------------------------------------------ 
  δ ̰ ٷ ü  ̽  Īϴ  ƴѰ
Ѵ.  , ſ  ʿ ʾҴ ׷  Ȥ  , Ȥ
   ,  ο     ٷµ ־ ü
 ̽ ʿ伺 Ͻ ϸ鼭,   ̽ ʿ ο 
 ø ϰ ִ  ϴ.

1.2 postgres Ʈ   

 postgres DBMS 1986 Ǳ Ͽ.  ýۿ   
[STON86]   Ǿ, [ROWE87]  𵨿   ǰ Ÿ
 ִ. rule ýۿ   [STON87a]   ó Ǿ,  ó
ο  ̷ ٰſ Űó   [STON87b]  ϰ Ǿ
ִ. 

postgres    ֿ ǥ , ù °  α׷ 1987⿡ ó
 ϰ Ǿ, 1988⿡ ACM-SIGMOD ȸǿ   Ǿ. [STON90a] 
 ϰ ִ 1 1989 6    ڵ鿡Ը  
ǥǾ. [STON89]  ù ° rule ý 򰡿 Ͽ [STON90b]
   Ǿ, 1990 6 ο rule ý   2 ǥ
. 1991   3   óο      
ȭ, ׸  ۼ rule  ý ߰ Ǿ. 

postgres  ٸ鼭 ſ       ϴµ 
    캸,   м ý, Ʈ  
 ͸ϴ  Ű, ༺  ϴ  ̽,   
 ̽,    ý   ϰ ִ.

postgres   п   Դ. ħ Illustra Informat-
on Technologies  Ϻ ڵ带 Ͽ װ ȭ Ͽ.

1992⿡  postgres Sequoia 2000 scientific computing project  ֿ 
ó Ǿ. ư 1993⿡     ũⰡ ι迡 
. ̰ ڵ  ϰ װ ϴ Ͽ  ̽ 
   ð ҴǾ  ִٴ  ϰ ش. ̷  
 ̱ ؼ    Ʈ  4.2  Ǿ. 

)  -------------------------------------------------------------------------
[ ]   postgres ϸ鼭 ǥ  ڸ Ѵ.

1.3 postgres 95 ΰ?

postgres  95 postgres   Ĺ  4.2 κ  Ļ ̴. ڵ
   ANSI C  ۼǾ, ڵ ũ⵵  25%  پ, ɰ
ڵ  κп    ȭ ־. postgres95  4.2 
  30% - 50%   ӵ δ. ׼    
     .

- Ǿ postquel    SQL  üǾ. ׷  subq-
  ueries    ʴ´.  (subqueries      Լ 
   ȿ    ִ.) Aggregate(Լ) ٽ Ǿ, group by  
     ߰  Ǿ.   LIBPQ κ  C  ȿϴ.

- ο ͸ α׷ psql α׷ ߰ Ǿ, GNU readline 
  Ѵ.

- Tcl   Ͽ  ̽   ִ ο 󸮺귯 li-
  bpgtcl  ߰Ͽ.   pgtclsh tcl α׷  postgres 
  Ҽ ִ ο Tcl ɾ Ѵ.

- Ը ü    ö 簡 ־. Ը ü  ȯ 
   Ը ü ϴ Ŀ   Ϳ  ξ. ( ̹ ŵ ȭ
   ý ȯ  ȥ ƴԿ  Ұ. )

-  rule ý  Ǿ,   rule ý  ȿ
  ϴ.

- Ϲ SQL   Ұϴ ª  ҽ ڵ  Ͽ
  .

- α׷ ϴµ ־ GNU make (BSD  make )  Ǿ,  
    gcc   Ǿ ִ. (  迭 Ǿ
  .)

1.4    ǿ Ͽ

postgres ̴.    Postgres95  1.0  Ͽ  ϰ ִ.
ڴ   ÷ϰ ׽Ʈ ƴ.

+--------------------------+------------+------------------------------------+
| architecture             |  processor |         Operation System           |
+--------------------------+------------+------------------------------------+
|  DECstation 3000         |  Alpha AX  |  OSF/1 2.1, 3.0, 3.2               |
|  DECstation 5000         |  MIPS      |  ULTRIX 4.4                        |
|  Sun4                    |  SPARC     |  SonOS 4.1.3, 4.1.3_U1,Solaris 2.4 |
|  HP - 9000/700 and 800   |  PA-RISC   |  HP-UX 9.00 9.01 9.03              |
|  Intel                   |  X86       |  Linux 1.2.8 ELF                   |
+--------------------------+------------+------------------------------------+

1.5   .

ݺ Postgres Postgres95 ǹϴ  ϰ̸,   ݺ
 ýۿ  ⺻  Postgres ý  ϴ   
Ѵ. ׸  Postgres  𵨰 SQL   ؼ  캸
   κп ؼ ϵ Ѵ.    
,  Լ ߰ν,  Postgres  Ȯų ִ,  ׸
  α׷ Լ ʿ װ͵  Ѵ.

Postgres rule ý  캻 , ׿  ڼ  ηϿ
 OS ý Ȯ Ͽ ϴ   .  е
̹ н C α׷ֿ ޵  Ѵ.



#70      (Embryo  )
[] postgres 1.0 ޴ (2,3)             08/24 04:24   240 line

2. Postgres95 ۹Ŀ  .

 ϱ  ⺻  Postgres ý ϴ Ŀ Ͽ 
 Ͽ Ѵ. Postgres Ϻΰ  ϴ ϸ    
ҳ Ȯ  ̴.

Postgres  μ   Ŭ̾Ʈ/  Ѵ. Po
stgres  н μ Ǿ ִ.

   -   μ postmaster

   -   α׷( , psql α׷, Ÿ  α׷)

   - Ѱ, Ǵ  ̻   ̽ (postgres μ  ü 
     )

postmaster  ϳ ȣƮ ־  ̽   Ѵ. ̷ 
 ̽   Ȥ site  θ.     α׷ 
̽ Ϸ LIBPQ ̺귯 ľ Ѵ.
 ̺귯  䱸 Ʈũ Ͽ postmaster  ûϰ, po
stmaster ο μ   ϳ   .  ̷ 
  μ   postmaster ̵  Ҽ ִ.
׷Ƿ  䱸 ٸ ؼ postmaster ׻ ̴.

LIBPQ ̺귯 ϳ α׷   μ ϴ  
ϵ ش. ׷   α׷   thread μ̸
Multi-thread  /   LIBPQ  ʴ´. 

 α׷ 𼭳 ɼ , postmaster   ׻ 
  Ǿ Ѵ. ̷  Ӹӿ ϰ ־ ϴµ, 
 Ŭ̾Ʈ ٰ   ̽  Ұ 
 ֱ ̴. ,  postmaster postgres Postgres superuser 
Ȯؾ Ѵ. Postgres superuser Ư ڰ ƴϴ. ( postgres
  ڸ Ѵ.) ư Posgres superuser   root ƴ
.    ̽ 谡 ִ  ȭ Postgres supreuser
  ִ.

) -------------
Ϲ 츮  ̽  εϰ Ҽ  ִ    
DBA(Data Base Adminstrator)̶ θ.  

Ϲ  α׷ ġ root   ϵ Ǿ  DB
 ̷       д. ̰   ̽ 
 ϱ  root  ־ δ ٿ ڴ Ͱ,  DBA ý
Ʈ    ϱ   ؼȴ.    ̽
  ߿ ڷ   ϵǾ Ƿ ý Ʈ̶ ϴ
     ؾ ʿ䰡 ִ.     ̽ 
 Ʈ          ʿ伺 ִ.
Ư  DB Ŭ 찡   Ҽ ִ.


3. Postgres ϱ.

 κп   α׷ ϱ ؼ  Postgres ϴ 
 ڽ ȯ濡 µ ϴ    Ѵ. ̹ Postgres 
 ġǾٰ Ѵ. ( Postgres ġϱ  ؼ ġ note 
.)

 κп õ   ܰ   Postgres ڿ  ̽ 
 ش Ǵ ̴.  ̽ ý ڴ Ʈ ġϰ, 
 ̽ , postmaster Ű ̴.  ̽ ڰ
н   root̰ų,  ǻ ϵ   ʿ .
⼭ Ϲ ڸ User ǥϰ,  ̽ ڸ Admin  ǥ
ϵ ϰڴ.

   % ȣ ۵Ǵ  н  ɾ ǹϸ, * 
ȣ Postgres SQL Ǿ  Ѵ.


3.1 Admin / User :  ȯ 

ܼ Ͽ Postgres /usr/local/postgres95 丮 ġǾ ִٰ 
 ϵ Ѵ. ׷Ƿ /usr/local/postgres95  丮  
  Postgres ġ 丮 ٲپ ؾ Ѵ. (ȥ ϱ 
Ͽ ƿ ó /usr/local/postgres95 丮  ġϴ ͵  
̴.)

 ɾ /usr/local/postgres95/bin  丮  ġǾ ִ. ׷Ƿ
 ɾ ¿  丮 path  ߰ؾ Ѵ.   Berkeley
C Shell  ̳, tcsh  ϰ ִٸ .login Ͽ    
Ѵ.

	% set path = ( /usr/local/postgres95/bin $path )

Bourne shell  ̳ ksh, bash  쿡 .profile    .

	$ PATH=/usr/local/postgres95/bin:$PATH
	$ export PATH

 Postgres  bin 丮 path ߰Ǿٰ ϰڴ. ΰ  
 Ͽ    Ǵ ȯ溯 ̶   ϰ  
̴.  path ϴµ ־       Ҽ ٸ,
 Ѿ       н  
 Ѵ.  

3.2 Admin : Postmaster ϱ.

ǿ ռ postmaster μ Ǿ    ̽  
 ۾ Ұϴٴ   ϰ Ѵ.   Admin ̶ pos
tmaster ϱ     ξ߸ ϴ° ִ.  
'Administering Postgres'   ִ κп  װ ٷ Ѵ. ׷
   Ͱ  Ȯϰ Postgres ġǾ ִٸ,  
 ɾ postmaster  Ҽ ִ.
  
	% postmaster &

postmaster  ߻    Ǵ   ޼   Ѵ. 
 postmaster   ޼   쿡 -d ɼ ְ, װ
redirect  ޾Ƽ ϸ ȴ.

	% postmaster -d >& pm.log &

޼    쿡   Ѵ.

	% postmaster -S

׷ postmaster  ƹ͵ ǥ  ̴.  &   ʾ 
϶.


3.3 Admin :  ߰  .

createuser ɾ  Ư ڰ Postgres  ϵ ش. 
 ݴ destroyuser ɾ  ڸ ϰ, Postgres   
Ѵ. ̷ ɾ  Postgresڿ ؼ ȿ ִٴ Ϳ 
϶. OS  ϴ ڿԴ ƹ ȿ .


3.4 User :  α׷ ϱ.

 ̽  (site adminstrator) ϰ postmaster μ 
 ,  ڷμ  ̽  Ҽ ִ  ο ޾
 α׷   ִ. տ ߴ  ó  path /usr/lo-
cal/postgres95/bin 丮 ߰ؾ Ѵ. κ     
ؾ   ̴.

 Postgres ɾ (psql̳ createdb)  ߿    ޼ 
ٸ, װ   쿡 شѴ.
  
	connectDB() failed : Is the postmaster running at 'localhost' on port
	'4322' ?

postmaster   ʾҰų, ߸ server host   õϷ  
̴.

   ޼ ٸ  ̽ ڰ ùٸ  
postmaster  Ű ʾҴٴ ̴.

	FATAL 1:Feb  17 23:19:55:process userid (2360) != database owner (268)

̷ ٽ postmaster ϵ Postgres superuser  ˷־ Ѵ.


3.5 User : Database ϱ

Postgres ̶      ̽  ִ.  
  ̽ ɾ ҰѴ.

Data Base ۼϱ

,  mydb  ̸  ̽  Ѵ. ׷ٸ   
 ɾ ָ ȴ.

	% createdb mydb

Postgres ־  󸶵    ̽   ֵ  
,   ڵ   ý ڰ ȴ.  ̽
̸ ĺ ڰ ó ; ϰ, ̴ 16ڷ ѵȴ.

 ڰ  ̽  ִ  ο޴  ƴϴ.  
 Postgres  ̽  ٸ  ̽ ڷ  
 ̽ Ҽ ִ 㰡  Ѵ. ̷   ̽ 
ڿ  ϵ .

Data Base ϱ

 ̽     Ҽ ִ.

   - Postgres ͹̳ ͸ α׷ Ѵ.(monitor Ǵ psql).
     ⼭ ȭ Է, , SQL ɾ   ϴ.

   - LIBPQ   ƾ ϴ C α׷ ۼѴ. ̰ ϸ C 
      SQL ɾ Ҽ ,   α׷   Ȳ ޼
        ִ. ̷  ؼ  κп  Ѵ.

     psql Ͽ Ѵ.    ɾ
mydb   ִ ° ȴ.

	% psql mydb

  ȯ ޼(?) ް  ̴.

	Welcome to the Postgres95 interactive sql monitor

		type \? for help on slash commands
		type \q to quit
		type \g to terminate with semicolon to excute query

	You are currently conneted to the database: mydb

	mydb =>

 Ʈ ͹̳θͰ    ޾Ƶ  ִٴ ̴.
 ͹̳ Ͱ ϴ۾  SQL    ִ. psql 
(\) ϴ escapeڵ忡 Ѵ.  , Postgres   
   ؼ ɾ ο   ԷѴ.

	mydb => \h

   ˸, ۾  Postgres ˷ֱ  
  ԷѴ.

	mydb => \g

̰    ϶ ̴.     ݷ 
 ߴٸ \g  ʿ. ݷ  psql  ڵ  óѴ. 

ܺ ȭϷ     о̱ ؼ  ȭ Ĵ  
ԷѴ.

	mydb => \i filename

н  ؼ   Ѵ.

	mydb => \q

׷ psql ǰ, н ɾ   ִ  ° ȴ. (  ڼ
 \h Է .)  Ȥ ĭ SQL  󸶵  ִ. dash
Ŀ   ٵ õǸ, װ  ּ ó ǹѴ.

Data Base ϱ

 mydb    ̽ ڶ   ɾ Ἥ  
ִ.

	% destroydb mydb

̷ ɾ  ̽   н ȭ   
      ȴ.  ׷Ƿ ۾ ռ  ϰ ؾ
 ̴.


#57   ѵ   (ddoch   )
[] postgres 1.0 ޴ (4-1)             08/10 04:23   154 line

----------------------------------------
 Postgres95 1.0  ޴ ѱ 
----------------------------------------

 : ѵ active@soback.kornet.nm.kr
¥ : Sun Aug 10 04:18:20 KST 1997

  Postgres95  1.0  ޴ ѱ۷ ϴ Դϴ.
ƴƴ Ͽ ø Դϴ.

----------

4. Ǿ

POSTGRES  ϴ Ǿ SQL-3  ̴.  POSTGRES  ǿ
Ȯ尡  ý, , Լ, Ģ    ִ. 
̷ Ư¡ POSTGRES Ǿ POSTQUEL  Դ.  忡
 ۵ POSTGRES SQL  Ͽ  . 

 Ŵ SQL  ൿ  ,  SQL  ƴϴ.  SQL 
 å ߿  ִ.  ׸ ⿡   Ư¡ ANSI
ǥ ƴϴ. 

Ʒ  .  ⿡, ٷ 忡 Դ Ͱ  mydb  
ͺ̽  ξ,  psql  Ѵٰ غ.
 ޴   /usr/local/postgres95/src/tutorial  ִ.
   ϴ  ش 丮 ִ README   о
 ȴ.    . 

    % cd /usr/local/postgres95/src/tutorial
    % psql -s mydb
    Welcome to the POSTGRES95 interactive sql monitor:

       type \? for help on slash commands
       type \q to quit
       type \g or terminate with semicolon to execute query
     You are currently connected to the database: jolly

    mydb=> \i basics.sql

'\i'  ϸ Ư ܺϿ Ǿ о ó  ִ. 
࿡ '-s' ɼ ϳ Ŀ backend    ϴ 
̱   ϴ ̴. 


4.1  

POSTGRES  ⺻  Ŭ(class) ̴.  Ŭ üμ ν
 (instances)  ü̴.   νϽ  Ӽ(attributes) 
  ְ,  Ӽ Ư (type)̴.   ư,  νϽ

  ü ĺ(object identifier - OID)  Ǹ,  ĺڴ 

ġ ú ߺ ʴ  ̴.  SQL  ̺(table) ϱ 

, Ŭ(class)   ̺(table)̶    ִ. 
 , (row)  νϽ(instance)̰, ÷(columns) Ӽ(attributes)

̴. 

տ 캻 ٿ , Ŭ ׷ȭǾ ͺ̽ ̷, ͺ
̽  ϳ postmaster μ  ȴ.

4.2  ο Ŭ 

Ŭ ̸ Ͽ ο Ŭ   ִ.  ̶  Ӽ 
 Ѵ. 

   CREATE TABLE weather (
           cityvarchar(80),
           temp_lo         int,               - µ
           temp_hi         int,               - ְµ
           prcp            real,              - 
           date            date
      );

(keyword) Űص  ĺڿ Ͽ Ѵ.  POSTGRES 
SQL   SQL Ÿ int, float, real, smallint, char(N), varchar(N), 
date, time  Ѵ.  ߿ 캸, POSTGRES  ڰ 
  ڽ    ִ.  ,  Ī  ƴ
.

ư, POSTGRES  'create' ɾ   ýۿ ̺ 
 ϴ  ϴ ɾ  ϴ.  ׷, Ŭ   
Ȯϴ  Ư    ˾ƺ ̴. 

4.3  Ŭ νϽ ϱ

Ŭ νϽ ϴ  'insert'  Ѵ.

   INSERT INTO weather
      VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')

'copy'  ϸ,  ƽŰ Ͽ Ը ͸  
ִ. 

4.4  Ŭ ϱ

weather Ŭ   , ȹ Ŀ   ִ.  SQL 'select'
 ̷    ִ.     κ(target list -
ȯ Ӽ Ϻκ)  Ǻκ(  ϴ κ)   
ִ.   ڸ, weather   (row) ˾ƺ ؼ  
ϸ  ̴.

    SELECT * FROM WEATHER;

׷,    ̴. 

city         |temp_lo|temp_hi|prcp|      date
-------------+-------+-------+----+----------
San Francisco|     46|     50|0.25|11-27-1994
San Francisco|     43|     57|   0|11-29-1994
Hayward      |     37|     54|    |11-29-1994
(3 rows)


ƿ﷯,  Ʈ ǥ    ִ.   , 
 Ǹ    ִ.

    SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
   
Ŀ ÿ Ŀκп οﷱ ۷(and, or, not)    ִ. 
 ,  . 

    SELECT *
      FROM weather
      WHERE city = 'San Francisco'
          and prcp > 0.0;


 city         |temp_lo|temp_hi|prcp|      date
 -------------+-------+-------+----+----------
 San Francisco|     46|     50|0.25|11-27-1994
 (1 row)

 , select    ϰų, ߺǴ νϽ
   ִ.

    SELECT *
      FROM weather
      ORDER BY city;


4.5  select  ̷Ʈϱ

select Ŀ ο Ŭ ̷Ʈ ϵ   ִ.

    SELECT * INTO temp from weather;

̷ select  ,  create ɾ Ͽ, ο Ŭ
temp  ϴ ,   Ӽ  'SELECT INTO' ɾ  Ʈ 

õ Ӽ Ī  ȴ.  , ٸ Ŭ󿡼  
Ŭ  ۵   ִ. 


#58   ѵ   (ddoch   )
[] postgres 1.0 ޴ (4-2)             08/10 22:06   95 line

4.6 Ŭ 

ݱ, ϳ Ǵ ѹ ϳ Ŭ ＼ ؿԴ.  Ǵ
ѹ  Ŭ ＼ϰų, Ŭ  νϽ ÿ ٷ
  Ŭ ＼   ִ.  ѹ  Ŭ δٸ
Ŭ  νϽ ϴ Ǹ ' (join query)'  θ.

ϳ , ٸ ڵ µ  ִ  ڵ带 ã   
غ.  , 츮  EMP νϽ temp_lo  temp_hi Ӽ 
ٸ  EMP νϽ temp_lo  temp_hi Ӽ  ʿ䰡 ִ. 

(2 - ̰    ̴.     ȿ 

  ̷  ,    ʴ´. )

̷ ۾  Ŀ   ִ.

    SELECT W1.city, W1.temp_lo, W1.temp_hi,
           W2.city, W2.temp_lo, W2.temp_hi
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
      and W1.temp_hi > W2.temp_hi;


city         |temp_lo|temp_hi|city         |temp_lo|temp_hi
-------------+-------+-------+-------------+-------+-------
San Francisco|     43|     57|San Francisco|     46|     50
Hayward      |     37|     54|San Francisco|     46|     50
(2 rows)


 쿡, W1  W2  Ŭ weather  νϽ  ̸,  

 Ŭ  νϽ Ѵ´.  (ͺ̽ ý  W1 
W2  " (range variables)" Ѵ.) ϳ Ǵ Ŭ ̸  
 Ӱ   ִ.

(3 - ̷  ǹ̴, Ǻκ īƮ product   
Ŭ   ǥ̶ ̴.  Ǻκ  īƮ product 
 ̷ νϽ ؼ, POSTGRES   Ʈ   
Ͽ ȯѴ.  POSTGRES SQL  ̷ ǥ  ϴ   ǹ̵
 
ο ʴ´.   ǹ̴ POSTGRES     Ʈ  
Ѵٴ  ǹѴ.  - ̰  οﷱ ǥ 'or'  Ǿ  Ÿ
.  ̷ 縦 ϱ ؼ, 'select distinct'  Ͽ 
.)


4.7 

'update'  ϸ ϴ νϽ   ִ.  11 28 

ְµ µ 2 ҽѾ  ʿ伺 ִٰ غ.  ̷
  Ƿ ó   ̴.

    UPDATE weather
    SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
    WHERE date > '11/28/1994';


4.8 

 'delete'  Ѵ.

   DELETE FROM weather WHERE city = 'Hayward';

Hayward  õ   Ѵ. 
   ؾ Ѵ.

   DELETE FROM classname;

'delete'  п  ʰ  Ŭ  νϽ 
 ؼ 㸸  ȴ.  ý ̷ ۾  Ȯ 䱸

  ̴. 


4.9 ü Լ 

ٸ Ǿ , POSTGRES  ü Լ Ѵ.  ׷,  
POSTGRES ü Լ ſ ̴.  Ư, Ƚ, հ, , ִ, ּ 

 꿡 õ ü Լ Ѵ.  ̵ Լ νϽ տ 

,   Ʈ Ÿ, Ǻκ(where ) Ÿ ʴ´.
 . 

    SELECT max(temp_lo)
    FROM weather;

ü Լ Բ 'GROUP BY'    ִ. 

    SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;


#59   ѵ   (ddoch   )
[] postgres 1.0 ޴ (5)               08/12 20:40   198 line

5.   POSTGRES SQL  Ư¡


 ⺻  ʹ Ǵ POSTGRES  Ư¡ ؼ   
̴.  ̷ Ư¡ , ð , ȭ   (array- 
set-valued Ӽ)  ִ. 

忡   (tutorial) 丮 'advance.sql'  ִ.
(̰  ϴ    κ .)

5.1 

ΰ Ŭ  .  'capitals' Ŭ ϳ ,  
 Ѵ.  ڿ, 'capitals' Ŭ 'cities' κ ӹ޾ƾ 
. 

   CREATE TABLE cities (
            name        text,
            population  float,
            altitude    int            -- (in ft)
   );

   CREATE TABLE capitals (
            state       char2
   ) INHERITS (cities);


 쿡, 'capitals'  νϽ  θκ  Ӽ ('name', 
'population', 'altitude')  ӹ޴´.  Ӽ 'name'  Ÿ 'text' ̴.
'text'    ƽŰ ڿ Ǵ POSTGRES  Ǿ ִ Ÿ
̴.  Ӽ 'population'  Ÿ 'float4' ̸,  Ÿ  е ε

Ҽ Ǵ POSTGRES ̴.   (state capitals) Ǵٸ 
Ӽ ָ Ÿ 'state'   ִ.  POSTGRES , ϳ Ŭ
 0 ̻ Ŭκ ӹ  ְ,(4)ϳ Ǵ  Ŭ
 
 νϽ ̰Ͱ Բ νϽ ڼձ   ִ.   , 
Ʒ Ǵ  500 Ʈ    ִ ø ˻ϴ ̴. 

4) ü ٷ acyclic ׷̴. 

   SELECT name, altitude
   FROM cities
   WHERE altitude > 500;


   name     |altitude
   ---------+--------
   Las Vegas|    2174
   Mariposa |    1953
   (2 rows)


500 Ʈ ̻ ִ  ø   Ͽ ã Ѵٸ, ش 
Ǵ  .

   SELECT c.name, c.altitude
   FROM cities* c
   WHERE c.altitude > 500;


  .

   name     |altitude
   ---------+--------
   Las Vegas|    2174
   Mariposa |    1953
   Madison  |     845
   (3 rows)

'cities'  '*'  'cities'  'cities'  ӹ  Ŭ Ÿ
.  ̹ տ 캻   - select, update, delete -  'alter' 

  ̷ '*' ǥ⸦ Ѵ. 


5.2  ð 

POSTGRES  ð ࿡  ǥ Ѵ.  ̷ Ư¡ ڷ Ͽ

 Ǹ   ֵ Ѵ.   , Mariposa   α
˻Ϸ,      ̴. 

   SELECT * FROM cities WHERE name = 'Mariposa';


   name    |population|altitude
   --------+----------+--------
   Mariposa|      1320|    1953
   (1 row)

POSTGRES  ڵ   ȿ Mariposa  ڵ带 ã 

̴.
⿡ ð ο  ִ.  Mariposa   α ˾ƺ,

  ϸ ȴ.

   SELECT name, population
   FROM cities['epoch', 'now']
   WHERE name = 'Mariposa';

'epoch'  ý ð ۽ Ÿ. (5) ݱ   
״ٸ,     ̴. 

5) UNIX ýۿ,   ׻1970 GMT 1 1 00̴.

   name    |population
   --------+----------
   Mariposa|      1200
   Mariposa|      1320
   (2 row)

ð Ʈ ۽ ý ð ǥ  ִ  ð̸,

Ʈ   ð̴.  ,  ð "[,]."   
   ִ. 

5.3 ȭ  

 𵨿  Ӽ  ȭ(atomic)ȴٴ ̴.  POSTGRES 
 ̷  .  Ӽ Ǿ   ִ (sub-values)
   ִ.   , ϳ Ӽ ⺻ Ÿ 迭   
. 

5.3.1 迭

POSTGRES  ϳ νϽ Ӽ  Ǵ   迭
    ִ.   ⺻̳   迭   ִ.
Ƿʸ ̱ , ⼭  ⺻ 迭 Ͽ Ŭ  
ڴ. 

   CREATE TABLE SAL_EMP (
          name             text,
          pay_by_quarter   int4[],
          schedule         char16[][]
   );

 Ǵ SAL_EMP  Ҹ Ŭ, text ڿ('name') int4 1 
迭('pay_by_quarter'), ׸ char16  2 迭('schedule')  
Ѵ.  'pay_by_quarter'  鿡 ޵Ǵ 4е ޷̸, 
'schedule'   ְȹ̴.   ͸  غ.  迭 
͸ ߰ , ش簪 ߰ȣ ̿ ; ϰ, ޸  еȴ. 
 
C  ˰ ִٸ, ü ʱȭ   ٸ ʴٴ     
̴.

   INSERT INTO SAL_EMP
        VALUES ('Bill',
                '{10000, 10000, 10000, 10000}',
                '{{"meeting", "lunch"}, {}}');

   INSERT INTO SAL_EMP
        VALUES ('Carol',
                '{20000, 25000, 25000, 25000}',
                '{{"talk", "consult"}, {"meeting"}}');


POSTGRES  ⺻ 迭 ȣ   1 Ѵ.  
n  Ҹ  迭 迭[1]  Ͽ 迭[n] . 

, SAL_EMP   Ǹ .  ,    迭 ϳ
 ҿ ϴ  ˾ƺ.   Ǵ   ޷ ι° 
   ˻Ѵ. 

   SELECT name
   FROM SAL_EMP
   WHERE SAL_EMP.pay_by_quarter[1] <>
         SAL_EMP.pay_by_quarter[2];


   name 
   -----
   Carol
   (1 row)

 Ǵ   ޷ °  ش.

   SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP;

   pay_by_quarter
   --------------
            10000
            25000
   (2 rows)

POSTGRES  迭 (slices), Ǵ 迭(subarrays)   
.   Ǵ Bill  ְȹ ó ι° ù°  
Ѵ. 

   SELECT SAL_EMP.schedule[1:2][1:1]
   FROM SAL_EMP
   WHERE SAL_EMP.name = 'Bill';

   schedule          
   ------------------
   {{"meeting"},{""}}
   (1 row)


#60   ѵ   (ddoch   )
[] postgres 1.0 ޴ (6)               08/13 02:34   121 line

6. Ȯ SQL : ̸


,   ߰Ͽ POSTGRES SQL Ǿ Ȯ  ִ 
˾ƺ ̴. 

 * Լ (functions)
 *  (types)
 * ۷ (operators)
 * հ (aggregates)

6.1  Ȯ  ۵ϳ 

POSTGRES   ۵ īŻα (catalog-driven)̱  Ȯ尡
.  е ǥ  ýۿ ģϴٸ,  ýۿ 
̽ ̺, ÷  õ  ý īŻα׶ Ҹ  
  ˰̴. ( ýۿ ̰   (data dictionary)
 θ.)  īŻα״ ڿԴ ٸ Ͱ  Ŭ ǥ
, DBMS  θ īŻα ȿ Ѵ.  ٸ ǥ  ý۰ 
POSTGRES  ٸ  POSTGRES  īŻα ο     Ѵ
 ̴.  Ǵ  ̺ ÷ õ  Ӹ ƴ϶, 
(types), Լ(functions),  (access methods)    Ե
.  ý īŻα׿ õ Ŭ ڰ   , POSTGRES 
 ۵ ̷ Ŭ Ѵ.   ǹ̴ ڰ POSTGRES  Ȯ
  ִٴ ̴.  񱳸 ڸ,  ͺ̽ ý DBMS 
 ϵڵ ν ϰų, DBMS ڰ Ư   
νḸ Ȯ尡. 

POSTGRES  ٸ κ  ڿʹ ޸, ڰ ۼ ڵ带
DBMS η  (dynamic loading) ؼ   ִٴ ̴. 
ڴ ÿ ο (type) Լ ǵ Ʈ ڵ 
(ϵ .o, Ǵ  ̺귯)    , POSTGRES  û
  ̴.  SQL ۼ ڵ   DBMS  ߰  ִ.

POSTGRES  ̷  ɷ Ư ο α׷ ż ۼ
 屸 ϴ. 

6.2 POSTGRES (type) ý 

POSTGRES  ý   ȭ ų  ִ. 

(type) ⺻(base) (composite)   ִ.  ⺻ int4
 , C    Ǵ ͵̴.  ⺻  "̷ 
(abstract)  " ˷ ͵ ġѴ.  POSTGRES  ڰ 
  ⺻  ۵  , ⺻ ൿ ڰ 
 Ȯθ Ѵ.   ڰ Ŭ 鶧 ȴ. 
'EMP'   ϳ ̴.  POSTGRES  , Ŭ  νϽ
    Ѱ , ڴ Ǿ Ͽ 
 Ӽκп θ 鿩   ִ.  ƿ﷯ ش Ӽ ( 
ڸ) ε ϴ   ȭ  ִ. 

ƿ﷯ POSTGRES  ⺻ (built-in) (user-defined) 
 ִ.  int4    ýۻ ϵ · ԵǾ ִ. 
 Ʒ ϴ  ڰ  ̴. 

6.3 POSTGRES ý īŻα

, īŻα   캸鼭, ⺻ Ȯ强 信 Թغ. 
 ǳʶٴ   , ޺κп ⿡    
ϱ   ؼ  κ ǥص. 

ý īŻαǸ Ī 'pg_'  Ѵ.  Ʒ Ŭ ڿ
 ̴.  (ٸ ý īŻα׵    ǿ  
 幰 ̴.)


 ----------------------------------------------------------------------
  īŻα ̸                
 ====================================================================== 
  pg_database          ͺ̽
  pg_class             Ŭ
  pg_attribute         Ŭ Ӽ
  pg_index             ι°() ε

  pg_proc          ν (C  SQL)
  pg_type               (⺻ )
  pg_operator          ۷
  pg_aggregate           Լ

  pg_am                 
  pg_amop                ۷
  pg_amproc               Լ
  pg_opclass             ۷ Ŭ
 ----------------------------------------------------------------------

۷ ޴ ̷ īŻα׿ Ӽ    ڼ  ִ.
׷, ׸3 ý īŻα ٽ ü ̷ 踦 ְ ִ. 
( ٸ ü  ʴ Ӽ ش Ӽ ̸Ӹ Ű Ϻκ ƴ
  쿡 ⿡ Ÿ ʴ´.)

 ǥ  īŻα   ̳ ٸ ͵ ȣ踦 
캸 ټҰ ϱ  ̴.   ǥ ߿  
 .

(1)  忡, ý Ȯϴ  ʿ  ִ ý 
    īŻα׻󿡼 پ join Ǹ   ̴.   ǥ 캸鼭
        ֵ ̷ join (  װ join)  
     ͵  ̴.  ǿ  Ӽ ٸ Ŭ
    ܺ Ű ¸ ̷ٴ    ִ. 

(2) ٸ  Ư¡ (Ŭ, Ӽ, Լ, ,   )鵵  ɸ
      Ѵ.  'create'   ̷ īŻα  
    κ   ִ.


    [׸ 3] POSTGRES  ٽ ý īŻα


(3) Ÿ԰ ν (6)  ɸ ̴߽.    īŻα״
    ̷ Ŭ ϳ Ǵ  ο ִ νϽ Ѵ. 
     ڸ, POSTGRES   ٸ īŻα  νϽ ĺϱ
      ñ׳(Լ ۷) Ѵ. 

 6) ⼭ ټҰ ȣȯ  'procedure(ν)' 'function(Լ)'
       ܾ Ѵ. 

(4) и ǹ̸   Ӽ 谡 , ׷  ( 
     Բ ϴ Ư )  .  'pg_am', 'pg_amop', 'pg_amproc',
    'pg_operator', 'pg_opclass'   ϱⰡ ſ ٷӱ ,
    ⺻ Ȯ忡  ˾ƺ ڿ  ɵ (types operators  
    ڼϰ ϴ 忡) ˾ƺ ̴. 


#62   ѵ   (ddoch   )
[] postgres 1.0 ޴ (7)               08/17 15:56   380 line

7. Ȯ SQL : Լ

Լ ϴ  ο (type) ϴ  Ϻκ̴.
ο (type)  ʰ Լ ϴ  ,   Ұ
ϴ.   ⼭ ο (type) ߰ϴ  ϱ⿡ ռ 
ο Լ ߰ϴ    ̴. 

忡   funcs.sql  C-code/funcs.c  ã  ִ. 

7.1 Ǿ (SQL) Լ

7.1.1  ⺻ SQL Լ

  SQL Լ Űڴ , int4   ⺻ ȯϴ 
ƴұ? 

    CREATE FUNCTION one() RETURNS int4
         AS 'SELECT 1 as RESULT' LANGUAGE 'sql';

    SELECT one() AS answer;


    answer
    ------
         1

⿡  Ʈ RESULT  ̸ Լ  Ͽ, Լ
ҷ ǿ  Ʈ Լ  Ʈ ȿȭѴ.  , 
  one  answer ̶ ̸  ٴ´. 

 Űڷ ⺻  SQL Լ غ.  Ʒ , Լ
ο Űڸ   $1, $2   Ѵٴ  ϶.

    CREATE FUNCTION add_em(int4, int4) RETURNS int4
         AS 'SELECT $1 + $2;' LANGUAGE 'sql';

    SELECT add_em(1, 2) AS answer;
    

    answer
    ------
         3


7.1.2   SQL Լ

Լ Űڰ  (EMP ó) 쿡, ϴ ( $1, $2 
ߴ ó) ڸ ø ؼ ȵǰ  Ӽ ؾ Ѵ.  
,  ޷Ḧ ι ϴ double_salary Լ 캸. 

    CREATE FUNCTION double_salary(EMP) RETURNS int4
         AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql';

    SELECT name, double_salary(EMP) AS dream
        FROM EMP
        WHERE EMP.dept = 'toy';


   name|dream
   ----+-----
   Sam | 2400


$1.salary  뿡 .

 ȯϴ Լ  ,  Ӽ õ Լ ǥ 
ƺ.  ̷ ǥĿ attribute(class)  class.attribute  ΰ 
  ٲپ   ִ. 

   --
   -- ̰   ϴ:
   --    SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
   --
   SELECT name(EMP) AS youngster
   FROM EMP 
   WHERE age(EMP) < 30;


   youngster
   ---------
   Sam      

 ׻ ̿  ʴ.

 Լ ǥ ϳ νϽ ȯϴ Լ   ߿ϴ. 

̷ ۾ Լ ü νϽ Ӽ ϳ ν   ִ.
 ϳ EMP νϽ ȯϴ Լ ̴. 

   CREATE FUNCTION new_emp() RETURNS EMP
      AS 'SELECT \'None\'::text AS name,
         1000 AS salary,
                  25 AS age,
                  \'none\'::char16 AS dept;'
      LANGUAGE 'sql';


⼭  Ӽ  Ͽ,  갪̳ ǥĵ
̷  ü  ִ. 

̿  Լ ϴ   Ʈ ̴.   ؾ ߿
   . 

*  Ʈ  CREATE TABLE  Ÿ Ӽ  Ȯ ġ
  ؾ Ѵ. 

* ǥ '::'  Ͽ ȯ   ſ ؾ ϸ, ׷ 
  쿡    ߻ ̴. 

   WARN::function declared to return type EMP does not retrieve (EMP.*)

* ϳ νϽ ȯϴ Լ ȣ , Ӽ ü ȸ  .
  ̰ νϽ  ϳ Ӽ ȸϰų ü νϽ Ǵٸ Լ
   ѱ  ִ. 

  SELECT name(new_emp()) AS nobody;


   nobody
   ------
   None  

* Լ ȯ Ӽ   ݵ Լ ǥ ؾ ϴ
  ,   Լȣ ؼ   ļ ('.')  ٸ 
    ϱ ̴. 

  SELECT new_emp().name AS nobody;
  WARN:parser: syntax error at or near "."


SQL Ǿ   յ Բ    Լ   ִ.
SQL  ۼǴ Լ select ǿ  (insert, update, delete
)    ִ.  ׷,   ݵ select  ϸ, 
Լ returntype     ȯϿ Ѵ.

   CREATE FUNCTION clean_EMP () RETURNS int4
     AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
         SELECT 1 AS ignore_this'
     LANGUAGE'sql';

   SELECT clean_EMP();


   x
   -
   1


7.2 α׷  Լ 

7.2.1 ⺻ α׷  Լ

POSTGRES   ⺻ ޸  νѴ.  ϳ  Ѿ 
   Լ POSTGRES Լ󿡼 ۵  ִ  ϴ 
̴.  POSTGRES  Լ ϰ ũ о ̸,  Է.
ó.   Լ  ̴. 

⺻     ϳ   ִ.

*   , 
*   , 
*   , 

  (type)  ǻͰ ٸ ũ  ¸ Ѵ 
,  ̴ 1,2,4 Ʈ   ִ.  POSTGRES  üδ  
޹  Ѱ  ִ.  ο (type)    
ŰĿ  ũ(Ʈ) Ǿ Ѵٴ  ؾ Ѵ.   
, long  ѵ,   int  4Ʈ κ н ӽ
  ӽ 4Ʈ̰ ٸ ӽ 8Ʈ   ֱ 
 (밳 PC  ׷ ʰ).  н ӽ󿡼 int4  
. 

    /* 4-byte integer, passwd by value */
    typedef int int4;

׿ 쿡,      ȴ.   , POSTGRES 
 char16   . 

   /* 16-byte structure, passed by reference */
   typedef struct {
       char data[16];
   } char16;

char16   POSTGRES  Ѱְų      ͸
  ִ. 

,       ̾ Ѵ.  ƿ﷯,
    Ȯ 4Ʈ ̸ Ÿ ʵ ۵Ǿ 
ϸ, ش     ʹ ǥ ʵ ٷ  ޸ 
ġ  ؾ Ѵ.   ʵ  ʵ  ü ̸ , 
ü  հ̴.  'text'       ִ. 

   typedef struct {
       int4 length;
       char data[1];
   } text;

, data ʵ   ڿ ϱ⿡  ʴ -- C 
̷  ü ϴ  Ұϴ.     ó
,   ޸𸮸 Ҵϰ  ʵ带 ʱȭؾ Ѵٴ  
ؾ Ѵ.   , text ü 40Ʈ Ϸ  ,  
¸   ̴. 

   #include "postgres.h"
   #include "utils/palloc.h"

   ...

   char buffer[40]; /* our source data */

   ...

   text *destination = (text *)palloc(VARHDRSZ + 40);
   destination->length = VARHDRSZ + 40;
   memmove(destination->data, buffer, 40);

   ...


ݱ ⺻ 밡   캸Ҵ.    Լ 
 캸 .  C-code/funcs.c    .

   #include <string.h>
   #include "postgres.h"   /* for char16, etc. */
   #include "utils/palloc.h"  /* for palloc */

   int 
   add_one(int arg) 
   {
       return(arg + 1);
   }

   char16 *
   concat16(char16 *arg1, char16 *arg2)
   {
       char16 *new_c16 = (char16 *) palloc(sizeof(char16));

       memset((void *) new_c16, 0, sizeof(char16));
       (void) strncpy(new_c16, arg1, 16);
       return (char16 *)(strncat(new_c16, arg2, 16));
   }

   text *
   copytext(text *t)
   {
       /*
        * VARSIZE is the total size of the struct in bytes.
        */
       text *new_t = (text *) palloc(VARSIZE(t));

       memset(new_t, 0, VARSIZE(t));

       VARSIZE(new_t) = VARSIZE(t);
       /*
        * VARDATA is a pointer to the data region of the struct.
        */
       memcpy((void *) VARDATA(new_t), /* destination */
              (void *) VARDATA(t),     /* source */
              VARSIZE(t)-VARHDRSZ);    /* how many bytes */
       return (new_t);
   }

OSF/1 ý 󿡼   Ÿ  ִ. 

    CREATE FUNCTION add_one(int4) RETURNS int4
         AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c';

    CREATE FUNCTION concat16(char16, char16) RETURNS char16
         AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c';

    CREATE FUNCTION copytext(text) RETURNS text
         AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c';
         

ٸ ýۿ, װ  ̺귯  Ÿ  Ƹ 
̸  .sl  ǵ ؾ  ̴


7.2.2  α׷  Լ

 C  üó Ǿ  ʴ.   νϽ  ʵ
   ִ. ƿ﷯,  ӵ  
   ٸ ʹ ٸ ʵ带   ִ.  , POSTGRES 
 C κ  ϴ  ʵ带  ν ̽ Ѵ. 

POSTGRES  νϽ  óϴ ó,  νϽ 
TUPLE  ü Լ Ѱ ̴. 

 Ǹ óϱ  Լ Ϸ Ѵٰ غ. 

   SELECT name, c_overpaid(EMP, 1500) AS overpaid
   FROM EMP
   WHERE name = 'Bill' or name = 'Sam';

 ǿ  c_overpaid      ִ. 

    #include "postgres.h"  /* for char16, etc. */
    #include "libpq-fe.h"  /* for TUPLE */

    bool
    c_overpaid(TUPLE t, /* the current instance of EMP */
               int4 limit)
    {
        bool isnull = false;
        int4 salary;

        salary = (int4) GetAttributeByName(t, "salary", &isnull);

        if (isnull)
            return (false);
        return (salary > limit);
    }

GetAttributeByName  POSTGRES ý Լμ  νϽ Ӽ ȯ
.  ⿡  Űڰ ִ , װ Լ Ѱ TUPLE 
Ű, ⸦ ٶ Ӽ ̸, ش Ӽ  Ÿ ȯ
ϴ Ű̴.  GetAttributeByName  Ƹ ͸  ̱⿡
ȯ ϴ  ȯ   ִ.   , char16  Ӽ name
 ִٸ, GetAttributeByName ȣ   ̴. 

    char *str;
    ...
    str = (char *) GetAttributeByName(t, "name", &isnull)

 Ǵ POSTGRES  c_overpaid Լ νĽŰ  ̴. 

   CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool
       AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c';


C Լο ο νϽ ϰų ϴ νϽ 
 ,  ޴󿡼 ϱ⿡ ʹ ϴ.


7.3  ǻ 

   ٷο α׷  Լ ۼϴ ӹ ƿͺ.
    α׷ӷ    ƴϴ.  е
 POSTGRES    Լ C  ۼϷ ϱ⿡ ռ C ( 
, malloc ޸   )  ϰ ־ Ѵ. 

C  ƴ ٸ α׷  Ͽ ۼ Լ POSTGRES   
 ֱ ,  ۾  ٷӴ.  ֳϸ Ʈ̳ ĽĮ 
ٸ   "ȣ "  C ʹ ٸ ̴.  , ٸ 
  Լ̿ Űڸ ϰų  ȯ  ٴ ̴.
̷ ,  α׷  Լ C  ۼǾٰ Ѵ. 

C  Լ ۼ  ⺻ Ģ  . 

(1) POSTGRES  ־ κ  /usr/local/postgres95/include 
     ġǾ ־ Ѵ(׸ 2 ).  cc ࿡ 
    -I/usr/local/postgres95/include  Ͽ  ׻ Ͽ
    Ѵ.  ,  ã   ҽ ߿   ִ
    (츮  ġ ߸  е  include  
     ġ ʿ伺 ִ).  ̷ 쿡   ϳ̻ ߰  
     ִ. 

         -I/usr/local/postgres95/src/backend
         -I/usr/local/postgres95/src/backend/include
         -I/usr/local/postgres95/src/backend/port/<PORTNAME>
         -I/usr/local/postgres95/src/backend/obj

    (⿡ <PORTNAME>  alpha  sparc   Ʈ Ī̴. )

(2) ޸ Ҵ  , C ̺귯 malloc  free ƾ  POSTGRES 
    palloc  pfree ƾ Ѵ.  palloc   Ҵ ޸𸮴 Ű Ʈ
     κп ڵ ޸𸮸 Ӱ ϸ, ޸  ´.

(3) ش ü ׻ memset Ǵ bzero  Ͽ 0 ʱȭϴ  
    .  (ؽ  ޽, ؽ ,  ˰  )  ƾ
    ü ִ ο(raw) Ʈ ۿ Ѵ.  ü ʵ带 
     ʱȭѴ ,   ϴ, Ľÿ ߻ϴ äֱ
    (ü Ȧ)Ʈ   ִ. 

(4) 밳  POSTGRES   postgres.h  ԵǾ Ǿ Ƿ,
       ϴ  . 

(5) POSTGRES    ڵ带 ϰ  ÿƯ
    ÷׸ ʿѴ.  Ư ü 󿡼 ̷ ۾ ϴ  
     ڼ  η A  ϶. 


#63   ѵ   (ddoch   )
[] postgres 1.0 ޴ (8)               08/17 17:39   109 line

8. Ȯ SQL :  (Type)

տ 캸, POSTGRES      ִ.  ⺻(base)
(α׷  ǵǾ ִ ) (composite)(νϽ) ִ.

⿡   complex.sql  complex.c  ִ.    
funcs.sql  ִ. 

8.1   

8.1.1    ʿ Լ

   ݵ input, output Լ  ־ Ѵ.  ̷ Լ
  (ڰ Էϰ ڿ µǴ)ڿ  Ÿ  
ش  ޸𸮿  Ǵ ˻Ѵ.  ԷԼ η  
 Է ޾Ƶ鿩 ش   ǥ ȯѴ.  Լ ش
  ǥ ޾Ƶ鿩 η  ڿ ȯѴ.

 ڸ ǥϴ complex   غ.    ޸𸮿 ǥ
ϱ  ڿ  C ü  ̴. 

    typedef struct Complex {
        double     x;
        double     y;
    } Complex;

(x,y)  ڿ ܺ ڿ ǥȴ. 

̷Լ  ۼϱⰡ , Ư Լ  .  ׷,
⿡ ؾ    ִ. 

(1) ܺ (ڿ) ǥ  ,  ԷԼ ش ǥ  
    ޾Ƶ̴ Ϻϰ ưư мⰡ ǵ ؾ Ѵٴ  ! 

        Complex *
        complex_in(char *str)
        {
            double x, y;
            Complex *result;

            if (sscanf(str, " ( %1f , %1f )", &x, &y) != 2) {
                elog(WARN, "complex_in: error in parsing \"%s\"", str);
                return NULL;
            }
            result = (Complex *) palloc(sizeof(Complex));
            result->x = x;
            result->y = y;
            return (result);
        }

Լ  ϰ   ִ.

        char *
        complex_out(Complex *complex) 
        {
            char *result;

            if (complex == NULL)
                return(NULL);

            result = (char *) palloc(60);
            sprintf(result, "(%g,%g)", complex->x, complex->y);
            return(result);
        }

(2) Է,  Լ        õغƾ Ѵ. 
     ̷   쿡, ش ͸ Ͽ ϰų (ٸ 
    ۻ  ٸ ͺ̽) Ųٷ о    
     ̴.  Ư ε Ҽ     Ÿ. 

complex  , ش    complex_in  complex_out 
ΰ   Լ  Ѵ. 


    CREATE FUNCTION complex_in(opaque)
       RETURNS complex
       AS '/usr/local/postgres95/tutorial/obj/complex.so'
       LANGUAGE 'c';

    CREATE FUNCTION complex_out(opaque)
       RETURNS opaque
       AS '/usr/local/postgres95/tutorial/obj/complex.so'
       LANGUAGE 'c';

    CREATE TYPE complex (
       internallength = 16,
       input = complex_in,
       output = complex_out
    );

տ , POSTGRES  ⺻ 迭 ǳϰ Ѵ.  ƿ﷯,
POSTGRES     迭  Ѵ.    , POSTGRES 
ڵ ش  迭 Ѵ.  , 迭 ̸  
 Īտ   '_'  տ ġϴ ° ȴ. 

  Լ ʿ ʴ.  ý ̹ ̵ θ ϱ 
̴. 

8.1.2  ü

ݱ    "(small)" ü̸ 8KB (7)   ͵̴. 
ȭ ý̳ Ʈ  ý۰    ʿϴٸ, POSTGRES 
ü (large object) ̽  ʿ䰡  ̴. 


7) 8 * 1024  8192 Ʈ̴.  POSTGRES  tuple    
      8KB Ѱ迡 ؾ ϱ   ϳ  8192 Ʈ 
     ۰ Ǿ Ѵ.  ̿    ش  ŰĿ 
     ̴. 


#64   ѵ   (ddoch   )
[] postgres 1.0 ޴ (9,10)            08/17 19:57   112 line

9. Ȯ SQL : 

POSTGRES  ,   ڿ ̳ʸ ڸ Ѵ.
ڴ  ٸ ڳ  Űڿ  ǰų   ִ.
ڰ ָ ġ ִٸ ý ùٸ ڸ   
,  ȯѴ.  ̷ 쿡,  ڸ   ظ 
  / ǿڸ ȯ   ִ.  

ΰ complex  ϴ ڸ  Ʒ  ϸ ȴ.  
ο  ʿ Լ  Ѵ.  ׸ , ش ڸ ̵ 
 Բ  ȴ. 

   CREATE FUNCTION complex_add(complex, complex)
      RETURNS complex
      AS '$PWD/obj/complex.so'
      LANGUAGE 'c';

   CREATE OPERATOR + (
      leftarg = complex,
      rightarg = complex,
      procedure = complex_add,
      commutator = +
   );


⿡ ̳ʸ ۷͸      ̴.   ۷
͸  leftarg(  ۷)  rightarg(  ۷
) ϳ ϸ ȴ. 

ýۿ ش     Ѵٸ, ý  ۷͸
ϴ   ڵ    ִ. 

   SELECT (a + b) AS c FROM test_complex;


   c              
   ---------------
   (5.2,6.05)     
   (133.42,144.95)



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
10. Ȯ SQL : ü (Aggregates)

POSTGRES  Aggregates   ȯ Լ(state transition functions) 
 ǥȴ.  Aggregate  ϳ νϽ ó  Ǵ 
(state)  ǵ  ִ.    Լ νϽ ο 
¸   Ư  ã(create aggregate  sfunc1), ٸ 
Լ ڽ   븦  Ѵ(sfunc2).

aggregate  sfunc1  Ѵٸ,  νϽ Ӽ󿡼 Ǵ
Լ   ִ.  "Sum"  ̷  aggregate  ̴.  
"Sum"  ó 0  ,   νϽ  հ Ѵ.
õڿ int4pl  Ͽ POSTGRES  ̷  ϵ  ̴. 

   CREATE AGGREGATE complex_sum (
        sfunc1 = complex_add,
        basetype = complex,
        stype1 = complex,
        initcond1 = '(0,0)'
   );

  SELECT complex_sum(a) FROM test_complex;


   complex_sum
   -----------
   (34,53.9)  


sfunc2  Ѵٸ,  νϽ Ӽ  Ǵ Լ
   ִ.  "Count"  ̷ aggregate  ǥ ̴.
"Count"  0 ۵ǰ,  νϽ հ迡 1  νϽ
 õȴ.  ⼭ POSTGRES  ƾ int4inc  Ѵ.   ƾ
ش   ϳ Ų. 

   CREATE AGGREGATE my_count (sfunc2 = int4inc, -- add one
                              basetype = int4, stype2 = int4,
                              initcond2 = '0'
                             );
   
   SELECT my_count(*) as emp_count from EMP;


   emp_count
   ---------
           4


"Average"  հ Ƚ, Ѵ ϴ Լ ʿ ϴ  aggregate  
.   νϽ óǰ , aggregate    հ踦 Ƚ
 .   ߴ int4pl  int4inc   int4div  
POSTGRES    ƾ̸, հ踦 Ƚ   Ѵ. 

   CREATE AGGREGATE my_average (sfunc1 = int4pl,  -- sum
                                basetype = int4,
                                stype1 = int4,
                                sfunc2 = int4inc, -- count
                                stype2 = int4,

          finalfunc = int4div, -- division
                                initcond1 = '0',
                                initcond1 = '0'
                               );

   SELECT my_average(salary) as emp_average FROM EMP;


   emp_average
   ------------
          1640
