2024-04-10 sql | Clojure Slack Archive

sql

k3nj1g 2024-04-10T06:28:52.316459Z

Can we write this expression {:select [:*] :from [:with_count [[:jsonb_to_record :derived] [:x [:raw "(work_days integer)"]]]]} that results to SELECT * FROM with_count AS wc, JSONB_TO_RECORD(derived) AS x (work_days integer) more natively without :raw using com.github.seancorfield/honeysql?

alpox 2024-04-10T06:43:45.130029Z

Are composite types what you are looking for? https://github.com/seancorfield/honeysql?tab=readme-ov-file#composite-types

k3nj1g 2024-04-10T06:59:15.852079Z

It almost fits. But unfortunately it sets unneded comma between field and its type.

p-himik 2024-04-10T07:37:59.719039Z

(sql/format {:select [:*]
             :from   [:with_count
                      [[:jsonb_to_record :derived]
                       [[:x :work-days :!integer]]]]})
=> ["SELECT * FROM with_count, JSONB_TO_RECORD(derived) AS X(work_days INTEGER)"]

p-himik 2024-04-10T07:38:23.835249Z

A bit of a hack, since :x is treated as a function call and hence will be using different quoting rules.

p-himik 2024-04-10T07:40:01.578089Z

Not sure if there's a better way. But you can always add your own clause, it's trivial.

k3nj1g 2024-04-10T07:44:04.658329Z

Thanks for the tip. I guess we need to update honeysql version to make it work the same.

p-himik 2024-04-10T07:45:21.151189Z

Found a better way:

(sql/format {:select [:*]
             :from   [:with_count
                      [[:jsonb_to_record :derived]
                       [:x {:with-columns [[:work-days :integer]]}]]]})
=> ["SELECT * FROM with_count, JSONB_TO_RECORD(derived) AS x (work_days INTEGER)"]

🎉 1

p-himik 2024-04-10T07:48:52.249009Z

Oh, and also - there's #honeysql.

k3nj1g 2024-04-10T07:53:41.379759Z

Oh, great! I love it, works like a charm. Thanks! Yes, I didn't notice this channel.

👍 1

Clojurians Log v2

sql