Open kblomster opened 3 weeks ago
Hi @kblomster ,
Had a similar thought. Luckily we have everything in place and we just have to export CustomExpression func and Token type. This will allow you to write any expression that is currently not supported. For instance:
func ARRAY_AGG_ORDER_BY(a Expression, b Expression) Expression {
return CustomExpression(Token("array_agg("), a, Token("ORDER BY"), b, Token(")"))
}
or a bit simpler using Func:
func ARRAY_AGG_ORDER_BY(a Expression, b Expression) Expression {
return Func("array_agg", CustomExpression(a, Token("ORDER BY"), b))
}
@go-jet That sounds really awesome, actually! Is that planned for the near future? If the design idea is already settled I could probably write up a PR if you haven't already started implementing it.
Yeah, the design is settled, so feel free to write up a PR.
Problem
Both standard SQL and many dialects have function signatures that look something like
SOME_FUNC(arg1, arg2 KEYWORD arg3)
. To name a few specific examples, we have things like:SUBSTRING(extraction_string FROM starting_position [FOR length] [COLLATE collation_name])
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
ARRAY_AGG(a ORDER BY b DESC)
json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
In Jet, currently all of these require using a
Raw
expression, which is inconvenient. This is especially true if the expressions you want to use as arguments are anything other than column names - column names are easily accessible in a type-safe way, but there's no public API for converting an arbitrary expression to a SQL fragment string.Possible solutions
Most convenient for me as a user would be if Jet knew the signature of every builtin function in every dialect and had type-safe builders for every one. A very rough sketch of what an API like that could look like:
That's probably unreasonable though, so the next best thing would be to have some kind of support for this in
jet.FUNC
, or a variant of it that supported things like this. Currently you can't just pass the keywords as raw arguments because they get commas inserted between them, which is not the syntax we need.I'm kind of stumped at what the signature of a
jet.FUNC
that does what I want would look like, though. I'm imagining something likebut how do we tell it there shouldn't be a comma before
ORDER BY
?Alternately, maybe this could be addressed in an entirely different manner by exposing a public
ToSQL
method on expressions so we could more easily build raw expressions from component parts, but that has an entirely different set of API design headaches.