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.
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
The function supplied must return a string-like expression that it can build
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
For example, below, we use
customExpr_ to access the
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.