"David G. Johnston" <david.g.johnston@gmail.com> writes:
> From observation PostgreSQL doesn't recognize the equivalency of the
> outer "group by substring(fileid from -1)" and the subquery expression.
Well, it would without the subselect in between. There's a code comment
in parse_agg.c about this:
* NOTE: we recognize grouping expressions in the main query, but only
* grouping Vars in subqueries. For example, this will be rejected,
* although it could be allowed:
* SELECT
* (SELECT x FROM bar where y = (foo.a + foo.b))
* FROM foo
* GROUP BY a + b;
* The difficulty is the need to account for different sublevels_up.
* This appears to require a whole custom version of equal(), which is
* way more pain than the feature seems worth.
It'd probably be possible to fix parse_agg.c if you didn't mind expending
lots of cycles on such cases. I'm not sure offhand whether there would
be implications in the planner, or what it would take to fix them if so.
regards, tom lane