Public Vars

Back

expr (clj)

(source)

function

(expr & args)
Like `distinct` but produces ... (i.e., just the expression that follows).

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 expr-tests
  ;; special-cased = nil:
  (is (= ["id IS NULL"]
         (sut/format-expr [:= :id nil])))
  (is (= ["id IS NULL"]
         (sut/format-expr [:is :id nil])))
  (is (= ["id = TRUE"]
         (sut/format-expr [:= :id true])))
  (is (= ["id IS TRUE"]
         (sut/format-expr [:is :id true])))
  (is (= ["id <> TRUE"]
         (sut/format-expr [:<> :id true])))
  (is (= ["id IS NOT TRUE"]
         (sut/format-expr [:is-not :id true])))
  (is (= ["id = FALSE"]
         (sut/format-expr [:= :id false])))
  (is (= ["id IS FALSE"]
         (sut/format-expr [:is :id false])))
  (is (= ["id <> FALSE"]
         (sut/format-expr [:<> :id false])))
  (is (= ["id IS NOT FALSE"]
         (sut/format-expr [:is-not :id false])))
  ;; special-cased <> nil:
  (is (= ["id IS NOT NULL"]
         (sut/format-expr [:<> :id nil])))
  ;; legacy alias:
  (is (= ["id IS NOT NULL"]
         (sut/format-expr [:!= :id nil])))
  ;; legacy alias:
  (is (= ["id IS NOT NULL"]
         (sut/format-expr [:not= :id nil])))
  (is (= ["id IS NOT NULL"]
         (sut/format-expr [:is-not :id nil])))
  ;; degenerate (special) cases:
  (is (= ["NULL IS NULL"]
         (sut/format-expr [:= nil nil])))
  (is (= ["NULL IS NOT NULL"]
         (sut/format-expr [:<> nil nil])))
  (is (= ["id = ?" 1]
         (sut/format-expr [:= :id 1])))
  (is (= ["id + ?" 1]
         (sut/format-expr [:+ :id 1])))
  (is (= ["? + (? + quux)" 1 1]
         (sut/format-expr [:+ 1 [:+ 1 :quux]])))
  (is (= ["? + ? + quux" 1 1]
         (sut/format-expr [:+ 1 1 :quux])))
  (is (= ["FOO(BAR(? + G(abc)), F(?, quux))" 2 1]
         (sut/format-expr [:foo [:bar [:+ 2 [:g :abc]]] [:f 1 :quux]])))
  (is (= ["id"]
         (sut/format-expr :id)))
  (is (= ["?" 1]
         (sut/format-expr 1)))
  (is (= ["INTERVAL ? DAYS" 30]
         (sut/format-expr [:interval 30 :days]))))

(deftest issue-486-interval
  (is (= ["INTERVAL '30 Days'"]
         (sut/format-expr [:interval "30 Days"]))))

