Public Vars

Back

returning (clj)

(source)

function

(returning & cols)
Accepts any number of column names to return from an insert operation: (returning :*) and (returning :a :b) Produce: RETURNING * and RETURNING a, b respectively.

Examples

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 upsert-test
  (testing "upsert sql generation for postgresql"
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               (do-update-set :dname)
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (upsert (-> (on-conflict :did)
                           (do-update-set :dname)))
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (on-conflict :did)
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (upsert (-> (on-conflict :did)
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with both name and clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with multiple names and a clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did :dname (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; almost identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               ;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
               (upsert (-> (on-conflict (on-constraint :distributors_pkey))
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
           (sql/format (-> (insert-into :foo)
                           (values [{:id 1 :data 42}])
                           (upsert (-> (on-conflict :id)
                                       (do-update-set [:state "enabled"]
                                                      [:modified [:now]])
                                       (where [:= :state "disabled"])))))))
    (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
           (sql/format (-> (insert-into :foo)
                           (values [{:id 1 :data 42}])
                           (upsert (-> (on-conflict :id)
                                       (do-update-set {:state "enabled"
                                                       :modified [:now]})
                                       (where [:= :state "disabled"])))))))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
           (sql/format {:insert-into :distributors
                        :values [{:did 10 :dname "Pinp Design"}
                                 {:did 11 :dname "Foo Bar Works"}]
                        ;; in nilenso, these two were a submap under :upsert
                        :on-conflict :did
                        :do-update-set :dname})))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
           (-> (insert-into :distributors)
               (values [{:did 23 :dname "Foo Distributors"}])
               (on-conflict :did)
               ;; nilenso:
               #_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
               ;; honeysql
               (do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
           ;; honeysql version:
           (-> (insert-into :distributors
                            [:did :dname]
                            (select 1 "whatever"))
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)
           ;; nilenso version:
           #_(-> (insert-into :distributors)
                 (columns :did :dname)
                 (query-values (select 1 "whatever"))
                 (upsert (-> (on-conflict-constraint :distributors_pkey)
                             do-nothing))
                 sql/format)))))

(deftest returning-test
  (testing "returning clause in sql generation for postgresql"
    (is (= ["DELETE FROM distributors WHERE did > 10 RETURNING *"]
           (sql/format {:delete-from :distributors
                        :where [:> :did :10]
                        :returning [:*]})))
    (is (= ["UPDATE distributors SET dname = ? WHERE did = 2 RETURNING did, dname" "Foo Bar Designs"]
           (-> (update :distributors)
               (set {:dname "Foo Bar Designs"})
               (where [:= :did :2])
               (returning :did :dname)
               sql/format)))))

