search

bitemyapp / esqueleto

New home of Esqueleto, please file issues so we can get things caught up!
BSD 3-Clause "New" or "Revised" License
376 stars 108 forks source link

Support [NOT] MATERIALIZED keyword for WITH queries/CTEs #345

Open joelmccracken opened 2 years ago

joelmccracken commented 2 years ago

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 commented 1 year ago

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 commented 1 year ago

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

joelmccracken commented 1 year ago

Thinking about working on this soon, any advice?

joelmccracken commented 1 year ago

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.