Skip to content

Instantly share code, notes, and snippets.

@bendlas
Created January 3, 2011 11:57
Show Gist options
  • Save bendlas/763393 to your computer and use it in GitHub Desktop.
Save bendlas/763393 to your computer and use it in GitHub Desktop.
First stab at a WITH RECURSIVE construct for ClojureQL
;; 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment