Clojurians Log v2

@ben.hammond has joined the channel

I have a big query that generates a bunch of transaction-only temp tables and then queries them

I can make this work as - an execute statement to generate 5 temp tablels - folllowed by a query of those tables

I can't make it work in a single query

I think I'd rather have 5 execute queries, each generating one temp table

if I cannot roll the whole thing into one hugsql db-fn

so what I've done is create a bunch of distinct :command!s and a single :query? thus

...
-- :name create-stage1-temptable :!
CREATE TEMP TABLE stage1_temp ON COMMIT DROP
...
-- :name create-stage2-temptable :!
-- :name create-stage3-temptable :!
-- :name create-stage4-temptable :!
-- :name select-stuff-from-temptables :? :*

(hugsql/def-db-fns "sql/multistage-query.sql" {:quoting :ansi})

(defn multistage-query
  [db customer-id]
  (let [data {:customer_id customer-id}]
    (jdbc/with-db-transaction
     [trans db]
      (create-stage1-temptable trans data)
      (create-stage2-temptable trans data)
      (create-stage3-temptable trans data)
      (create-stage4-temptable trans data)
      (select-stuff-from-temptables trans data))))

Sounds like a good approach to me.

so far so good