|
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 구문으로 보여주는게지요.
그러면, 웹프로그램이 한결 가벼워지겠지요. :)
다른 한 예로,
아주 복잡한 조건의 거래처 내역을 살펴보면서 마지막에는 현 잔액이 함께 나와야하는 경우도....
아무튼 이 글을 보시고, 이 기능의 확장성에 도전해 보는 것도 좋은 방법일 듯싶습니다. 괜찮으면, 이곳에서 함께 나눈다면 더할나위 없겠지요. |
