On Wed, Feb 22, 2017 at 10:08:38AM -0500, Tom Lane wrote: > Bernd Helmle <maili...@oopsware.de> writes: > >> From time to time, especially during migration projects from Oracle to > > PostgreSQL, i'm faced with people questioning why the alias in the FROM > > clause for subqueries in PostgreSQL is mandatory. The default answer > > here is, the SQL standard requires it. > > Indeed. When I wrote the comment you're referring to, quite a few years > ago now, I thought that popular demand might force us to allow omitted > aliases. But the demand never materialized. At this point it seems > clear to me that there isn't really good reason to exceed the spec here. > It just encourages people to write unportable SQL code.
I suspect most users, like me, just roll their eyes, grumble, and put up with it rather than complain. It's a pain point, but tolerable enough that no one bothers to demand a change. Now that it's been done though, allow me to add my voice in favor of it! > > The patch generates an auto-alias for subqueries in the format > > *SUBQUERY_<RTI>* for subqueries and *VALUES_<RTI>* for values > > expressions. <RTI> is the range table index it gets during > > transformRangeSubselect(). > > This is not a solution, because it does nothing to avoid conflicts with > table names elsewhere in the FROM clause. If we were going to relax this > --- which, I repeat, I'm against --- we'd have to come up with something > that would thumb through the whole query and make sure what it was > generating didn't already appear somewhere else. Or else not generate > a name at all, in which case there simply wouldn't be a way to refer to > the subquery by name; I'm not sure what that might break though. On alias conflict... backtrack and retry with a new set of sub-query names. For generating the alias names all you need is a gensym-style counter. But yes, even this is tricky because you'd have to check that the conflicting alias name is one of the gensym'ed ones. Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
