> If you know that the CTE is deterministic and has no side effects, you can just treat it like a macro. With PostgreSQL you can't really assume this. Conside

> If you know that the CTE is deterministic and has no side effects, you can just treat it like a macro. With PostgreSQL you can't really assume this. Consider this: CREATE FUNCTION stable_random() returns double language sql as $$ select random() $$ stable; Now what I have done is wrapped a volatile function in a stable function. Now the planner thinks it is deterministic and will fold it into the plan in a different way. I could further mark it as immutable and allow the planner to run it once per query even if it occurs multiple times. Now: select my_random() will be treated as fully deterministic, so select my_random() a from my_table will return a list of the same singular random number (all values of a will be the same). So how is the planner to know this? Obviously we could just let the planner decide based on function markers but then you'd run into the case where a stable function above would produce different results, while a volatile or an immutable one would produce the same result. That seems to make very little sense to me.....