Re: Reference column alias for common expressions

On Tue, May 24, 2022 at 4:12 PM Wood May <asdf_pg(at)outlook(dot)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