Make subquery alias optional in FROM clause

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Make subquery alias optional in FROM clause
Date: 2017-02-22 14:33:00
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

This also is exactly the comment in our parser about this topic:

 * The SQL spec does not permit a subselect
 * (<derived_table>) without an alias clause,
 * so we don't either.  This avoids the problem
 * of needing to invent a unique refname for it.
 * That could be surmounted if there's sufficient
 * popular demand, but for now let's just implement
 * the spec and see if anyone complains.
 * However, it does seem like a good idea to emit
 * an error message that's better than "syntax error".

So i thought i'm the one standing up for voting to relax this and
making the alias optional.

The main problem, as mentioned in the parser's comment, is to invent a
machinery to create an unique alias for each of the subquery/values
expression in the from clause. I pondered a little about it and came to
the attached result.

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

Doc patch and simple regression tests included.

Attachment Content-Type Size
subquery_alias.patch text/x-patch 9.5 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-02-22 14:35:54 Re: remove deprecated COMMENT ON RULE syntax
Previous Message Tom Lane 2017-02-22 14:32:42 Re: Replication vs. float timestamps is a disaster