Support for keyword arguments to functions

[kblomster]

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:

• Standard SQL SUBSTRING(extraction_string FROM starting_position [FOR length] [COLLATE collation_name])
• MySQL TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
• Postgres and SQLite ordered aggregations, such as (pg) ARRAY_AGG(a ORDER BY b DESC)
• Postgres nonsense like 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:

// first thing that comes to mind: ARRAY_AGG would take an AggregateArgs object,
// that knows how to separate the aggregated expressions from the ORDER BY clause
SELECT(
    ARRAY_AGG(
        AggregateArgs(a, b).
            ORDER_BY(c.DESC())))

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 like

jet.FUNC("ARRAY_AGG", SomeCol, jet.Raw("ORDER BY"), OtherCol.DESC())

but 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.

[go-jet]

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))
}

[kblomster]

@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.

[go-jet]

Yeah, the design is settled, so feel free to write up a PR.

[go-jet]

With the release v2.12.0 users can now build custom expressions and operators using newly exposed CustomExpression and Token functions. (wiki)