Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite67867b

Browse files
committed
Allow AS to be omitted when specifying an output column name in SELECT
(or RETURNING), but only when the output name is not any SQL keyword.This seems as close as we can get to the standard's syntax without agreat deal of thrashing. Original patch by Hiroshi Saito, amended by me.
1 parentcc80f0a commite67867b

File tree

10 files changed

+119
-51
lines changed

10 files changed

+119
-51
lines changed

‎doc/src/sgml/queries.sgml

Lines changed: 24 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.44 2007/02/01 19:10:24 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.45 2008/02/15 22:17:06 tgl Exp $ -->
22

33
<chapter id="queries">
44
<title>Queries</title>
@@ -491,7 +491,7 @@ FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceab
491491
<synopsis>
492492
FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
493493
</synopsis>
494-
The <literal>AS</literal> key word is noise.
494+
The <literal>AS</literal> key word isoptionalnoise.
495495
<replaceable>alias</replaceable> can be any identifier.
496496
</para>
497497

@@ -1040,13 +1040,32 @@ SELECT a AS value, b + c AS sum FROM ...
10401040
</para>
10411041

10421042
<para>
1043-
If no output column name is specified using <literal>AS</>, the system assigns a
1044-
default name. For simple column references, this is the name of the
1045-
referenced column. For function
1043+
If no output column name is specified using <literal>AS</>,
1044+
the system assigns adefaultcolumnname. For simple column references,
1045+
this is the name of thereferenced column. For function
10461046
calls, this is the name of the function. For complex expressions,
10471047
the system will generate a generic name.
10481048
</para>
10491049

1050+
<para>
1051+
The <literal>AS</> keyword is optional, but only if the new column
1052+
name does not match any
1053+
<productname>PostgreSQL</productname> keyword (see <xref
1054+
linkend="sql-keywords-appendix">). To avoid an accidental match to
1055+
a keyword, you can double-quote the column name. For example,
1056+
<literal>VALUE</> is a keyword, so this does not work:
1057+
<programlisting>
1058+
SELECT a value, b + c AS sum FROM ...
1059+
</programlisting>
1060+
but this does:
1061+
<programlisting>
1062+
SELECT a "value", b + c AS sum FROM ...
1063+
</programlisting>
1064+
For protection against possible
1065+
future keyword additions, it is recommended that you always either
1066+
write <literal>AS</literal> or double-quote the output column name.
1067+
</para>
1068+
10501069
<note>
10511070
<para>
10521071
The naming of output columns here is different from that done in