(deftest insert-into-with-alias
  (testing "insert into with alias"
    (is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
           ;; honeysql supports alias in insert-into:
           (-> (insert-into :distributors :d)
               ;; nilensor required insert-into-as:
               #_(insert-into-as :distributors :d)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               ;; honeysql supports names and a where clause:
               (do-update-set :dname (where [:<> :d.zipcode "21201"]))
               ;; nilenso nested those under upsert:
               #_(upsert (-> (on-conflict :did)
                             (do-update-set :dname)
                             (where [:<> :d.zipcode "21201"])))
               (returning :d.*)
               sql/format)))))
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 issue-293-sql
  ;; these tests are based on the README at https://github.com/nilenso/honeysql-postgres
  (is (= (-> (insert-into :distributors)
             (values [{:did 5 :dname "Gizmo Transglobal"}
                      {:did 6 :dname "Associated Computing, Inc"}])
             (-> (on-conflict :did)
                 (do-update-set :dname))
             (returning :*)
             sql/format)
         [(str "INSERT INTO distributors (did, dname)"
               " VALUES (?, ?), (?, ?)"
               " ON CONFLICT (did)"
               " DO UPDATE SET dname = EXCLUDED.dname"
               " RETURNING *")
          5 "Gizmo Transglobal"
          6 "Associated Computing, Inc"]))
  (is (= (-> (insert-into :distributors)
             (values [{:did 23 :dname "Foo Distributors"}])
             (on-conflict :did)
             ;; instead of do-update-set!
             (do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :distributors.dname ")"]
                             :downer :EXCLUDED.downer})
             sql/format)
         [(str "INSERT INTO distributors (did, dname)"
               " VALUES (?, ?)"
               " ON CONFLICT (did)"
               " DO UPDATE SET dname = EXCLUDED.dname || ? || distributors.dname || ?,"
               " downer = EXCLUDED.downer")
          23 "Foo Distributors" " (formerly " ")"]))
  ;; insert into / insert into as tests are below
  (is (= (-> (select :id
                     (over [[:avg :salary] (-> (partition-by :department) (order-by :designation)) :Average]
                           [[:max :salary] :w :MaxSalary]))
             (from :employee)
             (window :w (partition-by :department))
             sql/format)
         [(str "SELECT id,"
               " AVG(salary) OVER (PARTITION BY department ORDER BY designation ASC) AS Average,"
               " MAX(salary) OVER w AS MaxSalary"
               " FROM employee"
               " WINDOW w AS (PARTITION BY department)")]))
  ;; test nil / empty window function clause:
  (is (= (-> (select :id
                     (over [[:avg :salary] {} :Average]
                           [[:max :salary] nil :MaxSalary]))
             (from :employee)
             sql/format)
         [(str "SELECT id,"
               " AVG(salary) OVER () AS Average,"
               " MAX(salary) OVER () AS MaxSalary"
               " FROM employee")])))
babashka/babashka
(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 upsert-test
  (testing "upsert sql generation for postgresql"
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               (do-update-set :dname)
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (upsert (-> (on-conflict :did)
                           (do-update-set :dname)))
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (on-conflict :did)
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (upsert (-> (on-conflict :did)
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with both name and clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with multiple names and a clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did :dname (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; almost identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               ;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
               (upsert (-> (on-conflict (on-constraint :distributors_pkey))
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
           (sql/format {:insert-into :distributors
                        :values [{:did 10 :dname "Pinp Design"}
                                 {:did 11 :dname "Foo Bar Works"}]
                        ;; in nilenso, these two were a submap under :upsert
                        :on-conflict :did
                        :do-update-set :dname})))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
           (-> (insert-into :distributors)
               (values [{:did 23 :dname "Foo Distributors"}])
               (on-conflict :did)
               ;; nilenso:
               #_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
               ;; honeysql
               (do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
           ;; honeysql version:
           (-> (insert-into :distributors
                            [:did :dname]
                            (select 1 "whatever"))
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)
           ;; nilenso version:
           #_(-> (insert-into :distributors)
                 (columns :did :dname)
                 (query-values (select 1 "whatever"))
                 (upsert (-> (on-conflict-constraint :distributors_pkey)
                             do-nothing))
                 sql/format)))))

(deftest returning-test
  (testing "returning clause in sql generation for postgresql"
    (is (= ["DELETE FROM distributors WHERE did > 10 RETURNING *"]
           (sql/format {:delete-from :distributors
                        :where [:> :did :10]
                        :returning [:*]})))
    (is (= ["UPDATE distributors SET dname = ? WHERE did = 2 RETURNING did dname" "Foo Bar Designs"]
           (-> (update :distributors)
               (set {:dname "Foo Bar Designs"})
               (where [:= :did :2])
               (returning [:did :dname])
               sql/format)))))

(deftest insert-into-with-alias
  (testing "insert into with alias"
    (is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
           ;; honeysql supports alias in insert-into:
           (-> (insert-into :distributors :d)
               ;; nilensor required insert-into-as:
               #_(insert-into-as :distributors :d)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               ;; honeysql supports names and a where clause:
               (do-update-set :dname (where [:<> :d.zipcode "21201"]))
               ;; nilenso nested those under upsert:
               #_(upsert (-> (on-conflict :did)
                             (do-update-set :dname)
                             (where [:<> :d.zipcode "21201"])))
               (returning :d.*)
               sql/format)))))
