Data that contains itself
Menu categories have sub-categories; staff have managers who have managers; comments have replies. Rows that point to rows in the same table form a tree. A recursive CTE walks it to any depth in one query.
WITH RECURSIVE
CREATE TABLE categories (id int PRIMARY KEY, name text NOT NULL, parent_id int REFERENCES categories(id));
INSERT INTO categories VALUES
(1, 'Menu', NULL), (2, 'Food', 1), (3, 'Drinks', 1),
(4, 'Momo', 2), (5, 'Steamed', 4), (6, 'Fried', 4), (7, 'Hot drinks', 3);
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS depth, name AS path
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT repeat(' ', depth - 1) || name AS item, path FROM tree ORDER BY path;