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 ( expressiontable_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
- [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
+ [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] function_name ( [ argument [, ...] ] )
[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
@@ -408,8 +408,7 @@ TABLE [ ONLY ] table_name [ * ]
output were created as a temporary table for the duration of
this single SELECT command. Note that the
sub-SELECT must be surrounded by
- parentheses, and an alias must be
- provided for it. A
+ parentheses. An optional alias can be used to name the subquery. A
command
can also be used here.
@@ -1891,6 +1890,31 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
+ Optional subquery alias names in <literal>FROM</literal> clauses
+
+
+ PostgreSQL allows one to omit
+ alias names for subqueries used in FROM-clauses, which
+ are required in the SQL standard. Thus, the following SQL is valid in PostgreSQL:
+
+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)
+
+
+
+
+
<literal>ONLY</literal> and Inheritance
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
* () 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_*", if a value expression was specified it is
+ * named in the format "*VALUES_*". The 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);