(deftest where-test
  (is (= ["WHERE id = ?" 1]
         (#'sut/format-on-expr :where [:= :id 1]))))

(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"]))))

(deftest union-test
  ;; UNION and INTERSECT subexpressions should not be parenthesized.
  ;; If you need to add more complex expressions, use a subquery like this:
  ;;   SELECT foo FROM bar1
  ;;   UNION
  ;;   SELECT foo FROM (SELECT foo FROM bar2 ORDER BY baz LIMIT 2)
  ;;   ORDER BY foo ASC
  (is (= (format {:union [{:select [:foo] :from [:bar1]}
                          {:select [:foo] :from [:bar2]}]})
         ["SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))

(deftest compare-expressions-test
  (testing "Sequences should be fns when in value/comparison spots"
    (is (= ["SELECT foo FROM bar WHERE (col1 MOD ?) = (col2 + ?)" 4 4]
           (format {:select [:foo]
                    :from [:bar]
                    :where [:= [:mod :col1 4] [:+ :col2 4]]}))))

(deftest quoting-:%-syntax
  (testing "quoting of expressions in functions shouldn't depend on syntax"
    (is (= ["SELECT SYSDATE()"]
           (format {:select [[[:sysdate]]]})
           (format {:select :%sysdate})))
    (is (= ["SELECT COUNT(*)"]
           (format {:select [[[:count :*]]]})
           (format {:select :%count.*})))
    (is (= ["SELECT AVERAGE(`foo-foo`)"]
           (format {:select [[[:average :foo-foo]]]} :dialect :mysql)
           (format {:select :%average.foo-foo} :dialect :mysql)))
    (is (= ["SELECT GREATER(`foo-foo`, `bar-bar`)"]
           (format {:select [[[:greater :foo-foo :bar-bar]]]} :dialect :mysql)
           (format {:select :%greater.foo-foo.bar-bar} :dialect :mysql)))
    (is (= ["SELECT MIXED_KEBAB(`yum-yum`)"]
           (format {:select :%mixed-kebab.yum-yum} :dialect :mysql)))
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`)"]
           (format {:select :%mixed-kebab.yum-yum} :dialect :mysql :quoted-snake true)))
    ;; qualifier is always - -> _ converted:
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`.`bar-bar`, `a_b`.`c-d`)"]
           (format {:select (keyword "%mixed-kebab.yum-yum/bar-bar.a-b/c-d")} :dialect :mysql)))
    ;; name is only - -> _ converted when snake_case requested:
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`.`bar_bar`, `a_b`.`c_d`)"]
           (format {:select (keyword "%mixed-kebab.yum-yum/bar-bar.a-b/c-d")} :dialect :mysql :quoted-snake true)))
    (is (= ["SELECT RANSOM(`NoTe`)"]
           (format {:select [[[:ransom :NoTe]]]} :dialect :mysql)
           (format {:select :%ransom.NoTe} :dialect :mysql))))
  (testing "issue 352: literal function calls"
    (is (= ["SELECT sysdate()"]
           (format {:select [[[:'sysdate]]]})))
    (is (= ["SELECT count(*)"]
           (format {:select [[[:'count :*]]]})))
    (is (= ["SELECT Mixed_Kebab(yum_yum)"]
           (format {:select [[[:'Mixed-Kebab :yum-yum]]]})))
    (is (= ["SELECT `Mixed-Kebab`(`yum-yum`)"]
           (format {:select [[[:'Mixed-Kebab :yum-yum]]]} :dialect :mysql)))
    (is (= ["SELECT other_project.other_dataset.other_function(?, ?)" 1 2]
           (format {:select [[[:'other-project.other_dataset.other_function 1 2]]]})))
    (is (= ["SELECT \"other-project\".\"other_dataset\".\"other_function\"(?, ?)" 1 2]
           (format {:select [[[:'other-project.other_dataset.other_function 1 2]]]} :dialect :ansi)))))

(deftest issue-456-format-expr
  (is (= ["`x` + ?" 1]
         (sut/format [:+ :x 1] {:dialect :mysql}))))
honeysql
(ns honey.sql.postgres-test
  (:refer-clojure :exclude [update partition-by set])
  (:require [clojure.test :refer [deftest is testing]]
            ;; pull in all the PostgreSQL helpers that the nilenso
            ;; library provided (as well as the regular HoneySQL ones):
            [honey.sql.helpers :as sqlh :refer
             [upsert on-conflict do-nothing on-constraint
              returning do-update-set
              ;; not needed because do-update-set can do this directly
              #_do-update-set!
              alter-table rename-column drop-column
              add-column partition-by
              ;; not needed because insert-into can do this directly
              #_insert-into-as
              create-table rename-table drop-table
              window create-view over with-columns
              create-extension drop-extension
              select-distinct-on
              ;; already part of HoneySQL
              insert-into values where select
              from order-by update set]]
            [honey.sql :as sql]))

(deftest select-agg-order-by-test
  (testing "single expression in order by"
    (is (= ["SELECT ARRAY_AGG(a ORDER BY x) FROM products"])
        (sql/format
          {:select [[[:array_agg [:order-by :a :x]]]]
           :from   :products})))
  (testing "multiple expressions in order by"
    (is (= ["SELECT ARRAY_AGG(a ORDER BY x ASC, y DESC, z ASC) FROM products"]
           (sql/format
             {:select [[[:array_agg [:order-by :a [:x :asc] [:y :desc] :z]]]]
              :from   :products})))))
honeysql
(ns honey.sql.helpers-test
  (:refer-clojure :exclude [filter for group-by partition-by set update])
  (:require [clojure.test :refer [deftest is testing]]
            [honey.sql :as sql]
            [honey.sql.helpers :as h
             :refer [add-column add-index alter-table columns create-table create-table-as create-view
                     create-materialized-view drop-view drop-materialized-view
                     create-index
                     bulk-collect-into
                     cross-join do-update-set drop-column drop-index drop-table
                     filter from full-join
                     group-by having insert-into
                     join-by join lateral left-join limit offset on-conflict
                     on-duplicate-key-update
                     order-by over partition-by refresh-materialized-view
                     rename-column rename-table returning right-join
                     select select-distinct select-top select-distinct-top
                     values where window with with-columns
                     with-data within-group]]))

(deftest test-select
  (testing "large helper expression"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where [:= :example-column 0]))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where [:or
                         [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                         [:and [:< 1 2] [:< 2 3]]
                         [:in :f.e [1 [:param :param2] 3]]
                         [:between :f.e 10 20]])
                 (group-by :f.a)
                 (having [:< 0 :f.e])
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false}))))))
  (testing "large helper expression with simplified where"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where := :example-column 0))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where :or
                        [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                        [:and [:< 1 2] [:< 2 3]]
                        [:in :f.e [1 [:param :param2] 3]]
                        [:between :f.e 10 20])
                 (group-by :f.a)
                 (having :< 0 :f.e)
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false})))))))

(deftest from-expression-tests
  (testing "FROM can be a function invocation"
    (is (= ["SELECT foo, bar FROM F(?) AS x" 1]
           (sql/format {:select [:foo :bar] :from [[[:f 1] :x]]}))))
  ;; these two examples are from https://www.postgresql.org/docs/9.3/queries-table-expressions.html#QUERIES-LATERAL
  (testing "FROM can be a LATERAL select"
    (is (= ["SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) AS ss"]
           (sql/format {:select :*
                        :from [:foo
                               [[:lateral {:select :*
                                           :from :bar
                                           :where [:= :bar.id :foo.bar_id]}] :ss]]}))))
  (testing "FROM can be a LATERAL expression"
    (is (= [(str "SELECT p1.id, p2.id, v1, v2"
                 " FROM polygons AS p1, polygons AS p2,"
                 " LATERAL VERTICES(p1.poly) AS v1,"
                 " LATERAL VERTICES(p2.poly) AS v2"
                 " WHERE ((v1 <-> v2) < ?) AND (p1.id <> p2.id)") 10]
           (sql/format {:select [:p1.id :p2.id :v1 :v2]
                        :from [[:polygons :p1] [:polygons :p2]
                               [[:lateral [:vertices :p1.poly]] :v1]
                               [[:lateral [:vertices :p2.poly]] :v2]]
                        :where [:and [:< [:<-> :v1 :v2] 10] [:!= :p1.id :p2.id]]})))
    (is (= [(str "SELECT m.name"
                 " FROM manufacturers AS m"
                 " LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE"
                 " WHERE pname IS NULL")]
           (sql/format {:select :m.name
                        :from [[:manufacturers :m]]
                        :left-join [[[:lateral [:get_product_names :m.id]] :pname] true]
                        :where [:= :pname nil]})))))

(deftest test-create-index
  (testing "create index, commonly supported features"
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format {:create-index [:my-column-idx [:my-table :my-column]]})))
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format (create-index :my-column-idx [:my-table :my-column]))))
    (is (= ["CREATE UNIQUE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:unique :my-column-idx] [:my-table :my-column]))))
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column, my_other_column)"]
           (sql/format (create-index :my-column-idx [:my-table :my-column :my-other-column])))))
  (testing "PostgreSQL extensions (IF NOT EXISTS and expressions)"
    (is (= ["CREATE INDEX IF NOT EXISTS my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:my-column-idx :if-not-exists] [:my-table :my-column]))))
    (is (= ["CREATE UNIQUE INDEX IF NOT EXISTS my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:unique :my-column-idx :if-not-exists] [:my-table :my-column]))))
    (is (= ["CREATE INDEX my_column_idx ON my_table (LOWER(my_column))"]
           (sql/format (create-index :my-column-idx [:my-table :%lower.my-column]))))))