‎doc/src/sgml/ref/delete.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.32 2007/11/28 15:42:31 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.33 2008/02/15 22:17:06 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,7 +23,7 @@ PostgreSQL documentation
2323
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2424
[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
2525
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
26-
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
26+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [[ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2727
</synopsis>
2828
</refsynopsisdiv>
2929

‎doc/src/sgml/ref/insert.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.35 2007/01/31 23:26:04 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.36 2008/02/15 22:17:06 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<synopsis>
2323
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
2424
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
25-
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
25+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [[ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2626
</synopsis>
2727
</refsynopsisdiv>
2828

‎doc/src/sgml/ref/select.sgml

Lines changed: 59 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.102 2007/11/28 15:42:31 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.103 2008/02/15 22:17:06 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,7 +21,7 @@ PostgreSQL documentation
2121
<refsynopsisdiv>
2222
<synopsis>
2323
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
24-
* | <replaceable class="parameter">expression</replaceable> [AS <replaceable class="parameter">output_name</replaceable> ] [, ...]
24+
* | <replaceable class="parameter">expression</replaceable> [[ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
2525
[ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
2626
[ WHERE <replaceable class="parameter">condition</replaceable> ]
2727
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
@@ -477,23 +477,45 @@ HAVING <replaceable class="parameter">condition</replaceable>
477477
<literal>SELECT</> and <literal>FROM</>) specifies expressions
478478
that form the output rows of the <command>SELECT</command>
479479
statement. The expressions can (and usually do) refer to columns
480-
computed in the <literal>FROM</> clause. Using the clause
481-
<literal>AS <replaceable
482-
class="parameter">output_name</replaceable></literal>, another
483-
name can be specified for an output column. This name is
484-
primarily used to label the column for display. It can also be
485-
used to refer to the column's value in <literal>ORDER BY</> and
486-
<literal>GROUP BY</> clauses, but not in the <literal>WHERE</> or
487-
<literal>HAVING</> clauses; there you must write out the
488-
expression instead.
480+
computed in the <literal>FROM</> clause.
481+
</para>
482+
483+
<para>
484+
Just as in a table, every output column of a <command>SELECT</command>
485+
has a name. In a simple <command>SELECT</command> this name is just
486+
used to label the column for display, but when the <command>SELECT</>
487+
is a sub-query of a larger query, the name is seen by the larger query
488+
as the column name of the virtual table produced by the sub-query.
489+
To specify the name to use for an output column, write
490+
<literal>AS</> <replaceable class="parameter">output_name</replaceable>
491+
after the column's expression. (You can omit <literal>AS</literal>,
492+
but only if the desired output name does not match any
493+
<productname>PostgreSQL</productname> keyword (see <xref
494+
linkend="sql-keywords-appendix">). For protection against possible
495+
future keyword additions, it is recommended that you always either
496+
write <literal>AS</literal> or double-quote the output name.)
497+
If you do not specify a column name, a name is chosen automatically
498+
by <productname>PostgreSQL</productname>. If the column's expression
499+
is a simple column reference then the chosen name is the same as that
500+
column's name; in more complex cases a generated name looking like
501+
<literal>?column<replaceable>N</>?</literal> is usually chosen.
502+
</para>
503+
504+
<para>
505+
An output column's name can be used to refer to the column's value in
506+
<literal>ORDER BY</> and <literal>GROUP BY</> clauses, but not in the
507+
<literal>WHERE</> or <literal>HAVING</> clauses; there you must write
508+
out the expression instead.
489509
</para>
490510

491511
<para>
492512
Instead of an expression, <literal>*</literal> can be written in
493513
the output list as a shorthand for all the columns of the selected
494-
rows. Also,one can write <literal><replaceable
514+
rows. Also,you can write <literal><replaceable
495515
class="parameter">table_name</replaceable>.*</literal> as a
496-
shorthand for the columns coming from just that table.
516+
shorthand for the columns coming from just that table. In these
517+
cases it is not possible to specify new names with <literal>AS</>;
518+
the output column names will be the same as the table columns' names.
497519
</para>
498520
</refsect2>
499521

@@ -661,17 +683,17 @@ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC |
661683

662684
<para>
663685
The ordinal number refers to the ordinal (left-to-right) position
664-
of theresult column. This feature makes it possible to define an
686+
of theoutput column. This feature makes it possible to define an
665687
ordering on the basis of a column that does not have a unique
666688
name. This is never absolutely necessary because it is always
667-
possible to assign a name toa result column using the
689+
possible to assign a name toan output column using the
668690
<literal>AS</> clause.
669691
</para>
670692

671693
<para>
672694
It is also possible to use arbitrary expressions in the
673695
<literal>ORDER BY</literal> clause, including columns that do not
674-
appear in the <command>SELECT</command>result list. Thus the
696+
appear in the <command>SELECT</command>output list. Thus the
675697
following statement is valid:
676698
<programlisting>
677699
SELECT name FROM distributors ORDER BY code;
@@ -684,8 +706,8 @@ SELECT name FROM distributors ORDER BY code;
684706

685707
<para>
686708
If an <literal>ORDER BY</> expression is a simple name that
687-
matches botha result column name and an input column name,
688-
<literal>ORDER BY</> will interpret it as theresult column name.
709+
matches bothan output column name and an input column name,
710+
<literal>ORDER BY</> will interpret it as theoutput column name.
689711
This is the opposite of the choice that <literal>GROUP BY</> will
690712
make in the same situation. This inconsistency is made to be
691713
compatible with the SQL standard.
@@ -1135,16 +1157,25 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
11351157
</refsect2>
11361158

11371159
<refsect2>
1138-
<title>The <literal>AS</literal> Key Word</title>
1160+
<title>Omitting the <literal>AS</literal> Key Word</title>
1161+
1162+
<para>
1163+
In the SQL standard, the optional key word <literal>AS</> can be
1164+
omitted before an output column name whenever the new column name
1165+
is a valid column name (that is, not the same as any reserved
1166+
keyword). <productname>PostgreSQL</productname> is slightly more
1167+
restrictive: <literal>AS</> is required if the new column name
1168+
matches any keyword at all, reserved or not. Recommended practice is
1169+
to use <literal>AS</> or double-quote output column names, to prevent
1170+
any possible conflict against future keyword additions.
1171+
</para>
11391172

11401173
<para>
1141-
In the SQL standard, the optional key word <literal>AS</> is just
1142-
noise and can be omitted without affecting the meaning. The
1143-
<productname>PostgreSQL</productname> parser requires this key
1144-
word when renaming output columns because the type extensibility
1145-
features lead to parsing ambiguities without it.
1146-
<literal>AS</literal> is optional in <literal>FROM</literal>
1147-
items, however.
1174+
In <literal>FROM</literal> items, both the standard and
1175+
<productname>PostgreSQL</productname> allow <literal>AS</> to
1176+
be omitted before an alias that is an unreserved keyword. But
1177+
this is impractical for output column names, because of syntactic
1178+
ambiguities.
11481179
</para>
11491180
</refsect2>
11501181

@@ -1153,15 +1184,15 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
11531184

11541185
<para>
11551186
In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
1156-
only useresult column names or numbers, while a <literal>GROUP
1187+
only useoutput column names or numbers, while a <literal>GROUP
11571188
BY</literal> clause can only use expressions based on input column
11581189
names. <productname>PostgreSQL</productname> extends each of
11591190
these clauses to allow the other choice as well (but it uses the
11601191
standard's interpretation if there is ambiguity).
11611192
<productname>PostgreSQL</productname> also allows both clauses to
11621193
specify arbitrary expressions. Note that names appearing in an
11631194
expression will always be taken as input-column names, not as
1164-
result-column names.
1195+
output-column names.
11651196
</para>
11661197

11671198
<para>

‎doc/src/sgml/ref/select_into.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.39 2007/01/09 02:14:10 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.40 2008/02/15 22:17:06 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,7 +21,7 @@ PostgreSQL documentation
2121
<refsynopsisdiv>
2222
<synopsis>
2323
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
24-
* | <replaceable class="PARAMETER">expression</replaceable> [AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
24+
* | <replaceable class="PARAMETER">expression</replaceable> [[ AS ] <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
2525
INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable>
2626
[ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
2727
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]

‎doc/src/sgml/ref/update.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.45 2007/11/28 15:42:31 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.46 2008/02/15 22:17:06 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -25,7 +25,7 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
2525
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
2626
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
2727
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
28-
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
28+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [[ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2929
</synopsis>
3030
</refsynopsisdiv>
3131

‎doc/src/sgml/sql.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.46 2007/02/16 03:50:29 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.47 2008/02/15 22:17:06 tgl Exp $ -->
22

33
<chapter id="sql-intro">
44
<title>SQL</title>
@@ -853,7 +853,7 @@ A &lt; B + 3.
853853

854854
<synopsis>
855855
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
856-
* | <replaceable class="PARAMETER">expression</replaceable> [AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
856+
* | <replaceable class="PARAMETER">expression</replaceable> [[ AS ] <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
857857
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
858858
[ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
859859
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]

‎src/backend/parser/gram.y

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.606 2008/02/07 21:07:55 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.607 2008/02/15 22:17:06 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -477,6 +477,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args)
477477
%nonassocBETWEEN
478478
%nonassocIN_P
479479
%leftPOSTFIXOP/* dummy for postfix Op rules*/
480+
%nonassocIDENT/* to support target_el without AS*/
480481
%leftOpOPERATOR/* multi-character ops and user-defined operators*/
481482
%nonassocNOTNULL
482483
%nonassocISNULL
@@ -8705,7 +8706,6 @@ target_list:
87058706
| target_list',' target_el{$$ = lappend($1,$3); }
87068707
;
87078708

8708-
/* AS is not optional because shift/red conflict with unary ops*/
87098709
target_el:a_expr AS ColLabel
87108710
{
87118711
$$ = makeNode(ResTarget);
@@ -8714,6 +8714,22 @@ target_el:a_expr AS ColLabel
87148714
$$->val = (Node *)$1;
87158715
$$->location =@1;
87168716
}
8717+
/*
8718+
* We support omitting AS only for column labels that aren't
8719+
* any known keyword. There is an ambiguity against postfix
8720+
* operators: is "a ! b" an infix expression, or a postfix
8721+
* expression and a column label? We prefer to resolve this
8722+
* as an infix expression, which we accomplish by assigning
8723+
* IDENT a precedence higher than POSTFIXOP.
8724+
*/
8725+
| a_expr IDENT
8726+
{
8727+
$$ = makeNode(ResTarget);
8728+
$$->name =$2;
8729+
$$->indirection = NIL;
8730+
$$->val = (Node *)$1;
8731+
$$->location =@1;
8732+
}
87178733
| a_expr
87188734
{
87198735
$$ = makeNode(ResTarget);

‎src/interfaces/ecpg/preproc/preproc.y

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.360 2008/02/14 14:54:48 meskes Exp $*/
1+
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.361 2008/02/15 22:17:06 tgl Exp $*/
22

33
/* Copyright comment*/
44
%{
@@ -521,8 +521,9 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
521521
%nonassocOVERLAPS
522522
%nonassocBETWEEN
523523
%nonassocIN_P
524-
%leftPOSTFIXOP/* dummy for postfix Op rules*/
525-
%leftOpOPERATOR/* multi-character ops and user-defined operators*/
524+
%leftPOSTFIXOP/* dummy for postfix Op rules*/
525+
%nonassocIDENT/* to support target_el without AS*/
526+
%leftOpOPERATOR/* multi-character ops and user-defined operators*/
526527
%nonassocNOTNULL
527528
%nonassocISNULL
528529
%nonassocISNULL_PTRUE_PFALSE_PUNKNOWN
@@ -4695,9 +4696,10 @@ target_list: target_list ',' target_el
46954696
{$$ =$1;}
46964697
;
46974698

4698-
/* AS is not optional because shift/red conflict with unary ops*/
46994699
target_el:a_exprASColLabel
47004700
{$$ = cat_str(3,$1, make_str("as"),$3); }
4701+
|a_exprIDENT
4702+
{$$ = cat_str(3,$1, make_str("as"),$2); }
47014703
|a_expr
47024704
{$$ =$1; }
47034705
|'*'

‎src/test/regress/expected/plpgsql.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2337,9 +2337,9 @@ begin
23372337
end loop;
23382338
return 5;
23392339
end;$$ language plpgsql;
2340-
ERROR: syntax error at or near "fought"
2340+
ERROR: syntax error at or near "the"
23412341
LINE 1: select I fought the law, the law won
2342-
^
2342+
^
23432343
QUERY: select I fought the law, the law won
23442344
CONTEXT: SQL statement in PL/PgSQL function "bad_sql2" near line 3
23452345
-- a RETURN expression is mandatory, except for void-returning

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp