PG페이스북에 어느분이 질문 올린글 중에 File시스템 관리(파일이름과 그에 따른 폴더위치 관리)에 대한 질문이 올라 왔었는데

마침 궁금해서 만들어 봤습니다.  자기참조 테이블 만드는 것과 CTE의 RECURSIVE를 같이 사용하면 될거 같아서 구현~~

(실제 OS에서는 어떻게 되는지 궁금하네요...) 폴더 구조를 저장하는 테이블과 파일의 속성에서 폴더구조를 참조하는 테이블 

-------------------------------------
--directory
-------------------------------------
CREATE TABLE public.directory (
directory_id INTEGER NOT NULL,
directory_name VARCHAR(100),
directory_id_ref INTEGER, --부모 디렉토리, 최상위 부모일 경우는 null
CONSTRAINT directory_pkey PRIMARY KEY(directory_id),
CONSTRAINT directory_fk FOREIGN KEY (directory_id_ref)
REFERENCES public.directory(directory_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
)
WITH (oids = false);

ALTER TABLE public.directory
ALTER COLUMN directory_id_ref SET STATISTICS 0;

A폴더 밑에 AB, AB폴더 밑에 ABA폴더 식 구조

INSERT INTO directory VALUES
(1, 'A', NULL),
(2, 'B', NULL),
(3, 'C', NULL),
(4, 'AB', 1),
(5, 'AA', 1),
(6, 'ABA', 4);

WITH RECURSIVE cte_table(directory_id, directory_name, directory_id_ref, full_path) AS
(
select directory_id, directory_name, directory_id_ref, directory_name as full_path
from directory
WHERE directory_id_ref is null

union all

select a.directory_id, a.directory_name, a.directory_id_ref, cast(b.full_path || '\'|| a.directory_name as varchar(100))
from directory a inner join cte_table b
on (a.directory_id_ref = b.directory_id)
)
select * from cte_table
-------------------------------
directory_id directory_name directory_id_ref full_path
1 A A
2 B B
3 C C
4 AB 1 A\AB
5 AA 1 A\AA
6 ABA 4 A\AB\ABA

----------------------------------
--File Table
----------------------------------

CREATE TABLE public.files (
file_name VARCHAR(100) NOT NULL,
directory_id INTEGER NOT NULL, --폴더 위치
CONSTRAINT files_fk FOREIGN KEY (directory_id)
REFERENCES public.directory(directory_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
)
WITH (oids = false);

insert into files VALUES
('Postgresql 초보자 길잡이.txt', 6),
('Postgresql 튜닝 가이드.txt', 5),
('Postgresql DBA 실전 가이드.txt', 1)
('C#개발자를 위한 Postgresql.txt', 2)
('C#과 Postgresql를 활용한 SCM/WMS/TMS구축.txt', 3)

WITH RECURSIVE cte_table(directory_id, directory_name, directory_id_ref, full_path) AS
(
select directory_id, directory_name, directory_id_ref, directory_name as full_path
from directory
WHERE directory_id_ref is null

union all

select a.directory_id, a.directory_name, a.directory_id_ref, cast(b.full_path || '\'|| a.directory_name as varchar(100))
from directory a inner join cte_table b
on (a.directory_id_ref = b.directory_id)
)
select
b.file_name,
a.full_path --파일의 폴더위치
from
cte_table a
inner join files b on (a.directory_id = b.directory_id)

--------------------------------
--★ 파일의 폴더 위치가 변경되면
--------------------------------
해당 폴더(디렉토리) 경로를 directory 테이블에 입력하고 directory.directory_id값을
files.directory_id에 Update해줍니다.