sql 2023-02-02 | Slack Archive

Cam Saul01:02:08

Honey SQL 2 question: Is this intentional behavior? If you have a [expression alias] form inside a something like select and the alias itself is an [:fn & args] expression then it basically gets flattened out and compiled as if they were individual args. Simple example:

(sql/format {:select [[[:raw "A.B"] [:raw "C"]]]})
=> ["SELECT A.B AS \"raw\" ?" "C"]

You can make it work if you wrap the alias expression in another vector, e.g. [expression [alias]]

(sql/format {:select [[[:raw "A.B"] [[:raw "C"]]]]})
=> ["SELECT A.B AS C"]

I'll open a GH issue if this is a bug but I wanted to double check first

seancorfield04:02:04

Not a bug. Expected behavior.

Cam Saul04:02:19

gotcha, thanks!

Cam Saul03:02:20

Another Honey SQL question: In Honey SQL 1, honeysql.core/format worked with either arbitrary expressions or with query maps:

(honeysql.core/format (honeysql.core/call :+ 1 2))
=>
["(1 + 2)"]

However with Honey SQL 2 that doesn't work. There is honey.sql/format-expr, but it doesn't accept any options like :dialect or :quoted that you can pass to honey.sql/format. Anyone else running into this problem? My use case is that I have a some functions returning Honey SQL and tests they compile the way I'd expect

Cam Saul03:02:58

I hacked around it for now by manually binding things like sql/*dialect* and calling sql/format-expr, but it seems a little icky, since sql/*dialect* is private. Luckily binding doesn't care if things are private or not.

seancorfield04:02:51

When I'm testing expressions, I usually wrap them in a where clause for format. But it's an interesting use case and I can see an argument for a format-expr with an expression and options. Can you create a GH issue?

Cam Saul04:02:07

Sure, no problem. Thanks!

Cam Saul04:02:47

https://github.com/seancorfield/honeysql/issues/456

seancorfield06:02:50

Pushed a fix for it. If it seems to work for you via a git dep, I'll cut a new release.

stopa17:02:01

Hey team, honeysql question: Is there a way I can use the “jsonb” access functions, like ->>: I’m writing a query that looks something like:

WITH match AS (
  SELECT * FROM items WHERE e = '9a70439e-33c0-4b34-91f5-efac20b58301'
) SELECT * FROM items JOIN match ON (match.v ->> 0)::uuid = items.e;

Having some trouble figuring out how to do (match.v ->> 0)::uuid (playground for reference: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/6944)

seancorfield17:02:18

Does https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.969/doc/getting-started/postgresql-support#operators-with---and- help? (and the pg-ops ns it references)

stopa18:02:05

Ah! Does the trick, thank you @U04V70XH6

2023-02-02

Channels