Allow adding extra clauses for recursive CTE queries in PostgreSQL

[p-himik]

Initially reported on Slack at https://clojurians.slack.com/archives/C66EM8D5H/p1726737107546879

PostgreSQL (the only SQL DBMS that can do that among the ones I've looked up) has extra syntax for the WITH RECURSIVE clause where each subquery in parentheses can be followed by the CYCLE ... and/or the SEARCH ... clause.

An example from WITH Queries (Common Table Expressions):

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

The relevant part of the grammar from the SELECT documentation:

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]

Other relevant bit of info:

Both the SEARCH and the CYCLE clause are only valid for recursive WITH queries. The with_query must be a UNION (or UNION ALL) of two SELECT (or equivalent) commands (no nested UNIONs). If both clauses are used, the column added by the SEARCH clause appears before the columns added by the CYCLE clause.

[seancorfield]

Just a note that HoneySQL does no validation on whether the SQL produced is legal, so this will get addressed in a fairly generic way, I expect, and will almost certainly allow illegal SQL to be generated if users get too "creative" 😄

[seancorfield]

I have a tentative implementation for this in place but need to write documentation for it.

The rough outline is that you can provide these "tail" options after the with query portion:

{:with [[:query {:select [:foo] :from [:bar]} :cycle [:a :b :c] :set :d :to [:abs :e] :default 42 :using :x]]}
;; produces:
["WITH query AS (SELECT foo FROM bar) CYCLE a, b, c SET d TO ABS(e) DEFAULT ? USING x" 42]))