Skip to content

Custom queries

The beam-core library and respective backends strive to expose the full power of each underlying database. If a particular feature is missing, please feel free to file a bug report on the GitHub issue tracker.

However, in the meantime, beam offers a few options to inject raw SQL into your queries. Of course, beam cannot predict types of expressions and queries that were not created with its combinators, so caveat emptor.

Custom expressions

If you'd like to write an expression that beam currently does not support, you can use the customExpr_ function. Your backend's syntax must implement the IsSqlCustomExpressionSyntax type class. customExpr_ takes a function of arity n and n arguments, which must all be QGenExprs with the same thread parameter. The expressions may be from different contexts (i.e., you can pass an aggregate and scalar into the same customExpr_).

The function supplied must return a string-like expression that it can build using provided IsString and Monoid instances. The type of the expression is opaque to the user. The function's arguments will have the same type as the return type. Thus, they can be embedded into the returned expression using mappend. The arguments will be properly parenthesized and can be inserted whole into the final expression. You will likely need to explicitly supply a result type using the as_ function.

For example, below, we use customExpr_ to access the regr_intercept and regr_slope functions in postgres.

aggregate_ (\t -> ( as_ @Double @QAggregateContext $ customExpr_ (\bytes ms -> "regr_intercept(" <> bytes <> ", " <> ms <> ")") (trackBytes t) (trackMilliseconds t)
                  , as_ @Double @QAggregateContext $ customExpr_ (\bytes ms -> "regr_slope(" <> bytes <> ", " <> ms <> ")") (trackBytes t) (trackMilliseconds t) )) $
all_ (track chinookDb)
SELECT regr_intercept(("t0"."Bytes"), ("t0"."Milliseconds")) AS "res0",
       regr_slope(("t0"."Bytes"), ("t0"."Milliseconds")) AS "res1"
FROM "Track" AS "t0"


Custom queries (i.e., embedding arbitrary expressions into Q) is currently being planned, but not implemented.