Fork me on GitHub
#honeysql
<
2023-02-28
>
stopa14:02:05

(println (hsql/format
          {:with [[:foo {:select :* :from :foo}]
                  [:bar {:select :* :from :bar}]]
           :select :* :from :bar}
          {:pretty true}))

; (out) [
; (out) WITH foo AS (SELECT * FROM foo), bar AS (SELECT * FROM bar)
; (out) SELECT *
; (out) FROM bar
; (out) ]
Hey team! I noticed that newlines don't come in in the with part of the CTE. Is there a way I could see the string in a more pretty way?

markaddleman14:02:49

I recently discovered https://github.com/vertical-blank/sql-formatter . It does a decent job of formatting SQL

❤️ 4
stopa15:02:57

This works great! Thanks @U2845S9KL

👍 2
p-himik14:02:37

Would you say that format-entity is OK to use when I need to have a table name in plain string format but still want to use keywords in the code to facilitate searching? Like in:

{:select :relispopulated
 :from   :pg-class
 :where  [:= :relname (sql/format-entity :my-table)]}

seancorfield18:02:45

Yes, anything listed in the ns docstring should be considered fair game. Be aware that format binds a lot of dynamic Vars that affect formatting and you don't get those if you call something like format-entity outside of format.

👍 2
seancorfield18:02:05

If you want full-featured, full control, I'd use format instead, which understands expressions too:

user=> (honey.sql/format :my-table)
["my_table"]
user=> (honey.sql/format :my-table {:dialect :sqlserver})
["[my-table]"]
user=>

seancorfield18:02:31

