Support for SEARCH clause for recursive CTEs

[sim1984]

I don't have the opportunity to look at the SQL standard, so I'll just point out where it is described. The search clause is defined in ISO/IEC 9075-2:2023 §7.18 as part of optional feature T131, “Recursive query”

Some information is available at https://modern-sql.com/caniuse/search_(recursion)

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

See also https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-SEARCH

[sim1984]

@mrotteveel, can you provide a quote from the SQL standard?