stonecolddevin

  CREATE TABLE comments (
        id SERIAL PRIMARY KEY,
        message VARCHAR,
        author VARCHAR,
        parent_id INTEGER REFERENCES comments(id)
  );
  INSERT INTO comments (message, author, parent_id)
  VALUES ('This thread is really cool!', 'David', NULL), 
  ('Ya David, we love it!', 'Jason', 1), ('I agree David!', 'Daniel', 1), 
  ('gift Jason', 'Anton', 2),
  ('Very interesting post!', 'thedz', NULL), 
  ('You sir, are wrong', 'Chris', 5), 
  ('Agreed', 'G', 5), ('Fo sho, Yall', 'Mac', 5);

  WITH RECURSIVE cte (id, message, author, path, parent_id, depth)  AS (
  SELECT  id,
          message,
          author,
          array[id] AS path,
          parent_id,
          1 AS depth
  FROM    comments
  WHERE   parent_id IS NULL

  UNION ALL
 
  SELECT  comments.id,
          comments.message,
          comments.author,
          cte.path || comments.id,
          comments.parent_id,
          cte.depth + 1 AS depth
  FROM    comments
          JOIN cte ON comments.parent_id = cte.id
  )
  SELECT id, message, author, path, depth FROM cte
  ORDER BY path;