(although in all these cases, you'll get a string back and if you have [:= :relname "[my-table]"] that will turn into relname = ? unless you flag it as an :inline expression)

seancorfield18:02:22

i.e.,

user=> (honey.sql/format {:select :relispopulated
  #_=>  :from   :pg-class
  #_=>  :where  [:= :relname (honey.sql/format-entity :my-table)]})
["SELECT relispopulated FROM pg_class WHERE relname = ?" "my_table"]
user=>

seancorfield18:02:34

@U2FRKM4TW You would probably be better off using the :entity special syntax:

user=> (honey.sql/format {:select :relispopulated
  #_=>  :from   :pg-class
  #_=>  :where  [:= :relname [:entity :my-table]]})
["SELECT relispopulated FROM pg_class WHERE relname = my_table"]
user=>

p-himik19:02:08

Oh, didn't realize there's :entity, thanks!

p-himik19:02:42

Ah, wait - there should be quotes there, relname = 'my_table'.

seancorfield20:02:33

Oh... you wanted the table as a string?!? Then, yeah, format-entity is going to be your best path.

seancorfield20:02:58

(unless you need all the format bells and whistles, in which case (first (format :my-table)) 🙂

👍 2
Akiz16:02:48

Hi, I am trying to solve one problem. I am using HoneySQL and Next.jdbc. 1. Generally speaking - when you load JSON from DB, it is converted to hash-map…. 2. But when you have query like this

[[:coalesce :column1
      [:case [:= [:->> :attributes "is-required"] "true"]
       (j/write-value-as-string {:is-active true})
       :else
       (j/write-value-as-string {:is-active false})]] "new column"]
It will fail because ERROR: CASE types jsonb and text cannot be matched 3. If you update the first part of query to
[:cast :column1 :text]
4. Then it won’t fail but you will receive string and not map anymore 5. I would like to get back hashmap as I do in this example:
[[:coalesce :column1 [(j/write-value-as-string {:is-active false})]] "new-column"]
6. What am I missing? 🙂

seancorfield18:02:51

Can you explain what SQL you are trying to generate? I suspect the problem is that you are generating a (JSON) string from the Clojure data structure -- so HoneySQL sees a string, not a (JSON) data structure.

seancorfield18:02:27

You'll need to wrap the data in :lift so it is not parsed as part of the DSL:

user=> (require 'honey.sql.pg-ops)
nil
user=> (honey.sql/format {:select [[[:coalesce :column1
  #_=>       [:case [:= [:->> :attributes "is-required"] "true"]
  #_=>        [:lift {:is-active true}]
  #_=>        :else
  #_=>        [:lift {:is-active false}]]] "new column"]]})
["SELECT COALESCE(column1, CASE WHEN (attributes ->> ?) = ? THEN ? ELSE ? END) AS \"new column\"" "is-required" "true" {:is-active true} {:is-active false}]
user=>
(I doubt that's what you're trying to generate but it should be closer) edited to require the pg-ops ns so you get ->> treated correctly

Akiz18:02:52

LIFT did not help me, it seems that the “problem” is on SQL level. I mean, this generated SQL (5th step) works:

COALESCE(column1, '{"is-active": true}')
but this SQL (that I am generating) does not:
coalesce(column1,
CASE WHEN (attributes ->> 'is-required') = 'true' 
THEN '{"is-active": true}'
ELSE '{"is-active": false}'
END)
• it works once i cast JSONs in THEN and ELSE to ::jsonb. • I do not understand why SQL interprets string in first example as a JSONB but it is a different story for the second example

Akiz18:02:11

So, this is the solution (if there is nothing better ;-)) So it is not related to the honeysql / next.jdbc in the end..

[[:coalesce :column1
      [:case [:= [:->> :attributes "is-required"] "true"]
       [:cast (j/write-value-as-string {:is-active true}) :jsonb]
       :else
       [:cast (j/write-value-as-string {:is-active false}) :jsonb]]] "new column"]

seancorfield18:02:02

You could wrap the CAST around the CASE so you only have one of them...

👍 2
Akiz18:02:47

I asked ChatGPT about this one… 😅 > In PostgreSQL, the simple coalescence operator COALESCE returns the first non-null value in a list of expressions. When you use a string literal as a JSON value in a COALESCE expression, PostgreSQL implicitly casts it to a JSON value and returns it. > However, when you use the same string literal in a nested CASE expression, PostgreSQL may not be able to implicitly cast it to a JSON value. This is because the CASE expression has multiple possible return types, and PostgreSQL may not be able to determine which type to cast the string to.

seancorfield19:02:56

My experiences with ChatGPT have been less than stellar: it has very confidently told me stuff that is blatantly false and easily provable to be so. YMMV. I pretty much don't trust anything it generates.

Akiz21:02:14

Yep, that answer is not quite right. I’ve had a moment to think about it. I believe it’s because COALESCE’s arguments must all be convertible to a common data type, so it’s easy to guess what the second string should be converted to if the first element is JSON. But in the case of CASE, it’s not so easy, so it makes sense to explicitly convert it.

Jakub Holý (HolyJak)17:02:18

Hello good people! I want to write

SELECT '{"kids":[{"name":"Jo"}]}'::jsonb -> 'kids'
using Honey. Is the following really the best I can do:
(honey.sql/format {:select [[[honey.sql.pg-ops/-> [[:raw "'{\"kids\":[1,2]}'::jsonb"]] [:inline "kids"]]]]})
? Mainly I guess there is no way to avoid the :inline so Honey with [:-> :column [:inline "x"] [:inline "y"]] will always be more noisy than SQL’s column -> 'x' -> 'y' , right? 🙏 Also in SQL I can write ~ SELECT <some jsonb> -> 'kids' -> 0 ->> 'name' which in Honey becomes {:select [[[:->> [:-> <some jsonb> [:inline "kids"] [:inline 0]] [:inline "name"]]]]} .

2
Akiz18:02:40

You can not use anonymous sql params as? (honey.sql/format {:select [[[honey.sql.pg-ops/-> [[:raw "'{\"kids\":[1,2]}'::jsonb"]] "kids"]]]})

seancorfield18:02:55

@U0522TWDA Either of these close to what you need?

user=> (require 'honey.sql.pg-ops)
nil
user=> (honey.sql/format {:select [[[:-> [:cast [:lift {"kids" [{"name" "Jo"}]}] :jsonb] "kids"]]]})
["SELECT CAST(? AS JSONB) -> ?" {"kids" [{"name" "Jo"}]} "kids"]
user=> (honey.sql/format {:select [[[:-> [:cast [:lift {"kids" [{"name" "Jo"}]}] :jsonb] "kids"]]]} {:inline true})
["SELECT CAST({\"kids\" [{\"name\" \"Jo\"}]} AS JSONB) -> 'kids'"]
user=>

seancorfield18:02:59

(I suspect you don't need the cast if you pass a data structure here rather than a string?)

Jakub Holý (HolyJak)19:02:59

Sorry for being unclear, I do not care here about the jsonb input, that is just for demo, in practice it would be a :some-column. What bothers me is that I must :inline the arguments to :->, which IMO doesn't make sense (I don't believe Pg supports params at those positions).

Jakub Holý (HolyJak)19:02:04

@UBRV1HXPD when I don't wrap kids with :inline then I end up with ? in the sql

seancorfield19:02:21

@U0522TWDA OK, that makes (more) sense to me now. The ->, ->> etc operators are all generic at the moment, so they have the exact same semantics in the DSL as anything else. I could force them to inline their second/subsequent arguments if that is the case that no dialect of SQL uses them in a way that doesn't require literal SQL strings for arguments. Feel free to create an issue on GH. [:-> :col "a" "b" "c"] would then become col -> 'a' -> 'b' -> 'c'

👍 2
Akiz19:02:14

@U0522TWDA Ah, if you don’t want input parameters, you can do (hsql/format query {:inline true})

seancorfield19:02:33

Right, but that inlines everything in the query which is not always safe.

👍 2
Akiz19:02:32

Yeah, but the params at those positions should be supported by pg, no?

seancorfield19:02:39

(I was under the impression that PG actually understood JSON directly but when I read the docs it seems that it just treats certain strings as JSON instead which definitely makes using JSON with HoneySQL more painful!)

seancorfield19:02:27

@UBRV1HXPD You mean ? -> ? should be legal in PG? I would have expected so as well but apparently not?

Akiz19:02:54

Yeah, i have just tried this example, seems fine. https://www.loom.com/share/75e20bf54af343ce8e443d48e77336ab

Jakub Holý (HolyJak)20:02:16

I am sorry, you are right. I have not expected Pg to be able to do that 😅 Thank you for your support!

👍 2