Recursive tree crawling in Postgres.

RecurseTreeCrawl.sql

WITH RECURSIVE file_depth
AS (
  SELECT
    id,
    parent_folder_id,
    1 AS depth
  FROM files 
  WHERE files.parent_folder_id = '0'

  UNION

  SELECT
    f.id,
    f.parent_folder_id,
    parent.depth + 1 AS depth
  FROM file_depth AS parent, files f
  WHERE f.parent_folder_id = parent.id
    AND depth < 10

) SELECT * FROM file_depth;