Repository: tobymao/sqlglot

Issue:
ParseError when handling PostgreSQL recursive CTE with SEARCH DEPTH FIRST BY
**Description:**

Hello team,
I'm encountering an issue when parsing a PostgreSQL query that includes a recursive Common Table Expression (CTE) with SEARCH DEPTH FIRST BY. SQLGlot throws a ParseError, stating that alias does not support CTE.

**Steps to Reproduce:**

I am using SQLGlot to parse the following PostgreSQL query:

```
from sqlglot import parse_one
from sqlglot.errors import ParseError

sql = """
WITH RECURSIVE tmp AS (
  SELECT uid, lkuid, 0 AS depth, ARRAY[uid, lkuid] AS path, FALSE AS cycle
  FROM bs
  WHERE uid = 1
  UNION ALL
  SELECT bs.uid, bs.lkuid, tmp.depth + 1, tmp.path || bs.lkuid, bs.lkuid = ANY(tmp.path)
  FROM bs, tmp
  WHERE bs.uid = tmp.lkuid
  AND NOT bs.lkuid = ANY(tmp.path)
)
SEARCH DEPTH FIRST BY uid SET ordercol
SELECT * FROM tmp ORDER BY ordercol;
"""

try:
    parsed_statement = parse_one(sql, read="postgres")
    if parsed_statement:
        print("SQLGlot successfully parsed the PostgreSQL statement.")
    else:
        print("SQLGlot failed to parse the PostgreSQL statement (returned None).")
except ParseError as e:
    print(f"SQLGlot ParseError: {e}")
```

**Observed Behavior**

Executing the script results in the following error:

SQLGlot ParseError: alias does not support CTE. Line 12, Col: 18.

```
SQLGlot ParseError: alias does not support CTE. Line 12, Col: 18.
  p.path)
  FROM bs, tmp
  WHERE bs.uid = tmp.lkuid
  AND NOT bs.lkuid = ANY(tmp.path)
)
SEARCH DEPTH FIRST BY uid SET ordercol
SELECT * FROM tmp ORDER BY ordercol;
```


**SQLGlot Version:** 
 
26.6.0


**Python version**
3.11

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 6a4dd1101a..450a86baa6 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1568,8 +1568,12 @@ class CharacterSet(Expression):
     arg_types = {"this": True, "default": False}
 
 
+class RecursiveWithSearch(Expression):
+    arg_types = {"kind": True, "this": True, "expression": True, "using": False}
+
+
 class With(Expression):
-    arg_types = {"expressions": True, "recursive": False}
+    arg_types = {"expressions": True, "recursive": False, "search": False}
 
     @property
     def recursive(self) -> bool:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 1f0c63c5a0..d5ca2d1032 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1238,8 +1238,10 @@ def with_sql(self, expression: exp.With) -> str:
             if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
             else ""
         )
+        search = self.sql(expression, "search")
+        search = f" {search}" if search else ""
 
-        return f"WITH {recursive}{sql}"
+        return f"WITH {recursive}{sql}{search}"
 
     def cte_sql(self, expression: exp.CTE) -> str:
         alias = expression.args.get("alias")
@@ -4793,3 +4795,14 @@ def declareitem_sql(self, expression: exp.DeclareItem) -> str:
             kind = f"TABLE {kind}"
 
         return f"{variable} AS {kind}{default}"
+
+    def recursivewithsearch_sql(self, expression: exp.RecursiveWithSearch) -> str:
+        kind = self.sql(expression, "kind")
+        this = self.sql(expression, "this")
+        set = self.sql(expression, "expression")
+        using = self.sql(expression, "using")
+        using = f" USING {using}" if using else ""
+
+        kind_sql = kind if kind == "CYCLE" else f"SEARCH {kind} FIRST BY"
+
+        return f"{kind_sql} {this} SET {set}{using}"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8326ae0cfc..f267b63787 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1373,6 +1373,8 @@ class Parser(metaclass=_Parser):
 
     OPERATION_MODIFIERS: t.Set[str] = set()
 
+    RECURSIVE_CTE_SEARCH_KIND = {"BREADTH", "DEPTH", "CYCLE"}
+
     STRICT_CAST = True
 
     PREFIXED_PIVOT_COLUMNS = False
@@ -3174,6 +3176,24 @@ def _parse_select(
 
         return self._parse_set_operations(this) if parse_set_operation else this
 
+    def _parse_recursive_with_search(self) -> t.Optional[exp.RecursiveWithSearch]:
+        self._match_text_seq("SEARCH")
+
+        kind = self._match_texts(self.RECURSIVE_CTE_SEARCH_KIND) and self._prev.text.upper()
+
+        if not kind:
+            return None
+
+        self._match_text_seq("FIRST", "BY")
+
+        return self.expression(
+            exp.RecursiveWithSearch,
+            kind=kind,
+            this=self._parse_id_var(),
+            expression=self._match_text_seq("SET") and self._parse_id_var(),
+            using=self._match_text_seq("USING") and self._parse_id_var(),
+        )
+
     def _parse_with(self, skip_with_token: bool = False) -> t.Optional[exp.With]:
         if not skip_with_token and not self._match(TokenType.WITH):
             return None
@@ -3198,7 +3218,11 @@ def _parse_with(self, skip_with_token: bool = False) -> t.Optional[exp.With]:
             last_comments = self._prev_comments
 
         return self.expression(
-            exp.With, comments=comments, expressions=expressions, recursive=recursive
+            exp.With,
+            comments=comments,
+            expressions=expressions,
+            recursive=recursive,
+            search=self._parse_recursive_with_search(),
         )
 
     def _parse_cte(self) -> t.Optional[exp.CTE]:


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index a6061e5347..44f1e397e0 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1358,3 +1358,13 @@ def test_analyze(self):
         self.validate_identity("ANALYZE TBL(col1, col2)")
         self.validate_identity("ANALYZE VERBOSE SKIP_LOCKED TBL(col1, col2)")
         self.validate_identity("ANALYZE BUFFER_USAGE_LIMIT 1337 TBL")
+
+    def test_recursive_cte(self):
+        for kind in ("BREADTH", "DEPTH"):
+            self.validate_identity(
+                f"WITH RECURSIVE search_tree(id, link, data) AS (SELECT t.id, t.link, t.data FROM tree AS t UNION ALL SELECT t.id, t.link, t.data FROM tree AS t, search_tree AS st WHERE t.id = st.link) SEARCH {kind} FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol"
+            )
+
+        self.validate_identity(
+            "WITH RECURSIVE search_graph(id, link, data, depth) AS (SELECT g.id, g.link, g.data, 1 FROM graph AS g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph AS g, search_graph AS sg WHERE g.id = sg.link) CYCLE id SET is_cycle USING path SELECT * FROM search_graph"
+        )