enable jet.Windows passed in RawArgs to Raw expressions?

[sreuland]

Hello I'm using some of the Raw Expressions to use custom functions of postgres that aren't mapped in gojet yet, such as percentile_disc and within group, I'm running into limitation of using the Raw format, where I have within group in raw, but it needs to contain a standard ORDER BY clause inside it, there's no way for me to substitute the jet.ORDER_BY window into the Raw string via RawArgs, could that be enabled as a feature?

would look something like:

args := jet.RawArgs {
   	    "PERCENTILE": percentile, 
   	    "ORDER_BY": jet.ORDER_BY(table.MyTable.MyCol1.DIV(table.MyTable.MyCol2))}
projection := jet.CAST(jet.CEIL(jet.RawFloat("percentile_disc(0.:PERCENTILE) WITHIN GROUP (:ORDER_BY)", args))).AS_BIGINT().AS("MyProjectedColumn")  

Otherwise, have to resort to hardcoding the full raw snippet including the ORDER BY clause and its table/column references, which defeats the purpose of using the lib. Is it possible to achieve this any other ways?

[go-jet]

Hello, try using this method, until those aggregate functions are added.

func PERCENTILE_DISC(fraction float64, withinGroup Column) Expression {
    raw := fmt.Sprintf("PERCENTILE_DISC(#fraction) WITHIN GROUP (ORDER BY %s.%s)", withinGroup.TableName(), withinGroup.Name())
    return Raw(raw, RawArgs{"#fraction": fraction})
}

[sreuland]

Hello, thanks for alternative suggestion, it won't scale well into some of the more intricate uses cases with different expressions in the order by, like having to represent a quotient from two db columns etc, will end up with several variations of this snippet for each form, would like to avoid that from onset.

If that can be accomplished through Raw substitution would be one way to resolve, or if there was way for clients to implement their own expressions in a given dialect, we could attempt to implement our own expressions with sql builders for specific custom dialect functions we unfortunately have to deal with that aren't mapped yet, like PERCENTILE_DISC and WITHIN_GROUP. Thanks for quick response, much appreciated!

[go-jet]

Expression serialize method is not exposed(for safety reasons), so I don't see alternative. You'll have to hard code expression part into new functions.
There is a Func helper you can use to implement missing functions, but in this case it wont work because PERCENTILE_DISC is complex function of two parts.

[go-jet]

You can now find postgres PERCENTILE_DISC implementation on aggregate-func branch(the branch is in sync with master). Sample usage can be found here.

[sreuland]

Looks great, I will try out the branch, thanks for adding that support.

[sreuland]

confirmed using aggregate-func branch, changes for PERCENTILE_DISC and WITHIN_GROUP worked, thanks!

[go-jet]

Order set aggregate functions added in Release 2.7.0.
Sample usage:

jet/tests/postgres/chinook_db_test.go

Lines 748 to 758 in 3e802f8

	stmt := SELECT(
	PERCENTILE_DISC(Float(0.1)).WITHIN_GROUP_ORDER_BY(Invoice.InvoiceId).AS("percentile_disc_1"),
	PERCENTILE_DISC(Invoice.Total.DIV(Float(100))).WITHIN_GROUP_ORDER_BY(Invoice.InvoiceDate.ASC()).AS("percentile_disc_2"),
	PERCENTILE_DISC(RawFloat("(select array_agg(s) from generate_series(0, 1, 0.2) as s)")).
	WITHIN_GROUP_ORDER_BY(Invoice.BillingAddress.DESC()).AS("percentile_disc_3"),
	PERCENTILE_CONT(Float(0.3)).WITHIN_GROUP_ORDER_BY(Invoice.Total).AS("percentile_cont_1"),
	PERCENTILE_CONT(Float(0.2)).WITHIN_GROUP_ORDER_BY(INTERVAL(1, HOUR).DESC()).AS("percentile_cont_int"),
	MODE().WITHIN_GROUP_ORDER_BY(Invoice.BillingPostalCode.DESC()).AS("mode_1"),
	).FROM(