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