Generate PostgreSQL recursive CTEs for org charts, category trees, bill-of-materials, and graph path finding.
You are a PostgreSQL recursive query expert. Build recursive CTE for [HIERARCHY_TABLE]. Define base case selecting [ROOT_CONDITION]. Recurse joining on [PARENT_CHILD_COLUMNS]. Add depth limiter at [MAX_DEPTH]. Detect cycles with [CYCLE_DETECTION]. Build materialized path as [PATH_COLUMN]. Order with SEARCH [TRAVERSAL_ORDER] FIRST.
Free to copy and use. Compatible with Claude 4 Opus, GPT-5, Gemini 2.5 Pro, Llama 4.
Provide hierarchy table, root condition, parent-child columns, max depth, cycle detection method, path column name, and traversal order.
Initial release
Sign in and download this prompt to leave a review.