Fork me on GitHub
#honeysql
<
2021-03-03
>
Chris O’Donnell04:03:47

I tend to use the query data structure directly, personally. When the readme just shows how a helper is used, I usually evaluate it at the repl to see the structure of the map it returns.

borkdude12:03:04

Does / should honeysql support inlining arrays?

(def sql (sql/format (-> (h/insert-into :users)
                         ;; (sql/columns :foo)
                         (h/values [{:foo (into-array [1 2 3])}]))
                     {:inline true}))
["INSERT INTO users (foo) VALUES ([Ljava.lang.Long;@34b3d918)"]

seancorfield17:03:37

Does it work in v1 and just not in v2?

borkdude21:03:34

@seancorfield Just tried with v1, same thing

seancorfield21:03:53

There are some situations where HoneySQL expands collections and others where it assumes the JDBC library will handle the value.

seancorfield21:03:37

Oh, is this just about inlining? What should an array inline into? And is it portable across other databases?

seancorfield21:03:56

(I'm pretty sure the answer to 3) is "no")

borkdude21:03:52

There is an ANSI array standard now. I tried this:

(def array-sql2
  (sql/format {:insert-into :foo
               :values [{:foo [:raw (format "array [%s]" (str/join "," [1 2 3]))]}]}))
and that worked, but I haven't tested with e.g. postgres

borkdude21:03:04

can I do something like this?

:select [:raw "ARRAY['hello','bob','and','sally']"]
This comes back with: column :raw does not exist

rwstauner21:03:50

i think there's a (sql/raw "...")

borkdude21:03:33

Anyway, this seemed to work:

(pg/execute! db ["select ARRAY['hello','bob','and','sally'] from users limit 1"])
so I think the standard (ANSI) sql notation for arrays is ARRAY[1,2,3]. Maybe :inline could do that. More research needed probably.

seancorfield22:03:42

{:select [[[:raw ...]]]} because SELECT takes a sequence of things, and those things can be aliases (which are wrapped in a sequence) and the thing you would alias is a function call [:raw ...]

seancorfield22:03:03

That looks slightly less weird with the select helper but still a bit odd.

seancorfield22:03:16

and in v2 there's :array

borkdude22:03:25

confirming that :select [[[:raw "ARRAY['hello','bob','and','sally']"]]] works. also :select [[[:array [1 2 3]]]] works and also [:array (into-array [1 2 3])] works. TIL!

borkdude22:03:02

I'm not sure how this would compose with :inline still, e.g. when you insert values. Can you do {:values [{:foo [:array [1 2 3]]}]}?

borkdude22:03:29

(def sql (sql/format {:insert-into :foo
                      :values [{:foo [:array [1 2 3]]}]}
                     {:inline true}))
["INSERT INTO foo (foo) VALUES (ARRAY[1, 2, 3])"]

borkdude22:03:55

This lib is truly amazing ;)

❤️ 6
seancorfield22:03:44

Yeah, :inline knows how to inline basic stuff: strings, numbers, booleans, but not much else.

seancorfield22:03:42

Since [:array [1 2 3]] itself expands to ARRAY[?, ?, ?] and params, then :inline can handle it (by just inlining those numbers back into the SQL).