database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 4585 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 4585
7.3의 table function 이야기
작성자
김상기(ioseph)
작성일
2003-02-18 01:58ⓒ
2003-09-15 21:06ⓜ
조회수
13,701

7.3 으로 바뀌면서 SQL 구문쪽으로 ALTER 명령이 엄청나게 확장 되었다는 것과, SCHEMA 개념이 도입된 것, 그리고, DOMAIN 정의가 가능해진것(이놈은 다음번 글에서 자세히 다루겠습니다) 등입니다.

 

이와함께 놀라운(?) 발전이 function 쪽으로 드디어 table function과 함수의 재귀호출이 가능해 졌다는 것입니다.

 

이 글에서는 이 함수쪽 기능이 테이블 함수와 함수의 재귀호출에 대해서 다루워보겠습니다.

 

테이블 함수란 7.2.x 이하 사용자들이 아쉬워했던 함수의 리턴 값이 테이블인 것을 말합니다. 엄격히 말하면 테이블 리턴 함수겠지요. (오라클에서는 아주 오래전부터 지원되고 있었던 기능이었지요.)

 

함수의 재귀호출은 프로그래밍쪽에 조금의 관심이 있는 분이라면 당연히 알고있는 그 함수가 함수 구문안에서 또 그 자기 자신의 함수를 호출하는 것을 말합니다.

 

연습목표: 트리구조의 메뉴 만들기

 

이놈이 재귀호출과 테이블 함수를 모두 살펴보는데 가장 적격인 예제입니다.

 

메뉴의 자료구조

mydb=# \d 트리연습
    Table "ioseph.트리연습"
  Column  |  Type   | Modifiers
----------+---------+-----------
 노드번호 | integer |
 상위노드 | integer |
 이름     | text    |

 

이 테이블에 들어있는 데이터는 다음과 같습니다.

mydb=# select * from 트리연습;
 노드번호 | 상위노드 |   이름
----------+----------+----------
        0 |        0 | 대중음악
        1 |        0 | 발라드
        2 |        0 | 댄스
        3 |        0 | 락
        4 |        0 | 트로트
        5 |        1 | 신승훈
        6 |        1 | 이승환
        7 |        2 | 쿨
        8 |        2 | 박진영
        9 |        3 | 윤도현
       10 |        3 | 김장훈
       11 |        4 | 설운도
       12 |        4 | 태진아
       13 |        4 | 현철
(14 rows)

 

 

1. 테이블을 리턴하는 함수 만들기

 

가장 간단하게, 특정 상위 노드 값을 가진 튜플들을 모두 보여주는 함수를 만들어 봅시다.

CREATE FUNCTION 트리연습보기1(int) RETURNS SETOF record AS '
SELECT * FROM 트리연습 WHERE 상위노드 = $1;
' language 'SQL';

 

여기서 관심 가져 볼 사항은 RETURNS SETOF record 입니다.

이것이 새롭게 추가된 기능입니다(물론 7.2.x 대에서도 setof로 column 단위의 멀티 리턴이 가능하기는 했지만, 이렇게 setof record 형태의 자료형이 지정되지 않은 상태의 레코드를 리턴하는 형태는 7.3에서 가능해졌습니다)

 

이함수를 어떻게 사용하냐면,

mydb=# SELECT * FROM 트리연습보기1(2) as t (노드번호 int, 상위노드 int, 이름 text);
 노드번호  | 상위노드  |  이름
-----------+-----------+--------
         7 |         2 | 쿨
         8 |         2 | 박진영
(2 rows)

 

위와 같이 테이블 alias를 정의하고 그것을 함수의 리턴 형태로 지정합니다.

즉, 함수가 리턴되는 레코드의 자료형을 select 할때 정의해 줍니다.

물론 함수를 만들때, SETOF 다음에 기존에 있는 테이블을 지정하면, 이런 컬럼 정의까지 해줄 필요는 없겠지요.

 

 

2. 함수의 재귀호출

 

여기까지는 잘 되었는데, 트리구조를 표현하려면,

한 튜플씩 그 튜플의 노드번호를 상위노드로 하는 또다른 하위노드들을 찾아야하는지라, 위의 SQL로 만든 간단한 함수는 별로 도움이 되질 못합니다.

그래서, plpgsql의 for ... loop 구문을 이용하는 plpgsql 함수를 만들어봅니다.

 

조금 복잡하지만 재귀호출 함수를 한번 정도 만들어본 경험이 있을지 알고 그냥 소스만 언급합니다.

 

CREATE FUNCTION 트리연습보기(int,int) RETURNS SETOF record as '
DECLARE
  node1 record;
  subnode record;
  nextdepth int;
BEGIN
  nextdepth := $2 + 1;
  FOR node1 IN SELECT $2,* FROM 트리연습 WHERE 상위노드 = $1 AND 노드번호 > 0 LOOP
    RETURN NEXT node1;
    FOR subnode IN SELECT * FROM 트리연습보기(node1.노드번호, nextdepth) as t(깊이 int,노드번호 int,상위노드 int,이름 text) LOOP
      RETURN NEXT subnode;
    END LOOP;
  END LOOP;
  RETURN;
end;
' LANGUAGE 'plpgsql';

 

