Recursive file path construction of a single user for Old Pithos filesystem. PostgreSQL 8.4

gistfile1.sql

-- All files (and the folders they belong to) recursively
with recursive q as (
 select  folder,
         1 as level,
         '' || folder.name as folder_path,
         '' || folder.name || '/' || file.name as file_path,
         folder.id as folder_id,
         folder.parent_id as parent_folder_id,
         file.id as file_id
 from    folder,
         fileheader file
 where   folder.parent_id is null
 and     folder.owner_id = 1
 and     file.folder_id = folder.id
 union all
 select  fd,
         q.level + 1 as level,
         q.folder_path || '/' || fd.name as folder_path,
         q.folder_path || '/' || fd.name || '/' || file.name as folder_path,
         fd.id as folder_id,
         fd.parent_id as parent_folder_id,
         file.id as file_id
 from    q,
         folder fd,
         fileheader file
 where   fd.parent_id = (q.folder).id
 and     file.folder_id = fd.id
)
select  distinct level, folder_id, parent_folder_id, folder_path, file_id, file_path
from    q
order by level, folder_path, file_path
;