can not use the column after rename

Hi Salah,

This is equivalent:

WITH numbers AS (SELECT 1 AS a, 2 AS b) SELECT a, b, a+b AS c FROM numbers;

S

On Wed, Dec 21, 2011 at 11:39 AM, salah jubeh <s_jubeh@yahoo.com> wrote:

Hello Simon,

This question was raised up during writing a query where many columns are a result of long mathematical operations, so I thought why not to rename them and use the new names  instead of repeating the same operations

On the row level,  the value of a and b are know, so why we need a temporary table or CTE. Why I can not use it directly  as shown in the example below. why a and b can not be used as aliases for the column names ? I want to know the theoretical reason behind it?

Thanks

CREATE TABLE numbers (
    num1 integer,
    num2 integer
 );

 INSERT INTO numbers values (3,4);

 SELECT num1, num2, num1 + num2 FROM numbers

  SELECT num1 as a, num2 as b, a + b as c FROM number
  SELECT num1 as a, num2 as b, num1 + num2 as c FROM numbers

________________________________
From: Simon Tokumine <simon@vizzuality.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Wednesday, December 21, 2011 1:14 PM
Subject: Re: [GENERAL] can not use the column after rename

Hi Salah,

This is equivalent:

WITH numbers AS (SELECT 1 AS a, 2 AS b) SELECT a, b, a+b AS c FROM numbers;

S

On Wed, Dec 21, 2011 at 11:39 AM, salah jubeh <s_jubeh@yahoo.com> wrote:

Hello,

On Wed, Dec 21, 2011 at 15:24, salah jubeh <s_jubeh@yahoo.com> wrote:

Why I can not use it directly  as shown in the example below.
why a and b can not be used as aliases for the column names ? I want to know
the theoretical reason behind it?

As far as I know, that's how the SQL standard specifies it. PostgreSQL
is pretty strict about following the SQL standard. Diverging from the
standard in this aspect would cause ambiguities.

Regards,
Marti

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of salah jubeh
Sent: Wednesday, December 21, 2011 6:39 AM
To: pgsql
Subject: [GENERAL] can not use the column after rename

Hello,

Why I can not do something like this in Postgres.

SELECT 1 as a , 2 as b , a + b as c ;

Regards

In a declarative language same-level cross-references are difficult to
process (though admittedly not impossible). What if you had written:

SELECT a + b AS c, 1 as a, 2 as b

If scanned in a linear (top-to-bottom) fashion A and B would not be defined
at the time the "a + b" part was evaluated.

David J.

salah jubeh <s_jubeh@yahoo.com> writes:

On the row level,� the value of a and b are know, so why we need a temporary table or CTE. Why I can not use it directly� as shown in the example below. why a and b can not be used as aliases for the column names ? I want to know the theoretical reason behind it?

The SQL standard envisions all the expressions in a SELECT list being
computed concurrently and independently. So they can't refer to each
other. If they could, it would introduce ambiguity. Consider a table
t that provides columns x,y,z, and suppose we have

SELECT x AS z, y, y+z AS sum FROM t;

If cross-references were allowed, it would be unclear what the last "z"
is supposed to refer to.

regards, tom lane