Support [NOT] MATERIALIZED keyword for WITH queries/CTEs

[joelmccracken]

As mentioned in the esquelto docs (https://hackage.haskell.org/package/esqueleto-3.5.8.1/docs/Database-Esqueleto-Experimental-From-CommonTableExpression.html), postgres (>12) supports keywords which specifies that CTEs should be fully calculated/materialized. For us, this is a must for performance reasons.

It would be great if there was some way to do this in esquelto, so that we could use it everywhere instead of having to use sqlQQ in cases where we need to specify CTEs should be materialized.

[belevy]

Is there a reason to use a cte if you don't need the materialization? Can you not just use a Haskell variable for the subquery?

[belevy]

Sorry I misunderstood the question. Yes postgres module could have a with that specifies materialized

[joelmccracken]

Thinking about working on this soon, any advice?

[joelmccracken]

So, my initial thoughts was to add another element to CommonTableExpressionKind, like so:

data CommonTableExpressionKind
    = RecursiveCommonTableExpression
    | NormalCommonTableExpression
    | MaterializedCommonTableExpression
    deriving Eq

However, thinking this through, I realized that this has a problem: it omits the case where there are recursive AND materialized CTEs.

I guess for now I'm going to add another paraemter somewhere and make a withMaterialized and withRecursiveMaterialized functions, something like that.