PostgreSQL에서 "connect by" 계층 쿼리 구현

Open source RDBMS인 PostgreSQL은 현재 9.1.3버전까지 나온 잘 만들어진 DBMS이지만 아주 큰 약점들이 있다. 오늘은 그중 하나인 계층형 쿼리 기능에 대한 해법을 말해보고자한다.
계층형 쿼리, Hierachical Query는 보통 조직도나 다단계 분류표 등의 구조를 나타내는데 쓰는 구문으로 Oracle RDBMS에서 "start with ~ connect by~"의 형태로 쓰며, 보통 "connect by"로 불리운다. 그런데 PostgreSQL에서는 계층형 쿼리를 쓰려면 추가 모듈을 설치해야 하는데, 이 모듈을 설치하면 쓸 수 있는 connectby라는 함수는 속도가 아주 느리다. 그래서 "connect by"를 따로 구현해 보았다.

입력받은 테이블과 컬럼 이름으로 "with recursive" 구문을 문자열로 만들어서 PL/pgSQL의 dynamic query를 이용해서 실행하고, 결과를 result set 형식으로 반환하는 방식이다.


create or replace udf_connect_by (tab_name varchar, key_name varchar, parent_key_name varchar, start_with varchar)
return SETOF record
as $$
declare
v_rowcnt integer;
v_query varchar;
begin
select count(*) into v_rowcnt
from pg_tables
where tablename = tab_name;

if v_rowcnt = 0 then
raise exception 'Invalid table name';
end if;

v_query := 'with recursive T1(select *, 1 as r_level, array[' || key_name || '::varchar] as r_path, false r_flag
from ' || tab_name || '
where ' || start_with || '
union all
select B.*, T1.r_level + 1 as r_level, array_append(T1.r_path, ' || key_name || '::varchar) as r_path, B.' || key_name || '::varchar = any(T1.r_path) as r_flag
from ' || tab_name || ' B, T1
where not r_flag
and B.' || parent_key_name || ' = T1.' || key_name || ')
select * from T1 order by T1.r_path ';

return query execute v_query;


end;
$$
language pl/pgsql
;


iPhone 에서 작성된 글입니다.