seancorfield/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 upsert-test
  (testing "upsert sql generation for postgresql"
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               (do-update-set :dname)
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (upsert (-> (on-conflict :did)
                           (do-update-set :dname)))
               (returning :*)
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (on-conflict :did)
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
           ;; identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 7 :dname "Redline GmbH"}])
               (upsert (-> (on-conflict :did)
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; preferred in honeysql:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with both name and clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; with multiple names and a clause:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               (on-conflict :did :dname (on-constraint :distributors_pkey))
               do-nothing
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
           ;; almost identical to nilenso version:
           (-> (insert-into :distributors)
               (values [{:did 9 :dname "Antwerp Design"}])
               ;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
               (upsert (-> (on-conflict (on-constraint :distributors_pkey))
                           do-nothing))
               sql/format)))
    (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
           (sql/format (-> (insert-into :foo)
                           (values [{:id 1 :data 42}])
                           (upsert (-> (on-conflict :id)
                                       (do-update-set [:state "enabled"]
                                                      [:modified [:now]])
                                       (where [:= :state "disabled"])))))))
    (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
           (sql/format (-> (insert-into :foo)
                           (values [{:id 1 :data 42}])
                           (upsert (-> (on-conflict :id)
                                       (do-update-set {:state "enabled"
                                                       :modified [:now]})
                                       (where [:= :state "disabled"])))))))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
           (sql/format {:insert-into :distributors
                        :values [{:did 10 :dname "Pinp Design"}
                                 {:did 11 :dname "Foo Bar Works"}]
                        ;; in nilenso, these two were a submap under :upsert
                        :on-conflict :did
                        :do-update-set :dname})))
    (is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
           (-> (insert-into :distributors)
               (values [{:did 23 :dname "Foo Distributors"}])
               (on-conflict :did)
               ;; nilenso:
               #_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
               ;; honeysql
               (do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
               sql/format)))
    (is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
           ;; honeysql version:
           (-> (insert-into :distributors
                            [:did :dname]
                            (select 1 "whatever"))
               (on-conflict (on-constraint :distributors_pkey))
               do-nothing
               sql/format)
           ;; nilenso version:
           #_(-> (insert-into :distributors)
                 (columns :did :dname)
                 (query-values (select 1 "whatever"))
                 (upsert (-> (on-conflict-constraint :distributors_pkey)
                             do-nothing))
                 sql/format)))))

(deftest returning-test
  (testing "returning clause in sql generation for postgresql"
    (is (= ["DELETE FROM distributors WHERE did > 10 RETURNING *"]
           (sql/format {:delete-from :distributors
                        :where [:> :did :10]
                        :returning [:*]})))
    (is (= ["UPDATE distributors SET dname = ? WHERE did = 2 RETURNING did, dname" "Foo Bar Designs"]
           (-> (update :distributors)
               (set {:dname "Foo Bar Designs"})
               (where [:= :did :2])
               (returning :did :dname)
               sql/format)))))

(deftest insert-into-with-alias
  (testing "insert into with alias"
    (is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
           ;; honeysql supports alias in insert-into:
           (-> (insert-into :distributors :d)
               ;; nilensor required insert-into-as:
               #_(insert-into-as :distributors :d)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               ;; honeysql supports names and a where clause:
               (do-update-set :dname (where [:<> :d.zipcode "21201"]))
               ;; nilenso nested those under upsert:
               #_(upsert (-> (on-conflict :did)
                             (do-update-set :dname)
                             (where [:<> :d.zipcode "21201"])))
               (returning :d.*)
               sql/format)))))