Thread: Reference column alias for common expressions

On Tue, May 24, 2022 at 4:12 PM Wood May <asdf_pg@outlook.com> wrote:
>
> Hi,
>     Some databases (like Teradata) support the following syntax:
>
>     select col1, col2*20 as col2_1, col2_1*200 as col3_1 from your_table;
>
>     The last element in the target list can refer the second one using its alias.
>
>     This feature is similar to some programming languages (like Lisp)'s let*.

I think this is incompatible with SQL semantics.

>
>     For Postgres, it seems the only way is to write a subquery and then a new target list.

Another option is to use LATERAL subqueries, eg

select t.col1, level1.col2_1, level2.col3_1
from your_table as t
  lateral join
    (select t.col2*20 as col2_1) as level1 on true
  lateral join
    (select level1.col2_1*200 as col3_1) as level2 on true ;

>
>     Will Postgres plan to support this feature?
>
>     Thanks a lot!

Regards
 Pantelis Theodosiou

Wood May <asdf_pg@outlook.com> writes:
>     Some databases (like Teradata) support the following syntax:
>     select col1, col2*20 as col2_1, col2_1*200 as col3_1 from your_table;
>     The last element in the target list can refer the second one using its alias.
>     This feature is similar to some programming languages (like Lisp)'s let*​.
>     For Postgres, it seems the only way is to write a subquery and then a new target list.

>     Will Postgres plan to support this feature?

No.  It's flat out contrary to the SQL standard.

            regards, tom lane