`lag` and `lead` function support

[rinn7e]

I want to do a select as the following:

SELECT
    LAG(a.id, 1) OVER (
        ORDER BY a.sent_at DESC
    ) previous_a,
    lead(a.id, 1) OVER (
        ORDER BY a.sent_at DESC
    ) next_a
FROM
...

Since esqueleto doesn't have lag and lead yet, I want to create it using raw sql, but I believe using unsafeSqlFuntion doesn't work due to having over clause.

Hope someone could point me to the right direction.

[rinn7e]

Found a work around, inspired from unsafeSqlAggFunction:

unsafeSqlWindowFunction
    :: UnsafeSqlFunctionArgument a
    => TLB.Builder
    -> a
    -> [OrderByClause]
    -> SqlExpr (Value b)
unsafeSqlWindowFunction name args orderByClauses = ERaw noMeta $ \_ info ->
    let (orderTLB, orderVals) = makeOrderByNoNewline info orderByClauses
        (argsTLB, argsVals) =
            uncommas' $ map (\(ERaw _ f) -> f Never info) $ toArgList args

    in ( name <> parens (argsTLB) <> " OVER " <> parens(orderTLB)
       , argsVals <> orderVals
       )

lag :: SqlExpr (Value a) -> SqlExpr (Value Int64) -> [OrderByClause] -> SqlExpr (Value (Maybe a))
lag fieldArg offsetArg clause = unsafeSqlWindowFunction "lag" (fieldArg, offsetArg) clause

lead :: SqlExpr (Value a) -> SqlExpr (Value Int64) -> [OrderByClause] -> SqlExpr (Value (Maybe a))
lead fieldArg offsetArg clause = unsafeSqlWindowFunction "lead" (fieldArg, offsetArg) clause