Re: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?

Tom Ellis <tom-postgresql(dot)org(at)jaguarpaw(dot)co(dot)uk> writes:
> A. Am I right in thinking that the two forms are equivalent?

No. In the first case the SUM() aggregate does not use any variables
belonging to the "LATERAL (SELECT ..." query level, therefore, per
SQL standard, it is not an aggregate of that query but an aggregate
of the next query level up, whose variable(s) it does use. And
within the structure of *that* query level, it's in the wrong place.

> A1. And am I right to presume that it's always possibly to rewrite
> more complicated examples that yield the same error to valid versions,
> just by coming up with a local name for the problematic fields?

In the particular case here, the problem is to control which query level
the aggregate is deemed to belong to. I think the issue is less about
"can I rewrite the query" and more about "do I actually understand the
semantics this is asking for", so I'd be hesitant to let a tool think
that it can rearrange things like this.

regards, tom lane