First stab at a WITH RECURSIVE construct for ClojureQL

gistfile1.clj

;; http://www.postgresql.org/docs/9.0/static/queries-with.html


(defmacro with-recursive [rectable cols [start-relation union step-relation] body-relation]
  ;; store info in meta data, since compile doesn't dispatch on relation type
  `(let [start# ~start-relation
         ~rectable (project (table ~(keyword (name rectable)))
                            ~cols)
         body# ~body-relation
         step# ~step-relation
         rec-steps# (:with-recursive (meta body#))]
     (with-meta body#
       {:with-recursive
        (cons {:start start#
               :step step#
               :rectbl ~rectable
               :all ~(= union :all)}
              rec-steps#)})))

;; a possible approach to implement this properly could be to have compile dispatch on [(type tble) (:dialect tble)]
(defmethod clojureql.core/compile :recursive [tble db]
  (loop [[{:keys [start step rectbl all]} & rest] (seq (:with-recursive (meta tble)))
         [fmt & _ :as sql] ["%s"]]
    (letfn [(merge-sql [body-str & param-groups]
                       (into (assoc sql 0 (format fmt body-str))
                             (flatten
                              (remove nil? param-groups))))]
      (if (and start step rectbl)
        (let [[start-sql & start-params] (compile start nil)
              [step-sql & step-params] (compile step nil)
              stmt (format "WITH RECURSIVE %s(%s)AS(%s UNION%s %s) %%s"
                           (name (:tname rectbl))
                           (apply str
                                  (interpose
                                   \, 
                                   (map name (:tcols rectbl))))
                           start-sql
                           (if all " ALL" "")
                           step-sql)]
          (recur rest
                 (merge-sql stmt start-params step-params)))
        (apply merge-sql (compile tble nil))))))

;;;;;;;;;;; EXAMPLE USAGE
(compile
 (with-recursive temp [:col1 :col2]
   [(project (table :start) [:col1 :col2])
    :union
    (join
     (project (table :step) [:col1 :col2])
     temp :col1)]
   (project temp [:col1 :col2]))
 {:dialect :recursive})

; -> WITH RECURSIVE temp(col1,col2)AS(SELECT start.col1,start.col2 FROM start UNION SELECT step.col1,step.col2,temp.col1,temp.col2 FROM step JOIN temp USING(col1)) SELECT temp.col1,temp.col2 FROM temp