Back
with-recursive (clj)
(source)function
(with-recursive & args)
Accepts one or more CTE definitions.
See the documentation for the `:with` clause.
Examples
honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(deftest test-cte
(is (= (format {:with [[:query {:select [:foo] :from [:bar]}]]})
["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :materialized]]})
["WITH query AS MATERIALIZED (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :not-materialized]]})
["WITH query AS NOT MATERIALIZED (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :unknown]]})
["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query1 {:select [:foo] :from [:bar]}]
[:query2 {:select [:bar] :from [:quux]}]]
:select [:query1.id :query2.name]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{:values [[1 2] [4 5 6]]}]]
:select [:*]
:from [:static]})
["WITH static (a, b, c) AS (VALUES (?, ?, NULL), (?, ?, ?)) SELECT * FROM static" 1 2 4 5 6]))
(testing "When the expression passed to WITH clause is a string or `ident?` the syntax of WITH clause is `with expr AS ident`"
(is (= (format
{:with [[:ts_upper_bound "2019-08-01 15:23:00"]]
:select [:*]
:from [:hits]
:where [:= :EventDate :ts_upper_bound]})
["WITH ? AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound" "2019-08-01 15:23:00"]))
(is (= (format
{:with [[:ts_upper_bound :2019-08-01]]
:select [:*]
:from [:hits]
:where [:= :EventDate :ts_upper_bound]})
["WITH 2019_08_01 AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound"])))
(testing "Mixing the syntax of WITH in the resulting clause"
(is (= (format
{:with [[:ts_upper_bound "2019-08-01 15:23:00"]
[:stuff {:select [:*]
:from [:songs]}]]
:select [:*]
:from [:hits :stuff]
:where [:= :EventDate :ts_upper_bound]})
["WITH ? AS ts_upper_bound, stuff AS (SELECT * FROM songs) SELECT * FROM hits, stuff WHERE EventDate = ts_upper_bound"
"2019-08-01 15:23:00"]))))
babashka/babashka
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(deftest test-cte
(is (= (format {:with [[:query {:select [:foo] :from [:bar]}]]})
["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query1 {:select [:foo] :from [:bar]}]
[:query2 {:select [:bar] :from [:quux]}]]
:select [:query1.id :query2.name]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{:values [[1 2] [4 5 6]]}]]
:select [:*]
:from [:static]})
["WITH static (a, b, c) AS (VALUES (?, ?, NULL), (?, ?, ?)) SELECT * FROM static" 1 2 4 5 6])))
seancorfield/honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(deftest test-cte
(is (= (format {:with [[:query {:select [:foo] :from [:bar]}]]})
["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :materialized]]})
["WITH query AS MATERIALIZED (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :not-materialized]]})
["WITH query AS NOT MATERIALIZED (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query {:select [:foo] :from [:bar]} :unknown]]})
["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query1 {:select [:foo] :from [:bar]}]
[:query2 {:select [:bar] :from [:quux]}]]
:select [:query1.id :query2.name]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{:values [[1 2] [4 5 6]]}]]
:select [:*]
:from [:static]})
["WITH static (a, b, c) AS (VALUES (?, ?, NULL), (?, ?, ?)) SELECT * FROM static" 1 2 4 5 6]))
(testing "When the expression passed to WITH clause is a string or `ident?` the syntax of WITH clause is `with expr AS ident`"
(is (= (format
{:with [[:ts_upper_bound "2019-08-01 15:23:00"]]
:select [:*]
:from [:hits]
:where [:= :EventDate :ts_upper_bound]})
["WITH ? AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound" "2019-08-01 15:23:00"]))
(is (= (format
{:with [[:ts_upper_bound :2019-08-01]]
:select [:*]
:from [:hits]
:where [:= :EventDate :ts_upper_bound]})
["WITH 2019_08_01 AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound"])))
(testing "Mixing the syntax of WITH in the resulting clause"
(is (= (format
{:with [[:ts_upper_bound "2019-08-01 15:23:00"]
[:stuff {:select [:*]
:from [:songs]}]]
:select [:*]
:from [:hits :stuff]
:where [:= :EventDate :ts_upper_bound]})
["WITH ? AS ts_upper_bound, stuff AS (SELECT * FROM songs) SELECT * FROM hits, stuff WHERE EventDate = ts_upper_bound"
"2019-08-01 15:23:00"]))))