여기서 RETURNS SETOF 는 위에서 언급한 것이고,

plpgsql 에서 새롭게 추가 된 것이 바로 RETURN NEXT 입니다.

각 해당 튜플(로우)를 리턴할 때는 RETURN NEXT를 모든 작업이 다끝나면 그냥 RETURN을 이렇게 정의합니다.

 

이렇게 만들어진 함수를 호출해보면,

mydb=# SELECT * FROM 트리연습보기(0,0) AS t (깊이 int,노드번호 int,상위노드 int,이름 text);
 깊이 | 노드번호 | 상위노드 |  이름
------+----------+----------+--------
    0 |        1 |        0 | 발라드
    1 |        5 |        1 | 신승훈
    1 |        6 |        1 | 이승환
    0 |        2 |        0 | 댄스
    1 |        7 |        2 | 쿨
    1 |        8 |        2 | 박진영
    0 |        3 |        0 | 락
    1 |        9 |        3 | 윤도현
    1 |       10 |        3 | 김장훈
    0 |        4 |        0 | 트로트
    1 |       11 |        4 | 설운도
    1 |       12 |        4 | 태진아
    1 |       13 |        4 | 현철
(13 rows)

 

자, 마지막 테스트

트리구조의 깊이를 더 만들어서 정상적으로 작동하는지를 살펴봅시다.

 

mydb=# INSERT INTO 트리연습 VALUES (14,13,'차차차');

mydb=# INSERT INTO 트리연습 VALUES (15,7,'작년,오늘');

mydb=# SELECT * FROM 트리연습보기(0,0) AS t (깊이 int,노드번호 int,상위노드 int,이름 text);

 

 

3. 마무리

 

글이 초보자를 위한 글이라고 하기에는 조금 무거운 감이 있기는 한데,

꼭 필요한 글이라서 남겨둡니다.

 

이 글에서 다룬 함수의 기능은 그 확장성이 무궁무진합니다.

아주 유용한 예로,

select * from tohtmltable('tablename') t as (row text);

결과는 지정한 테이블의 내용을 하나의 문자열 column으로 리턴하는데, 각 한 row씩이 바로 html 의 table 구문으로 보여주는게지요.

그러면, 웹프로그램이 한결 가벼워지겠지요. :)

 

다른 한 예로,

아주 복잡한 조건의 거래처 내역을 살펴보면서 마지막에는 현 잔액이 함께 나와야하는 경우도....

 

아무튼 이 글을 보시고, 이 기능의 확장성에 도전해 보는 것도 좋은 방법일 듯싶습니다. 괜찮으면, 이곳에서 함께 나눈다면 더할나위 없겠지요.

이 글에 대한 댓글이 총 5건 있습니다.

감명깊게 읽었습니다. 감사합니다.

정호영님이 2003-02-18 11:43에 작성한 댓글입니다.

오.... 저도 카테고리를 처리하는 부분에 있어서

상위노드(어미 카테고리)를 or 로 여러번 검색하거나

하나씩 찾는 쿼리문을 union으로 합친후 정렬을 하게끔 해서 한개의 쿼리로 해결을 보곤 했는데....

이런 방법이!! =_=

그동안 겉만 핱아왔다는 생각이 듭니다 ^^;;

다음 편도 기대를.. 그럼~

신기배(nonun)님이 2003-02-24 15:08에 작성한 댓글입니다.

이제 짐을 상기님께 떠 넘기고 나니 저는 PostgreSQL 계를 떠나는 느낌이 듭니다. ^^;

이번 글은 감명 깊게 읽었습니다. 7.3 changelog 만 읽어 보았었는데, 많은 이들에게 도움이 될 것이라고 생각됩니다.

정재익(advance)님이 2003-04-09 01:43에 작성한 댓글입니다.

ㅋㅋㅋㅋㅋ

 

지금막 테스트를 끝냈습니다.

 

질문 소스를 보니 거리가 멀긴 멀군요.

 

이제사 디비를 만지는 느낌이 나는군요.

 

답변준분과 예문을 제시한 분에게 고맙군요.

 

수고하세요.

미달이님이 2003-07-15 01:39에 작성한 댓글입니다.

김장훈이 락가수였군요.. 음... 비묘다나....

지금 일본에서 포스트그래 가지고 개발을 하고 있는데...

좀 힘들지만 그럭저럭 하고 있네요...

이곳에서 정말 많은 영감을 얻었습니다.

앞으로도 자주 들르고 싶어요....

임형구님이 2007-06-15 16:40에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
47627.2.x 대에서 replace 함수 구현하기
김상기
2003-06-20
6136
4708PostgreSQL에서 Oracle 의 nvl() 함수 구현하기 [2]
정재익
2003-05-15
9376
4629PostgreSQL 7.3.1 윈도우2000에 설치하기
옥용수
2003-03-20
8036
45857.3의 table function 이야기 [5]
김상기
2003-02-18
13701
4578SQL 이야기 2. INSERT INTO ... SELECT
김상기
2003-02-12
9250
4551SQL 이야기 1. UNION [4]
김상기
2003-01-25
8958
4516PostgreSQL clog xlog 손상에 따른 복구 이야기
김상기
2003-01-08
11247
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다