Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZeCrj-00018B-0M for pgsql-docs@arkaria.postgresql.org; Tue, 22 Sep 2015 01:59:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZeCri-0004ox-3k for pgsql-docs@arkaria.postgresql.org; Tue, 22 Sep 2015 01:59:30 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZeCrL-0004RC-Ip for pgsql-docs@postgresql.org; Tue, 22 Sep 2015 01:59:07 +0000 Received: from mail-io0-x22f.google.com ([2607:f8b0:4001:c06::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZeCrE-0001Li-Da for pgsql-docs@postgresql.org; Tue, 22 Sep 2015 01:59:06 +0000 Received: by ioii196 with SMTP id i196so2620317ioi.3 for ; Mon, 21 Sep 2015 18:58:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=xEzL9i2nS76sNvUmnCWKkpkExbXsySmfPBFhJWuWxO0=; b=JRvbm42pw2bu3OtH/EwhI7sxmj9Fvz5GyfnPUoRj9Cm8MnVVEbDg1OHhnZHJXpqC1O HUVMKLnKlLKGEcyrD9wgYGVpoAaP7jSHaIISS0Hnet1KI3FbXCpYPfDDUoIUuZFnfx7M 7SS8PDKTQm6WVhlFPmBXngmDPW45AxY1KyDLNnQaPMo2RdJ3924QO74O14N8b0c4227V Df2E9UoaZR7kuaPbSfTgvzRpKK2G+8tzIeHkLtSlCwXsdGdNHUFwM4RGXreZj9mytOuR /sYyQ0MLR/3vK89mKtJ3iLElN9clRGGoZ1yGPSHhnf9pWopJN7qY8DERv6uX/hbQDZZL yxaA== MIME-Version: 1.0 X-Received: by 10.107.6.73 with SMTP id 70mr28125556iog.158.1442887138925; Mon, 21 Sep 2015 18:58:58 -0700 (PDT) Received: by 10.36.143.141 with HTTP; Mon, 21 Sep 2015 18:58:58 -0700 (PDT) In-Reply-To: References: Date: Mon, 21 Sep 2015 21:58:58 -0400 Message-ID: Subject: Re: Docs claim that "select myTable.*" wildcard won't let you assign column names From: "David G. Johnston" To: Amir Rohan Cc: "pgsql-docs@postgresql.org" Content-Type: multipart/alternative; boundary=001a113f8cb85de29205204c5776 X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --001a113f8cb85de29205204c5776 Content-Type: text/plain; charset=UTF-8 On Monday, September 21, 2015, Amir Rohan wrote: > On Monday, September 21, 2015, Amir Rohan > wrote: > > > >> From > http://www.postgresql.org/docs/9.4/static/sql-select.html[http://www.postgresql.org/docs/9.4/static/sql->> > select.html] (and previous version too): > >> > >> ##SELECT List > >> > >> <...> > >> Instead of an expression, * can be written in the output list as a > shorthand for all the columns of the > >> selected rows. > >> Also, you can write table_name.* as a shorthand for the columns > coming from just that table. In these > >> cases it is not possible to specify new names with AS; the output > column names will be the same as the > >> table columns' names. > >> > >> But, the docs elsewhere feature a query example show the use of a > wildcard for columns > >> as well as allowing you to assign names to as many of the leading > columns as you wish: > >> > >> > >> WITH T0 as ( SELECT 1,2,3 ) > >> SELECT T0.* from T0 as T0(foo,bar) ;<...> > > On Monday, September 21, 2015, David G. Johnston wrote: > > > Neither of those examples is: > > > > SELECT * AS "how would one alias this?" FROM table > > > > So what's your point? > > My point is that "In these cases it is not possible to specify new names > with AS" is misleading because > it *is* possible and useful, but requires syntax which isn't clearly shown > (if at all) where I'd expect it. > I think that could be improved. > > > Obviously you can alias stuff before it makes its way into a select-list > that refers to it using * > > "obvious" to whom? probably not to someone who's level of SQL mastery has > brought him/her to reading > the exciting "SELECT" documentation. I do see your point though, in the > grammar the "AS" in my example > belongs not to `output_name ` but to `from_item`. So this syntax is hidden > away behind the `column_alias` > production. > > > In this case the FROM clause is what is being aliased. It is documented > though I'd need to look to > > identify the specific location. > > This belongs in the page describing SELECT, and though I've looked I > haven't found it. If I'm wrong ( > I did look again just now), please correct me. > > To be honest, if you have a situation where you think you need to alias "*" you should probably be expanding that "*" out into individual column names anyway, which you can easily alias. The docs don't make a big deal of alising other than noting individually where they can happen (cte, from clause, select-list) and few questions that are raised are probably better served to be simply handled on these lists than cluttering the docs. David J. --001a113f8cb85de29205204c5776 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Monday, September 21, 2015, Amir Rohan <amir.rohan@mail.com> wrote:
On Monday, September 21, 2015, Amir Rohan <amir.r= ohan@mail.com> wrote:


>> From h= ttp://www.postgresql.org/docs/9.4/static/sql-select.html[http://www.postgre= sql.org/docs/9.4/static/sql->> select.html] (and previous version= too):
>>
>>=C2=A0 =C2=A0##SELECT List
>>
>>=C2=A0 =C2=A0 =C2=A0<...>
>>=C2=A0 =C2=A0 =C2=A0Instead of an expression, * can be written in t= he output list as a shorthand for all the columns of the
>>=C2=A0 =C2=A0 =C2=A0selected rows.
>>=C2=A0 =C2=A0 =C2=A0Also, you can write table_name.* as a shorthand= for the columns coming from just that table. In these
>>=C2=A0 =C2=A0 =C2=A0cases it is not possible to specify new names w= ith AS; the output column names will be the same as the
>>=C2=A0 =C2=A0 =C2=A0table columns' names.
>>
>> But, the docs elsewhere feature a query example show the use of a = wildcard for columns
>> as well as allowing you to assign names to as many of the leading = columns as you wish:
>>
>>
>> WITH T0 as ( SELECT 1,2,3 )
>> SELECT T0.* from T0 as T0(foo,bar) ;<...>

On Monday, September 21, 2015, David G. Johnston wrote:

> Neither of those examples is:
>
> SELECT * AS "how would one alias this?" FROM table
>
> So what's your point?

My point is that "In these cases it is not possible to specify new nam= es with AS" is misleading because
it *is* possible and useful, but requires syntax which isn't clearly sh= own (if at all) where I'd expect it.
I think that could be improved.

> Obviously you can alias stuff before it makes its way into a select-li= st that refers to it using *

"obvious" to whom? probably not to someone who's level of SQL= mastery has brought him/her to reading
the exciting "SELECT" documentation. I do see your point though, = in the grammar the "AS" in my example
belongs not to `output_name ` but to `from_item`. So this syntax is hidden = away behind the `column_alias`
production.

> In this case the FROM clause is what is being aliased.=C2=A0 It is doc= umented though I'd need to look to
> identify the specific location.

This belongs in the page describing SELECT, and though I've looked I ha= ven't found it. If I'm wrong (
I did look again just now), please correct me.


To be honest, if you have a situation wher= e you think you need to alias "*" you should probably be expandin= g that "*" out into individual column names anyway, which you can= easily alias.

The docs don't make a big deal of al= ising other than noting individually where they can happen (cte, from claus= e, select-list) and few questions that are raised are probably better serve= d to be simply handled on these lists than cluttering the docs.
David J.=C2=A0
--001a113f8cb85de29205204c5776--