>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
transformRangeSubselect().
Doc patch and simple regression tests included.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 211e4c3..f2d21aa 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -51,7 +51,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
[ TABLESAMPLE <replaceable class="parameter">sampling_method</replaceable> ( <replaceable class="parameter">argument</replaceable> [, ...] ) [ REPEATABLE ( <replaceable class="parameter">seed</replaceable> ) ] ]
- [ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
+ [ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
<replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
@@ -408,8 +408,7 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
output were created as a temporary table for the duration of
this single <command>SELECT</command> command. Note that the
sub-<command>SELECT</command> must be surrounded by
- parentheses, and an alias <emphasis>must</emphasis> be
- provided for it. A
+ parentheses. An optional alias can be used to name the subquery. A
<xref linkend="sql-values"> command
can also be used here.
</para>
@@ -1891,6 +1890,31 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
</refsect2>
<refsect2>
+ <title>Optional subquery alias names in <literal>FROM</literal> clauses</title>
+
+ <para>
+ <productname>PostgreSQL</productname> allows one to omit
+ alias names for subqueries used in <literal>FROM</literal>-clauses, which
+ are required in the SQL standard. Thus, the following SQL is valid in PostgreSQL:
+<programlisting>
+SELECT * FROM (VALUES(1), (2), (3));
+ column1
+---------
+ 1
+ 2
+ 3
+(3 rows)
+
+SELECT * FROM (SELECT 1, 2, 3);
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ 1 | 2 | 3
+(1 row)
+</programlisting>
+ </para>
+ </refsect2>
+
+ <refsect2>
<title><literal>ONLY</literal> and Inheritance</title>
<para>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6c6d21b..865b3ce 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11302,30 +11302,13 @@ table_ref: relation_expr opt_alias_clause
/*
* 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".
+ * but PostgreSQL isn't that strict here. We
+ * provide an unique, auto-generated alias
+ * name instead, which will be done through
+ * the transform/analyze phase later. See
+ * parse_clause.c, transformRangeSubselect() for
+ * details.
*/
- if ($2 == NULL)
- {
- if (IsA($1, SelectStmt) &&
- ((SelectStmt *) $1)->valuesLists)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("VALUES in FROM must have an alias"),
- errhint("For example, FROM (VALUES ...) [AS] foo."),
- parser_errposition(@1)));
- else
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("subquery in FROM must have an alias"),
- errhint("For example, FROM (SELECT ...) [AS] foo."),
- parser_errposition(@1)));
- }
$$ = (Node *) n;
}
| LATERAL_P select_with_parens opt_alias_clause
@@ -11335,22 +11318,6 @@ table_ref: relation_expr opt_alias_clause
n->subquery = $2;
n->alias = $3;
/* same comment as above */
- if ($3 == NULL)
- {
- if (IsA($2, SelectStmt) &&
- ((SelectStmt *) $2)->valuesLists)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("VALUES in FROM must have an alias"),
- errhint("For example, FROM (VALUES ...) [AS] foo."),
- parser_errposition(@2)));
- else
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("subquery in FROM must have an alias"),
- errhint("For example, FROM (SELECT ...) [AS] foo."),
- parser_errposition(@2)));
- }
$$ = (Node *) n;
}
| joined_table
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index fecc1d6..bfa422a 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -93,6 +93,7 @@ static List *addTargetToGroupList(ParseState *pstate, TargetEntry *tle,
static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Node *clause);
+static Alias *makeSubqueryAlias(SelectStmt *subquery, int rti);
/*
@@ -267,6 +268,22 @@ interpretOidsOption(List *defList, bool allowOids)
return default_with_oids;
}
+static Alias
+*makeSubqueryAlias(SelectStmt *subquery, int rti)
+{
+ char aliasname[NAMEDATALEN];
+
+ if (subquery->valuesLists != NIL) {
+ /* subquery describes SELECT ... FROM (VALUES(...)) */
+ snprintf(aliasname, NAMEDATALEN - 1, "*VALUES_%d*", rti);
+ } else {
+ snprintf(aliasname, NAMEDATALEN - 1, "*SUBQUERY_%d*", rti);
+ }
+
+ return makeAlias(aliasname, NIL);
+}
+
+
/*
* Extract all not-in-common columns from column lists of a source table
*/
@@ -441,14 +458,19 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
RangeTblEntry *rte;
/*
- * We require user to supply an alias for a subselect, per SQL92. To relax
- * this, we'd have to be prepared to gin up a unique alias for an
- * unlabeled subselect. (This is just elog, not ereport, because the
- * grammar should have enforced it already. It'd probably be better to
- * report the error here, but we don't have a good error location here.)
+ * SQL92 mandates an alias for a subselect. PostgreSQL isn't that
+ * strict here, we allow omitting the subquery alias and instead
+ * generate a unique alias for it. A subquery is named in the format
+ * "*SUBQUERY_<RTI>*", if a value expression was specified it is
+ * named in the format "*VALUES_<RTI>*". The <RTI> placeholder is
+ * the range table index it gets after transformRangeSubselect(), though
+ * we generate it on our own since it gets added to the range table
+ * at the end of this function.
*/
+ Assert(IsA(r->subquery, SelectStmt));
if (r->alias == NULL)
- elog(ERROR, "subquery in FROM must have an alias");
+ r->alias = makeSubqueryAlias((SelectStmt *)r->subquery,
+ list_length(pstate->p_rtable) + 1);
/*
* Set p_expr_kind to show this parse level is recursing to a subselect.
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index f84f8ac..f69c25d 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -456,6 +456,13 @@ select foo from (select 'xyzzy',1,null) as foo;
(xyzzy,1,)
(1 row)
+-- same above, without explicit alias
+select * from (select 'xyzzy',1,null);
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ xyzzy | 1 |
+(1 row)
+
--
-- Test VALUES lists
--
@@ -490,6 +497,16 @@ select * from onek
99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
(3 rows)
+-- try VALUES without an explicit alias
+select * from (values (1,1), (20,0), (99,9), (17,99));
+ column1 | column2
+---------+---------
+ 1 | 1
+ 20 | 0
+ 99 | 9
+ 17 | 99
+(4 rows)
+
-- VALUES is also legal as a standalone query or a set-operation member
VALUES (1,2), (3,4+4), (7,77.7);
column1 | column2
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index abdd785..1fca8e1 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -120,6 +120,9 @@ select foo from (select 1) as foo;
select foo from (select null) as foo;
select foo from (select 'xyzzy',1,null) as foo;
+-- same above, without explicit alias
+select * from (select 'xyzzy',1,null);
+
--
-- Test VALUES lists
--
@@ -139,6 +142,9 @@ select * from onek
where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
order by unique1;
+-- try VALUES without an explicit alias
+select * from (values (1,1), (20,0), (99,9), (17,99));
+
-- VALUES is also legal as a standalone query or a set-operation member
VALUES (1,2), (3,4+4), (7,77.7);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
