The literal SQL needs double-quotes in it?

Can you link me to some sql/db docs that explain what that syntax means? I've never seen anything like it...

Double quotes are just escaping of record. That syntax is record expansion in PostgreSQL. For example, you have to use it here to get a proper table instead of a column of records:

SELECT (v).*
FROM (SELECT v, row_number() OVER () rn FROM (VALUES (1, 2), (2, 3)) v(a, b)) v2
WHERE rn = 2;

If you use just v, it'll return a column of records. If you use v.*, it'll complain with [42P01] ERROR: missing FROM-clause entry for table "v".

The docs mention it here: https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE > PostgreSQL will apply this expansion behavior to any composite-valued expression, although as shown above, you need to write parentheses around the value that .* is applied to whenever it's not a simple table name.

Gods, I hate PostgreSQL because of stuff like this 🙂

Can you create a GH issue and I'll give it some thought... if you have any suggestions for a suitable syntax, feel free to share...

There's already :composite but it wouldn't support column selection of the result -- although maybe there's a way to expand that syntax?

[:composite :record.*] perhaps? Not sure if that would break any existing code since [:composite :record.a :record.b] is valid today and means (record.a, record.b)

(and so it wouldn't help with (record).x would it...)

> I hate PostgreSQL because of stuff like this To the best of my knowledge, every DB has its own idiosyncrasies. :) Sometimes justified, sometimes not so much.

Created: https://github.com/seancorfield/honeysql/issues/474 BTW, why does :raw require a vector? I see that it can be used with a single argument, as in [:raw "value"] but you can't use [:raw "(" "stuff" ")"], you have to wrap all those in a vector. Otherwise, "stuff" and ")" will be silently ignored.

I don't know -- I'd have to compare v1 and v2 to see why I chose to do it that way...

How would something like this be:

user=> (sql/register-fn! :project (fn [_ [expr col]] (let [[sql & params] (sql/format-expr expr)] (into [(str "(" sql ")." (sql/format-entity col))] params))))
...
user=> (sql/format {:select [[[:project :v :*]]]})
["SELECT (v).*"]
user=> (sql/format {:select [[[:project :v :*]]]} {:quoted true})
["SELECT (\"v\").*"]
user=> (sql/format {:select [[[:project :v :a]]]} {:quoted true})
["SELECT (\"v\").\"a\""]
user=>

Seems alright!

user=> (sql/format {:select [[[:project [:myfunc :b] :a]]]} {:quoted true})
["SELECT (MYFUNC(\"b\")).\"a\""]

I'm not in love with the name so suggestions welcome...

Maybe :field since it selects a field (or *) from a composite?

:field feels better. As an alternative, maybe it should be a combination of :nest and e.g. :dot. :dot is actually useful by itself to some extent, at least for me because I used to use qualify from v1 and now I have it in my own code (dynamic field names).

So [:dot [:nest [:myfunc :b]] :a].

Oh, dots are actually "legal but not documented" characters in keywords, so maybe even :.. :D

Hmm... can you explain what your use of :dot would be (without :nest)?

[:dot table-name :some-field] or [:dot :my-table some-field]. E.g. suppose a user orders a table in the UI. The column name is sent as is to the backend and then used with :dot to add it to :order-by. It's not always that you can use the column name as is because you can potentially have multiple tables used in the same query that have the same column.

(Needless to say, I was kinda surprised that qualify was removed in v2.)

Ah, OK. That's reasonable.

So... :. or '. seems like the obvious compact syntax -- datalog style '{select (((. (nest (myfunc b)) a)))}

Yeah!

I'll update the ticket...